Mysql 服務(wù)器主從復(fù)制 主主+keepalived實(shí)現(xiàn)Mysql故障自動切換參考模板

1、目錄原理1主從同步配置2主服務(wù)器同步用戶授權(quán)2配置MySQL主服務(wù)器的f文件3備機(jī)配置：4常用命令：5雙主配置f6binlog_ignore_db引起的同步復(fù)制故障7常見錯誤11Mysql Binlog三種格式介紹及分析11原理經(jīng)過抓包分析，tcpdump -n -i eth0 -A -s0 -v host 192.168.0.253。當(dāng)從與主處于正常連接狀態(tài)時（而不是slave第一次啟動時），主發(fā)生sql操作時，是將binlog主動推送給從服務(wù)器。當(dāng)正常同步之后，如果Slave mysql 停止，如服務(wù)停止了，或者設(shè)備故障了。那么在slave重新正常后，在這期間的主的變化都會正常同步到sla

2、ve。一 MySQL 復(fù)制的基本過程如下：（各部分學(xué)習(xí)自Google，謝謝）1. Slave 上面的IO線程連接上 Master，并請求從指定日志文件的指定位置(或者從最開始的日志)之后的日志內(nèi)容;2. Master 接收到來自 Slave 的 IO 線程的請求后，通過負(fù)責(zé)復(fù)制的 IO線程根據(jù)請求信息讀取指定日志指定位置之后的日志信息，返回給 Slave 端的 IO線程。返回信息中除了日志所包含的信息之外，還包括本次返回的信息在 Master 端的 Binary Log 文件的名稱以及在 BinaryLog 中的位置;3. Slave 的 IO 線程接收到信息后，將接收到的日志內(nèi)容依次寫入到

3、Slave 端的RelayLog文件(mysql-relay-lin.xxxxxx)的最末端，并將讀取到的Master端的bin-log的文件名和位置記錄到 master-info文件中，以便在下一次讀取的時候能夠清楚的高速M(fèi)aster“我需要從某個bin-log的哪個位置開始往后的日志內(nèi)容，請發(fā)給 我”1 / 204. Slave 的 SQL 線程檢測到 Relay Log 中新增加了內(nèi)容后，會馬上解析該 Log 文件中的內(nèi)容成為在 Master端真實(shí)執(zhí)行時候的那些可執(zhí)行的 Query 語句，并在自身執(zhí)行這些 Query。這樣，實(shí)際上就是在 Master 端和 Slave端執(zhí)行了同樣的 Qu

4、ery，所以兩端的數(shù)據(jù)是完全一樣的。主從同步配置安裝：yum install mysql mysql-server#安裝cp /usr/share/mysql/my-f /etc/f #復(fù)制配置文件service mysql start#啟動chkconfig mysql on#設(shè)置開機(jī)自動啟動mysql_secure_installation#初始化數(shù)據(jù)庫，刪除test庫；禁止root遠(yuǎn)程登錄；mysql root密碼：81233744!#192.168.0.80；192.168.0.253 的mysql密碼修改為 root/A81233744同步用的賬號和密碼：HELLO_backup/H

5、ELLO135!#%修改mysql的服務(wù)端口vim /etc/f主服務(wù)器同步用戶授權(quán)CREATE DATABASE backup DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;建庫CREATE USER 'HELLO_backup''192.168.0.80' IDENTIFIED BY 'HELLO135!#%'建備份用的用戶和密碼（建用戶時填寫允許用戶備份操作的IP；在給此用戶賦權(quán)時的IP必須與此相同，否則賦不上權(quán)限）GRANT FILE,REPLICATION SLAVE ON *.*

6、 TO 'HELLO_backup''192.168.0.80' IDENTIFIED BY 'HELLO135!#%' #給備份用戶相應(yīng)的權(quán)限，REVOKE FILE,REPLICATION SLAVE ON *.* FROM 'HELLO_backup''192.168.0.232' #收回授權(quán)如果中間還通過防火墻做的靜態(tài)地址映射還需要增加防火墻外網(wǎng)口的地址和映射用的地址，否則連不上GRANT FILE,REPLICATION SLAVE ON *.* TO 'HELLO_backup''

7、;192.168.0.151' IDENTIFIED BY 'HELLO135!#%'GRANT FILE,REPLICATION SLAVE ON *.* TO 'HELLO_backup''192.168.0.155' IDENTIFIED BY 'HELLO135!#%'GRANT ALL PRIVILEGES ON *.* TO 'HELLO_backup''192.168.0.155' IDENTIFIED BY 'HELLO135!#%'flush privile

