MySql主主(主從)同步配置詳解
MySQL支持單向、異步復制，復制過程中一個服務器充當主服務器，而一個或多個其它服務器充當從服務器。MySQL復制基于主服務器在二進制日志中跟蹤所有對數據庫的更改(更新、刪除等等)。因此，要進行復制，必須在主服務器上啟用二進制日志。每個從服務器從主服務器接收主服務器上已經記錄到其二進制日志的保存的更新。當一個從服務器連接主服務器時，它通知主服務器定位到從服務器在日志中讀取的最后一次成功更新的位置。從服務器接收從那時起發生的任何更新，并在本機上執行相同的更新。然后封鎖并等待主服務器通知新的更新。從服務器執行備份不會干擾主服務器，在備份過程中主服務器可以繼續處理更新。
二、復制實現細節
MySQL使用3個線程來執行復制功能，其中兩個線程(Sql線程和IO線程)在從服務器，另外一個線程(IO線程)在主服務器。當發出START SLAVE時，從服務器創建一個I/O線程，以連接主服務器并讓它發送記錄在其二進制日志中的語句。主服務器創建一個線程將二進制日志中的內容發送到從服務器。該線程可以即為主服務器上SHOW PROCESSLIST的輸出中的Binlog Dump線程。從服務器I/O線程讀取主服務器Binlog Dump線程發送的內容并將該數據拷貝到從服務器數據目錄中的本地文件中，即中繼日志。第3個線程是SQL線程，由從服務器創建，用于讀取中繼日志并執行日志中包含的更新。在從服務器上，讀取和執行更新語句被分成兩個獨立的任務。當從服務器啟動時，其I/O線程可以很快地從主服務器索取所有二進制日志內容，即使SQL線程執行更新的遠遠滯后。
1、復制線程狀態
通過show slave status\G和show master status可以查看復制線程狀態。常見的線程狀態有：
(1)主服務器Binlog Dump線程
Has sent all binlog to slave; waiting for binlog to be updated
線程已經從二進制日志讀取所有主要的更新并已經發送到了從服務器。線程現在正空閑，等待由主服務器上新的更新導致的出現在二進制日志中的新事件。
(2)從服務器I/O線程狀態
Waiting for master to send event
線程已經連接上主服務器，正等待二進制日志事件到達。如果主服務器正空閑，會持續較長的時間。如果等待持續slave_read_timeout秒，則發生超時。此時，線程認為連接被中斷并企圖重新連接。
(3)從服務器SQL線程狀態
Reading event from the relay log
線程已經從中繼日志讀取一個事件，可以對事件進行處理了。
Has read all relay log; waiting for the slave I/O thread to update it
線程已經處理了中繼日志文件中的所有事件，現在正等待I/O線程將新事件寫入中繼日志。
2、復制過程中使用的傳遞和狀態文件
默認情況，中繼日志使用host_name-relay-bin.nnnnnn形式的文件名，其中host_name是從服務器主機名，nnnnnn是序列號。中繼日志與二進制日志的格式相同，并且可以用mysqlbinlog讀取。
從服務器在data目錄中另外創建兩個小文件。這些狀態文件默認名為主master.info和relay-log.info。狀態文件保存在硬盤上，從服務器關閉時不會丟失。下次從服務器啟動時，讀取這些文件以確定它已經從主服務器讀取了多少二進制日志，以及處理自己的中繼日志的程度。
如果要備份從服務器的數據，還應備份這兩個小文件以及中繼日志文件。它們用來在恢復從服務器的數據后繼續進行復制。如果丟失了中繼日志但仍然有relay-log.info文件，可以通過檢查該文件來確定SQL線程已經執行的主服務器中二進制日志的程度。然后可以用Master_Log_File和Master_LOG_POS選項執行CHANGE MASTER TO來告訴從服務器重新從該點讀取二進制日志。
三、MySQL建立主從服務器配置方法
A、環境描述
服務器A(主) 192.168.1.106
服務器B(從) 192.168.1.107
Mysql版本：5.5.11
System OS：CentOS 5.6 X64
主從需同步的數據庫內容保持一致。
B、主從配置過程
主服務器
a)創建同步用戶
在主服務器上為從服務器建立一個連接帳戶，該帳戶必須授予REPLICAITON SLAVE權限。
服務器A：
添加一個有 slave權限etuy
grant replication slave on *.* to 'replication'@'192.168.1.%' identified by '000000'; flush privileges;b)修改mysql配置文件
[mysqld]
server-id = 1
log-bin=/data0/mysql/binlog/binlog
binlog-do-db = test
binlog-ignore-db=mysql
c)重啟mysql服務 # /data0/mysql/mysql restart Restarting MySQL... Stoping MySQL... Starting MySQL...d)、查看主服務器狀態
ysql>flush tables with read lock;
mysql>show master status\G
*************************** 1. row ***************************
File: binlog.000006
Position: 107
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
mysql>unlock tables;
注：這里鎖表的目的是為了生產環境中不讓進新的數據，好讓從服務器定位同步位置。初次同步完成后，記得解鎖。
從服務器
a)修改mysql配置文件
[mysqld] server-id = 2 log-bin = /data0/mysql/binlog/binlog replicate-do-db = test replicate-ignore-db = mysql,information_schemab)重啟mysql服務
# /data0/mysql/mysql restart Restarting MySQL... Stoping MySQL... Starting MySQL...c)用change master語句指定同步位置
mysql>change master to master_host='192.168.1.106', master_user='replication', master_password='000000', master_log_file='binlog.000006', master_log_pos=107;注：master_log_file，master_log_pos由上面主服務器查出的狀態值中確定。master_log_file對應File，master_log_pos對應Position。
mysql 5.x以上版本已經不支持在配置文件中指定主服務器相關選項。
d)啟動從服務器線程
mysql>start slave;
我在實際操作中出現了啟動不了的情況，錯誤提示出下： mysql> start slave; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 3 Current database: data_center ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO 使用show slave status查看狀態時發現有這樣的錯誤提示： Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). GOOGLE說是從服務器配置文件里的server_id沒有生效，使用set global server_id=2;動態修改server_id，然后start slave;成功
mysql>show slave status\G; Slave_IO_Running: Yes Slave_SQL_Running: Yes查看以上兩項的值，均為Yes則表示狀態正常。
C、測試主從同步
先在主庫中插入一條新的數據
mysql> use test Database changed mysql> desc wp_terms; +------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra +------------+---------------------+------+-----+---------+----------------+ | term_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment | name | varchar(200) | NO | MUL | | slug | varchar(200) | NO | UNI | | term_group | bigint(10) | NO | | 0 +------------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.02 sec) mysql> insert wp_terms values(1116,'MikeTestMySQL','Mike-Test-Mysql-Repli',0); Query OK, 1 row affected (0.03 sec)在從庫中查詢，看到之前主庫新增的數據就成功了。
mysql> select * from test.wp_terms where term_id=1116; +---------+---------------+-----------------------+------------+ | term_id | name | slug | term_group +---------+---------------+-----------------------+------------+ | 1116 | MikeTestMySQL | Mike-Test-Mysql-Repli | 0 +---------+---------------+-----------------------+------------+ 1 row in set (0.00 sec)
四、MySQL建立主主服務器配置方法
MySQL主主服務器的思路和主從差不多，本質就是讓多臺MySQL服務器間互為主從。
下面的是完整配置過程，如已按上面配置了主從架構，只需調整差異部分就好了。
A、環境描述
服務器A(主) 192.168.1.106
服務器B(主) 192.168.1.107
Mysql版本：5.5.11
System OS：CentOS 5.6 X64
主從需同步的數據庫內容保持一致。
B、主主配置過程
a)創建同步用戶
在主服務器上為從服務器建立一個連接帳戶，該帳戶必須授予REPLICAITON SLAVE權限。
這里服務器A和服務器B互為主從，所以都要分別建立一個同步用戶。
服務器A和B：
grant replication slave on *.* to 'replication'@'192.168.1.%' identified by '000000'; flush privileges;b)修改mysql配置文件
[mysqld] server-id = 1 log-bin=/data0/mysql/binlog/binlog binlog-do-db = test binlog-ignore-db=mysql #主主需加入的部分 log-slave-updates sync_binlog=1 auto_increment_offset=1 auto_increment_increment=2 replicate-do-db = test replicate-ignore-db = mysql,information_schema
服務器B
[mysqld] server-id = 2 log-bin = /data0/mysql/binlog/binlog replicate-do-db = test replicate-ignore-db = mysql,information_schema #主主需要加入部分 binlog-do-db = test binlog-ignore-db=mysql log-slave-updates sync_binlog=1 auto_increment_offset=2 auto_increment_increment=2
c)分別重啟服務器A、B上的mysql服務
# /data0/mysql/mysql restart Restarting MySQL... Stoping MySQL... Starting MySQL...
d)、分別在服務器A、B上查看做為主服務器狀態
A服務器
mysql>flush tables with read lock; mysql> show master status\G *************************** 1. row *************************** File: binlog.000007 Position: 107 Binlog_Do_DB: test Binlog_Ignore_DB: mysql 1 row in set (0.00 sec) mysql>unlock tables;
B服務器
mysql> show master status\G *************************** 1. row *************************** File: binlog.000005 Position: 107 Binlog_Do_DB: test Binlog_Ignore_DB: mysql 1 row in set (0.00 sec)
注：這里鎖表的目的是為了生產環境中不讓進新的數據，好讓從服務器定位同步位置。初次同步完成后，記得解鎖。
c)分別在服務器A、B上用change master語句指定同步位置
A、服務器
mysql>change master to master_host='192.168.1.107', master_user='replication', master_password='000000', master_log_file='binlog.000005', master_log_pos=107;
B、服務器
mysql>change master to master_host='192.168.1.106', master_user='replication', master_password='000000', master_log_file='binlog.000007', master_log_pos=107;
注：master_log_file，master_log_pos由上面主服務器查出的狀態值中確定。master_log_file對應File，master_log_pos對應Position。
mysql 5.x以上版本已經不支持在配置文件中指定主服務器相關選項。
d)分別在服務器A、B上啟動從服務器線程
mysql>start slave;
e)分別在服務器A、B上查看從服務器狀態
mysql>show slave status\G; Slave_IO_Running: Yes Slave_SQL_Running: Yes
查看以上兩項的值，均為Yes則表示狀態正常。
C、測試主主同步
五、配置參數說明
server-id
ID值唯一的標識了復制群集中的主從服務器，因此它們必須各不相同。master_id必須為1到232–1之間的一個正整數值，slave_id值必須為2到232–1之間的一個正整數值。
log-bin
表示打開binlog,打開該選項才可以通過I/O寫到Slave的relay-log,也是可以進行replication的前提;
binlog-do-db
表示需要記錄進制日志的數據庫。如果有多個數據庫可用逗號分隔，或者使用多個binlog-do-db選項
binlog-ignore-db
表示不需要記錄二進制日志的數據庫。如果有多個數據庫可用逗號分隔，或者使用多個binlog-do-db選項
replicate-do-db
表示需要同步的數據庫，如果有多個數據庫可用逗號分隔，或者使用多個replicate-do-db選項
replicate-ignore-db=mysql
表示不需要同步的數據庫，如果有多個數據庫可用逗號分隔，或者使用多個replicate-ignore-db=mysql選項
log-slave-updates
配置從庫上的更新操作是否寫入二進制文件，如果這臺從庫，還要做其他從庫的主庫，那么就需要打這個參數，以便從庫的從庫能夠進行日志同步
slave-skip-errors
在復制過程，由于各種原因導致binlog中的sql出錯，默認情況下，從庫會停止復制，要用戶介入。可以設置Slave-skip-errors來定義錯誤號，如果復制過程中遇到的錯誤號是定義的錯誤號，便可以跳過。如果從庫是用來做備份，設置這個參數會存在數據不一致，不要使用。如果是分擔主庫的查詢壓力，可以考慮。
sync_binlog=1 or N
sync_binlog的默認值是0，這種模式下，MySQL不會同步到磁盤中去。這樣的話，MySQL依賴操作系統來刷新二進制日志binary log，就像操作系統刷其他文件的機制一樣。因此如果操作系統或機器(不僅僅是MySQL服務器)崩潰，有可能binlog中最后的語句丟失了。要想防止這種情況，你可以使用sync_binlog全局變量，使binlog在每N次binlog寫入后與硬盤同步。當sync_binlog變量設置為1是最安全的，因為在crash崩潰的情況下，你的二進制日志binary log只有可能丟失最多一個語句或者一個事務。但是，這也是最慢的一種方式（除非磁盤有使用帶蓄電池后備電源的緩存cache，使得同步到磁盤的操作非常快）。
即使sync_binlog設置為1,出現崩潰時，也有可能表內容和binlog內容之間存在不一致性。如果使用InnoDB表，MySQL服務器處理COMMIT語句，它將整個事務寫入binlog并將事務提交到InnoDB中。如果在兩次操作之間出現崩潰，重啟時，事務被InnoDB回滾，但仍然存在binlog中。可以用–innodb-safe-binlog選項來增加InnoDB表內容和binlog之間的一致性。(注釋：在MySQL 5.1中不需要–innodb-safe-binlog；由于引入了XA事務支持，該選項作廢了），該選項可以提供更大程度的安全，使每個事務的 binlog(sync_binlog =1)和(默認情況為真)InnoDB日志與硬盤同步，該選項的效果是崩潰后重啟時，在滾回事務后，MySQL服務器從binlog剪切回滾的 InnoDB事務。這樣可以確保binlog反饋InnoDB表的確切數據等，并使從服務器保持與主服務器保持同步(不接收回滾的語句)。
auto_increment_offset和auto_increment_increment
auto_increment_increment和auto_increment_offset用于主－主服務器(master-to-master)復制，并可以用來控制AUTO_INCREMENT列的操作。兩個變量均可以設置為全局或局部變量，并且假定每個值都可以為1到65,535之間的整數值。將其中一個變量設置為0會使該變量為1。
這兩個變量影響AUTO_INCREMENT列的方式：auto_increment_increment控制列中的值的增量值,auto_increment_offset確定AUTO_INCREMENT列值的起點。
如果auto_increment_offset的值大于auto_increment_increment的值，則auto_increment_offset的值被忽略。例如：表內已有一些數據，就會用現在已有的最大的自增值做為初始值。
六、二進制日志清除
主同步服務器產生的二進制日志會占據大量的磁盤空間，應定期刪除過期的bin-log。
A、通過PURGE MASTER LOGS刪除
如果您有一個在用的從屬服務器，該服務器當前正在讀取您正在試圖刪除的日志之一，則本語句不會起作用，而是會失敗，并伴隨一個錯誤。不過，如果從屬服務器是停止的，并且您碰巧清理了其想要讀取的日志之一，則從屬服務器啟動后不能復制。當從屬服務器正在復制時，本語句可以安全運行。您不需要停止它們。
要清理日志，需按照以下步驟：
1、在每個從屬服務器上，使用SHOW SLAVE STATUS來檢查它正在讀取哪個日志。
2、使用SHOW MASTER LOGS獲得主服務器上的一系列日志。
3、在所有的從屬服務器中判定最早的日志。這個是目標日志。如果所有的從屬服務器是更新的，這是清單上的最后一個日志。
4、制作您將要刪除的所有日志的備份。(建議備份)
5、清理所有的日志，但是不包括目標日志。
PURGE 語法
PURGE {MASTER | BINARY} LOGS TO ‘log_name’
PURGE {MASTER | BINARY} LOGS BEFORE ‘date’
用于刪除列于在指定的日志或日期之前的日志索引中的所有二進制日志。這些日志也會從記錄在日志索引文件中的清單中被刪除，這樣被給定的日志成為第一個。
BEFORE變量的date自變量可以為’YYYY-MM-DD hh:mm:ss’格式。MASTER和BINARY是同義詞。
例如：
#刪除binlog.000002之前的而不包含binlog.000002 mysql> PURGE MASTER LOGS TO 'binlog.000002'; #刪除2011-05-28 1:35:00之前的 mysql> PURGE MASTER LOGS BEFORE '2011-05-28 1:35:00'; #清除3天前的binlog mysql> PURGE MASTER LOGS BEFORE DATE_SUB(NOW( ), INTERVAL 3 DAY);
B、設置expire-logs-days參數
缺省expire-logs-days為30天。這里設為7天，可根據自己情況調整。
[mysqld] expire-logs-days = 7
七、用于控制主、從服務器的SQL語句
A、用于控制主服務器的SQL語句
PURGE MASTER LOGS
用于刪除列于在指定的日志或日期之前的日志索引中的所有二進制日志。這些日志也會從記錄在日志索引文件中的清單中被刪除，這樣被給定的日志成為第一個。
RESET MASTER
可以刪除列于索引文件中的所有二進制日志，把二進制日志索引文件重新設置為空，并創建一個新的二進制日志文件。
SET SQL_LOG_BIN
如果客戶端使用一個有SUPER權限的賬戶連接，則可以禁用或啟用當前連接的二進制日志記錄。如果客戶端沒有此權限，則語句被拒絕，并伴隨有錯誤。
SHOW BINLOG EVENTS
用于在二進制日志中顯示事件。如果您不指定’log_name’，則顯示第一個二進制日志。
SHOW MASTER LOGS
用于列出服務器中的二進制日志文件。
SHOW MASTER STATUS
用于提供主服務器二進制日志文件的狀態信息。
SHOW SLAVE HOSTS
用于顯示當前使用主服務器注冊的復制從屬服務器的清單。
B、用于控制從服務器的SQL語句
CHANGE MASTER TO
可以更改從屬服務器用于與主服務器進行連接和通訊的參數。
LOAD DATA FROM MASTER
用于對主服務器進行快照，并拷貝到從屬服務器上。
LOAD TABLE tbl_name FROM MASTER
用于把表的拷貝從主服務器轉移到從屬服務器。
MASTER_POS_WAIT()
這實際上是一個函數，而不是一個語句。它被用于確認，從屬服務器已讀取并執行了到達主服務器二進制日志的給定位置。
RESET SLAVE
用于讓從屬服務器忘記其在主服務器的二進制日志中的復制位置。
SET GLOBAL SQL_SLAVE_SKIP_COUNTER
從主服務器中跳過后面的n個事件。要復原由語句導致的復制中止，這是有用的。
SHOW SLAVE STATUS
用于提供有關從屬服務器線程的關鍵參數的信息。
START SLAVE
用于啟動從屬服務器線程
STOP SLAVE
用于中止從屬服務器線程
以上內容摘自MySQL官方手冊，具體用法詳見：http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#reset-master
八、主從復制如何提高可靠性
主從單向復制，從服務器只是實時的保存了主服務器的一個副本。當主服務器發生故障時，可以切換到從服務器繼續做查詢，但不能更新。
如果采用雙向復制，即兩臺mysql服務器即作為主服務器，又作為從服務器。那么兩者都可以執行更新操作并能實現負載均衡，當一方出現故障時，另一方不受影響。但是，除非能保證任何更新操作順序都是安全的，否則雙向復制會導致失敗。
為了更好的提高可靠性和可用性，需要當主服務器不可用時，令從服務器成為Master。原來的主服務器設定為Slave，并從新的Master上同步更新。現在已經有了一個這樣開源解決方案[MySQL Master-Master Replication Manager]，后面我會在寫一篇關MySQL MMM架構的方案，敬請期待！
九、參考文檔
http://www.google.com
http://blogold.chinaunix.net/u3/93755/showart.php?id=2213538
http://hahaxiao.techweb.com.cn/archives/465.html
http://blog.csdn.net/libraworm/archive/2007/07/23/1703365.aspx
http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#reset-master
來自：http://www.mike.org.cn/articles/mysql-master-slave-sync-conf-detail/
來自：http://www.mike.org.cn/articles/mysql-master-slave-sync-conf-detail/
本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！