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復制模式。