8、ges; 每次賦權(quán)后必須刷新三、把MySQL主服務(wù)器中的數(shù)據(jù)庫osyunweidb導(dǎo)入到MySQL從服務(wù)器中1、導(dǎo)出數(shù)據(jù)庫osyunweidb備注：在導(dǎo)出之前可以先進(jìn)入MySQL控制臺執(zhí)行下面命令flush tables with read lock;    #生產(chǎn)環(huán)境必須先鎖定。數(shù)據(jù)庫只讀鎖定命令，防止導(dǎo)出數(shù)據(jù)庫的時候有數(shù)據(jù)寫入。這個命令是全局讀鎖定，執(zhí)行了命令之后所有庫所有表都被鎖定只讀。一般都是用在數(shù)據(jù)庫聯(lián)機(jī)備份，這個時候數(shù)據(jù)庫的寫操作將被阻塞，讀操作順利進(jìn)行。解鎖的語句也是unlock tables。mysqldump -u root -p

9、60;osyunweidb > /home/osyunweidbbak.sql    #在MySQL主服務(wù)器進(jìn)行操作，導(dǎo)出數(shù)據(jù)庫osyunweidb到/home/osyunweidbbak.sql unlock tables;   #解除鎖定2、導(dǎo)入數(shù)據(jù)庫到MySQL從服務(wù)器mysql  -u root -p  #進(jìn)入從服務(wù)器MySQL控制臺create database osyunweidb;   #創(chuàng)建數(shù)據(jù)庫use osyunweidb

10、    #進(jìn)入數(shù)據(jù)庫source  /home/osyunweidbbak.sql  #導(dǎo)入備份文件到數(shù)據(jù)庫mysql -u osyunweidbbak -h 192.168.21.169 -p  #測試在從服務(wù)器上登錄到主服務(wù)器MySQL主服務(wù)器配置use backup; 將backup庫設(shè)置為當(dāng)前庫create table mytest (username varchar(20),password varchar(20); 創(chuàng)建一個測試用表vi /etc/f #編輯配置文件，在my

11、sqld部分添加下面內(nèi)容server-id=1 #設(shè)置服務(wù)器id，為1表示主服務(wù)器，注意：如果原來的配置文件中已經(jīng)有這一行，就不用再添加了。log-bin=mysql-bin #啟動MySQL二進(jìn)制日志系統(tǒng)，注意：如果原來的配置文件中已經(jīng)有這一行，就不用再添加了。binlog_format           = MIXED #建議使用MIXED格式。使用show variables like 'binlog_format'查看；expire_logs_days 

12、60;  = 10#binlog過期清理時間，根據(jù)每日生成的日志量，磁盤空間等設(shè)置過期時間。max_binlog_size         = 100M#每個日志文件大小的最大值binlog-do-db=backup #需要同步的數(shù)據(jù)庫名，如果有多個數(shù)據(jù)庫，可重復(fù)此參數(shù)，每個數(shù)據(jù)庫一行binlog-ignore-db=mysql #不同步mysql數(shù)據(jù)庫binlog-ignore-db=information_schemabinlog-ignore-db=performance_schemase

13、rvice mysql restart #重啟MySQLmysql -u root -p #進(jìn)入mysql控制臺 查看主服務(wù)器，出現(xiàn)以下類似信息mysql> show master status; +-+-+-+-+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-+-+-+-+| mysql-bin.000001 | 106 | backup | |+-+-+-+-+1 row in set (0.00 sec)查看server-id：mysql> show variables like 'server

14、_id' +-+-+| Variable_name | Value |+-+-+| server_id | 1 |+-+-+1 row in set (0.00 sec)Mysql從服務(wù)器配置vi /etc/f   #編輯配置文件，在mysqld部分添加下面內(nèi)容server-id=2   #配置文件中已經(jīng)有一行server-id=1，修改其值為2，主從不能相同log-bin=mysql-bin  #啟動MySQL二進(jìn)制日志系統(tǒng)，如果只做從數(shù)據(jù)庫則不起也可以，建議開啟。binlog_format &

15、#160;= MIXED #建議使用MIXED格式。使用show variables like 'binlog_format'查看；expire_logs_days    = 10#binlog過期清理時間，根據(jù)每日生成的日志量，磁盤空間等設(shè)置過期時間。max_binlog_size  = 100M#每個日志文件大小的最大值read-only = 1#設(shè)置為只讀，以免被誤寫入而導(dǎo)致主從不同步replicate-do-db=backup   #需要同步的數(shù)據(jù)庫名，如果有多個數(shù)據(jù)庫，可重復(fù)此參

16、數(shù)，每個數(shù)據(jù)庫一行replicate-ignore-db=mysql   #不同步mysql系統(tǒng)數(shù)據(jù)庫:wq!    #保存退出service mysql restart   #重啟MySQL注意：MySQL 5.1.7版本之后，已經(jīng)不支持把master配置屬性寫入f配置文件中了，配置文件中只需要把同步的數(shù)據(jù)庫和要忽略的數(shù)據(jù)庫寫入即可。mysql  -u root -p  #進(jìn)入MySQL控制臺slave stop;   #停

