[實操筆記]MySQL主從同步功能實現

標籤：timeout   寫的不好   中繼   不同的   sqlt   dump   basedir   not   targe   

寫在前邊：

這兩天來了個需求，配置部署兩台伺服器的MySQL資料同步，折騰了兩天查了很多相關資料，一直連不上，後來發現其實是資料庫授權的ip有問題，我們用的伺服器是機房中的虛擬機器加上反向 Proxy出來的，坑的不行。看了好多部落格，寫的怎麼說呢，寫的好的是太好了太詳細了；寫的不好的，配置什麼的都講的不清楚，剛接觸這塊的時候不曉得原理，一味的複製粘貼，後來看到有個博主寫的好文，瞬間醍醐灌頂，也有了自己的思路，我就簡單的記錄下操作步驟和一些細節的注釋，原理就直接搬運了，原理圖也畫了份，就不獻醜了，有寫錯的地方，還望各位大佬不吝賜教，在下感激不盡！

配置MySQL的主從同步有什麼好處？

　1--資料分布 (Data distribution )
    2--Server Load Balancer(load balancing)
    3--資料備份(Backups) ，保證資料安全（最主要的作用）
    4--高可用性和容錯行(High availability and failover)
    5--實現讀寫分離，緩解資料庫壓力

MySQL主從同步實現原理（參考博文點這裡）：

master伺服器將資料的改變記錄二進位binlog日誌，當master上的資料發生改變時，則將其改變寫入二進位日誌中；salve伺服器會在一定時間間隔內對master二進位日誌進行探測其是否發生改變，如果發生改變，則開始一個I/OThread請求master二進位事件，同時主節點為每個I/O線程啟動一個dump線程，用於向其發送二進位事件，並儲存至從節點本地的中繼日誌中，從節點將啟動SQL線程從中繼日誌中讀取二進位日誌，在本地重放，使得其資料和主節點的保持一致，最後I/OThread和SQLThread將進入睡眠狀態，等待下一次被喚醒。
注意幾點：
     1--master將動作陳述式記錄到binlog日誌中，然後授予slave遠端連線的許可權（master一定要開啟binlog二進位日誌功能；通常為了資料安全考慮，slave也開啟binlog功能）。
     2--slave開啟兩個線程：IO線程和SQL線程。其中：IO線程負責讀取master的binlog內容到中繼日誌relay log裡；SQL線程負責從relay log日誌裡讀出binlog內容，並更新到slave的資料庫裡，這樣就能保證slave資料和master資料保持一致了。
     3--Mysql複製至少需要兩個Mysql的服務，當然Mysql服務可以分布在不同的伺服器上，也可以在一台伺服器上啟動多個服務。
     4--Mysql複製最好確保master和slave伺服器上的Mysql版本相同（如果不能滿足版本一致，那麼要保證master主節點的版本低於slave從節點的版本）
     5--master和slave兩節點間時間需同步

Mysql複製的流程圖如下：

MySQL是怎樣同步的？

　1--基於語句的複製： 在主伺服器上執行的SQL語句，在從伺服器上執行同樣的語句。MySQL預設採用基於語句的複製，效率比較高。一旦發現沒法精確複製時，會自動選著基於行的複製。 
    2--基於行的複製：把改變的內容複寫過去，而不是把命令在從伺服器上執行一遍. 從mysql5.0開始支援
    3--混合類型的複製: 預設採用基於語句的複製，一旦發現基於語句的無法精確的複製時，就會採用基於行的複製。

實現環境：

| System　　 | mysql 　　　  |  ip　　　 　  　|

|:----　　　　 |:----　  　　　  |:----

|win7 　　　  | mysql-5.6.24   | 192.168.1.129 |

|centos 6.7　 | mysql-5.6.39   | 192.168.1.128 |

註：從伺服器的mysql版本最好和主伺服器相同，或者大於主伺服器版本

MySQL主從同步的實現部分：

首先是Master(主節點)的配置：

#主Master伺服器配置：

1.進入mysql的安裝目錄，建立一個log檔案夾（這個是儲存binary log的路徑）2.主伺服器開啟log_bin，需修改my.ini，配置如下：

#*********************master my.ini設定檔開始*****************************************#路徑均為當前伺服器的實際路徑basedir = D:\\apps\\mysql-5.6.24-win32datadir = D:\\apps\\mysql-5.6.24-win32\\dataport = 3306#產生記錄檔案位置，同步必須，請勿手動刪除,格式位置為 ：log-bin=mysql安裝路徑/log/mysql-bin.loglog-bin=D:\\apps\\mysql-5.6.24-win32\\log\\mysql-bin.log#服務ID，用於區分服務，範圍1~2^32-1,需要與從伺服器不同server_id= 1#MySQL 磁碟寫入策略以及資料安全性#每次事務提交時MySQL都會把log buffer的資料寫入log file，並且flush(刷到磁碟)中去innodb_flush_log_at_trx_commit=1#當sync_binlog =N (N>0) ，MySQL 在每寫 N次 二進位日誌binary log時，會使用fdatasync()函數將它的寫二進位日誌binary log同步到磁碟中去。
sync_binlog 的預設值是0，像作業系統刷其他檔案的機制一樣，MySQL不會同步到磁碟中去而是依賴作業系統來重新整理binary log。sync_binlog= 1#同步資料庫，如果多庫，就以此格式另寫幾行即可binlog-do-db=test#無需同步的資料庫，以下幾行基本一樣，無需改動binlog-ignore-db = clusterbinlog-ignore-db = mysqlbinlog-ignore-db = performance_schemabinlog-ignore-db = information_schema#mysql複製模式，三種：SBR（基於sql語句複製），RBR（基於行的複製），MBR（混合模式複製）#混合模式複製binlog_format=MIXED#binlog到期清理時間expire_logs_days=7#binlog每個記錄檔大小max_binlog_size=20M#*********************master my.ini設定檔結束*****************************************

