一、Mysql複制基本原理
二、Mysql複制中Binlog的三種格式
2.1 三種格式的介紹
2.2 Binlog格式的優缺點
2.3 Binlog基本配置
三、Mysql常見兩種複制方式
3.1 異步複制(Asynchronous Replication)
3.2 半同步複制(Semi-synchroous Replicaion)
四、提升主從複制性能的方法
五、Mysql複制遇到的一些問題
-
Mysql主庫在事務送出時會将資料變更作為Events記錄在Binlog中，Mysql主庫的sync_binlog參數(預設值為0
可參考http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_sync_binlog)控制Binlog日志重新整理到磁盤
- 主庫推送Binlog中的事件到從庫的Relay Log，之後從庫根據Relay Log重做DML操作
-
Mysql通過3個線程完成主從複制：Binlog Dump線程跑在主庫，I/O線程和SQL線程跑在從庫；
當從庫啟動複制，首先建立I/O線程連接配接到主庫，主庫随後建立Binlog Dump線程讀取資料庫事件并發給I/O線程，I/O線程擷取到事件資料後更新到從庫的Relay Log中去，之後從庫上的SQL線程讀取Relay Log中更新的資料庫事件并應用
注釋：
從庫上兩個重要檔案：
master.info
：記錄I/O線程連接配接主庫的一些參數；
relay-log.info
：記錄SQL線程應用Relay Log的一些參數
二、Mysql複制中Binlog的三種格式 2.1 三種格式的介紹
Statement (statement-based replication:SBR)：基于SQL語句級别的Binlog，每條修改資料的SQL都會儲存在Binlog裡面；
Row(RBR)：基于行級别，記錄每一行資料的變化，也就是将每行資料的變化都記錄到Binlog裡面，記錄得非常詳細，單并不記錄原始SQL；在複制過程，并不會因為存儲過程或者觸發器造成主從資料不一緻問題，但記錄的binlog大小會比Statement格式大很多,CREATE、DROP、ALTER操作隻記錄原始SQL，而不會記錄每行資料的變化到Binlog；
Mixed(MBR):混合Statement和Row模式，預設是Statement模式記錄，某些情況下會切換到Row模式，例如SQL中包含與時間、使用者相關的函數等statement無法完成主從複制的操作；
2.2 Binlog格式的優缺點
基于Statement複制(Mysql5.5預設格式):
優點：
Binlog日志量少，節約IO，和減少了主從網絡binlog傳輸量
隻記錄在master上所執行的語句的細節，以及執行語句的上下文資訊
同時，審計資料庫變的更容易
缺點：
由于此格式是記錄原始執行的SQL，保證能在slave上正确執行必須記錄每條語句的上下文資訊
部分修改資料庫時使用的函數可能出現無法複制：sleep()、last_insert_id()、 load_file()、uuid()、user()、found_rows()、sysdate()(除非啟動時—sysdate-is-now=true)
可能會導緻觸發器或者存儲過程複制導緻資料不一緻，如調用NOW()函數
INSERT…SELECT 可能會産生比RBR更多的行級鎖，例如沒有order by的insert…select
複制需要執行全表掃描(WHERE中沒有使用索引)的UPDATE時，需比row請求更多的行級鎖
對于AUTO_INCREMENT字段的InnoDB引擎表，INSERT會阻塞其他INSERT語句
注：如果statement不能保證主從正常複制,error日志會有提示：Statement may not be safe to log in statement format
基于Row複制:
隻記錄每一行資料變化的細節，不需要記錄上下文資訊
不會出現某些情況下auto_increment columns,timestamps,.triger、function、procedure無法正常複制的問題
新的row格式已經有了優化， CREATE、DROP、ALTER操作隻記錄原始SQL，而不會記錄每行資料的變化到Binlog
适用于主從複制要求強一緻性的環境
update、delete、load data local infile等頻繁更新或者***大量行時會産生大量的binlog日志，會有一定的I/O壓力，主從同步産生不必要的流量
如：UPDATE products set status=‘sold’ where product_id BETWEEN 30000 and 50000;
無法很好的進行資料庫審計
2.3 Binlog基本配置
修改配置檔案my.cnf
binlog_format=row binlog日志格式
max_binlog_size = 512M 每個日志檔案大小
binlog_cache_size=1M 二進制日志緩沖大小,uncommitted事務産生的日志寫在cache，committed的持久化到磁盤binlog裡面，此參數不是全局的，是針對session的
expire_logs_days = 3 binlog有效期
log-bin=/datas/mysql/logs/mysql-bin binlog日志目錄
relay-log=/datas/mysql/logs/relay-bin 從庫中繼日志目錄
#slave_skip_errors = all 3.1 異步複制（Asynchronous Replication）
主庫執行完Commit後，在主庫寫入Binlog日志後即可成功傳回用戶端，無需等等Binlog日志傳送給從庫
異步複制主從配置：
主 : 192.168.10.216
從 : 192.168.10.217
步驟：主從版本一緻—>主庫授權複制帳号—>確定開啟binlog及主從server_id唯一—>主庫隻讀，記錄主binlog名稱及偏移量—>拷貝主資料檔案到從相應位置—>從庫change master to —>slave start—>檢查兩個yes
1.主MySQL配置
mysql>GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.10.217' IDENTIFIED BY 'geekwolf';
mysql>FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
将主庫資料檔案拷貝到從庫對應目錄
mysql>UNLOCK TABLES; 2.從MySQL配置
mysql>CHANGE MASTER TO MASTER_HOST='192.168.10.216',MASTER_USER='rep',MASTER_PASSWORD='geekwolf',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=120;
mysql>START SLAVE;
mysql> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.216
Master_User: rep
Master_Port: 3306
Connect_Retry: 1
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 120
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 120
Relay_Log_Space: 450
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 216
Master_UUID: bd2a4c6b-d954-11e3-8c0a-0200c0a80ad8
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec) 注: 異步複制中隻要binlog不丢失即可保證資料的完整性；當主當機，從庫未收到binlog時，就會丢失資料（主磁盤正常時可以提取差異binlog在從執行），此時就需要用到半同步複制方式
3.2 半同步複制(Semi-synchroous Replicaion)
主庫每次事務成功送出時并不及時回報給前端，而是等待其中一個從庫也接收到Binlog事務并成功寫入Relay Log之後，才傳回Commit操作成功給用戶端；如此半同步就保證了事務成功送出後，至少有兩份日志記錄，一份在主庫Binlog上，另一份在從庫的Relay Log上，進而進一步保證資料完整性；半同步複制很大程度取決于主從網絡RTT（往返時延）；以插件形式存在，
半同步複制主從配置：
1.判斷是否支援動态增加插件
mysql> select @@have_dynamic_loading;
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES |
+------------------------+ 2.檢查是否存在半同步插件,分别在主從安裝
/usr/local/mysql/lib/mysql/plugin/semisync_master.so
/usr/local/mysql/lib/mysql/plugin/semisync_slave.so
主MySQL上安裝semisync_master.so:
mysql>install plugin rpl_semi_sync_master SONAME ‘semisync_master.so’
從MySQL上安裝semisync_slave.so:
mysql>install plugin rpl_semi_sync_slave SONAME ‘semisync_slave.so’
安裝後通過show plugins;檢視安裝的插件
3.分别在主從打開semi-sync(預設關閉)
主：
修改my.cnf
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=30000(毫秒) 從庫當機或網絡故障導緻binlog沒有及時傳送到從庫，此時主庫上的事務需要等待的時間；此時間内沒恢複，MySQL自動調整複制為異步複制模式
mysql> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set global rpl_semi_sync_master_timeout=30000;
Query OK, 0 rows affected (0.00 sec) 從:
rpl_semi_sync_master_enabled=1
mysql> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)
由于之前配置的複制是異步的，是以需要重新開機下從庫I/O線程(或者直接重新開機主從stop slave;start slave;)：
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.04 sec)
mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec) 4.驗證
mysql> show status like '%semi_sync%'; Rpl_semi_sync_master_clients: 值為2，表示有2個semi-sync的備庫
Rpl_semi_sync_master_net_avg_wait_time: 表示事務送出後，等待備庫響應的平均時間
Rpl_semi_sync_master_no_times: 表示有幾次從半同步切換到異步複制
Rpl_semi_sync_master_status : 值為ON，表示半同步複制處于打開狀态
Rpl_semi_sync_master_tx_avg_wait_time ：開啟Semi-sync，事務傳回需要等待的平均時間
Rpl_semi_sync_master_wait_sessions：目前有幾個線程在等備庫響應
Rpl_semi_sync_master_yes_tx : 值為1054，表示主庫有1054個事務是通過半同步複制到從庫
Rpl_semi_sync_master_no_tx : 值為0，表示目前有0個事務不是通過半同步模式同步到從庫的 從：
檢查半同步是否開啟
show status like '%semi_sync%'; 檢查複制是否正常
show slave status \G; 方案1：多級主從架構，将不同庫分開複制到不同從上
注意事項：
M2上打開log-slave-updates配置，保證M1傳送的binlog能夠被記錄在M2的RelayLog和Binlog；M2可以選擇BLACKHOLE引擎降低M2的I/O；并且Binlog日志的過濾可以在M2去做
BLACKHOLE引擎的使用測試參考: http://jroller.com/dschneller/entry/mysql_replication_using_blackhole_engine
http://blog.csdn.net/kylinbl/article/details/8903336
方案2：多線程複制（MySQL5.6+）
多線程複制是基于庫的，允許從庫并行更新，若單庫壓力大，此處的多線程複制沒有意義；從庫設定slave_parallel_workers=4表示MySQL從庫在複制時啟動4個SQL線程
MySQL5.6一下版本可以嘗試Transfer更新檔http://dinglin.iteye.com/blog/1888640
1.指定特定的資料庫或者表
replicate-do-db 告訴從伺服器限制預設資料庫(由USE所選擇)為db_name的語句的複制，指定多個庫時多次使用此參數，一次指定一個庫，不能跨資料庫更新；需要跨資料庫進行更新，使用--replicate-wild-do-table=db_name.%
比如：
如果用--replicate-do-db=sales啟動從伺服器，并且在主伺服器上執行下面的語句，UPDATE語句不會複制：
USE prices; UPDATE sales.january SET amount=amount+1000;
replicate-do-table 隻複制某個表 ，支援跨庫更新，指定多個表時多次使用此參數，一次指定一個表
replicate-ignore-db告訴從伺服器不要複制預設資料庫(由USE所選擇)為db_name的語句。要想忽略多個資料庫，應多次使用該選項，每個資料庫使用一次。如果正進行跨資料庫更新并且不想複制這些更新，不應使用該選項。應使用--replicate-wild-ignore-table=db_name.%
replicate-ignore-table 告訴從伺服器線程不要複制更新指定表的任何語句(即使該語句可能更新其它的表)。要想忽略多個表，應多次使用該選項，每個表使用一次。同--replicate-ignore-db對比，該選項可以跨資料庫進行更新
replicate-wild-do-table 告訴從伺服器線程限制複制更新的表比對指定的資料庫和表名模式的語句。模式可以包含‘%’和‘_’通配符，與LIKE模式比對操作符具有相同的含義。要指定多個表，應多次使用該選項，每個表使用一次。該選項可以跨資料庫進行更新。請讀取該選項後面的注意事項。
例如：--replicate-wild-do-table=foo%.bar%隻複制資料庫名以foo開始和表名以bar開始的表的更新。
replicate-wild-ignore-table告訴從伺服器線程不要複制表比對給出的通配符模式的語句
從庫增加(同步test庫的bench1表，忽略同步mysql庫所有表)：
replicate-wild-do-table=test.bench1
replicate-wild-ignore-table=mysql.% 2.從庫複制出錯跳過
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 3.log event entry exceeded max_allowed_packet的處理
适當增加max_allowed_packet大小
4.因主庫大量滞後binlog，啟動slave時，可能會跑滿網卡帶寬
前段時間微網誌上@zolker遇到這類問題，網友也給了很多解決辦法，趁此blog總結下
A.級聯備庫方式，避免主MySQL網卡跑滿影響
B.腳本的方式每隔幾秒(sleep)把io_thread停一會，進行緩解 （這種方法簡單、粗暴、有效,但有抖動）
C.使用facebook的patch https://github.com/facebook/mysql-5.6/commit/d3b0c7814090bded6563fee7d46d2ae41ed32a60
以上是本人在學習過程中的筆記，一碼一字敲出來的，有錯誤地方請留言，謝謝~轉載請寫明出處~