17、止slave同步進(jìn)程進(jìn)入主庫，鎖定主庫表：flush tables with read lock; #生產(chǎn)環(huán)境必須先鎖定。change master to master_host='192.168.0.155', MASTER_PORT=3306,master_user='HELLO_backup',master_password='HELLO135!#%',master_log_file='mysql-bin.000001' ,master_log_pos=106;   #執(zhí)行同步語句（執(zhí)

18、行同步必須在mysql> stop slave; 的狀態(tài)下進(jìn)行）start slave;    #開啟slave同步進(jìn)程進(jìn)入主庫，解鎖主庫標(biāo)：unlock tables;#解鎖。SHOW SLAVE STATUSG   #查看slave同步信息，出現(xiàn)以下內(nèi)容* 1. row *               Slave_IO_State: Waiting for maste

19、r to send event                  Master_Host: 192.168.21.169                  Master_User: osyunweidbbak  

20、0;               Master_Port: 3306                Connect_Retry: 60             

21、 Master_Log_File: mysql-bin.000019          Read_Master_Log_Pos: 7131               Relay_Log_File: MySQLSlave-relay-bin.000002       &

22、#160;        Relay_Log_Pos: 253        Relay_Master_Log_File: mysql-bin.000019             Slave_IO_Running: Yes       &

23、#160;    Slave_SQL_Running: Yes              Replicate_Do_DB: osyunweidb          Replicate_Ignore_DB: mysql        

24、0;  Replicate_Do_Table:       Replicate_Ignore_Table:1 row in set (0.00 sec)測試：在主備數(shù)據(jù)庫服務(wù)器上：CREATE DATABASE backup DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;建庫create table cc(id int auto_increment,name varchar(20),primary key(id);建表insert into cc (

25、name) values('Mr.chai');插數(shù)據(jù)在備數(shù)據(jù)庫服務(wù)器上查看是否有新建的表和數(shù)據(jù)。常用命令：1、用戶創(chuàng)建完，賦完權(quán)限之后在備服務(wù)器上用此命令進(jìn)行測試。如果能進(jìn)行登錄則表明權(quán)限和連通性沒問題。mysql -u osyunweidbbak -h 192.168.21.169 -p  #測試在從服務(wù)器上登錄到主服務(wù)器1、查看用戶及權(quán)限SELECT DISTINCT CONCAT('User: ''',user,'''''',host,''

26、9;') AS query FROM mysql.user;Show grants for 'HELLO_backup''10.34.34.232'允許root用戶從192.168.0.253訪問mysql>GRANT ALL PRIVILEGES ON *.* TO 'root''192.168.0.253' IDENTIFIED BY 'A81233744'mysql> flush privileges; 修改權(quán)限之后需要執(zhí)行此句以使之生效。1、手動同步主數(shù)據(jù)庫；必須在slave stop的

27、狀態(tài)下；mysql>change master to master_host='192.168.0.155',master_user='HELLO_backup',master_password='HELLO135!#%',master_log_file='mysql-bin.000002' ,master_log_pos=2394;2、查看slave狀態(tài)mysql> show slave statusG; 3、手動從主數(shù)據(jù)庫下載mysql> load data from master;4、在主備上可以查看各自的

28、進(jìn)程狀態(tài)，主上一個BinlogDump，從上兩個一個是I/O一個是SQL進(jìn)程，I/O進(jìn)程負(fù)責(zé)接收更新，SQL負(fù)責(zé)寫入本地庫。show processlist;  5、查看參數(shù)配置show variables like 'slave%'6、查看某用戶權(quán)限Show grants for 'HELLO_backup''192.168.0.232'7、修改配置文件f，在mysqld下添加slave_net_timeout = 600；此參數(shù)的默認(rèn)值是3600（秒，1小時），是指 slave 端（備數(shù)據(jù)庫）的 I/O 線程處于 “waiting

29、for master to send event”狀態(tài)，如果這個等待狀態(tài)超過 slave_net_timeout 時間，就會觸發(fā)重連 master 的動作。slave_net_timeout = 600 此參數(shù)的合理值需要在實(shí)際環(huán)境中進(jìn)行一下測試，時間太長，容易導(dǎo)致同步不及時，時間太短，則備機(jī)會頻繁連接主機(jī)。在一個已經(jīng)建立主從復(fù)制關(guān)系的系統(tǒng)里面,正常情況下,由從庫向主庫發(fā)送一個 COM_BINLOG_DUMP 命令后,主庫有新的binlog event，會向備庫發(fā)送binlog。但是由于網(wǎng)絡(luò)故障或者其他原因（如主從數(shù)據(jù)庫之間跨防火墻）導(dǎo)致主庫與從庫的連接斷開或者主庫長時間沒有向從庫發(fā)送binl

30、og。例如該例子中數(shù)據(jù)庫集群 10s 左右還沒有寫入的情況，超過slave_net_timeout設(shè)置的值，從庫會向主庫發(fā)起重連請求。5.6 版本slave 發(fā)起重連請求時，MySQL都會判斷有沒有用明文的用戶名密碼,如果有則發(fā)出上述信息到error.log。8、-logs-slave-updates  參數(shù)這個是在f文件配置的 通常情況，從服務(wù)器從主服務(wù)器接收到的更新不記入它的二進(jìn)制日志。該選項(xiàng)告訴從服務(wù)器將其SQL線程執(zhí)行的更新記入到從服務(wù)器自己的二進(jìn)制日志。為了使該 選項(xiàng)生效，還必須用-logs-bin選項(xiàng)啟動從服務(wù)器以啟用二進(jìn)制日志。如果想要應(yīng)用鏈?zhǔn)綇?fù)制服務(wù)器，應(yīng)使

