mysql 主從故障恢複[MySQL FAQ]系列 — MySQL複制中slave延遲監控

有碰到這樣的主從故障，都是yes和0，但是exec和relay相差太多，從一直卡住在update

有能力的話，深入分析relay-log和master binlog來判斷到底是哪條語句，卡住了主從複制

正常的監控圖

<a>IO thread read position</a>

<a>SQL thread execute position</a>

兩個必須一緻

兩者對應的是

Exec_Master_Log_Pos: 18036251

Read_Master_Log_Pos: 18037252

主從不同步解決方案

1.簡單，慢

重建立立m-s，讓它自動同步資料

stop slave;reset slave all;change master to master_host='ip', master_user='replusers',master_password='xx'

2.快速

重新mysqldump導到備庫，然後建立m-s

3.pt工具，但是pt我這邊支援不了mysql5.7

高端的解決辦法

1、可以在MASTER上維護一個監控表，它隻有一個字段，存儲這最新最新時間戳（高版本可以采用event_scheduler來更新，低版本可以用cron結合自動循環腳本來更新），在SLAVE上讀取該字段的時間，隻要MASTER和SLAVE的系統時間一緻，即可快速知道SLAVE和MASTER延遲差了多少。不過，在高并發的系統下，這個時間戳可以細化到毫秒，否則哪怕時間一緻，也是有可能會延遲數千個binlog event的。

2、網友（李大玉，QQ：407361231）細心支出上面的計算延遲有誤，應該是mysql-bin.000009的最大事件數減去已經被執行完的事件數，即1073742063 – 654409041= 419333022個binlog event，再加上mysql-bin.000010這個binlog已經産生的107374193個binlog event，共526707215 個binlog event。

優化

5.7的優化

http://www.cnblogs.com/zengkefu/p/5669887.html

slave節點開啟并發複制（slave_parallel_workers＝3）

MySQL 5.7 引入Enhanced Muti-threaded slaves,當slave配置slave_parallel_workers>0并且global.slave_parallel_type＝‘LOGICAL_CLOCK’,可支援一個schema下，slave_parallel_workers個的worker線程并發執行relay log中主庫送出的事務。但是要實作以上功能，需要在master機器标記binary log中的送出的事務哪些是可以并發執行，雖然MySQL 5.6已經引入了binary log group commit，但是沒有将可以并發執行的事務标記出來。

假設當slave_parallel_workers=7時，Coordinator線程（SQL線程）配置設定這一組事務到worker中排隊去執行。這裡可以看出增加master庫binary log group commit組中事務的數量可以提高slave機器并發處理事務的數量，MySQL5.7引入 binlog_group_commit_sync_delay和 binlog_group_commit_sync_no_delay_count參數即提高binary log組送出并發數量。MySQL等待binlog_group_commit_sync_delay毫秒的時間直到binlog_group_commit_sync_no_delay_count個事務數時，将進行一次組送出

MySQL 5.7 GA版本推出的 Enhanced Multi-threaded Slaves功能，徹底解決了之前版本主備資料複制延遲的問題，開啟該功能參數如下：

1. # slave機器

2. slave-parallel-type=LOGICAL_CLOCK

3. ＃slave-parallel-type=DATABASE #相容MySQL 5.6基于schema級别的并發複制

4. slave-parallel-workers=16 ＃開啟多線程複制

5. master_info_repository=TABLE

6. relay_log_info_repository=TABLE

7. relay_log_recovery=ON

精确方式如在master上表中定期插入timestamp,在slave上通過系統時間和timestamp進行對比得到精确slave延遲），簡單看了下代碼time_diff（Seconds_Behind_Master值）

stop slave;

change master to master_heartbeat_period = 10;

set global slave_net_timeout = 25;

start slave;

主從延遲

主備基本無延遲的時候，主庫執行了一個大事務，你會發現備庫延遲突然很大，然後又沒了，"延遲跳躍"

主庫的網絡有問題，io thread拉binlog的速度很慢，備庫實際有很大的延遲，而sql thread執行的時候永遠是hot log，這個時候會出現，備庫一直有很大的延遲，突然一下延遲沒了(=0)，接下去延遲又變得很大

到這裡Seconds_Behind_Master的計算就結束了。總結起來就是Seconds_Behind_Master的計算有3部分組成，備庫目前時間，sql thread最近一次執行的event的時間，主備系統的時間差。備庫出現延遲跳躍，可能是大事務引起，也可能是網絡問題

