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

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

2、ve。一 MySQL 復制的基本過程如下：（各部分學習自Google，謝謝）1. Slave 上面的IO線程連接上 Master，并請求從指定日志文件的指定位置(或者從最開始的日志)之后的日志內(nèi)容;2. Master 接收到來自 Slave 的 IO 線程的請求后，通過負責復制的 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文件中，以便在下一次讀取的時候能夠清楚的高速Master“我需要從某個bin-log的哪個位置開始往后的日志內(nèi)容，請發(fā)給 我”1 / 204. Slave 的 SQL 線程檢測到 Relay Log 中新增加了內(nèi)容后，會馬上解析該 Log 文件中的內(nèi)容成為在 Master端真實執(zhí)行時候的那些可執(zhí)行的 Query 語句，并在自身執(zhí)行這些 Query。這樣，實際上就是在 Master 端和 Slave端執(zhí)行了同樣的 Qu

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

5、ELLO135!#%修改mysql的服務端口vim /etc/f主服務器同步用戶授權(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!#%' #給備份用戶相應的權(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主服務器中的數(shù)據(jù)庫osyunweidb導入到MySQL從服務器中1、導出數(shù)據(jù)庫osyunweidb備注：在導出之前可以先進入MySQL控制臺執(zhí)行下面命令flush tables with read lock;    #生產(chǎn)環(huán)境必須先鎖定。數(shù)據(jù)庫只讀鎖定命令，防止導出數(shù)據(jù)庫的時候有數(shù)據(jù)寫入。這個命令是全局讀鎖定，執(zhí)行了命令之后所有庫所有表都被鎖定只讀。一般都是用在數(shù)據(jù)庫聯(lián)機備份，這個時候數(shù)據(jù)庫的寫操作將被阻塞，讀操作順利進行。解鎖的語句也是unlock tables。mysqldump -u root -p

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

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

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

12、60;  = 10#binlog過期清理時間，根據(jù)每日生成的日志量，磁盤空間等設置過期時間。max_binlog_size         = 100M#每個日志文件大小的最大值binlog-do-db=backup #需要同步的數(shù)據(jù)庫名，如果有多個數(shù)據(jù)庫，可重復此參數(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 #進入mysql控制臺 查看主服務器，出現(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從服務器配置vi /etc/f   #編輯配置文件，在mysqld部分添加下面內(nèi)容server-id=2   #配置文件中已經(jīng)有一行server-id=1，修改其值為2，主從不能相同log-bin=mysql-bin  #啟動MySQL二進制日志系統(tǒng)，如果只做從數(shù)據(jù)庫則不起也可以，建議開啟。binlog_format &

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

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  #進入MySQL控制臺slave stop;   #停

17、止slave同步進程進入主庫，鎖定主庫表：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)下進行）start slave;    #開啟slave同步進程進入主庫，解鎖主庫標：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ù)庫服務器上：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ù)庫服務器上查看是否有新建的表和數(shù)據(jù)。常用命令：1、用戶創(chuàng)建完，賦完權(quán)限之后在備服務器上用此命令進行測試。如果能進行登錄則表明權(quán)限和連通性沒問題。mysql -u osyunweidbbak -h 192.168.21.169 -p  #測試在從服務器上登錄到主服務器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、進程狀態(tài)，主上一個BinlogDump，從上兩個一個是I/O一個是SQL進程，I/O進程負責接收更新，SQL負責寫入本地庫。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ù)的默認值是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ù)的合理值需要在實際環(huán)境中進行一下測試，時間太長，容易導致同步不及時，時間太短，則備機會頻繁連接主機。在一個已經(jīng)建立主從復制關(guān)系的系統(tǒng)里面,正常情況下,由從庫向主庫發(fā)送一個 COM_BINLOG_DUMP 命令后,主庫有新的binlog event，會向備庫發(fā)送binlog。但是由于網(wǎng)絡故障或者其他原因（如主從數(shù)據(jù)庫之間跨防火墻）導致主庫與從庫的連接斷開或者主庫長時間沒有向從庫發(fā)送binl

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

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

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

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

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

35、數(shù)據(jù)庫的偏移值必須唯一，且在1和auto_increment_increment之間（也就是說兩個主數(shù)據(jù)庫的這個設置分別是1和2）。如果按以下設置，肯定不會出現(xiàn)這個問題，但如果業(yè)務有要求，ID必須連續(xù)，那就不能設置這兩個參數(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這樣配置之后，兩臺設備在生成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主機1的配置1主機2的配置2架構(gòu)圖及原理首先，配置mysql雙主，見相關(guān)文檔。使用keepalived做高可用，當主設備發(fā)生主機故障時切換到備機。然而keepalived只能做到對網(wǎng)絡故障和keepalived本身的監(jiān)控

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

38、3秒左右，具體需要實際測試。主機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 主機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引起的同步復制故障*在binlog_format 設置為ROW格式時，并且主服務器f配置文件設置了參數(shù)：binlog_ignore_db=xxx時會出現(xiàn)此問題。將mysql的binlog_fromat 設置為MIXED格式可以解決此問題。 slave服務器是可以同步執(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配置，binlog格式化為row，跟他要了當時的語句，如下：mysql -e "create table db.tb like db.tb1" 演示：結(jié)果創(chuàng)建的表，Slave上一個都沒有，導致杯具發(fā)生。到底是什么原因引起的呢？那就是沒有使用use 庫名導致的，如果使用了，就可以記錄binlog，如圖：所以，如果想在Slave上忽略一個庫的

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

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

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

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

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

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

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

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

51、，例如建立視圖時使用了 UUID() 函數(shù)；設定主從復制模式：1234log-bin=mysql-bin#binlog_format="STATEMENT"#binlog_format="ROW"binlog_format="MIXED"也可以在運行時動態(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)點歷史悠久，技術(shù)成熟；產(chǎn)生的 binlog 文件較??；binlog 中包含了所有數(shù)據(jù)庫修改信息，可以據(jù)此來審核數(shù)據(jù)庫的安全等情況；binlog 可以用于實時的還原

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

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

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

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

57、很多原先出現(xiàn)的主鍵重復問題；常見錯誤mysql主從復制，經(jīng)常會遇到錯誤而導致slave端復制中斷，這個時候一般就需要人工干預，跳過錯誤才能繼續(xù)跳過錯誤有兩種方式：1.跳過指定數(shù)量的事務：mysql>slave stop;mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1        #跳過一個事務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)點：不需要記錄每一行的變化，減少了binlog日志量，節(jié)約了IO，提高性能。(相比row能節(jié)約多少性能與日志量，這個取決于應用的SQL情況，正常同一條記錄修改或者插

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

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

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