31、用-logs-slave- updates。例如，可能你想要這樣設(shè)置： A -> B -> C 也就是說，A為從服務(wù)器B的主服務(wù)器，B為從服務(wù)器C的主服務(wù)器。為了能工作，B必須既為主服務(wù)器又為從服務(wù)器。你必須用-logs-bin啟動A和B以啟用二進(jìn)制日志，并且用-logs-slave-updates選項(xiàng)啟動B。 在f文件設(shè)置此選項(xiàng)log_slave_updates=1當(dāng)然在這種機(jī)制下可能有的同學(xué)會存在這么個問題：如果a->b b->a 這樣的雙master架構(gòu)下，a,b都打開log_slave_updates選項(xiàng)會不會出現(xiàn)無限循環(huán)的狀態(tài)。m

32、ysql已經(jīng)考濾到了這個問題，每條bin-log都會記錄執(zhí)行語句的源server_id.當(dāng)slave讀到語句的server_id等于本身的ID的時候，不會執(zhí)行，所以我們不用擔(dān)心a,b會不會無限循環(huán)下去?；谝陨线@種情況，mysql的replication集群將更加靈活，你如果需要可以做成各種各樣的鏈?zhǔn)綇?fù)制。比如 a->b b->a b中設(shè)置log_slave_updates后還可以b->c. 這樣a,c中的數(shù)據(jù)也是一致的。雙主配置f1. log_slave_updates = 1    #添加(將復(fù)制事件寫入binl

33、og,一臺服務(wù)器既做主庫又做從庫此選項(xiàng)必須要開啟) 2. replicate-same-server-id=0 #添加(防止MySQL循環(huán)更新) 3. relay_log_recovery = 1   #添加(MySQLrelay_log的自動修復(fù)功能) *從MySQL5.5.X版本才開始支持，增加了relay_log_recovery參數(shù)，這個參數(shù)的作用是：當(dāng)slave從庫宕機(jī)后，假如relay-log損壞了，導(dǎo)致一部分中繼日志沒有處理，則自動放棄所有未執(zhí)行的relay-log，并且重新從master上

34、獲取日志，這樣就保證了relay-log的完整性。默認(rèn)情況下該功能是關(guān)閉的，將relay_log_recovery的值設(shè)置為 1時，可在slave從庫上開啟該功能，建議開啟。4. server-id：數(shù)據(jù)庫標(biāo)識，每個數(shù)據(jù)庫標(biāo)識必須唯一； 5. auto_increment_increment=2 ：這是循環(huán)鏡像里最重要的參數(shù)之一，表示自動增量為2，就是指字段一次遞增多少，這將允許最多2臺數(shù)據(jù)庫加入這個循環(huán)鏡像的陣列，而自動遞增字段不會重復(fù)。當(dāng)然如果有10臺主可以將這個值設(shè)成10。6. auto_increment_offset=1 ：這是循環(huán)鏡像里最重要的參數(shù)之一，表示自增字段的起始值，每個主

35、數(shù)據(jù)庫的偏移值必須唯一，且在1和auto_increment_increment之間（也就是說兩個主數(shù)據(jù)庫的這個設(shè)置分別是1和2）。如果按以下設(shè)置，肯定不會出現(xiàn)這個問題，但如果業(yè)務(wù)有要求，ID必須連續(xù)，那就不能設(shè)置這兩個參數(shù)了：主1： 遞增初始值是1，一次遞增2。也就是值將是1、3、5.。auto-increment-increment=2 auto-increment-offset=1 主2： 遞增初始值是2，一次遞增2。也就是值將是2、4、6.。auto-increment-increment=2 auto-increment-offset

36、=2這樣配置之后，兩臺設(shè)備在生成auto_increment的列時就是主1是1、3、5、7奇數(shù)；主2是2、4、6、8偶數(shù)。注意，生成的值不一定連續(xù)，最后這個字段有可能是，1、2、3、4、5、7、8、10；邏輯是這樣，假如主1 執(zhí)行insert 3條記錄，則這個字段是1、3、5，主2再執(zhí)行insert的時候，是從6開始，6、8、10。然后主1再11、13、15。目錄架構(gòu)圖及原理1主機(jī)1的配置1主機(jī)2的配置2架構(gòu)圖及原理首先，配置mysql雙主，見相關(guān)文檔。使用keepalived做高可用，當(dāng)主設(shè)備發(fā)生主機(jī)故障時切換到備機(jī)。然而keepalived只能做到對網(wǎng)絡(luò)故障和keepalived本身的監(jiān)控