可能主伺服器已經有大量操作，但是從伺服器的io線程并沒有擷取到這些操作，而從機的sql線程和io線程之間時間差又為0

Seconds_Behind_Master值是slave io thread目前已經擷取的master binlog pos - slave sql thread目前已經執行完的master binlog pos

設計實驗來驗證，用取一天前的備份集新做了一個slave，change master且start slave後，Seconds_Behind_Master顯示為60000多秒（20小時左右），顯然是master目前系統時間戳 - slave sql thread目前已經執行完的master binlog pos的結果

1.slave,show global variables like 'GTID_EXECUTED';

2.master上mysqldump all database

mysqldump -uroot -p'xx' --all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs --socket=xx > $DumpFile

發送到slave，mysql -uroot -p'xx' <$DumpFile

3.stopslave;reset slave

4.change master to master_host='ip', master_user='replusers',master_password='xx', master_auto_position=1;

5.start slave;

資料庫核心月報－ 2015 / 10

http://mysql.taobao.org/monthly/2015/10/05/

stop slave; set global slave_parallel_workers=0; start slave;

http://imysql.com/2014/07/31/mysql-faq-exception-replication-with-gtid.shtml?utm_source=tuicool&utm_medium=referral

[MySQL FAQ]系列 — 5.6版本GTID複制異常處理一例

碰到1236

http://www.fromdual.com/replication-troubleshooting-classic-vs-gtid

mysqldump -u root -p --all-databases --flush-privileges --single-transaction --flush-logs --triggers --routines --events -B>all.sql

--all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs --socket=/opt/3306/mysql.sock --set-gtid-purged=OFF >all.sql

head -n 50 all.sql|grep PURGED

reset master;

這步啟動有可能會報錯

如果還不行，就去master上reset master，重新生成uuid

1236

參考 http://suifu.blog.51cto.com/9167728/1845457

使用：slave_exec_mode參數

slave-skip-errors = 1062,1032,1060

end_log_pos 有了它，根據pos值，直接就能找到，找到delete那條資料，反做（變成insert）

SET @@GLOBAL.GTID_PURGED = 'b30dcce8-3395-11e6-902b-0050569d58f6:1-24414141';

begin;commit;

set session gtid_next=automatic;

其他的一些資料

參考 http://ylw6006.blog.51cto.com/470441/1788809

注意

1.事物不能跳過的，絕對不能跳過

2.master上不要使用reset master，如果操作，slave不知道從哪裡位置進行同步。因為master删除了的gtid不是以前的同步的位置了。

3.slave使用reset slave之前必須使用stop slave 指令将複制程序停止

執行reset slave，其實是把master.info和relay-log.info檔案給删除，但裡面的同步資訊還在，那麼可以用這個方法，讓其清除的徹徹底底

reset slave all;

以下的問題屬于比較基本的主從恢複，深入就要學習分析relay-log和binlog

在使用Mysql的主從複制架構中，有兩個比較頭疼的問題：

1、主從資料不同步後如何處理

2、主從同步延遲問題如何解決

本文将根據實際案例來分析下問題1，至于問題2多數文檔介紹的辦法是啟用多線程複制來解決，言歸正傳，這裡的問題1還可以細分成兩種情況。

1、Slave_IO_Running和Slave_SQL_Running在YES情況下，主從資料不同步如何處理？

2、Slave_SQL_Running在NO情況下，主從資料不同步如何處理？

這個辦法其實回答了前面的問題1，Slave_IO_Running和Slave_SQL_Running在YES情況下，主從資料不同步如何處理

# yum -y install perl-TermReadKey

# wget ftp://ftp.netbsd.org/pub/pkgsrc/distfiles/maatkit-7540.tar.gz

# tar -zxvpf maatkit-7540.tar.gz

# cd maatkit-7540

# perl Makefile.PL

# make && make install

# mk-table-checksum h=192.168.115.6,u=root,p=123456,P=3306 \

h=192.168.115.7,u=root,p=123456,P=3306 -d test | mk-checksum-filter

# mk-table-checksum h=192.168.115.6,u=root,p=123456,P=3306 \

h=192.168.115.7,u=root,p=123456,P=3306 -d test

如果主從資料不一緻則采用mk-table-sync進行資料同步

# mk-table-sync --execute --print --no-check-slave --transaction --databases test \

h=192.168.115.6,u=root,p=123456 h=192.168.115.7,u=root,p=123456

采用跳過這個事務的辦法來處理主從資料庫不同步問題

