生產環境搭建MySQL複製的教程

【導讀】

網路上有很多關於MySQL複製搭建的步驟和範例，以及手冊上有一章完整的篇幅，講述MySQL複製的原理、搭建步驟、優化等，但依然存在很多剛開 始學習MySQL知識或者剛進入DBA行業的朋友諮詢，知道搭建步驟，但還是在諮詢生產環境的搭建或搭建過程碰到的問題，為此給大家簡單講述下複製的原 理，並且講述一下生產環境該如何搭建複製架構。

n  複製的基本原理及基礎知識

使用3個執行緒來完成主從資料庫伺服器之間的資料複製功能，其中1 個I/O執行緒跑在主伺服器上，另兩個執行緒跑在從伺服器上。當從伺服器上執行START SLAVE時，從伺服器會向主伺服器申請建立一個資料庫連線，連線到主資料庫伺服器，並用它傳送記錄在其二進位制日誌中的語句內容，此執行緒可以在主伺服器上 檢視到，在主伺服器上執行命令：SHOW PROCESSLIST，其輸出資訊中Command列值內容為：Binlog Dump，其連線資訊中的HOST列值包含從伺服器的IP地址。

從伺服器上，執行SHOW PROCESSLIST;可以看到2個執行緒，其中一個為I/O執行緒，一個為SQL執行緒，其功能：

l  I/O執行緒負責讀取主伺服器Binlog Dump執行緒傳送的內容並將該資料拷貝到從資料庫伺服器資料目錄中的本地檔案中，也即中繼日誌；

l  SQL執行緒，是從資料庫伺服器建立，用於讀取解析中繼日誌並執行日誌中的SQL語句；

MySQL的主從資料複製結構，如圖1-1：

 

n  生產環境複製搭建範例

假設需要搭建複製的二臺資料庫伺服器IP地址分別為：10.10.1.210、10.10.1.211，mysqld服務都跑在埠號：3306 上。若是隻有一臺線上的場景，即假定10.10.1.210為線上伺服器，10.10.1.211為新增加的伺服器；若是要進行撤伺服器的場景，那麼拆除 的伺服器假定為10.10.1.211。

u  非線上環境搭建資料庫的複製結構步驟（雙主複製）

l  10.10.1.210/211：安裝mysql軟體包，用rpm包做示範：rpm –ivh /home/software/MySQL*

l  10.10.1.210/211上，執行SQL語句：

DELETE FROM mysql.user where user=’’ OR host=’’;FLUSH PRIVILEGES;

GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%’ IDENTIFIED ‘repl#3306’;

備註：

帳號名稱和密碼可按自行習慣或規範設定，建議帳號名稱好區分用處，以及獨立

專用，許可權要限制，不多賦予任何許可權，以免混淆或執行錯誤命令等而造成危害

l  10.10.1.210/211 停掉mysqld服務：mysqladmin –uroot shutdown

l  10.10.1.210上，必須修改的my.cnf引數：server-id和開啟二進位制功能，推薦與複製有關的引數設定：

server-id =  3306

port = 3306

log-bin = /data/mysqldata/binlog/mysql-bin  （備註：路徑替換為你所需要的）

log-bin-index = /data/mysqldata/binlog/mysql-bin.index

 

report_host =

report_port = 3306

innodb_flush_log_at_trx_commit = 2

sync_binlog = 0

binlog-format = mixed

transaction_isolation = REPEATABLE-READ

l  10.10.1.211上，必須修改的my.cnf引數：server-id，若是想做雙主複製，也得開啟二進位制功能，推薦與複製有關的引數配置設定：

server-id =  33060

port = 3306

log-bin = /data/mysqldata/binlog/mysql-bin  （備註：路徑替換為你所需要的）

log-bin-index = /data/mysqldata/binlog/mysql-bin.index

 

report_host =

report_port = 3306

innodb_flush_log_at_trx_commit = 2

sync_binlog = 0

binlog-format = mixed

transaction_isolation = REPEATABLE-READ

log-slave-updates

l  10.10.1.210/211 啟動mysqld服務：mysqld_safe &