37、，即當(dāng)出現(xiàn)網(wǎng)絡(luò)故障或者keepalived本身出現(xiàn)問題時，進(jìn)行切換。但是這些還不夠，我們還需要監(jiān)控keepalived所在服務(wù)器上的mysql進(jìn)程，根據(jù)mysql進(jìn)程的運(yùn)行狀態(tài)決定是否需要進(jìn)行主備切換。這個時候，我們可以通過編寫腳本對業(yè)務(wù)進(jìn)程進(jìn)行檢測監(jiān)控。當(dāng)然我們可以通過添加更多腳本監(jiān)控所有需要監(jiān)控的點(diǎn)，觸發(fā)主備切換。*注意*這里兩個主機(jī)都沒有配置nopreempt，如果配置了nopreempt就必須在監(jiān)控腳本中kill掉keepalived 否則不會發(fā)生切換。搶占模式有時候由于交換機(jī)的arp保持時間，發(fā)生切換的時候，可能會有幾秒鐘的IP不通的情況發(fā)生（一般2秒左右），對于數(shù)據(jù)庫的操作可能會有

38、3秒左右，具體需要實(shí)際測試。主機(jī)1的配置! Configuration File for keepalivedglobal_defs notification_emailcycwll notification_email_from localhost smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id LVS_DEVEL_232vrrp_script chk_mysql script "killall -0 mysqld" interval 1 weight -15vrrp_instanc

39、e VI_1 state BACKUP interface ens33 virtual_router_id 81 priority 100 advert_int 1 authentication auth_type PASS auth_pass 123qwe virtual_ipaddress 10.34.34.239/24 主機(jī)2的配置! Configuration File for keepalivedglobal_defs notification_emailcycwll notification_email_from localhost smtp_server

40、 127.0.0.1 smtp_connect_timeout 30 router_id LVS_DEVEL_233vrrp_script chk_mysql script "killall -0 mysqld" interval 1 weight -15vrrp_instance VI_1 state BACKUP interface ens33 virtual_router_id 81 priority 90 advert_int 1 authentication auth_type PASS auth_pass 123qwe virtual_ipaddress 10.

41、34.34.239/24 binlog_ignore_db引起的同步復(fù)制故障*在binlog_format 設(shè)置為ROW格式時，并且主服務(wù)器f配置文件設(shè)置了參數(shù)：binlog_ignore_db=xxx時會出現(xiàn)此問題。將mysql的binlog_fromat 設(shè)置為MIXED格式可以解決此問題。 slave服務(wù)器是可以同步執(zhí)行-e 的操作的。mysql -u root -p -e "insert into backup.test values(11)"關(guān)于MySQL的 binlog_format 的幾中類型詳見本文最后的介紹(Mixed,Statement,Row)今天一個

42、同事跟我說了一個問題，"mysql master使用了binlog_ignore_db一個庫以后，使用mysql -e 執(zhí)行的所有語句就不寫binlog了？"詢問了他的情況，他是想在主從復(fù)制時，有一個庫不復(fù)制，查了他的f配置，binlog格式化為row，跟他要了當(dāng)時的語句，如下：mysql -e "create table db.tb like db.tb1" 演示：結(jié)果創(chuàng)建的表，Slave上一個都沒有，導(dǎo)致杯具發(fā)生。到底是什么原因引起的呢？那就是沒有使用use 庫名導(dǎo)致的，如果使用了，就可以記錄binlog，如圖：所以，如果想在Slave上忽略一個庫的

43、復(fù)制，最好不要用binlog_ignore_db這個參數(shù)，使用replicate-ignore-db = yourdb，取代之。MySQL binlog_format (Mixed,Statement,Row)MySQL 5.5 中對于二進(jìn)制日志 (binlog) 有 3 種不同的格式可選：Mixed,Statement,Row，默認(rèn)格式是 Statement。總結(jié)一下這三種格式日志的優(yōu)缺點(diǎn)。MySQL Replication 復(fù)制可以是基于一條語句 (Statement Level) ，也可以是基于一條記錄 (Row Level)，可以在 MySQL 的配置參數(shù)中設(shè)定這個復(fù)制級別，不同復(fù)制級