在未啟用GTID複制的情況下采用下面的方法跳過事務：

mysql>slave stop;

mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; //跳過一個事務

mysql>slave start;

啟用GTID

在show slave status \G;輸出中的最後幾條裡面，

Retrieved_Gtid_Set項：記錄了relay日志從Master擷取了binlog日志的位置

Executed_Gtid_Set項：記錄本機執行的binlog日志位置（如果是從機，包括Master的binlog日志位置和slave本身的binlog日志位置）

我們要跳過事務的GTID在錯誤日志中有記錄

# tail -f /home/mydata/localhost.localdomain.err

mysql> set session gtid_next='bd9e9912-2bc7-11e6-bade-000c29b8871c:1440';

mysql> begin;commit;

mysql> set session gtid_next=automatic;

mysql> start slave;

mysql> show slave status \G;

驗證從庫資料是否和主庫一緻

mysql> select * from test.asm_user;

前面模拟了Slave_SQL_Running在NO情況下，主從資料不同步情況的處理過程，在現實的環境中，往往情況要複雜的多，下面分享一則記憶體開發庫因為斷電導緻主從資料不一緻的故障處理：

1、因為電源故障，導緻主從資料庫全部當機，電源恢複後，主庫啟動正常，從庫無法啟動，通過分析日志發現可能是電源故障導緻從庫的固态盤異常，許多的binlog檔案權限出現？？？，這些檔案甚至無法正常檢視

1、通過fsck -y進行檔案系統校驗修複壞塊，修複完成後從庫資料庫可以啟動，但開啟複制程序的時候報中繼日志丢失

2、在沒有辦法的情況下，采用主庫dump資料，從庫重新source的辦法線上重做主從資料同步。整個操作過程中，主庫的資料不斷的寫入。

下面是大緻的步驟：

3.1、主庫導出全庫資料，注意一定要使用--single-transaction參數

# /usr/local/mysql/bin/mysqldump --all-databases --single-transaction --triggers --routines > /tmp/1.sql

3.2、将備份檔案拷貝到從庫進行source

3.3、開啟從庫的複制程序

mysql> change master to master_host='192.168.1.15',

master_user='rep1',master_password='123456',MASTER_AUTO_POSITION=1;

0.Big transaction and MySQL replication lag

After running the script, I do really found something astonishing, during that time the master had one transaction per minute, and each was comparatively big, nearly 10k delete operations for one transaction

After the discussion with developer, we decided to stop the job. Besides that, we consider to increase the thread(slave_parallel_worker) of slave since we currently use MySQL 5.7 which is its big selling point.

解決方法:Identifying useful info from MySQL row-based binary logs

參考 http://www.cnblogs.com/xiaoyanger/p/5576652.html

1.Got fatal error 1236 from master when reading data from binary log: 'unknown error reading log event on the master; the first event '' at 4, the last event read from '/opt/3306/mysql-bin.000001' at 308, the last byte read from '/opt/3306/mysql-bin.000001' at 308.'

在source那邊，執行：

flush logs;

show master status;

記下File, Position。

在target端，執行：

CHANGE MASTER TO MASTER_LOG_FILE='testdbbinlog.000008',MASTER_LOG_POS=107;

slave start;

show slave status \G

一切正常。

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replica'

3.Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

4.MySQL master 當機導緻slave資料比master多的case

5.reset master導緻主從GTID不一緻的處理方法

為什麼從庫的GTID會比主庫的小呢？應該是有同學在從庫做了reset master的操作，重置了GTID導緻的

由于這是從從庫同步到主庫的資訊，而從庫是隻讀的，隻會産生備份的臨時資訊。是以這些事務都可以跳過。但由于從庫的GTID比主庫上的小，跳過了1413号事務，後面還會有更多的類似的問題。是以還需要在從庫上将事務号推高到跟主庫一緻的水準，這樣後面從庫産生的事務号都不會再跟主庫上的有沖突。推高事務号可以通過執行空事務達到：

set gtid_next='44d3c576-ca26-11e3-a90a-a0369f38458a:1421;

set gtid_next='44d3c576-ca26-11e3-a90a-a0369f38458a:1422;

…………………………………

set gtid_next='44d3c576-ca26-11e3-a90a-a0369f38458a:7383;

set gtid_next='AUTOMATIC';

檢查主庫relaylog，發現7388、7389這兩個事務主庫是有擷取到的，但7388看起來跳過沒執行，為什麼7389這次執行卻沒報錯呢？

檢查主庫的binlog。

