欢迎光临,赤鹿小组
记录过程,分享经验

MySQL主从复制常见报错汇总

MySQL主从复制涉及到三个线程:

1.一个运行在主节点(log dump thread)
2.其余两个(I/O thread, SQL thread)运行在从节点,如下图所示:

通过图片我们可以看到,从库在同步的过程中主要涉及两个线程:
IO线程和SQL线程(I/O thread, SQL thread)
所以常见的报错时围绕这两个线程出现
IO线程:把主库binlog日志的内容记录到本机的中继日志文件里
IO线程报错的原因有两个,第一是指定主库信息时参数信息有误;第二时安全限制(包括firewall,selinux)
SQL线程:执行本机中继日志文件里的SQL命令,把数据写进本机数据库里
SQL线程出错原因:一般时执行中继日志文件里sql命令用到的库或表在本机不存在

MySql主从同步问题之Last_IO_Errno 1045

主从不同步了!我们查看一下报错信息:
mysql> show slave status\G;
……

Last_IO_error:报错提示

图片我们可以发现,错误信息码是1045,信息提示:error connecting to master ‘[email protected]:3306’
报错提示在从数据库服务器上指定主库信息参数时有误错误1045通常是与主库授权时的用户或密码不一致造成的,简单来说就是账号密码/权限不够导致的。
解决办法:重新配置主从(也叫修复SQL线程)解决办法:重新配置主从(也叫修复SQL线程)
mysql> stop slave;
mysql> change master to
-> master_host="ip地址",
-> master_user="授权时指定的用户名",
-> master_password="用户密码",
-> master_log_file="binlog日志",
-> master_log_pos=偏移量;
mysql> start slave;

MySql主从同步问题之Last_IO_Errno 1146

看一眼报错:
Replicate_Wild_Ignore_Table:
Last_Errno: 1146
Last_Error: Error 'Table 'ans.test1146' doesn't exist' on query. Default database: 'ans'. Query: 'insert into test1146 values('bigdiao')'
通过报错提示我们可以发现:ans库下缺少test1146表为什么会缺表?
1、导入数据不完全
2、误删
3、单库同步配置没有写对(replicate-wild-do-table=database.%)
解决办法:
A.重建Slave上缺失的表
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> create table test1146(name char(25));
Query OK, 0 rows affected (0.00 sec)

mysql> start slave ;
Query OK, 0 rows affected (0.00 sec)
B.压根没有用到,直接设置忽略
配置文件my.cnf中添加
replicate-ignore-table=mydb.test1146
重启slave/change master to
/etc/init.d/mysqld restart
mysql> change master to replicate-ignore-table=mydb.test1146;

MySql主从同步问题之Last_IO_Errno 1236

从库报错:
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event 'mysql-bin.000019' at 556,<br>the last event read from '/data/3306/mysql-bin.000022' at 107, the last byte read from '/data/3306/mysql-bin.000022' at 107.'
               Last_SQL_Errno: 0
问题原因分析:
错误提示找不到log了!
根据主从同步的原理,slave向master请求binlog时,master不仅把log发送过来
还会发送日志文件是哪个,以及下一次读取的位置信息
可能原因:
主库上的log被重置了,如你执行力reset master
解决方法:
master上找出当前日志文件最接近上一次slave同步的位置。下面可以找到是at 4,也可以使用at 107
[[email protected] ~]# mysqlbinlog /data/3306/mysql-bin.000001 &gt;bin-1.log
[[email protected] ~]# less bin-1.log
[[email protected] ~]# cat bin-1.log
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @[email protected]@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170117 16:55:09 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.32-log created 170117 16:55:09 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
7dt9WA8BAAAAZwAAAGsAAAABAAQANS41LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADt231YEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#170117 16:56:18 server id 1  end_log_pos 188   Query   thread_id=150   exec_time=0 error_code=0
SET TIMESTAMP=1484643378/*!*/;
SET @@session.pseudo_thread_id=150/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database t99
/*!*/;
DELIMITER ;
End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET [email protected]_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
slave执行:
mysql&gt; stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql&gt; change master to master_log_file='mysql-bin.000001',master_log_pos=107;
Query OK, 0 rows affected (0.03 sec)

