线上MYSQL同步报错故障处理方法总结(必看篇)

Last_SQL_Error: Could not execute Write_rows event on table hcy.t1; Duplicate entry '2' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 924

mysql> desc hcy.t1;+-------+---------+------+-----+---------+-------+| Field | Type  | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id  | int(11) | NO  | PRI | 0    |    | | name | char(4) | YES |   | NULL  |    | +-------+---------+------+-----+---------+-------+

mysql> delete from t1 where id=2;Query OK, 1 row affected (0.00 sec)mysql> start slave;Query OK, 0 rows affected (0.00 sec)mysql> show slave status/G;……Slave_IO_Running: YesSlave_SQL_Running: Yes……mysql> select * from t1 where id=2;

Last_SQL_Error: Could not execute Update_rows event on table hcy.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000010, end_log_pos 794

/usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000010 | grep -A '10' 794#120302 12:08:36 server id 22 end_log_pos 794 Update_rows: table id 33 flags: STMT_END_F### UPDATE hcy.t1### WHERE###  @1=2 /* INT meta=0 nullable=0 is_null=0 */###  @2='bbc' /* STRING(4) meta=65028 nullable=1 is_null=0 */### SET###  @1=2 /* INT meta=0 nullable=0 is_null=0 */###  @2='BTV' /* STRING(4) meta=65028 nullable=1 is_null=0 */# at 794#120302 12:08:36 server id 22 end_log_pos 821 Xid = 60COMMIT/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

mysql> select * from t1 where id=2;+----+------+| id | name |+----+------+| 2 | BTV | +----+------+1 row in set (0.00 sec)