44、別的設(shè)置會影響到 Master 端的 bin-log 日志格式。1. Row日志中會記錄成每一行數(shù)據(jù)被修改的形式，然后在 slave 端再對相同的數(shù)據(jù)進(jìn)行修改。優(yōu)點(diǎn)：在 row 模式下，bin-log 中可以不記錄執(zhí)行的 SQL 語句的上下文相關(guān)的信息，僅僅只需要記錄那一條記錄被修改了，修改成什么樣了。所以 row 的日志內(nèi)容會非常清楚的記錄下每一行數(shù)據(jù)修改的細(xì)節(jié)，非常容易理解。而且不會出現(xiàn)某些特定情況下的存儲過程或 function ，以及 trigger 的調(diào)用和觸發(fā)無法被正確復(fù)制的問題。缺點(diǎn)：在 row 模式下，所有的執(zhí)行的語句當(dāng)記錄到日志中的時候，都將以每行記錄的修改來記錄，這樣可能會

45、產(chǎn)生大量的日志內(nèi)容，比如有這樣一條 update 語句：1UPDATE product SET owner_member_id = 'b' WHERE owner_member_id = 'a'執(zhí)行之后，日志中記錄的不是這條 update 語句所對應(yīng)的事件 (MySQL 以事件的形式來記錄 bin-log 日志) ，而是這條語句所更新的每一條記錄的變化情況，這樣就記錄成很多條記錄被更新的很多個事件。自然，bin-log 日志的量就會很大。尤其是當(dāng)執(zhí)行 alter table 之類的語句的時候，產(chǎn)生的日志量是驚人的。因?yàn)?MySQL 對于 alter table

46、之類的表結(jié)構(gòu)變更語句的處理方式是整個表的每一條記錄都需要變動，實(shí)際上就是重建了整個表。那么該表的每一條記錄都會被記錄到日志中。2. Statement每一條會修改數(shù)據(jù)的 SQL 都會記錄到 master 的 bin-log 中。slave 在復(fù)制的時候 SQL 進(jìn)程會解析成和原來 master 端執(zhí)行過的相同的 SQL 再次執(zhí)行。優(yōu)點(diǎn)：在 statement 模式下，首先就是解決了 row 模式的缺點(diǎn)，不需要記錄每一行數(shù)據(jù)的變化，減少了 bin-log 日志量，節(jié)省 I/O 以及存儲資源，提高性能。因?yàn)樗恍枰涗浽?master 上所執(zhí)行的語句的細(xì)節(jié)，以及執(zhí)行語句時候的上下文的信息。缺點(diǎn)：在

47、 statement 模式下，由于他是記錄的執(zhí)行語句，所以，為了讓這些語句在 slave 端也能正確執(zhí)行，那么他還必須記錄每條語句在執(zhí)行的時候的一些相關(guān)信息，也就是上下文信息，以保證所有語句在 slave 端杯執(zhí)行的時候能夠得到和在 master 端執(zhí)行時候相同的結(jié)果。另外就是，由于 MySQL 現(xiàn)在發(fā)展比較快，很多的新功能不斷的加入，使 MySQL 的復(fù)制遇到了不小的挑戰(zhàn)，自然復(fù)制的時候涉及到越復(fù)雜的內(nèi)容，bug 也就越容易出現(xiàn)。在 statement 中，目前已經(jīng)發(fā)現(xiàn)的就有不少情況會造成 MySQL 的復(fù)制出現(xiàn)問題，主要是修改數(shù)據(jù)的時候使用了某些特定的函數(shù)或者功能的時候會出現(xiàn)，比如：sle

48、ep() 函數(shù)在有些版本中就不能被正確復(fù)制，在存儲過程中使用了 last_insert_id() 函數(shù)，可能會使 slave 和 master 上得到不一致的 id 等等。由于 row 是基于每一行來記錄的變化，所以不會出現(xiàn)類似的問題。3. Mixed從官方文檔中看到，之前的 MySQL 一直都只有基于 statement 的復(fù)制模式，直到 5.1.5 版本的 MySQL 才開始支持 row 復(fù)制。從 5.0 開始，MySQL 的復(fù)制已經(jīng)解決了大量老版本中出現(xiàn)的無法正確復(fù)制的問題。但是由于存儲過程的出現(xiàn)，給 MySQL Replication 又帶來了更大的新挑戰(zhàn)。另外，看到官方文檔說，從 5

49、.1.8 版本開始，MySQL 提供了除 Statement 和 Row 之外的第三種復(fù)制模式：Mixed，實(shí)際上就是前兩種模式的結(jié)合。在 Mixed 模式下，MySQL 會根據(jù)執(zhí)行的每一條具體的 SQL 語句來區(qū)分對待記錄的日志形式，也就是在 statement 和 row 之間選擇一種。新版本中的 statment 還是和以前一樣，僅僅記錄執(zhí)行的語句。而新版本的 MySQL 中對 row 模式也被做了優(yōu)化，并不是所有的修改都會以 row 模式來記錄，比如遇到表結(jié)構(gòu)變更的時候就會以 statement 模式來記錄，如果 SQL 語句確實(shí)就是 update 或者 delete 等修改數(shù)據(jù)的語句