l  10.10.1.210/211上執行SQL：SHOW MASTER STATUS;以獲得各自上的二進位制日誌檔名稱及日誌位置

l  10.10.1.210->10.10.1.211的複製，則在10.10.1.211上執行SQL語句：

CHANGE MASTER TO master_host=’10.10.1.210′,

master_user=’repl’,master_password=’repl#3306′,

master_log_file=’mysql-bin.000002′,

master_log_pos=106,master_port=3306;

START SLAVE;SELECT SLEEP(1);SHOW SLAVE STATUS\G

備註：

若是SQL執行緒和I/O執行緒都顯示為OK狀態，則複製搭建成功；若是有錯誤資訊，則看SQL語句的輸出資訊，以及檢視錯誤日誌檔案內容（註釋：error.log）

l  10.10.1.211->10.10.1.210的複製，則在10.10.1.210上執行SQL語句：

CHANGE MASTER TO master_host=’10.10.1.211′,

master_user=’repl’,master_password=’repl#3306′,

master_log_file=’mysql-bin.000002′,

master_log_pos=106,master_port=3306;

START SLAVE;SELECT SLEEP(1);SHOW SLAVE STATUS\G

對於如何閱讀輸出的資訊，同上

 

u  給線上資料庫伺服器搭建複製步驟

前面假設了伺服器IP：10.10.1.210為線上跑的資料庫伺服器，可能有不少公司為提高效能或疏忽等原因，二進位制日誌登記功能有可能沒有開啟。

l  10.10.1.210：檢查是否開啟二進位制日誌功能，若沒有開啟，則修改my.cnf檔案，新增：

log-bin = /data/mysqldata/binlog/mysql-bin

log-bin-index = /data/mysqldata/binlog/mysql-bin.index

其他引數建議配置為：

server-id =  3306

port = 3306

report_host =

report_port = 3306

innodb_flush_log_at_trx_commit = 2

sync_binlog = 0

binlog-format = mixed

transaction_isolation = REPEATABLE-READ

然後申請停機維護時間，肯定要是使用者訪問量最小，且不影響內部其他業務運轉的時間點，一般都是凌晨幾點進行，把mysqld伺服器重新啟動，命令：

mysqladmin –uroot –p*** shutdown;mysqld_safe &

l  10.10.1.210：對於無服務上所有資料庫完整備份的資料庫伺服器，則編寫一個資料庫備份的指令碼程式，比如shell語言，然後crontab呼叫任務了，時間肯定要設定在伺服器壓力最小的時刻點；

l  10.10.1.210：指令碼程式需要注意的引數：mysqldump工具備份的話，則建議加上引數：

–single-transaction  –master-data=1 default-character-set=utf8（備註：修改為自己需要的字符集）

l  10.10.1.210：指令碼寫完之後，修改下備份的資料庫名稱指定引數為：-B test，測試下備份指令碼，是否能執行成功，測試完畢再修改回來

l  10.10.1.211：安裝mysql參考上述步驟，設定的my.cnf引數推薦：

server-id =  33060

port = 3306

log-bin = /data/mysqldata/binlog/mysql-bin

log-bin-index = /data/mysqldata/binlog/mysql-bin.index

report_host =

report_port = 3306

innodb_flush_log_at_trx_commit = 2

sync_binlog = 0

binlog-format = mixed

transaction_isolation = REPEATABLE-READ

log-slave-updates

l  10.10.1.211：重新啟動mysqld服務，執行：

mysqladmin –uroot shutdown;mysqld_safe &

l  10.10.1.210/211上：增加複製帳號，執行的SQL語句：

GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%’ IDENTIFIED BY ‘repl#3306’;

l  10.10.1.211上進行10.10.1.211的資料庫備份檔案恢復:

mysql –uroot  –default-character-set=utf8 < ***.bak

l  10.10.1.211的備份恢復完成之後,執行：more ***.bak，可以看到備份檔案有一段類似的語句：

CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000019′, MASTER_LOG_POS=383196279;

可以把此語句複製出來後，修改為：

