Mysql 5.6主從同步配置

標籤：tar   mysql5.6   syn   同步   tor   nlog   table   限制   指定   

主從同步，本質是利用資料庫日誌，將主庫資料複製一份到從庫，本質上是使用了資料複製技術。

本文概要

1. 主庫的基本配置
2. 從庫的基本配置
3. 完全同步的步驟
4. 注意事項
5. 工作原理

1. 主庫的基本配置

做兩件事：啟用日誌（記錄資料庫操作），賦予從庫複製許可權。配置如下：

1. 啟用日誌：

# sync_binlog=1 #預設為0，當 sync_binlog=0，當事務提交之後，MySQL不做fsync之類的磁碟同步指令重新整理binlog_cache中的資訊到磁碟，而讓Filesystem自行決定什麼時候來做同步，或者cache滿了之後才同步到磁碟。 #當每進行n次事務提交之後，MySQL將進行一次fsync之類的磁碟同步指令來將binlog_cache中的資料強制寫入磁碟。log_bin=mysql-bin #開啟日誌，並指定日誌基本名稱。log_bin_index=mysql-bin.indexlog_bin_trust_function_creators=1expire_logs_days = 3

1. 給予從庫複製許可權
   （不同的主機，配置不同的使用者名稱，以區分。如slave95,m95m96，或者採用統一使用者名稱，如下所示：）

mysql> grant replication slave on *.* to ‘slave‘@‘%‘ identified by ‘[email protected]‘;mysql> flush privileges;

2. 從庫的基本配置

做兩件事：啟用（中繼）日誌，指定從庫從主庫某個檔案（某個位置）複製日誌。

1. 啟用（中繼）日誌

#log-bin=mysql-bin #從庫用不上relay-log=slave-relay-binrelay-log-index=slave-relay-bin.indexskip-slave-start   #防止複製隨著mysql啟動而自動啟動slave-skip-errors=allrelay_log_info_repository = TABLEmaster_info_repository    = TABLErelay_log_recovery        = 1log_bin_trust_function_creators=1slave-net-timeout = 60            #預設值3600.意味著沒有得到更多資料之後slave等待時間expire_logs_days = 3read-only=1  #可選項，做讀寫分離時，可配置replicate_wild_do_table=datacenter.% #告訴從伺服器線程限制複製更新的表匹配指定的資料庫和表名模式的語句。replicate_wild_do_table=kettle.%

1. 指定從庫從主庫某個檔案（某個位置）複製日誌

mysql>CHANGE MASTER TO   MASTER_HOST=‘192.168.100.104‘,    MASTER_PORT=3306,    MASTER_USER=‘slave‘,    MASTER_PASSWORD=‘[email protected]‘,    MASTER_LOG_FILE=‘mysql-bin.000001‘,MASTER_LOG_POS=839;

3. 完全同步的步驟主庫

1. 進行鎖表，防止資料寫入
   使用命令：

mysql> flush tables with read lock;

2.備份資料

mysqldump -uroot -p -B kettle datacenter|gzip>/ddhome/data.sql.gz

3.查看master 狀態，解開鎖表功能

mysql> show master status;mysql> unlock tables; 

4.把mysql備份檔案傳到從庫機器，進行資料恢複

scp mysql.bak.sql [email protected]:/tmp/

從庫

1. 停止從庫的狀態

mysql> stop slave;

1. 匯入資料

mysql -uroot -p  </tmp/mysql.bak.sqlormysql> source /tmp/mysql.bak.sql

1. 設定從庫同步，注意該處的同步點，就是主庫show master status資訊裡的| File| Position兩項

change master to master_host = ‘10.130.254.13‘, master_user = ‘slave‘, master_port=3306, master_password=‘[email protected]‘, master_log_file = ‘mysql-bin.000104‘, master_log_pos=481224382;

1. 重新開啟從同步

mysql> start slave;

1. 查看同步狀態

mysql> show slave status\G

查看：（參考：mysql主從複製搭建中幾種log和pos詳解 - 學無涯，愈進而愈惘 - CSDN部落格）

Slave_IO_Running: YesSlave_SQL_Running: YesSeconds_Behind_Master: 0          Master_Log_File: mysql-bin.000125      Read_Master_Log_Pos: 947053504    #從庫IO線程讀取主庫日誌的位置           Relay_Log_File: slave-relay-bin.000064            Relay_Log_Pos: 947053667    #從庫IO線程寫入本地日誌    Relay_Master_Log_File: mysql-bin.000125      Exec_Master_Log_Pos: 947053504    #從庫sql線程執行到哪裡

簡單來講就是從庫先通過io線程讀取主庫的二進位檔案（Master_Log_File）和位置（Read_Master_Log_Pos）然後緩衝到本地（從程式庫伺服器）的中繼檔案（Relay_Log_File）中並記錄已經讀取到的位置（Relay_Log_Pos），再通過從庫的sql線程去讀取中繼檔案（Relay_Log_File），這個sql線程執行會記錄已經執行到了哪個檔案（Relay_Master_Log_File）和哪個位置（Exec_Master_Log_Pos）。

4. 注意事項

1. 從庫每次都要手動start slave，不能自動啟動。如果是雙主熱備時，則自動啟動;

2. 在Slave上使用 replicate_wild_do_table 和 replicate_wild_ignore_table 來解決跨庫更新的問題

5. 工作原理

1. master將改變記錄到二進位日誌(binary log)中（這些記錄叫做二進位日誌事件，binary log events）。
2. Slave將Master的日誌拷貝到自己的中繼日誌(relay log)中。
3. Slave重新執行中繼日誌中的事件並放到自己的資料庫中。

MySQL複製的缺點

MySQL的複製（replication）功能讓人且愛且恨。MySQL複製配置簡單，深受開發人員的喜歡，基於複製的讀寫分離方案也非常流行。而MySQL資料庫高可用大多也是基於複製技術，但是MySQL複製本身依然存在部分缺陷，最為主要的問題如下：

• 資料丟失問題（consistency）
• 資料同步延遲問題（delay）
• 擴充性問題（scalability）
  從MySQL 5.7的lossless semi-sync replication已經解決了資料丟失的問題，MySQL 5.7的multi-thread slave也解決了資料同步延遲的問題，MySQL 5.7的Group replication也擴充性問題。

參考文獻

• MySQL5.6複製技術(1)-原理詳解
• Mysql 5.6主從同步配置與解決方案

tips:本文屬於自己學習和實踐過程的記錄，很多圖和文字都粘貼自網上文章，沒有註明引用請包涵！如有任何問題請留言或郵件通知，我會及時回複。

Mysql 5.6主從同步配置