盾怪网教程：是一个免费提供流行杀毒软件教程、在线学习分享的学习平台！

详细说明在线上MYSQL同步报错故障处理方法代码总结

时间：2025/3/15作者：未知来源：盾怪网教程人气：

[摘要]+----+------+1 row in set (0.00 sec)mysql> stop slave ;set global sql_slave_skip_counter=1;start...
+----+------+ 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 ……

中继日志损坏

slave的中继日志relay-bin损坏。

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

手工修复

解决方法：找到同步的binlog和POS点，然后重新做同步，这样就可以有新的中继日值了。

例子：

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

Slave_IO_Running ：接收master的binlog信息

Master_Log_File
Read_Master_Log_Pos

Slave_SQL_Running：执行写操作

Relay_Master_Log_File
Exec_Master_Log_Pos

以执行写的binlog和POS点为准。

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

各种大招都用上了，无奈slave数据丢失过多，ibbackup（需要银子）该你登场了。

Ibbackup热备份工具，是付费的。xtrabackup是免费的，功能上一样。

Ibbackup备份期间不锁表，备份时开启一个事务（相当于做一个快照），然后会记录一个点，之后数据的更改保存在ibbackup_logfile文件里，恢复时把ibbackup_logfile 变化的数据再写入到ibdata里。

Ibbackup 只备份数据（ ibdata、.ibd ），表结构.frm不备份。

下面一个演示例子：

备份：ibbackup /bak/etc/my_local.cnf /bak/etc/my_bak.cnf

恢复：ibbackup --apply-log /bak/etc/my_bak.cnf

[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

把ibdata1 ib_logfile0 ib_logfile1拷贝到从，把.frm也拷贝过去，启动MySQL后，做同步，那个点就是上面输出的：

ibbackup: Last MySQL binlog file position 0 1191, file name ./mysql-bin.000010
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010',MASTER_LOG_POS=1191;

Maatkit工具包

简介

maatkit是一个开源的工具包，为mysql日常管理提供了帮助。目前，已被Percona公司收购并维护。其中：

mk-table-checksum是用来检测master和slave上的表结构和数据是否一致。

mk-table-sync是发生主从数据不一致时，来修复的。

这两个工具包，没有在现网实际操作的经验，这里仅仅是新技术探讨和学术交流，下面展示下如何使用。

[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

提示缺少perl-DBI模块，那么直接 yum install perl-DBI。

[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

如果表数据不一致，CHECKSUM的值是不相等的。

解释下输出的意思：

DATABASE：数据库名
TABLE：表名
CHUNK：checksum时的近似数值
HOST：MYSQL的地址
ENGINE：表引擎
COUNT：表的行数
CHECKSUM：校验值
TIME：所用时间
WAIT：等待时间
STAT：MASTER_POS_WAIT()返回值
LAG：slave的延时时间

如果你想过滤出不相等的都有哪些表，可以用mk-checksum-filter这个工具，只要在后面加个管道符就行了。

[root@vm02 ~]# mk-table-checksum h=vm01,u=admin,p=123456 h=vm02,u=admin,p=123456 -d hcy

关键词：详细说明在线上MYSQL同步报错故障处理办法代码总结


Copyright © 2012-2018 盾怪网教程(http://www.dunguai.com) .All Rights Reserved 网站地图 友情链接

免责声明：本站资源均来自互联网收集 如有侵犯到您利益的地方请及时联系管理删除，敬请见谅!

QQ:1006262270   邮箱:kfyvi376850063@126.com   手机版