CHANGE MASTER TO MASTER_HOST=’10.10.1.210′,

MASTER_USER=’repl’,MASTER_PASSWORD=’repl#3306′,

MASTER_LOG_FILE=’mysql-bin.000019′,

MASTER_LOG_POS=383196279,MASTER_PORT=3306;

l  搭建10.10.1.210-> 10.10.1.211的複製，在10.10.1.211上執行：

SHOW MASTER STATUS; — 記錄其日誌檔案的名稱和位置資訊

CHANGE MASTER TO MASTER_HOST=’10.10.1.210′,

MASTER_USER=’repl’,MASTER_PASSWORD=’repl#3306′,

MASTER_LOG_FILE=’mysql-bin.000019′,

MASTER_LOG_POS=383196279,MASTER_PORT=3306;

START SLAVE;SELECT SLEEP(1);SHOW SLAVE STATUS\G

備註：

判斷複製是否搭建成功就看I/O和SQL執行緒是否顯示為OK狀態，另外必須注意的事項，一般此類複製必須放到伺服器壓力最小的時候搭建，因為會從主伺服器上傳送大量的二進位制日誌資訊到從伺服器，從而會加重主伺服器的I/O、網路卡和CPU負載，從而影響使用者的體驗。

l  搭建10.10.1.211->10.10.1.210複製，在10.10.1.210上執行：

CHANGE MASTER TO master_host=’10.10.1.211′,

master_user=’repl’,master_password=’repl#3306′,

master_log_file=’10.10.1.211上檢視到的二進位制日誌檔名稱’,

master_log_pos=10.10.1.211上檢視到的二進位制日誌的位置,master_port=3306;

START SLAVE;SELECT SLEEP(1);SHOW SLAVE STATUS\G

備註：

10.10.1.211上無需要執行的日誌資訊傳送到10.10.1.210上，故任何時間點搭建都可以，只要記住了二進位制日誌檔名稱和位置資訊.

 

u  對線上資料庫伺服器撤銷複製功能步驟

假設雙主複製模式的資料庫伺服器架構：10.10.1.21010.10.1.211，且兩臺伺服器都有不同應用程式連線，進行讀寫操作，以分散讀的壓力，保留伺服器IP：10.10.1.210。

l  10.10.1.211：把所有連線到10.10.1.211的程式資料庫聯接資訊IP地址修改為：

10.10.1.210，然後對此部分的應用，分批重新啟動程式服務，確保其連線更改完畢

l  10.10.1.211：檢查是否已經沒有資料庫聯接了，若沒有，在檢查下是否開啟引數：log-slave-updates，若沒有直接執行SQL語 句：SHOW MASTER STATUS;間隔15分鐘再執行，發現二進位制日誌位置沒有變化，則基本說明沒有任何程式連線到此；若是開啟log-slave-updates引數，則 建議先執行：STOP SLAVE;再用上述辦法觀察；

l  10.10.1.211上：執行停止複製的SQL語句：STOP SLAVE;並且關掉mysqld服務，以防止漏網之魚的程式連線存在，不報錯而無法發現：mysqladmin –uroot –p***  shutdown

l  10.10.1.210上：執行停止複製的SQL語句：STOP SLAVE;RESET SLAVE;

 

u  總結

本想再講一下對於MySQL伺服器遷移的事情，告訴大家生產環境一般是如何做的，比如雙向複製架 構：10.10.1.21010.10.1.211，要進行物理伺服器的替換或搬遷，那麼我們一般會通過複製的方式，把資料複製到新服 務器上，然後再進行停機維護，切換資料庫的方式完成，相信大家閱讀了上面的案例，可以整合借鑑的。

給大家講述了2種生產環境下，搭建複製的做法，以及拆除資料庫複製的做法，其實技術含量都是非常有限的，而且手冊上也能學習到基本操作，但請大家思考這些步驟的意義，是否是為了：減少停機維護的時間，減少對使用者體驗的影響，減少犯錯而導致資料丟失或錯亂等。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15688952/viewspace-700144/，如需轉載，請註明出處，否則將追究法律責任。