巧用Percona Toolkit解決MySQL主從不同步問題
由於各種原因,mysql主從架構經常會出現資料不一致的情況出現,大致歸結為如下幾類
- 1:備庫寫資料
- 2:執行non-deterministic query
- 3:回滾摻雜事務表和非事務表的事務
- 4:binlog或者relay log資料損壞
資料不同步給應用帶來的危害是致命的,當出現主從資料不一致的情況,常見的應對方法是先把從庫下線,然後找個半夜三更的時間把應用停掉,重新執行同步,如果資料庫的體積十分龐大,那工作量可想而知,會讓人崩潰。本文介紹使用percona-toolkit工具對mysql主從資料庫的同步狀態進行檢查和重新同步。
一:安裝percona-toolkit
# yum -y install perl-Time-HiRes # wget http://www.percona.com/downloads/percona-toolkit/2.2.13/tarball/percona-toolkit-2.2.13.tar.gz # tar -zxvpf percona-toolkit-2.2.13.tar.gz # cd percona-toolkit-2.2.13 # perl Makefile.PL # make # make install
二:修改mysql 的binlog格式binlog_format引數為row格式
mysql binlog日誌有三種格式,分別為Statement, Mixed,以及ROW!
1.Statement:
每一條會修改資料的sql都會記錄在binlog中。
優點:不需要記錄每一行的變化,減少了binlog日誌量,節約了IO,提高效能。(相比row能節約多少效能與日誌量,這個取決於應用的SQL情況,正常同一條記錄修改或者插入row格式所產生的日誌量還小於Statement產生的日誌量,但是考慮到如果帶條件的update操作,以及整表刪除,alter表等操作,ROW格式會產生大量日誌,因此在考慮是否使用ROW格式日誌時應該跟據應用的實際情況,其所產生的日誌量會增加多少,以及帶來的IO效能問題。)
缺點:由於記錄的只是執行語句,為了這些語句能在slave上正確執行,因此還必須記錄每條語句在執行的時候的一些相關資訊,以保證所有語句能在slave得到和在master端執行時候相同 的結果。另外mysql 的複製,像一些特定函式功能,slave可與master上要保持一致會有很多相關問題(如sleep()函式, last_insert_id(),以及user-defined functions(udf)會出現問題).
2.Row
不記錄sql語句上下文相關資訊,僅儲存哪條記錄被修改。
優點: binlog中可以不記錄執行的sql語句的上下文相關的資訊,僅需要記錄那一條記錄被修改成什麼了。所以rowlevel的日誌內容會非常清楚的記錄下每一行資料修改的細節。而且不會出現某些特定情況下的儲存過程,或function,以及trigger的呼叫和觸發無法被正確複製的問題
缺點:所有的執行的語句當記錄到日誌中的時候,都將以每行記錄的修改來記錄,這樣可能會產生大量的日誌內容,比如一條update語句,修改多條記錄,則binlog中每一條修改都會有記錄,這樣造成binlog日誌量會很大,特別是當執行alter table之類的語句的時候,由於表結構修改,每條記錄都發生改變,那麼該表每一條記錄都會記錄到日誌中。
3.Mixed
是以上兩種level的混合使用,一般的語句修改使用statment格式儲存binlog,如一些函式,statement無法完成主從複製的操作,則採用row格式儲存binlog,MySQL會根據執行的每一條具體的sql語句來區分對待記錄的日誌形式,也就是在Statement和Row之間選擇一種.新版本的MySQL中隊row level模式也被做了優化,並不是所有的修改都會以row level來記錄,像遇到表結構變更的時候就會以statement模式來記錄。至於update或者delete等修改資料的語句,還是會記錄所有行的變更。
主從資料庫分別修改my.cnf檔案相關配置項如下:
binlog_format=ROW
三:使用pt-table-checksum工具檢查資料一致性情況
用法參考:
假設192.168.1.205是主庫,192.168.1.207是它的從庫,埠在3306。
1. 先校驗
# pt-table-checksum --user=root --password=123456 \ --host=192.168.1.205 --port=3306 \ --databases=test --tables=t2 --recursion-method=processlist \ --no-check-binlog-format --nocheck-replication-filters \ --replicate=test.checksums
2. 根據校驗結果,只修復192.168.1.207從庫與主庫不一致的地方:
# pt-table-sync --execute --replicate \ test.checksums --sync-to-master h=192.168.1.207,P=3306,u=root,p=123456
3. 修復後,再重新校驗一次。執行第一步的語句即可。
4. 檢查修復結果: 登陸到192.168.1.207,執行如下sql語句返回若為空,則說明修復成功:
SELECT * FROM test.checksums WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)
各引數含義
- –nocheck-replication-filters:不檢查複製過濾器,建議啟用。後面可以用–databases來指定需要檢查的資料庫。
- –no-check-binlog-format:不檢查複製的binlog模式,要是binlog模式是ROW,則會報錯。
- –replicate-check-only:只顯示不同步的資訊。
- –replicate=:把checksum的資訊寫入到指定表中,建議直接寫到被檢查的資料庫當中。
- –databases=:指定需要被檢查的資料庫,多個則用逗號隔開。
- –tables=:指定需要被檢查的表,多個用逗號隔開
- h=127.0.0.1:Master的地址
- u=root:使用者名稱
- p=123456:密碼
- P=3306:埠
下面我們來模擬下主從資料庫不同步情況下的pt-table-checksum,為了方便,這裡我們採用test schema
1: 主庫上建表,插入測試資料
mysql> create table t2 (id int primary key,name varchar(100) not null,salary int); mysql> CREATE PROCEDURE test_insert () BEGIN DECLARE i INT DEFAULT 0; WHILE i<10000 DO INSERT INTO t2 VALUES (i,CONCAT('員工',i), i); SET i=i+1; END WHILE ; END;; mysql> CALL test_insert();
從庫上校驗當前資料的同步情況為正常。
從庫上刪除一半的資料
mysql> delete from t2 where id > 5000; Query OK, 4999 rows affected (0.14 sec) mysql> select count(*) from t2; +----------+ | count(*) | +----------+ | 5001 | +----------+ 1 row in set (0.01 sec)
2:使用pt-table-checksum工具進行校驗:
# pt-table-checksum --user=root --password=123456 \ --host=192.168.1.205 --port=3306 \ --databases=test --tables=t2 --recursion-method=processlist \ --no-check-binlog-format --nocheck-replication-filters \ --replicate=test.checksums
3:登陸從庫進行查詢checksum表
mysql> SELECT * FROM test.checksums WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)
4:使用pt-table-sync工具進行資料重新同步
# pt-table-sync --execute --replicate \ test.checksums --sync-to-master h=192.168.1.207,P=3306,u=root,p=123456
5:從庫上驗證資料,中文“員工”變成了“??”
檢查主庫,發現出現一樣的情況,中文“員工”變成了“??”,猜想和字符集設定相關。
於是檢查資料庫字符集設定,發現test庫字符集非utf8
主從庫my.cnf檔案新增如下配置項後重啟資料庫例項
character_set_client=utf8 character_set_server=utf8
重新執行以上1-4步,發現一切正常!關鍵第4步要加–charset=utf8 引數
# pt-table-sync --execute --replicate \ test.checksums --charset=utf8 \ --sync-to-master h=192.168.1.207,P=3306,u=root,p=123456
相關文章
- MySQL主從不同步問題分析與處理思路MySql
- Percona Toolkit工具連線MySQL 8報錯的解決方案MySql
- mysql主從複製配置與問題解決MySql
- MySQL主從複製問題解決一例MySql
- 如何解決 MySQL 主從延時問題?MySql
- MySQL主從資料庫同步延遲問題怎麼解決MySql資料庫
- mysql主從同步問題整理MySql主從同步
- 如何解決MySQL主從複製太慢的問題MySql
- MySQL基礎運維——percona-toolkit運維工具MySql運維
- 一個案例,教你巧用DMAIC解決“大問題”!AI
- Percona Toolkit 神器全攻略
- 使用percona-toolkit操作MySQL的實用命令小結MySql
- 例項解讀:MySQL並行複製如何解決特定的主從問題?MySql並行
- 巧用 Base62 解決欄位太短的問題
- mysql運維利器percona-toolkit工具之pt-query-digestMySql運維
- Percona-Toolkit 之 pt-archiverHive
- 解決git 不同branch 下node_moudes不同步的問題Git
- 解決Docker容器時區及時間不同步的問題Docker
- MySQL主從複製延遲解決方案MySql
- 高頻面試:如何解決MySQL主從複製延時問題面試MySql
- MySQL 中文 like 問題解決MySql
- mysql大小寫問題解決MySql
- Percona-Toolkit 之 pt-kill 用法
- Percona Toolkit 神器全攻略(效能類)
- Percona Toolkit 神器全攻略(配置類)
- MySQL 主從切換延時高問題分析MySql
- 如何解決MySQL 主從複製資料不一致問題MySql
- MySql中文亂碼問題解決MySql
- Percona-Toolkit 之 pt-kill 低效SQLSQL
- Percona Toolkit 神器全攻略(複製類)
- Percona Toolkit 神器全攻略(系統類)
- Percona Toolkit 神器全攻略(監控類)
- Percona Toolkit 神器全攻略(實用類)
- Oracle資料不同步的問題分析和解決思路Oracle
- 上手MySQL之解決問題:not allowed to connect to this MySQL serverMySqlServer
- windows 安裝 MongoDB 卡主問題解決WindowsMongoDB
- 解決bash: mysql: command not found的問題MySql
- Percona-Toolkit工具包之pt-archiverHive
- Mysql主從架構搭建的時候遇到的問題MySql架構