3.重啟mysql服務，mysql命令列執行：

show master status;#記錄檔案名稱以及緊跟的當前行數數字

4.建立並授權使用者，後兩個slave分別是使用者名稱和密碼

grant replication slave ,replication client on *.* to [email protected]‘192.168.1.128‘ identified by "slave";flush privileges; #許可權修改立即生效flush tables with read lock; #鎖定資料庫為唯讀，確保備份資料一致性

5.退出mysql命令列，執行備份命令

#備份當前所有資料庫，可以參考備份單庫mysqldump -u root -p --all-databases --master-data > dbdump.sql

6.將sql指令碼在從伺服器執行
7.從伺服器啟動slave（前提是配置好從伺服器）
8.從伺服器啟動完畢後關閉表鎖

unlock tables;

 

#從伺服器的配置

1.停掉slave服務

service mysqld stop

2.修改設定檔：

vim /etc/my.cnf

#從資料庫（Slave）配置：#***********************************slave my.cnf配置開始******************************#從庫日誌記錄檔案位置或名稱首碼log_bin = /var/lib/mysql/mylogbin.log#同步處理記錄記錄的頻率，1為每條都記錄，安全但效率低sync_binlog = 1#server的id，不能與相同id的mysql主從串連server-id=2#從庫日誌忽略的資料庫名稱，不記錄
#這裡記錄從庫的binlog是為了安全，如果覺得沒必要，可以去掉從庫binlog的配置binlog-ignore-db = clusterbinlog-ignore-db = mysqlbinlog-ignore-db = performance_schemabinlog-ignore-db = information_schema#此處添加需要同步的資料庫名稱，那麼它會只接收這個資料庫的資訊，多個資料庫需同步按照此格式另寫幾行即可
#這裡同步資料有兩種思路，一種是主伺服器只發從庫需要的，在主庫指定；一種是主伺服器把所有資料同步過來，從庫按需過濾接收
#為了讓配置更詳細些，此處配置了從庫過濾接收的配置replicate-do-db=test#忽略接收的庫名replicate-ignore-db = clusterreplicate-ignore-db = mysqlreplicate-ignore-db = performance_schemareplicate-ignore-db = information_schema#跳過所有錯誤繼續slave-skip-errors=all#設定延時時間slave-net-timeout=60#mysql複製模式，三種：SBR（基於sql語句複製），RBR（基於行的複製），MBR（混合模式複製）binlog_format=MIXED #混合模式複製expire_logs_days=7 #binlog到期清理時間max_binlog_size=20M #binlog每個記錄檔大小#***********************************slave my.cnf配置結束******************************

3.儲存退出:wq

4.啟動mysqld服務

service mysqld start

5.刪除多餘資料庫，匯入資料，刪除部分不予示範,sql的位置請自行指定

mysqldump -u root -p < ~/dbdump.sql #這裡示範就是上傳到了root的根目錄，具體請使用“find / -name sql指令碼名” 命令查詢

6.從伺服器指定master

CHANGE MASTER TOMASTER_HOST=‘192.168.1.129‘,MASTER_USER=‘slave‘,MASTER_PASSWORD=‘slave‘,MASTER_LOG_FILE=‘mysql-bin.000001‘,MASTER_LOG_POS=593;

註：最後兩行是之前在主伺服器show master status 所記錄的資料

　　如果之前已經啟動了一個slave進程，那麼以上的命令會失效，並提示stop slave first，所以先stop slave; 然後重試

7.啟動slave

start slave;show slave status\G #注意，沒有分號

 輸出如下，顯示兩個都為yes即成功，可以測試一下

mysql> show slave status;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.1.129                  Master_User: replication                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000001          Read_Master_Log_Pos: 593               Relay_Log_File: mysql-relay-log.000004                Relay_Log_Pos: 441        Relay_Master_Log_File: mysql-bin.000001             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: 52360              Relay_Log_Space: 597              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: 0Master_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: 1"""

8.去主伺服器開啟表唯讀鎖

unlock tables;

--------------------------------實現部分到此結束--------------------------------------

徹底解除主從複製關係
1)stop slave;
2)reset slave; #或直接刪除master.info和relay-log.info這兩個檔案；
3)修改my.cnf刪除主從相關配置參數。

4)Delete FROM user Where User=‘slave‘ and Host=‘192.168.1.128‘;#刪除主伺服器配置的串連slave使用者

 

本文參考博文列表：

Mysql主從同步(1)-主從/主主環境部署梳理MySQL5.7 添加使用者、刪除使用者與授權mysql設定指定ip訪問，使用者權限相關操作win7下mysql5.6與centos下mysql5.6主從複製mysql5.6 主從複製同步詳細配置(圖文)MySQL5.6 資料庫主從（Master/Slave）同步安裝與配置詳解centos 7下mysql5.7 主從資料庫同步配置MySql 5.7.18 資料庫主從（Master/Slave）同步安裝與配置詳解mysql從庫Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file‘報錯處理

【MySQL】Last_IO_Errno: 1593 server-uuid重複導致slave報錯 

【MySQL】MySQL5.6資料庫基於binlog主從（Master/Slave）同步安裝與配置詳解Window 下mysql binlog開啟及查看，mysqlbinlogmysql主從複製-CHANGE MASTER TO 文法詳解[trouble] error connecting to master ‘[email protected]:3306‘ - retry-time: 60 retries: 86400

 

 

 

[實操筆記]MySQL主從同步功能實現