mysql主從同步中應註意的問題

MYSQL主從同步的作用

 

(1) 數據分佈

 

(2) 負載平衡(load balancing)

 

(3) 備份

 

(4) 高可用性(high availability)和容錯

 

MYSQL主從同步的原理

 

關於MYSQL的主從同步,最主要的是要瞭解MYSQL的主從同步是如何工作的也即主從同步的原理,通過下圖能很明白的指導其工作的過程:

大致描述一下過程:從伺服器的IO線程從主伺服器獲取二進制日志,並在本地保存為中繼日志,然後通過SQL線程來在從上執行中繼日志中的內容,從而使從庫和主庫保持一致。主從同步的詳細過程如下:

 

1. 主伺服器驗證連接。

 

2. 主伺服器為從伺服器開啟一個線程。

 

3. 從伺服器將主伺服器日志的偏移位告訴主伺服器。

 

4. 主伺服器檢查該值是否小於當前二進制日志偏移位。

 

5.  如果小於,則通知從伺服器來取數據。

 

6.  從伺服器持續從主伺服器取數據,直至取完,這時,從伺服器線程進入睡眠,主伺服器線程同時進入睡眠。

 

7. 當主伺服器有更新時,主伺服器線程被激活,並將二進制日志推送給從伺服器,並通知從伺服器線程進入工作狀態。

 

8. 從伺服器SQL線程執行二進制日志,隨後進入睡眠狀態。

 

MYSQL主從同步的搭建實戰

 

主從同步的搭建是一項比較細的技術活,前期做好瞭一些事情會讓你在以後的工作中減少很多工作,搭建的時候需要註意一些問題,一會搭建的時候會一邊搭建一邊介紹需要註意的問題,讓初學者能在剛開始的時候就有效的規避掉一些潛在的問題(MYSQL安裝這裡不做介紹):

 

1.  主從同步環境介紹

操作系統環境:Centos 5.5 64 bit

MYSQL版本:MYSQL 5.1.50

主伺服器的IP:10.1.1.75

從伺服器的IP:10.1.1.76

 

2.   在主伺服器上建立同步帳號

GRANT REPLICATION SLAVE,FILE ON *.* TO 'replication'@'10.1.1.%' IDENTIFIED BY '123456';

FLUSH PRIVILEGES;

 

註意:大傢在設置權限的時候不要將密碼設置過於簡單!

 

3.   從伺服器配置文件的更改

server-id = 2

replicate-wild-ignore-table=mysql.%

log-slave-updates  #這個有需要可以開啟

 

註意:

 

server-id這一項需要認真檢查,一定不能和主伺服器沖突瞭,不然到時候會出現莫民其妙的問題,因為同步的時候會會根據server-id做判斷,如果server-id一樣就不進行同步瞭,不然可能會導致死循環(主主同步或者環狀同步的時候)。

 

有的人會感覺奇怪我這裡為什麼要使用replicate-wild-ignore-table參數,而不是用replicate-do-db或者replicate-ignore-db來過濾需要同步的資料庫和不需要同步的資料庫。這裡有幾個原因:

 

replicate-wild-ignore-table參數能同步所有跨資料庫的更新,比如replicate-do-db或者replicate-ignore-db不會同步類似

use mysql;

UPDATE test.aaa SET amount=amount+10;

 

B. replicate-wild-ignore-table=mysql.%在以後需要添加同步資料庫的時候能方便添加而不需要重新啟動從伺服器的資料庫。因為以後很可能需要同步其他的資料庫。

 

3) auto_increment_increment和auto_increment_offset參數,這 兩個參數一般用在主主同步中,用來錯開自增值, 防止鍵值沖突。

 

4)  –slave-skip-errors參數,不要胡亂使用這些跳過錯誤的參數,除非你非常確定你在做什麼。當你使用這些參數時候,MYSQL會忽略那些錯誤,這樣會導致你的主從伺服器數據不一致。

 

4.  從主伺服器得到一個快照版本

 

如果你的是MYISAM或者既有MYISAM又有INNODB的話就在主伺服器上使用如下命令導出伺服器的一個快照:

mysqldump -uroot -p –lock-tables –events –triggers –routines –flush-logs –master-data=2 –databases test > db.sql

試過隻有INNODB的話就是用如下命令:

mysqldump -uroot -p –single-transaction –events –triggers –routines –flush-logs –master-data=2 –databases test > db.sql

 

這裡需要註意幾個參數的使用:

–single-transaction 這個參數隻對innodb適用。

–databases 後面跟除mysql以後的其他所有資料庫的庫名,我這裡隻有一個test庫。

–master-data 參數會記錄導出快照時候的mysql二進制日志位置,一會會用到。

 

5.  將快照版本還原到從伺服器上

mysqldump -uroot -p -h 10.1.1.76 test < db.sql

 

將快照版本還原到從伺服器上以後,此時從伺服器上的數據和主伺服器的數據是一致的。

 

6.  在從伺服器上使用change master從主伺服器上同步

 

使用grep命令查找到二進制日志的名稱以及位置

[root@ns1 ~]# grep -i "change master" db.sql

— CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=106;

生成CHANGE MASTER語句,然後在從上執行

STOP SLAVE; 

CHANGE MASTER TO MASTER_HOST='10.1.1.75',MASTER_USER='replication',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=106;

START SLAVE;

這樣就完成瞭主從同步的搭建,最後使用SHOW SLAVE STATUS\G;查看Slave_IO_Running和Slave_SQL_Running的狀態,如果都為Yes,就大功告成瞭。

 

註意:不要將同步的信息寫入配置文件中,不方便管理,尤其是有變動需要重啟。

 

MYSQL主從同步的管理

這裡介紹一些管理MYSQL主從同步的命令:

1.  停止MYSQL同步

STOP SLAVE IO_THREAD;    #停止IO進程

STOP SLAVE SQL_THREAD;    #停止SQL進程

STOP SLAVE;                               #停止IO和SQL進程

2.  啟動MYSQL同步

START SLAVE IO_THREAD;    #啟動IO進程

START SLAVE SQL_THREAD;  #啟動SQL進程

START SLAVE;                             #啟動IO和SQL進程

3.   重置MYSQL同步

RESET SLAVE;

 

用於讓從屬伺服器忘記其在主伺服器的二進制日志中的復制位置, 它會刪除master.info和relay-log.info文件,以及所有的中繼日志,並啟動一個新的中繼日志,當你不需要主從的時候可以在從上執行這個操作。不然以後還會同步,可能會覆蓋掉你的資料庫,我以前就遇到過這樣傻叉的事情。哈哈!

 

4.   查看MYSQL同步狀態

 

SHOW SLAVE STATUS;

 

這個命令主要查看Slave_IO_Running、Slave_SQL_Running、Seconds_Behind_Master、Last_IO_Error、Last_SQL_Error這些值來把握復制的狀態。

 

5.  臨時跳過MYSQL同步錯誤

 

經常會朋友mysql主從同步遇到錯誤的時候,比如一個主鍵沖突等,那麼我就需要在確保那一行數據一致的情況下臨時的跳過這個錯誤,那就需要使用SQL_SLAVE_SKIP_COUNTER = n命令瞭,n是表示跳過後面的n個事件,比如我跳過一個事件的操作如下:

STOP SLAVE;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

START SLAVE;

 

6.  從指定位置重新同步

 

有的時候主從同步有問題瞭以後,需要從log位置的下一個位置進行同步,相當於跳過那個錯誤,這時候也可以使用CHANGE MASTER命令來處理,隻要找到對應的LOG位置就可以,比如:

CHANGE MASTER TO MASTER_HOST='10.1.1.75',MASTER_USER='replication',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=106;

START SLAVE;

 

MYSQL主從同步的管理經驗介紹

 

1.   不要亂使用SQL_SLAVE_SKIP_COUNTER命令。

 

這個命令跳過之後很可能會導致你的主從數據不一致,一定要先將指定的錯誤記錄下來,然後再去檢查數據是否一致,尤其是核心的業務數據。

 

2.   結合percona-toolkit工具pt-table-checksum定期查看數據是否一致。

 

這個是DBA必須要定期做的事情,呵呵,有合適的工具何樂而不為呢?另外percona-toolkit還提供瞭對資料庫不一致的解決方案,可以采用pt-table-sync,這個工具不會更改主的數據。還可以使用pt-heartbeat來查看從伺服器的復制落後情況。

 

3.   使用replicate-wild-ignore-table選項而不要使用replicate-do-db或者replicate-ignore-db。

 

原因已經在上面做瞭說明。

 

4.   將主伺服器的日志模式調整成mixed。

 

5.   每個表都加上主鍵,主鍵對資料庫的同步會有影響尤其是居於ROW復制模式。

You May Also Like