MYSQL管理之主從同步管理
MYSQL主從同步架構是目前使用最多的資料庫結構描述之一,尤其是負載比較大的網站,因此對於主從同步的管理也就顯得非常重要,新手往往在出現主從同步錯誤的時候不知道如何入手,這篇文章就是根據自己的經驗來詳細敘述mysql主從的管理。
MYSQL主從同步的作用
(1) 資料分布
(2) Server Load Balancer(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 #這個有需要可以開啟
注意:
1)server-id這一項需要認真檢查,一定不能和主伺服器衝突了,不然到時候會出現莫民其妙的問題,因為同步的時候會會根據server-id做判斷,如果server-id一樣就不進行同步了,不然可能會導致死迴圈(主主同步或者環狀同步的時候)。
2)有的人會感覺奇怪我這裡為什麼要使用replicate-wild-ignore-table參數,而不是用replicate-do-db或者replicate-ignore-db來過濾需要同步的資料庫和不需要同步的資料庫。這裡有幾個原因:
A.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來查看從伺服器的複製落後情況。具體的請查看:http://blog.chinaunix.net/uid-20639775-id-3229211.html。
3.使用replicate-wild-ignore-table選項而不要使用replicate-do-db或者replicate-ignore-db。
原因已經在上面做了說明。
4.將主伺服器的記錄模式調整成mixed。
5.每個表都加上主鍵,主鍵對資料庫的同步會有影響尤其是居於ROW複製模式。