mysql> insert into t1 values (2,'BTV');Query OK, 1 row affected (0.00 sec)mysql> select * from t1 where id=2;  +----+------+| id | name |+----+------+| 2 | BTV | +----+------+1 row in set (0.00 sec)mysql> stop slave ;set global sql_slave_skip_counter=1;start slave;Query OK, 0 rows affected (0.01 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql> show slave status/G;…… Slave_IO_Running: Yes Slave_SQL_Running: Yes……

Last_SQL_Error: Error initializing relay log position: I/O error reading the header from the binary logLast_SQL_Error: Error initializing relay log position: Binlog has bad magic number; It's not a binary log file that can be used by this version of MySQL

mysql> show slave status/G;*************************** 1. row ***************************       Master_Log_File: mysql-bin.000010     Read_Master_Log_Pos: 1191        Relay_Log_File: vm02-relay-bin.000005        Relay_Log_Pos: 253    Relay_Master_Log_File: mysql-bin.000010       Slave_IO_Running: Yes      Slave_SQL_Running: No       Replicate_Do_DB:      Replicate_Ignore_DB:       Replicate_Do_Table:     Replicate_Ignore_Table:    Replicate_Wild_Do_Table:  Replicate_Wild_Ignore_Table:           Last_Errno: 1593          Last_Error: Error initializing relay log position: I/O error reading the header from the binary log         Skip_Counter: 1     Exec_Master_Log_Pos: 821

Relay_Master_Log_File: mysql-bin.000010Exec_Master_Log_Pos: 821mysql> stop slave;Query OK, 0 rows affected (0.01 sec)mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010',MASTER_LOG_POS=821;Query OK, 0 rows affected (0.01 sec)mysql> start slave;Query OK, 0 rows affected (0.00 sec)mysql> show slave status/G;*************************** 1. row ***************************        Slave_IO_State: Waiting for master to send event         Master_Host: 192.168.8.22         Master_User: repl         Master_Port: 3306        Connect_Retry: 10       Master_Log_File: mysql-bin.000010     Read_Master_Log_Pos: 1191        Relay_Log_File: vm02-relay-bin.000002        Relay_Log_Pos: 623    Relay_Master_Log_File: mysql-bin.000010       Slave_IO_Running: Yes      Slave_SQL_Running: Yes       Replicate_Do_DB:      Replicate_Ignore_DB:       Replicate_Do_Table:     Replicate_Ignore_Table:    Replicate_Wild_Do_Table:  Replicate_Wild_Ignore_Table:           Last_Errno: 0          Last_Error:          Skip_Counter: 0     Exec_Master_Log_Pos: 1191       Relay_Log_Space: 778       Until_Condition: None        Until_Log_File:         Until_Log_Pos: 0      Master_SSL_Allowed: No      Master_SSL_CA_File:       Master_SSL_CA_Path:        Master_SSL_Cert:       Master_SSL_Cipher:         Master_SSL_Key:     Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No        Last_IO_Errno: 0        Last_IO_Error:         Last_SQL_Errno: 0        Last_SQL_Error: Ibbackup

[root@vm01 etc]# more my_local.cnf datadir =/usr/local/mysql/datainnodb_data_home_dir = /usr/local/mysql/datainnodb_data_file_path = ibdata1:10M:autoextendinnodb_log_group_home_dir = /usr/local/mysql/datainnodb_buffer_pool_size = 100Minnodb_log_file_size = 5Minnodb_log_files_in_group=2[root@vm01 etc]# ibbackup /bak/etc/my_local.cnf /bak/etc/my_bak.cnf InnoDB Hot Backup version 3.0.0; Copyright 2002-2005 Innobase OyLicense A21488 is granted to vm01 (chunyang_he@126.com)(--apply-log works in any computer regardless of the hostname)Licensed for use in a computer whose hostname is 'vm01'Expires 2012-5-1 (year-month-day) at 00:00See http://www.innodb.com for further informationType ibbackup --license for detailed license terms, --help for helpContents of /bak/etc/my_local.cnf:innodb_data_home_dir got value /usr/local/mysql/datainnodb_data_file_path got value ibdata1:10M:autoextenddatadir got value /usr/local/mysql/datainnodb_log_group_home_dir got value /usr/local/mysql/datainnodb_log_files_in_group got value 2innodb_log_file_size got value 5242880Contents of /bak/etc/my_bak.cnf:innodb_data_home_dir got value /bak/datainnodb_data_file_path got value ibdata1:10M:autoextenddatadir got value /bak/datainnodb_log_group_home_dir got value /bak/datainnodb_log_files_in_group got value 2innodb_log_file_size got value 5242880ibbackup: Found checkpoint at lsn 0 1636898ibbackup: Starting log scan from lsn 0 1636864120302 16:47:43 ibbackup: Copying log...120302 16:47:43 ibbackup: Log copied, lsn 0 1636898ibbackup: We wait 1 second before starting copying the data files...120302 16:47:44 ibbackup: Copying /usr/local/mysql/data/ibdata1ibbackup: A copied database page was modified at 0 1636898ibbackup: Scanned log up to lsn 0 1636898ibbackup: Was able to parse the log up to lsn 0 1636898ibbackup: Maximum page number for a log record 0120302 16:47:46 ibbackup: Full backup completed![root@vm01 etc]#[root@vm01 etc]# cd /bak/data/[root@vm01 data]# lsibbackup_logfile ibdata1[root@vm01 data]# ibbackup --apply-log /bak/etc/my_bak.cnf InnoDB Hot Backup version 3.0.0; Copyright 2002-2005 Innobase OyLicense A21488 is granted to vm01 (chunyang_he@126.com)(--apply-log works in any computer regardless of the hostname)Licensed for use in a computer whose hostname is 'vm01'Expires 2012-5-1 (year-month-day) at 00:00See http://www.innodb.com for further informationType ibbackup --license for detailed license terms, --help for helpContents of /bak/etc/my_bak.cnf:innodb_data_home_dir got value /bak/datainnodb_data_file_path got value ibdata1:10M:autoextenddatadir got value /bak/datainnodb_log_group_home_dir got value /bak/datainnodb_log_files_in_group got value 2innodb_log_file_size got value 5242880120302 16:48:38 ibbackup: ibbackup_logfile's creation parameters:ibbackup: start lsn 0 1636864, end lsn 0 1636898,ibbackup: start checkpoint 0 1636898ibbackup: start checkpoint 0 1636898InnoDB: Doing recovery: scanned up to log sequence number 0 1636898InnoDB: Starting an apply batch of log records to the database...InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 .....99Setting log file size to 0 5242880ibbackup: We were able to parse ibbackup_logfile up toibbackup: lsn 0 1636898ibbackup: Last MySQL binlog file position 0 1191, file name ./mysql-bin.000010ibbackup: The first data file is '/bak/data/ibdata1'ibbackup: and the new created log files are at '/bak/data/'120302 16:48:38 ibbackup: Full backup prepared for recovery successfully![root@vm01 data]# lsibbackup_logfile ibdata1 ib_logfile0 ib_logfile1

[root@vm02]# mk-table-checksum h=vm01,u=admin,p=123456 h=vm02,u=admin,p=123456 -d hcy -t t1Cannot connect to MySQL because the Perl DBI module is not installed or not found. Run 'perl -MDBI' to see the directories that Perl searches for DBI.If DBI is not installed, try: Debian/Ubuntu apt-get install libdbi-perl RHEL/CentOS  yum install perl-DBI OpenSolaris  pgk install pkg:/SUNWpmdbi

[root@vm02 bin]# mk-table-checksum h=vm01,u=admin,p=123456 h=vm02,u=admin,p=123456 -d hcy -t t1DATABASE TABLE CHUNK HOST ENGINE   COUNT     CHECKSUM TIME WAIT STAT LAGhcy   t1    0 vm02 InnoDB    NULL    1957752020  0  0 NULL NULLhcy   t1    0 vm01 InnoDB    NULL    1957752020  0  0 NULL NULL

[root@vm02 ~]# mk-table-checksum h=vm01,u=admin,p=123456 h=vm02,u=admin,p=123456 -d hcy | mk-checksum-filter    hcy   t2    0 vm01 InnoDB    NULL    1957752020  0  0 NULL NULLhcy   t2    0 vm02 InnoDB    NULL    1068689114  0  0 NULL NULL

mysql> select * from t2;         mysql> select * from t2;  +----+------+               +----+------+| id | name |               | id | name |+----+------+               +----+------+| 1 | a  |               | 1 | a  | | 2 | b  |               | 2 | b  | | 3 | ss  |               | 3 | ss  | | 4 | asd |               | 4 | asd | | 5 | ss  |               +----+------++----+------+               4 rows in set (0.00 sec)5 rows in set (0.00 sec)                      mysql> /! hostname; mysql> /! hostname;            vm02    vm01 [root@vm02 ~]# mk-table-sync --execute --print --no-check-slave --transaction --databases hcy h=vm01,u=admin,p=123456 h=vm02,u=admin,p=123456 INSERT INTO `hcy`.`t2`(`id`, `name`) VALUES ('5', 'ss') /*maatkit src_db:hcy src_tbl:t2 src_dsn:h=vm01,p=...,u=admin dst_db:hcy dst_tbl:t2 dst_dsn:h=vm02,p=...,u=admin lock:0 transaction:1 changing_src:0 replicate:0 bidirectional:0 pid:3246 user:root host:vm02*/;

If C<--transaction> is specified, C<LOCK TABLES> is not used. Instead, lockand unlock are implemented by beginning and committing transactions.The exception is if L<"--lock"> is 3.If C<--no-transaction> is specified, then C<LOCK TABLES> is used for anyvalue of L<"--lock">. See L<"--[no]transaction">.When enabled, either explicitly or implicitly, the transaction isolation levelis set C<REPEATABLE READ> and transactions are started C<WITH CONSISTENTSNAPSHOT>

#!/bin/bash##check_mysql_slave_replication_status###parasum=2help_msg(){ cat << help+---------------------++Error Cause:+you must input $parasum parameters!+1st : Host_IP+2st : Host_Porthelpexit} [ $# -ne ${parasum} ] && help_msg #若参数不够打印帮助信息并退出 export HOST_IP=$1export HOST_PORt=$2MYUSER="root"     MYPASS="123456" MYSQL_CMD="mysql -u$MYUSER -p$MYPASS"MailTitle=""        #邮件主题Mail_Address_MysqlStatus="root@localhost.localdomain"  #收件人邮箱   time1=$(date +"%Y%m%d%H%M%S")time2=$(date +"%Y-%m-%d %H:%M:%S") SlaveStatusFile=/tmp/salve_status_${HOST_PORT}.${time1} #邮件内容所在文件echo "--------------------Begin at: "$time2 > $SlaveStatusFileecho "" >> $SlaveStatusFile #get slave status${MYSQL_CMD} -e "show slave status/G" >> $SlaveStatusFile #取得salve进程的状态 #get io_thread_status,sql_thread_status,last_errno  取得以下状态值 IOStatus=$(cat $SlaveStatusFile|grep Slave_IO_Running|awk '{print $2}')SQLStatus=$(cat $SlaveStatusFile|grep Slave_SQL_Running |awk '{print $2}')  Errno=$(cat $SlaveStatusFile|grep Last_Errno | awk '{print $2}')  Behind=$(cat $SlaveStatusFile|grep Seconds_Behind_Master | awk '{print $2}') echo "" >> $SlaveStatusFile if ["$IOStatus" =="No" ] || [ "$SQLStatus" =="No" ];then  #判断错误类型    if ["$Errno" -eq 0 ];then  #可能是salve线程未启动      $MYSQL_CMD -e "start slave io_thread;start slave sql_thread;"      echo "Cause slave threads doesnot's running,trying start slsave io_thread;start slave sql_thread;" >> $SlaveStatusFile      MailTitle="[Warning] Slave threads stoped on $HOST_IP $HOST_PORT"    elif ["$Errno" -eq 1007 ] || [ "$Errno" -eq 1053 ] || [ "$Errno" -eq 1062 ] || [ "$Errno" -eq 1213 ] || [ "$Errno" -eq 1032 ]/      || [ "Errno" -eq 1158 ] || [ "$Errno" -eq 1159 ] || [ "$Errno" -eq 1008 ];then #忽略此些错误      $MYSQL_CMD -e "stop slave;set global sql_slave_skip_counter=1;start slave;"      echo "Cause slave replication catch errors,trying skip counter and restart slave;stop slave ;set global sql_slave_skip_counter=1;slave start;" >> $SlaveStatusFile      MailTitle="[Warning] Slave error on $HOST_IP $HOST_PORT! ErrNum: $Errno"    else      echo "Slave $HOST_IP $HOST_PORT is down!" >> $SlaveStatusFile      MailTitle="[ERROR]Slave replication is down on $HOST_IP $HOST_PORT ! ErrNum:$Errno"    fifiif [ -n "$Behind" ];then    Behind=0fiecho "$Behind" >> $SlaveStatusFile #delay behind master 判断延时时间if [ $Behind -gt 300 ];then  echo `date +"%Y-%m%d %H:%M:%S"`"slave is behind master $Bebind seconds!" >> $SlaveStatusFile  MailTitle="[Warning]Slave delay $Behind seconds,from $HOST_IP $HOST_PORT"fi if [ -n "$MailTitle" ];then #若出错或者延时时间大于300s则发送邮件    cat ${SlaveStatusFile} | /bin/mail -s"$MailTitle" $Mail_Address_MysqlStatusfi #del tmpfile:SlaveStatusFile> $SlaveStatusFile

#!/bin/sh# check_mysql_slave_replication_status# 参考:http://www.tianfeiyu.com/?p=2062 Usage(){  echo Usage:  echo "$0 HOST PORT USER PASS"} [ -z "$1" -o -z "$2" -o -z "$3" -o -z "$4" ] && Usage && exit 1HOST=$1PORT=$2USER=$3PASS=$4 MYSQL_CMD="mysql -h$HOST -P$PORT -u$USER -p$PASS" MailTitle=""        #邮件主题Mail_Address_MysqlStatus="root@localhost.localdomain"  #收件人邮箱   time1=$(date +"%Y%m%d%H%M%S")time2=$(date +"%Y-%m-%d %H:%M:%S") SlaveStatusFile=/tmp/salve_status_${HOST_PORT}.${time1} #邮件内容所在文件echo "--------------------Begin at: "$time2 > $SlaveStatusFileecho "" >> $SlaveStatusFile #get slave status${MYSQL_CMD} -e "show slave status/G" >> $SlaveStatusFile #取得salve进程的状态 #get io_thread_status,sql_thread_status,last_errno  取得以下状态值  IOStatus=$(cat $SlaveStatusFile|grep Slave_IO_Running|awk '{print $2}')SQLStatus=$(cat $SlaveStatusFile|grep Slave_SQL_Running |awk '{print $2}')  Errno=$(cat $SlaveStatusFile|grep Last_Errno | awk '{print $2}')  Behind=$(cat $SlaveStatusFile|grep Seconds_Behind_Master | awk '{print $2}') echo "" >> $SlaveStatusFile if ["$IOStatus" ="No" -o"$SQLStatus" ="No" ];then  case "$Errno" in  0)    # 可能是slave未启动    $MYSQL_CMD -e "start slave io_thread;start slave sql_thread;"    echo "Cause slave threads doesnot's running,trying start slsave io_thread;start slave sql_thread;" >> $SlaveStatusFile    ;;  1007|1053|1062|1213|1032|1158|1159|1008)    # 忽略这些错误    $MYSQL_CMD -e "stop slave;set global sql_slave_skip_counter=1;start slave;"    echo "Cause slave replication catch errors,trying skip counter and restart slave;stop slave ;set global sql_slave_skip_counter=1;slave start;" >> $SlaveStatusFile    MailTitle="[Warning] Slave error on $HOST:$PORT! ErrNum: $Errno"    ;;  *)    echo "Slave $HOST:$PORT is down!" >> $SlaveStatusFile    MailTitle="[ERROR]Slave replication is down on $HOST:$PORT! Errno:$Errno"    ;;  esacfi if ["$Behind" ="NULL" -o -z "$Behind" ];then  Behind=0fiecho "Behind:$Behind" >> $SlaveStatusFile #delay behind master 判断延时时间if [ $Behind -gt 300 ];then  echo `date +"%Y-%m%d %H:%M:%S"`"slave is behind master $Bebind seconds!" >> $SlaveStatusFile  MailTitle="[Warning]Slave delay $Behind seconds,from $HOST $PORT"fi if [ -n "$MailTitle" ];then #若出错或者延时时间大于300s则发送邮件  cat ${SlaveStatusFile} | /bin/mail -s"$MailTitle" $Mail_Address_MysqlStatusfi #del tmpfile:SlaveStatusFile> $SlaveStatusFile