mysql主从双向同步复制
2019獨角獸企業重金招聘Python工程師標準>>>
Mysql?主從雙向同步復制
MySQL支持單向、異步復制，復制過程中一個服務器充當主服務器，而一個或多個其它服務器充當從服務器。主服務器將更新寫入二進制日志文件，并維護日志文件的一個索引以跟蹤日志循環。當一個從服務器連接到主服務器時，它通知主服務器從服務器在日志中讀取的最后一次成功更新的位置。從服務器接收從那時起發生的任何更新，然后封鎖并等待主服務器通知下一次更新。
為什么使用主從復制？
1、主服務器/從服務器設置增加了健壯性。主服務器出現問題時，你可以切換到從服務器作為備份。
2、通過在主服務器和從服務器之間切分處理客戶查詢的負荷，可以得到更好的客戶響應時間。但是不要同時在主從服務器上進行更新，這樣可能引起沖突。
3、使用復制的另一個好處是可以使用一個從服務器執行備份，而不會干擾主服務器。在備份過程中主服務器可以繼續處理更新。
MySQL使用3個線程來執行復制功能(其中1個在主服務器上，另兩個在從服務器上。當發出START?SLAVE時，從服務器創建一個I/O線程，以連接主服務器并讓主服務器發送二進制日志。主服務器創建一個線程將二進制日志中的內容發送到從服務器。從服務器I/O線程讀取主服務器Binlog?Dump線程發送的內容并將該數據拷貝到從服務器數據目錄中的本地文件中，即中繼日志。第3個線程是SQL線程，從服務器使用此線程讀取中繼日志并執行日志中包含的更新。SHOW?PROCESSLIST語句可以查詢在主服務器上和從服務器上發生的關于復制的信息。
默認中繼日志使用host_name-relay-bin.nnnnnn形式的文件名，其中host_name是從服務器主機名，nnnnnn是序列號。用連續序列號來創建連續中繼日志文件，從000001開始。從服務器跟蹤中繼日志索引文件來識別目前正使用的中繼日志。默認中繼日志索引文件名為host_name-relay-bin.index。在默認情況，這些文件在從服務器的數據目錄中被創建。中繼日志與二進制日志的格式相同，并且可以用mysqlbinlog讀取。當SQL線程執行完中繼日志中的所有事件后，中繼日志將會被自動刪除。
從服務器在數據目錄中另外創建兩個狀態文件--master.info和relay-log.info。狀態文件保存在硬盤上，從服務器關閉時不會丟失。下次從服務器啟動時，讀取這些文件以確定它已經從主服務器讀取了多少二進制日志，以及處理自己的中繼日志的程度。
下面來看一下具體的配置：
一、準備服務器
由于MySQL不同版本之間的(二進制日志)binlog格式可能會不一樣，因此最好的搭配組合是Master的MySQL版本和Slave的版本相同或者更低，Master的版本肯定不能高于Slave版本。
本文中，我們假設主服務器(以下簡稱Master)和從服務器(以下簡稱Slave)的版本都是5.1.35，操作系統是Centos?6.3。
假設同步Master的主機名為：master(IP:192.168.1.123)，Slave主機名為：slave(IP:192.168.1.124)，2個MySQL的basedir目錄都是/usr/local/mysql，datadir都是：/var/lib/mysql。
二、設置同步服務器
1、設置同步Master
修改?my.cnf?文件，在
#?Replication?Master?Server?(default)#?binary?logging?is?required?for?replication添加如下內容：
#log-bin=/var/log/mysql/updatelog?? server-id?=?1?? auto_increment_increment?=?2?? auto_increment_offset?=?1?? binlog-do-db=discuz?? binlog-ignore-db=mysql?? #log-slave-updates??? slave-skip-errors=all?
注：
slave-skip-errors?是跳過錯誤，繼續執行復制操作
多主互備和主從復制有一些區別，因為多主中?都可以對服務器有寫權限，所以設計到自增長重復問題
出現的問題（多主自增長ID重復）
1:首先我們通過A,B的test表結構
2:掉A，在B上對數據表test(存在自增長ID)執行插入操作，返回插入ID為1
3:后停掉B，在A上對數據表test(存在自增長ID)執行插入操作，返回的插入ID也是1
4:然后?我們同時啟動A,B，就會出現主鍵ID重復
解決方法：
我們只要保證兩臺服務器上插入的自增長數據不同就可以了
如：A查奇數ID，B插偶數ID，當然如果服務器多的話，你可以定義算法，只要不同就可以了
在這里我們在A,B上加入參數，以實現奇偶插入
A：my.cnf上加入參數?
auto_increment_offset?=?1?? auto_increment_increment?=?2?
這樣A的auto_increment字段產生的數值是：1,?3,?5,?7,?…等奇數ID了
B：my.cnf上加入參數?
auto_increment_offset?=?2?? auto_increment_increment?=?2?
這樣B的auto_increment字段產生的數值是：2,?4,?6,?8,?…等偶數ID了
可以看出，你的auto_increment字段在不同的服務器之間絕對不會重復，所以Master-Master結構就沒有任何問題了。當然，你還可以使用3臺，4臺，或者N臺服務器，只要保證auto_increment_increment?=?N?再設置一下auto_increment_offset為適當的初始值就可以了，那樣，我們的MySQL可以同時有幾十臺主服務器，而不會出現自增長ID重復。
?
重啟MySQL,創建一個MySQL帳號為同步專用
#?/usr/local/mysql/bin/mysql?-u?root?-p?? mysql>?GRANT?REPLICATION?SLAVE?ON?*.*?TO?'root'@'%'?IDENTIFIED?BY?'root';?? mysql>GRANT?FILE,SELECT,REPLICATION?SLAVE?ON?*.*?TO???'root'@'%'?IDENTIFIED?BY?'root';?? mysql>?FLUSH?PRIVILEGES?;?
如果想要在Slave上有權限執行?"LOAD?TABLE?FROM?MASTER"?或?"LOAD?DATA?FROM?MASTER"?語句的話，必須授予全局的?FILE?和?SELECT?權限：
2、設置同步Slave
修改my.cnf文件，添加
server-id?=?2?? auto_increment_increment?=?2?? auto_increment_offset?=?2?? master-host?=?192.168.1.123?? master-user?=?root?? master-password?=?root?? master-port?=?3306?? replicate-ignore-db=mysql??? replicate-do-db=discuz?? #log-slave-updates??? slave-skip-errors=all?
重啟MySQL
3、啟動同步
在主服務器master?MySQL命令符下：
#?/usr/local/mysql/bin/mysql?-u?root?-p???? mysql>?show?master?status;??顯示（當然這個是我機器的情況，你的不可能跟我一樣哈，只是個例子）：???? +------------------+----------+-------------------+------------------+???? |?File?|?Position?|?Binlog_Do_DB?|?Binlog_Ignore_DB?|???? +------------------+----------+-------------------+------------------+???? |?mysql-bin.000009?|?98?|?discuz?|?mysql?|????? +------------------+----------+-------------------+------------------+?
在從服務器master?MySQL命令符下：
#?/usr/local/mysql/bin/mysql?-u?root?-p?? mysql>?slave?stop;?? mysql>?change?master?to?master_host='192.168.1.123',?master_user='root',?master_password='root',?master_log_file='mysql-bin.000009',?master_log_pos=98;?? mysql>?slave?start;?? mysql>?show?slave?status\G;????Slave_IO_Running:?Yes??Slave_SQL_Running:?Yes?
如果都是yes，那代表已經在同步
往表里面寫點數據測試一下看是否同步成功，如果不成功，絕對不是你的RP問題，再檢查一下操作步驟！?
4、設置雙向同步
修改slave服務器的my.cnf，添加
log-bin=/var/log/mysql/updatelog?? binlog-do-db=discuz?? binlog-ignore-db=mysql?
重啟MySQL,創建一個MySQL帳號為同步專用
mysql>?GRANT?REPLICATION?SLAVE?ON?*.*?TO?'root'@'%'?IDENTIFIED?BY?'root';?? mysql>?GRANT?FILE,SELECT,REPLICATION?SLAVE?ON?*.*?TO???'root'@'%'?IDENTIFIED?BY?'root';?? mysql>?FLUSH?PRIVILEGES?;?
修改master服務器的my.cnf,添加
master-host?=?192.168.1.124?? master-user?=?root?? master-password?=?root?? master-port?=?3306?? replicate-ignore-db=mysql?? replicate-do-db=discuz?
重啟MySQL?
在主服務器slave?MySQL命令符下：?
show?master?status;???? +------------------+----------+-------------------+------------------+???? |?File?|?Position?|?Binlog_Do_DB?|?Binlog_Ignore_DB?|???? +------------------+----------+-------------------+------------------+???? |?mysql-bin.000013?|?98?|?discuz?|?mysql?|????? +------------------+----------+-------------------+------------------+在服務器A?MySQL命令符下：
mysql>?slave?stop；?? mysql>?change?master?to?master_host='192.168.1.124',?master_user='root',?master_password='root',?master_log_file='mysql-bin.000013',?master_log_pos=98;?? mysql>?slave?start;?
其實也就是A->B單向同步的反向操作！雙向同步，就這么簡單啦！??
提示：如果修改了主服務器的配置，記得刪除從服務器上的master.info文件。否則從服務器使用的還是老配置，可能會導致錯誤。
-----------------------------------------------------------------------------------
注意：關于要復制多個數據庫時，binlog-do-db和replicate-do-db選項的設置，網上很多人說是用半角逗號分隔，經過測試，這樣的說法是錯誤的，MySQL官方文檔也明確指出，如果要備份多個數據庫，只要重復設置相應選項就可以了。
比如：
binlog-do-db=a?? binlog-do-db=b?? replicate-do-db=a?? replicate-do-db=b?
補：
# 啟用從庫日志，這樣可以進行鏈式復制??
log-slave-updates# 從庫是否只讀，0表示可讀寫，1表示只讀??
read-only=1# 只復制某個表??
replicate-do-table=tablename# 只復制某些表（可用匹配符）??
replicate-wild-do-table=tablename%# 只復制某個庫??
replicate-do-db=dbname# 只復制某些庫??
replicte-wild-do-db=dbname%# 不復制某個表??
replicate-ignore-table=tablename#不復制某些表??
replicate-wild-ignore-table=tablename%#不復制某個庫??
replicate-ignore-db=dbname#復制完的sql語句是否立即從中繼日志中清除，1表示立即清除??
relay-log-purge=1?
?
?
轉載于:https://my.oschina.net/51clocker/blog/388258
總結
以上是生活随笔為你收集整理的mysql主从双向同步复制的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 央广记者在自律联盟现场采访京都贷执行董事
- 下一篇： C++Singleton的DCLP（双重