MySQL 5.6.10 跨平台GTID複製實踐

標籤：

根據業務需要，建立MySQL複製來實現資料冗餘。

MySQL 5.6.10版本提供了更方便的基於GTID的複製功能，MySQL可以通過GTID自動識別上次同步的點，極大地方便了營運人員，減少出錯的幾率。

在官方文檔中提到，最保險可靠的複製方式，是基於row的複製，所以寧可犧牲一些效能也要保證資料的安全。

現實環境中，master主要資料庫MySQL 5.6.10(msi安裝方式)安裝在Windows 2008 Server x64上，slave從伺服器是一台老舊的DELL伺服器，運行CentOS 6.4 x64系統，源碼編譯安裝MySQL 5.6.10的Linux版本，安裝過程可以參考我以前的博文：http://www.cnblogs.com/jlzhou/archive/2013/03/09/2951544.html

不同平台下，MySQL是有一些差異的，要小心處理。

第一個問題是，Windows平台下，檔案名稱大小寫不敏感，造成對應的MySQL的資料表名稱預設都採用小寫字母方式，同時大小不寫敏感，參考我以前的博文：http://www.cnblogs.com/jlzhou/archive/2013/03/18/2966106.html 為了能將資料同步複製到Linux平台的MySQL，我們需要設定Linux平台下MySQL的資料表名稱設定：(修改my.cnf檔案)

?[mysqld]lower_case_table_names=1

第二個問題是，自增欄位0值的問題。因為現有資料庫是MSSQL，商務邏輯需要某些表的自增欄位從0開始。參考我以前的博文：http://www.cnblogs.com/jlzhou/archive/2013/03/18/2965384.html 為了在Windows平台和Linux平台的MySQL之間複製資料，增加全域變數設定，在my.ini和my.cnf中分別添加NO_AUTO_VALUE_ON_ZERO設定到sql-mode行：

//my.ini 該檔案預設在Windows7或Windows2008作業系統中位於 C:\ProgramData\MySQL\MySQL Server 5.6 目錄下（採用MSI安裝方式），如果你自訂了資料目錄，則該設定檔在資料目錄下。# Set the SQL mode to strictsql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO"

現在開始配置GTID複製，先配置master端的my.ini檔案，加入下述配置，然後重啟master的MySQL服務：

binlog-format=ROWlog-bin=master-bin.loglog-bin-index=master-bin.indexlog-slave-updates=truegtid-mode=onenforce-gtid-consistency=truemaster-info-repository=TABLErelay-log-info-repository=TABLEsync-master-info=1slave-parallel-workers=2binlog-checksum=CRC32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log-events=1server-id=1sync_binlog=1

 再修改slave端的my.cnf檔案，加入下述配置，然後重啟slave的MySQL服務：

binlog-format=ROWlog-bin=slave-bin.loglog-bin-index=slave-bin.indexlog-slave-updates=truegtid-mode=onenforce-gtid-consistency=truemaster-info-repository=TABLErelay-log-info-repository=TABLEsync-master-info=1slave-parallel-workers=2binlog-checksum=CRC32master-verify-checksum=1slave-sql-verify-checksum=1binlog-rows-query-log-events=1server-id=2sync_binlog=1

其實，並不需要在slave端啟用binlog，但是為了在master故障時，方便的轉換slave到master，並且方便建立slave的slave，所以採用和主伺服器類似的配置。

複製設定會將用於複製的使用者和密碼以明文形式儲存在master.info檔案中，最好為複製建立專用的使用者，授予 REPLICATION SLAVE 許可權。

在master端執行：

GRANT REPLICATION SLAVE ON *.* TO ‘repluser‘@‘192.168.1.101‘ IDENTIFIED BY ‘12345678‘;

最後，在slave執行指向master的命令，並開啟slave複製。

CHANGE MASTER TO MASTER_HOST=‘192.168.1.100‘, MASTER_PORT=3306, MASTER_USER=‘repluser‘,MASTER_PASSWORD=‘12345678‘, master_auto_position=1;

START slave;

這時就可以測試在master上建立資料庫，建表，然後監控slave的複製狀態了。

本文中沒有涉及到已有資料庫在master上的情況，請參考這篇博文：http://www.zhaokunyao.com/archives/4131

 

後記：

附上備份和恢複指令碼：命令列執行

Backup from remote server scripts:// for test database:"C:\MySQL\MySQL Server 5.6\bin\mysqldump.exe" --user=root --max_allowed_packet=1G --host=10.192.8.105 --port=3306 --default-character-set=utf8 --set-gtid-purged=OFF --password --databases test > "C:\\Backup\\test.dump.sql" Restore to local dev machine scripts:// for test database:"C:\MySQL\MySQL Server 5.6\bin\mysql.exe" --host=localhost --user=root --port=3306 --password --default-character-set=utf8 --comments < "C:\\Backup\\test.dump.sql"

注意，上述指令碼中，備份的部分要加入--set-gtid-purged=OFF參數，防止在備份出的sql指令碼中產生 SET @@global.gtid_purged 語句：

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don‘t want to restore GTIDs, pass --set-gtid-purged=OFF.

官方文檔關於set-gtid-purged是這樣寫的：

This option enables control over global transaction ID (GTID) information written to the dump file, by indicating whether to add a SET @@global.gtid_purged statement to the output. 

MySQL 5.6.10 跨平台GTID複製實踐