线上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: Yes Slave_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 = 60 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* 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 log Last_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.000010 Exec_Master_Log_Pos: 821 mysql> 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: 0 Master_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/data innodb_data_home_dir = /usr/local/mysql/data innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/data innodb_buffer_pool_size = 100M innodb_log_file_size = 5M innodb_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 Oy License 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:00 See http://www.innodb.com for further information Type ibbackup --license for detailed license terms, --help for help Contents of /bak/etc/my_local.cnf: innodb_data_home_dir got value /usr/local/mysql/data innodb_data_file_path got value ibdata1:10M:autoextend datadir got value /usr/local/mysql/data innodb_log_group_home_dir got value /usr/local/mysql/data innodb_log_files_in_group got value 2 innodb_log_file_size got value 5242880 Contents of /bak/etc/my_bak.cnf: innodb_data_home_dir got value /bak/data innodb_data_file_path got value ibdata1:10M:autoextend datadir got value /bak/data innodb_log_group_home_dir got value /bak/data innodb_log_files_in_group got value 2 innodb_log_file_size got value 5242880 ibbackup: Found checkpoint at lsn 0 1636898 ibbackup: Starting log scan from lsn 0 1636864 120302 16:47:43 ibbackup: Copying log... 120302 16:47:43 ibbackup: Log copied, lsn 0 1636898 ibbackup: We wait 1 second before starting copying the data files... 120302 16:47:44 ibbackup: Copying /usr/local/mysql/data/ibdata1 ibbackup: A copied database page was modified at 0 1636898 ibbackup: Scanned log up to lsn 0 1636898 ibbackup: Was able to parse the log up to lsn 0 1636898 ibbackup: Maximum page number for a log record 0 120302 16:47:46 ibbackup: Full backup completed! [root@vm01 etc]# [root@vm01 etc]# cd /bak/data/ [root@vm01 data]# ls ibbackup_logfile ibdata1 [root@vm01 data]# ibbackup --apply-log /bak/etc/my_bak.cnf InnoDB Hot Backup version 3.0.0; Copyright 2002-2005 Innobase Oy License 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:00 See http://www.innodb.com for further information Type ibbackup --license for detailed license terms, --help for help Contents of /bak/etc/my_bak.cnf: innodb_data_home_dir got value /bak/data innodb_data_file_path got value ibdata1:10M:autoextend datadir got value /bak/data innodb_log_group_home_dir got value /bak/data innodb_log_files_in_group got value 2 innodb_log_file_size got value 5242880 120302 16:48:38 ibbackup: ibbackup_logfile's creation parameters: ibbackup: start lsn 0 1636864, end lsn 0 1636898, ibbackup: start checkpoint 0 1636898 ibbackup: start checkpoint 0 1636898 InnoDB: Doing recovery: scanned up to log sequence number 0 1636898 InnoDB: 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 .....99 Setting log file size to 0 5242880 ibbackup: We were able to parse ibbackup_logfile up to ibbackup: lsn 0 1636898 ibbackup: Last MySQL binlog file position 0 1191, file name ./mysql-bin.000010 ibbackup: 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]# ls ibbackup_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 t1 Cannot 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 t1 DATABASE TABLE CHUNK HOST ENGINE   COUNT     CHECKSUM TIME WAIT STAT LAG hcy   t1    0 vm02 InnoDB    NULL    1957752020  0  0 NULL NULL hcy   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 NULL hcy   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 is not used. Instead, lock and unlock are implemented by beginning and committing transactions. The exception is if L<"--lock"> is 3. If C<--no-transaction> is specified, then C is used for any value of L<"--lock">. See L<"--[no]transaction">. When enabled, either explicitly or implicitly, the transaction isolation level is set C and transactions are started C 

 #!/bin/bash # #check_mysql_slave_replication_status # # # parasum=2 help_msg(){ cat << help +---------------------+ +Error Cause: +you must input $parasum parameters! +1st : Host_IP +2st : Host_Port help exit } [ $# -ne ${parasum} ] && help_msg #若参数不够打印帮助信息并退出 export HOST_IP=$1 export HOST_PORt=$2 MYUSER="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 > $SlaveStatusFile echo "" >> $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" fi fi if [ -n "$Behind" ];then Behind=0 fi echo "$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_MysqlStatus fi #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 1 HOST=$1 PORT=$2 USER=$3 PASS=$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 > $SlaveStatusFile echo "" >> $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" ;; esac fi if [ "$Behind" = "NULL" -o -z "$Behind" ];then Behind=0 fi echo "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_MysqlStatus fi #del tmpfile:SlaveStatusFile > $SlaveStatusFile