mysql&gt; start slave;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; show slave status\G;
...
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...

Mysql主从同步问题之Last_IO_Errno 1007

场景:
(master—>slave1—->slave2)此处我是在slave2上执行的reset slave操作
slave2从库执行reset slave
数据库:
mysql&gt; reset slave;
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first
mysql&gt; stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql&gt; reset slave;
Query OK, 0 rows affected (0.01 sec)
mysql&gt; show slave status\G;
No 
No

mysql&gt; start slave;
Query OK, 0 rows affected (0.02 sec)

mysql&gt; show slave status\G;
            ... 
            Slave_IO_Running: Yes
            Slave_SQL_Running: No
            ...
            Last_Errno: 1007
            Last_Error: Error 'Can't create database 'tt'; database exists' on query. Default database: 'tt'. Query: 'create database tt'
解决方法:
找到master点的最新位置信息at 348
[[email protected] ~]# mysqlbinlog /application/mysql/data/mysql-bin.000004 &gt;bin4.log
[[email protected] ~]# tail -10 bin4.log
# at 267
#170117 16:56:18 server id 1  end_log_pos 348   Query   thread_id=150   exec_time=618   error_code=0
SET TIMESTAMP=1484643378/*!*/;
create database t99
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET [email protected]_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[[email protected] ~]# mysqlbinlog /application/mysql/data/mysql-bin.000004 &gt;bin4.log
[[email protected] ~]# tail -10 bin4.log
# at 348
#170117 17:43:17 server id 1  end_log_pos 429   Query   thread_id=152   exec_time=3 error_code=0
SET TIMESTAMP=1484646197/*!*/;
create database t10
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET [email protected]_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[[email protected] ~]#

mysql&gt;  change master to master_log_file='mysql-bin.000004',master_log_pos=348;
Query OK, 0 rows affected (0.01 sec)

mysql&gt; start slave;
Query OK, 0 rows affected (0.00 sec)
完事

Mysql主从同步问题之Last_IO_Errno 1008

其实这个问题的解决办法就有点简单了、、、跳过跳过跳过
1:停止slave
2:跳过错误
3:开启slave
stop slave
set global sql_slave_skip_counter=5
start slave

主从常见错误1062 1032

故障类型:
  1. 1062,主键数据重复;
  2. 1062,复合唯一索引数据重复;
  3. 1032,更新时记录不存在;
  4. 1032,删除时记录不存在;

1062,主键数据重复

修复思路:
直接去从库把重复记录删除, 删除前记得保存下(切记)
delete from test.test_repl where id = 7;
start slave;

Yes
Yes

1062,复合唯一索引数据重复

                    Last_Errno: 1062
                    Last_Error: Could not execute Write_rows event on table test.test_repl; Duplicate entry 'a4-b4' for key 'uiq_index', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log binlog.000057, end_log_pos 256534236
修复思路:
获取索引中的列顺序, uiq_index (a,b)
获取错误中的显示的重复记录,’a4-b4’
使其顺序对应,这边的结果为 a=’a4’, b=’b4’,这边作为条件,去从库删除数据
delete from test.test_repl where a='a4' and b='b4';
start slave;

1032,更新时记录不存在

修复思路
从主库中找到对应的具体数据,然后
插入不存在的数据
分析主库 binlog:
mysqlbinlog  -v --base64-output=decode-row binlog.000000 --start-position=? stop-position=?
拿着分析出来的binlog,加上对应的列名去从库验证数据是否在插入不存在的数据

1032,删除时记录不存在

修复思路
从主库中找到对应的具体数据,然后
插入不存在的数据
分析主库 binlog:
mysqlbinlog  -v --base64-output=decode-row binlog.000000 --start-position=? stop-position=?
拿着分析出来的binlog,加上对应的列名去从库验证数据是否在插入不存在的数据

如何减少 1062、1032 错误出现的次数

  • 从库设置只读
  • 应用账号的权限不能 super 权限
  • 定期使用 pt 工具校验数据一致性
赞(0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址