可以發現：7388的确是被跳過了，而7389這個本該是insert的卻變成了空事務！

這個問題比較坑，看了下文檔，mysql 在row format的情況下，對于temporary tab的處理情況如下：

1、不進行tempory table 複制（這個應該是指建立）

2、從上面的binlog中也可以看到，對于temporary tab 的dml操作，在複制的時候，用空事務代替。

6.大坑:用SQLyog連mysql的部分操作不能同步到從庫

解析日志發現relaylog裡面有相關的alter table的操作，但是從庫就是沒有執行。比較奇怪的是另外那個從庫很正常。

通過這次的問題，發現用工具連資料庫還是會有很多隐患，要從根本上解決這個問題隻能嚴格控制開發權限，隻給他們select權限。

dba平時操作資料庫的時候盡量在指令行操作

7.GTID複制報錯處理：Last_Error: Error 'Can't drop database 'test'; database doesn't exist' on query

去檢視主庫上的binlog日志，查找drop database test相關的日志

要了解GTID代表的是什麼，每個事務的送出都代表着一個GTID的生成，正如其全名：全局事務ID(global transaction identifier)，是以如果想跳過導緻錯誤的事務不執行的話，需要找到對應事務的gtid号，設定(set gtid_next="....")并送出空事務後重新啟用自動模式後，再重新開機slave就可以，并不是每個導緻錯誤的事務都是binlog中最後一個事務

了解了這個後就能明白，之前的處理還是太複雜，其實直接看show slave satus\G的結果，看這兩個參數的值：

Slave_IO_Running: Yes

Slave_SQL_Running: No

Last_Error: Error 'Can't drop database 'test'; database doesn't exist' on query. Default database: 'test'. Query: 'drop database test'

Retrieved_Gtid_Set: 988b8684-3e21-22e6-a801-24505689c77d:1-9

Executed_Gtid_Set:

能夠看到，Executed_Gtid_Set是空的，而且GTID是肯定開啟了的，是以，說明日志傳過來後壓根還沒開始執行，是以，第一個事務就已經被卡住了，首先應該跳過的事務号就是1，也不必再去看日志了

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> set gtid_next="988b8684-3e21-22e6-a801-24505689c77d:1";

mysql> begin;commit;

Query OK, 0 rows affected (0.01 sec)

mysql> set gtid_next="AUTOMATIC";

mysql> start slave;

8.Error 'Cannot add or update a child row: a foreign key constraint fails

mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; #跳過一個事務

mysql>start slave;

接下來就是主從資料不一緻的問題，可以使用pt-table-checksum來檢查下不一緻的資料，再進行同步，具體步驟如下

在主庫執行：

mysql>GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE,CREATE,DELETE,INSERT,UPDATE ON *.* TO 'USER'@'MASTER_HOST' identified by 'PASSWORD';

注：建立使用者，這些權限都是必須的，否則會報錯

shell> ./pt-table-checksum --host='master_host' --user='user' --password='password' --port='port' --databases=zabbix --ignore-tables=ignore_table --recursion-method=processlist

注：(1)因為涉及到的表太多，檢視後發現很多表都有外鍵關聯，錯綜複雜，而且因為是監控表，即使丢失一些也沒什麼關系，是以查出較大的且沒有外鍵關聯的表用ignore-tables選項排除，對其他表進行比對，如果表比較少的話直接指定--TABLES

(2)recursion-method如果不設的話，會報錯：Diffs cannot be detected because no slaves were found. 其參數有四：processlist/hosts/dsn/no,用來決定查找slave的方式是show full processlist還是show slave hosts還是直接給出slave資訊，具體用法在另一随筆pt-table-checksum介紹中詳述

shell>./pt-table-sync --print --replicate=percona.checksums h=master_host,u=user,p=password,P=port h=slave_host,u=user,p=password,P=port --recursion-method=processlist >pt.log

注：最好使用--print，不要直接使用--execute，否則如果弄出問題，就更麻煩了，列印出直接執行的語句，去從庫執行就好了

複制代碼

将pt.log傳到從庫，直接執行，然後再次在主庫上進行一緻性檢查，如果還有不一緻的資料，記得登入mysql去把checksums表清空，然後再次進行檢查同步，直到沒有不一緻的資料

本文轉自 liqius 51CTO部落格，原文連結：http://blog.51cto.com/szgb17/1828390，如需轉載請自行聯系原作者

mysql 主從故障恢複[MySQL FAQ]系列 — MySQL複制中slave延遲監控

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述