MySql主主(主從)同步配置詳解

一、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：

　　b)修改mysql配置檔案

　　c)重新開機mysql服務

　　d)、檢視主伺服器狀态

　　注：這裡鎖表的目的是為了生産環境中不讓進新的資料，好讓從伺服器定位同步位置。初次同步完成後，記得解鎖。

　　從伺服器

　　a)修改mysql配置檔案

　　b)重新開機mysql服務

　　c)用change master語句指定同步位置

　　注：master_log_file，master_log_pos由上面主伺服器查出的狀态值中确定。master_log_file對應file，master_log_pos對應position。

　　　　mysql 5.x以上版本已經不支援在配置檔案中指定主伺服器相關選項。

　　d)啟動從伺服器線程

　　e)檢視從伺服器狀态

　　檢視以上兩項的值，均為yes則表示狀态正常。

　　c、測試主從同步

　　先在主庫中插入一條新的資料

　　在從庫中查詢，看到之前主庫新增的資料就成功了。

　　四、mysql建立主主伺服器配置方法

　　mysql主主伺服器的思路和主從差不多，本質就是讓多台mysql伺服器間互為主從。本來隻想寫差異的地方，但文章邏輯不太好組織。

　　下面的是完整配置過程，如已按上面配置了主從架構，隻需調整差異部分就好了。

　　伺服器b(主) 192.168.1.107

　　b、主主配置過程

　　這裡伺服器a和伺服器b互為主從，是以都要分别建立一個同步使用者。

　　伺服器a和b：

　　伺服器a

　　伺服器b

　　c)分别重新開機伺服器a、b上的mysql服務

　　d)、分别在伺服器a、b上檢視做為主伺服器狀态

　　a伺服器

　　b伺服器

　　c)分别在伺服器a、b上用change master語句指定同步位置

　　a、伺服器

　　b、伺服器

　　d)分别在伺服器a、b上啟動從伺服器線程

　　e)分别在伺服器a、b上檢視從伺服器狀态

　　c、測試主主同步

　　測試伺服器a

　　在伺服器a中插入一條新的資料

　　在伺服器b中查詢，看到之前伺服器a新增的資料就成功了。

　　測試伺服器b

　　在伺服器b中插入一條新的資料

　　在伺服器a中查詢，看到之前伺服器b新增的資料就成功了。

　　五、配置參數說明

　　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是同義詞。

　　例如：

　　b、設定expire-logs-days參數

　　預設expire-logs-days為30天。這裡設為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伺服器即作為主伺服器，又作為從伺服器。那麼兩者都可以執行更新操作并能實作負載均衡，當一方出現故障時，另一方不受影響。但是，除非能保證任何更新操作順序都是安全的，否則雙向複制會導緻失敗。

　　為了更好的提高可靠性和可用性，需要當主伺服器不可用時，令從伺服器成為master。原來的主伺服器設定為slave，并從新的master上同步更新。現在已經有了一個這樣開源解決方案[mysql master-master replication manager]，後面我會在寫一篇關mysql mmm架構的方案，敬請期待！

　　九、參考文檔