50、，那么還是會記錄所有行的變更。其他參考信息除以下幾種情況外，在運(yùn)行時可以動態(tài)改變 binlog 的格式：. 存儲流程或者觸發(fā)器中間；. 啟用了 NDB；. 當(dāng)前會話使用 row 模式，并且已打開了臨時表；如果 binlog 采用了 Mixed 模式，那么在以下幾種情況下會自動將 binlog 的模式由 statement 模式變?yōu)?row 模式：. 當(dāng) DML 語句更新一個 NDB 表時；. 當(dāng)函數(shù)中包含 UUID() 時；. 2 個及以上包含 AUTO_INCREMENT 字段的表被更新時；. 執(zhí)行 INSERT DELAYED 語句時；. 用 UDF 時；. 視圖中必須要求運(yùn)用 row 時

51、，例如建立視圖時使用了 UUID() 函數(shù)；設(shè)定主從復(fù)制模式：1234log-bin=mysql-bin#binlog_format="STATEMENT"#binlog_format="ROW"binlog_format="MIXED"也可以在運(yùn)行時動態(tài)修改 binlog 的格式。例如：123mysql> SET SESSION binlog_format = 'STATEMENT'mysql> SET SESSION binlog_format = 'ROW'mysql> SET

52、SESSION binlog_format = 'MIXED'456mysql> SET GLOBAL binlog_format = 'STATEMENT'mysql> SET GLOBAL binlog_format = 'ROW'mysql> SET GLOBAL binlog_format = 'MIXED'兩種模式的對比：Statement 優(yōu)點(diǎn)歷史悠久，技術(shù)成熟；產(chǎn)生的 binlog 文件較??；binlog 中包含了所有數(shù)據(jù)庫修改信息，可以據(jù)此來審核數(shù)據(jù)庫的安全等情況；binlog 可以用于實(shí)時的還原

53、，而不僅僅用于復(fù)制；主從版本可以不一樣，從服務(wù)器版本可以比主服務(wù)器版本高；Statement 缺點(diǎn)：不是所有的 UPDATE 語句都能被復(fù)制，尤其是包含不確定操作的時候；調(diào)用具有不確定因素的 UDF 時復(fù)制也可能出現(xiàn)問題；運(yùn)用以下函數(shù)的語句也不能被復(fù)制：* LOAD_FILE()* UUID()* USER()* FOUND_ROWS()* SYSDATE() (除非啟動時啟用了 sysdate-is-now 選項(xiàng))INSERT SELECT 會產(chǎn)生比 RBR 更多的行級鎖；復(fù)制須要執(zhí)行全表掃描 (WHERE 語句中沒有運(yùn)用到索引) 的 UPDATE 時，須要比 row 請求更多的行級鎖；對于

54、有 AUTO_INCREMENT 字段的 InnoDB 表而言，INSERT 語句會阻塞其他 INSERT 語句；對于一些復(fù)雜的語句，在從服務(wù)器上的耗資源情況會更嚴(yán)重，而 row 模式下，只會對那個發(fā)生變化的記錄產(chǎn)生影響；存儲函數(shù)(不是存儲流程 )在被調(diào)用的同時也會執(zhí)行一次 NOW() 函數(shù)，這個可以說是壞事也可能是好事；確定了的 UDF 也須要在從服務(wù)器上執(zhí)行；數(shù)據(jù)表必須幾乎和主服務(wù)器保持一致才行，否則可能會導(dǎo)致復(fù)制出錯；執(zhí)行復(fù)雜語句如果出錯的話，會消耗更多資源；Row 優(yōu)點(diǎn)任何情況都可以被復(fù)制，這對復(fù)制來說是最安全可靠的；和其他大多數(shù)數(shù)據(jù)庫系統(tǒng)的復(fù)制技能一樣；多數(shù)情況下，從服務(wù)器上的表如果

55、有主鍵的話，復(fù)制就會快了很多；復(fù)制以下幾種語句時的行鎖更少：* INSERT SELECT* 包含 AUTO_INCREMENT 字段的 INSERT* 沒有附帶條件或者并沒有修改很多記錄的 UPDATE 或 DELETE 語句執(zhí)行 INSERT，UPDATE，DELETE 語句時鎖更少；從服務(wù)器上采用多線程來執(zhí)行復(fù)制成為可能；Row 缺點(diǎn)生成的 binlog 日志體積大了很多；復(fù)雜的回滾時 binlog 中會包含大量的數(shù)據(jù)；主服務(wù)器上執(zhí)行 UPDATE 語句時，所有發(fā)生變化的記錄都會寫到 binlog 中，而 statement 只會寫一次，這會導(dǎo)致頻繁發(fā)生 binlog 的寫并發(fā)請求；UD

56、F 產(chǎn)生的大 BLOB 值會導(dǎo)致復(fù)制變慢；不能從 binlog 中看到都復(fù)制了寫什么語句(加密過的)；當(dāng)在非事務(wù)表上執(zhí)行一段堆積的 SQL 語句時，最好采用 statement 模式，否則很容易導(dǎo)致主從服務(wù)器的數(shù)據(jù)不一致情況發(fā)生；另外，針對系統(tǒng)庫 MySQL 里面的表發(fā)生變化時的處理準(zhǔn)則如下：如果是采用 INSERT，UPDATE，DELETE 直接操作表的情況，則日志格式根據(jù) binlog_format 的設(shè)定而記錄；如果是采用 GRANT，REVOKE，SET PASSWORD 等管理語句來做的話，那么無論如何都要使用 statement 模式記錄；使用 statement 模式后，能處理

57、很多原先出現(xiàn)的主鍵重復(fù)問題；常見錯誤mysql主從復(fù)制，經(jīng)常會遇到錯誤而導(dǎo)致slave端復(fù)制中斷，這個時候一般就需要人工干預(yù)，跳過錯誤才能繼續(xù)跳過錯誤有兩種方式：1.跳過指定數(shù)量的事務(wù)：mysql>slave stop;mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1        #跳過一個事務(wù)mysql>slave start2.修改mysql的配置文件，通過slave_skip_errors參數(shù)來跳所有錯誤或指定類型的錯誤vi /etc/fmysqld#slave-skip-errors=10

58、62,1053,1146 #跳過指定error no類型的錯誤#slave-skip-errors=all #跳過所有錯誤Mysql Binlog三種格式介紹及分析一Mysql Binlog格式介紹      Mysql binlog日志有三種格式，分別為Statement,MiXED,以及ROW！1.Statement：每一條會修改數(shù)據(jù)的sql都會記錄在binlog中。優(yōu)點(diǎn)：不需要記錄每一行的變化，減少了binlog日志量，節(jié)約了IO，提高性能。(相比row能節(jié)約多少性能與日志量，這個取決于應(yīng)用的SQL情況，正常同一條記錄修改或者插

59、入row格式所產(chǎn)生的日志量還小于Statement產(chǎn)生的日志量，但是考慮到如果帶條件的update操作，以及整表刪除，alter表等操作，ROW格式會產(chǎn)生大量日志，因此在考慮是否使用ROW格式日志時應(yīng)該跟據(jù)應(yīng)用的實(shí)際情況，其所產(chǎn)生的日志量會增加多少，以及帶來的IO性能問題。)缺點(diǎn)：由于記錄的只是執(zhí)行語句，為了這些語句能在slave上正確運(yùn)行，因此還必須記錄每條語句在執(zhí)行的時候的一些相關(guān)信息，以保證所有語句能在slave得到和在master端執(zhí)行時候相同 的結(jié)果。另外mysql 的復(fù)制,像一些特定函數(shù)功能，slave可與master上要保持一致會有很多相關(guān)問題(如sleep()函數(shù)， last_

60、insert_id()，以及user-defined functions(udf)會出現(xiàn)問題).使用以下函數(shù)的語句也無法被復(fù)制：* LOAD_FILE()* UUID()* USER()* FOUND_ROWS()* SYSDATE() (除非啟動時啟用了 -sysdate-is-now 選項(xiàng))同時在INSERT .SELECT 會產(chǎn)生比 RBR 更多的行級鎖2.Row:不記錄sql語句上下文相關(guān)信息，僅保存哪條記錄被修改。優(yōu)點(diǎn)： binlog中可以不記錄執(zhí)行的sql語句的上下文相關(guān)的信息，僅需要記錄那一條記錄被修改成什么了。所以rowlevel的日志內(nèi)容會非常清楚的記錄下每一行數(shù)據(jù)

61、修改的細(xì)節(jié)。而且不會出現(xiàn)某些特定情況下的存儲過程，或function，以及trigger的調(diào)用和觸發(fā)無法被正確復(fù)制的問題缺點(diǎn):所有的執(zhí)行的語句當(dāng)記錄到日志中的時候，都將以每行記錄的修改來記錄，這樣可能會產(chǎn)生大量的日志內(nèi)容,比如一條update語句，修改多條記錄，則binlog中每一條修改都會有記錄，這樣造成binlog日志量會很大，特別是當(dāng)執(zhí)行alter table之類的語句的時候，由于表結(jié)構(gòu)修改，每條記錄都發(fā)生改變，那么該表每一條記錄都會記錄到日志中。3.Mixedlevel: 是以上兩種level的混合使用，一般的語句修改使用statment格式保存binlog，如一些函數(shù)，statement無法完成主從復(fù)制的操作，則采用row格式保存binlog,MySQL會根據(jù)執(zhí)行的每一條具體的sql語句來區(qū)分對待記錄的日志形式，也就是在Statement和Row之間選擇一種.新版本的MySQL中隊(duì)row level模式也被做了優(yōu)化，并不是所有的修改都會以row level來記錄，像遇到表結(jié)構(gòu)變更的時候就會以statement模式來記錄。至于update或者delete等修改數(shù)據(jù)的語句，還是會記錄