MySQL主从复制问题总结及排查过程

目录
  • 一、概述
  • 二、mysql主从复制原理
    • 1.MYSQL主从复制过程
  • 三、问题及解决方法
    • 1.show slave status \G 显示如下报错信息
    • 2.根据提示信息定位报错位置
  • 四、通用解决方法
    • 1. 跳过指定数量的事务
    • 2. 跳所有错误或指定类型的错误

一、概述

mysql主从是常用的高可用架构之一,也是使用最广泛的的系统架构。在生产环境中mysql主从复制有时会出现复制错误问题。MySQL主从复制中的问题(Coordinator stopped beacause there were errors in the workers......)

二、mysql主从复制原理

mysql主从复制是一个异步复制过程(总体感觉是实时同步的),mysql主从复制整个过程是由三个线程完成。slave端有两个线程(SQL线程和IO线程),Master端有另一个(IO线程)。

1.MYSQL主从复制过程

  • 在Slave服务器上执行start slave,开启主从复制开关。
  • 此时,Slave 服务器上的 IO 线程通过 Master 服务器上授权复制用户的请求连接到 Master 服务器。它还请求从 binlog 日志文件的指定位置发送 binlog 日志内容。 (配置主从复制任务时执行change master命令时指定日志文件名和位置)
  • Master服务器收到Slave服务器IO线程的请求后,Master服务器上的IO线程是基于Slave的。 服务器的IO线程请求的信息在指定binlog日志文件的指定位置后读取binlog日志信息,然后返回给Slave端IO线程。除了binlog日志内容,在日志内容返回后Master服务器端还有一个新的binlog。 binlog 中的文件名和下一个指定的更新位置。
  • 当 Slave 服务器的 IO 线程从 Master 服务器获取 IO 线程发送的日志内容、日志文件和位置点时,添加 binlog。日志内容依次写入Slave端自身的relay log文件(mysql-relay-bin.xxxxxx)的末尾。并将新的binlog文件名和位置记录到master-info文件中,以便下次读取Master端新的binlog日志时,可以告诉Master服务器从新的binlog日志中从哪个文件以及从哪里开始请求新的binlog日志内容.
  • Slave server端的SQL线程实时检测本地relay log中新增的日志内容,及时relay log。 该文件的内容被解析成在Master端执行的SQL语句的内容,在Slave服务器本身按照语句的顺序执行SQL的应用。
  • 经过上述过程,可以保证在Master和Slave端执行相同的SQL语句。当复制状态正常时,Master 端和lave端的数据是完全一致的。

三、问题及解决方法

1.show slave status \G 显示如下报错信息

Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction ...

2.根据提示信息定位报错位置

情况一:"**Delete_rows"**

select * from performance_schema.replication_applier_status_by_worker \G

原因:在master上删除一条记录,而slave上找不到。

解决方法: 由于master要删除一条记录,而slave上找不到故报错,这种情况主上都将其删除了,那么从机可以直接跳过。

stop slave;
set global sql_slave_skip_counter=1;
start slave;

如上命令若报错:ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction或者可以换用如下命令:

STOP SLAVE;
SET @@SESSION.GTID_NEXT= 'f396f867-d755-11xxx85-005xxxxxb5a:264261655'    --在session里设置gtid_next,即跳过这个GTID
BEGIN; COMMIT;       --设置空事物
SET SESSION GTID_NEXT = AUTOMATIC;     -- 恢复GTID
START SLAVE;xxxx

情况二:"Duplicate "

Last_SQL_Error: Could not execute Write_rows event on table xxx;
Duplicate entry 'xxx' for key 'PRIMARY',

原因:在slave已经有该记录,又在master上插入了同一条记录

解决方法:在从库上删除该记录,或者跳过该记录。然后在master上和slave上再分别确认一下。

情况三:"Update_rows" (还未碰到 待验证)

Last_SQL_Error: Could not execute Update_rows event on table xxx;
Can't find record in 'xxx',

参考原因:master上更新一条记录,而slave上找不到,丢失了数据。

参考方法:在master上,用mysqlbinlog 分析下出错的binlog日志在干什么。

/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*/;

slave上,查找下更新后的那条记录,应该是不存在的。

mysql> select * from t1 where id=2; Empty set (0.00 sec)

然后再到master查看

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

把丢失的数据在slave上填补,然后跳过报错即可。

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

四、通用解决方法

mysql主从复制,经常会遇到错误而导致slave端复制中断,这个时候一般就需要人工干预,跳过错误才能继续 跳过错误有两种方式

1. 跳过指定数量的事务

mysql>slave stop;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1        #跳过一个事务
mysql>slave start

2. 跳所有错误或指定类型的错误

修改mysql的配置文件,通过slave_skip_errors参数来跳所有错误或指定类型的错误

vi /etc/my.cnf
[mysqld]
#slave-skip-errors=1062,1053,1146 #跳过指定error no类型的错误
#slave-skip-errors=all #跳过所有错误

到此这篇关于MySQL主从复制问题总结及排查过程的文章就介绍到这了,更多相关MySQL主从复制内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • Mysql数据库的主从复制与读写分离精讲教程

    目录 前言 一.MySQL主从复制 1.支持的复制类型 2.主从复制的工作过程是基于日志 3.请求方式 4.主从复制的原理 5.MySQL集群和主从复制分别适合在什么场景下使用 6.为什么使用主从复制.读写分离 7.用途及条件 8.mysql主从复制存在的问题 9.MySQL主从复制延迟 二.主从复制的形式 三.读写分离 1.原理 2.为什么要读写分离呢? 3.什么时候要读写分离? 5.目前较为常见的MySQL读写分离 四.案例实施 1.案例环境 2.实验思路(解决需求) 3.准备 4.搭建My

  • MySQL中主从复制重复键问题修复方法

    -------------------quote begin------------------------ 3. If you decide that you can skip the next statement from the master, issue the following statements: mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n; mysql> START SLAVE; The value of n should be

  • 深入mysql主从复制延迟问题的详解

    面试mysqldba的时候遇到一个题: 描述msyql replication 机制的实现原理,如何在不停掉mysql主库的情况下,恢复数据不一致的slave的数据库节点? MySQL的复制(replication)是一个异步的复制,从一个MySQL instace(称之为Master)复制到另一个MySQL instance(称之Slave).实现整个复制操作主要由三个进程完成的,其中两个进程在Slave(Sql进程和IO进程),另外一个进程在Master(IO进程)上. 引用新浪某位大牛的话

  • MySQL数据库主从复制与读写分离

    目录 一.主从复制 主从复制三线程 主从复制的过程: 主从复制的策略: 主从复制高延迟 二.读写分离 读写分离概念 读写分离原因与场景 总结 一.主从复制        主从复制:在实际的生产中,为了解决Mysql的单点故障以及提高MySQL的整体服务性能,一般都会采用主从复制.即:对数据库中的数据.语句做备份. 主从复制三线程        Mysql的主从复制中主要有三个线程:master(binlog dump thread).slave(I/O thread .SQL thread),M

  • 一篇文章看懂MySQL主从复制与读写分离

    目录 引言 一.MySQL主从复制 1.MySQL的复制类型 2.MySQL主从复制的原理 3.MySQL主从复制延迟 二.MySQL读写分离 1.常见的 MySQL 读写分离分 2.MySQL 读写分离原理 三.MySQL主从复制与读写分离配置步骤 1.搭建环境 2.解决需要 3.准备阶段(关闭防火墙及控制访问机制) 4.搭建MySQL主从复制 5.搭建MySQL读写分离步骤 6.测试测试读写分离 总结 引言 企业中的业务通常数据量都比较大,而单台数据库在数据存储.安全性和高并发方面都无法满足

  • MySQL主从复制之GTID模式详细介绍 

    目录 一.GTID概述 二.GTID相较与传统复制的优势 三.GTID自身存在哪些限制 四.GTID工作原理简单介绍 五.如何开启GTID复制 六.查看GTID相关参数 七.GTID与传统模式建立复制时候语句的不同点 八.GTID同步状态简单解析 一.GTID概述 MySQL5.6 在原有主从复制的基础上增加了一个新的复制方式,即基于GTID的复制方式,它由UUID和事务ID两个部分组成,具有如下特点. GTID事务是全局唯一性的,并且一个事务对应一个GTID值. 一个GTID值在同一个MySQ

  • MySQL 主从复制数据不一致的解决方法

    目录 1. 准备工作 1.1 主机配置 1.2 从机配置 2. 数据不一致问题 3. 原因分析 4. 问题解决 5. 小结 今天来说说 MySQL 主从复制数据不一致的问题,通过几个具体的案例,来向小伙伴们展示 binlog 不同 format 之间的区别. 1. 准备工作 以下配置基于 Docker. 我这里有一张简单的图向大伙展示 MySQL 主从的工作方式: 这里,我们准备两台机器: 主机:10.3.50.27:33061 从机:10.3.50.27:33062 1.1 主机配置 主机的配

  • MySQL主从复制问题总结及排查过程

    目录 一.概述 二.mysql主从复制原理 1.MYSQL主从复制过程 三.问题及解决方法 1.show slave status \G 显示如下报错信息 2.根据提示信息定位报错位置 四.通用解决方法 1. 跳过指定数量的事务 2. 跳所有错误或指定类型的错误 一.概述 mysql主从是常用的高可用架构之一,也是使用最广泛的的系统架构.在生产环境中mysql主从复制有时会出现复制错误问题.MySQL主从复制中的问题(Coordinator stopped beacause there were

  • mysql主从复制配置过程

    主库配置 1. 配置mysql vim /etc/my.cn # 在文件中增加以下内容 server-id=1 # 保证server id唯一 log-bin = /var/lib/mysql/mysql-bin.log binlog-do-db = db1 binlog-do-db = db2 其中db1和db2是计划进行主从复制的库,如果有多个,写多行即可.配置完毕后,重启数据库: service mysqld restart 2. 添加复制用户 通过phpmyadmin,添加新用户,并授予

  • 分享MySQL生产库内存异常增高的排查过程

    目录 修改performance_schema 打开内存监控 查找内存消耗 统计事件消耗内存 统计线程消耗内存 定位具体SQL     近期频繁收到一个MySQL实例的内存使用率高的报警,今天我们花时间排查一下问题出在哪里. 修改performance_schema 因为公司生产环境使用的阿里云RDS,修改参数相对方便,performance_schema默认为0,此次修改为1.修改之后提交参数,数据库会进行重启,建议在业务低峰进行. 打开内存监控 登录MySQL数据库,执行如下SQL,打开内存

  • 生产环境MySQL索引时效的排查过程

    早上收到开发同学求助,有个SQL查询耗时特别长,看了执行计划发现没有走索引,但是不知道原因在哪里,我们一起来分析一下. mysql>explain SELECT * FROM artisan_income WHERE parent_id IN ( 222645481, 222583953, 222181775, 222180931, 222081126, 221678753, 221616102, 221591783, 221219312, 221195482, 221118672, 22076

  • MySQL主从复制断开的常用修复方法

    01 问题描述 在生产环境中,我们经常会遇见MySQL主从复制断开的情况,在遇到主从复制断开是,通常情况,解决问题的步骤如下: 1.从库上show slave status查看复制断开的直观原因,并记录当前的复制位点 2.查看error log,分析更详细的复制断开原因 3.修复主从复制关系 4.如果复制关系无法修复,则需要重新搭建从库 02 解决问题的方法 主从复制关系断裂,有各种各样的原因.有些时候,我们没有时间去客观分析原因,因为应用程序处于无法使用状态,需要立即恢复,这种情况下,我们对复

  • 深入解析半同步与异步的MySQL主从复制配置

    简单来讲MySQL的主从复制就是一个C/S架构的应用.master可以认为是我们通常意义上所认为的server,slave可以当作是一台client.slave上的I/O线程去请求master上数据,而master验证通过slave的信息后就允许slave接入,然后进行数据变化信息的发送. 一.MySQL主从复制原理 这里我以MySQL5.5为例来说一下MySQL的主从复制的原理: 首先由备节点的I/O线程负责向主节点请求数据,主节点验证通过以后会由dump线程把数据发送给备用节点.备用节点的I

  • 详解如何利用docker快速构建MySQL主从复制环境

    在学习MySQL的过程中,常常会测试各种参数的作用.这时候,就需要快速构建出MySQL实例,甚至主从. 考虑如下场景: 譬如我想测试mysqldump在指定--single-transaction参数的情况下,对于myisam表的影响. 本来想在现成的测试环境中进行,但测试环境中,有大量的数据,执行mysqldump进行全备,产生的SQL文件,很难基于表进行搜索. 这个时候,就特别渴望能有一套干净的实例进行测试. 此刻,快速构建能力就显得尤为必要,很多童鞋可能会问,通过脚本不就能实现么?为什么要

  • linux系统中使用openssl实现mysql主从复制

            证书准备: CA证书: 第一步:创建CA私钥 [root@localhost CA]# (umask 066;openssl genrsa -out /etc/pki/CA/private/cakey.pem 2048) 第二步:生成自签证书 [root@localhost CA]# openssl req -new -x509 -key /etc/pki/CA/private/cakey.pem  -days 7300 -out /etc/pki/CA/cacert.pem -

  • Mysql主从复制注意事项的讲解

    一.报错error connecting to master 'x@x.x.x.x:x' - retry-time: 60  retries: 86400 今天搭建mysql主从复制,一直报这个错.我是在一台虚拟机上使用多实例创建的2个不同端口的数据库,查了很久,才解决. 1.检查主从复制的用户名密码: 2.检查MASTER_LOG_FILE和MASTER_LOG_POS. 记住配置从库的命令,这些参数都要参考主库的配置: mysql> CHANGE MASTER TO MASTER_HOST=

  • Windows下MySQL主从复制的配置方法

    MySQL主从复制允许将来自一个数据库(主数据库)的数据复制到一个或多个数据库(从数据库). 主数据库一般是实时的业务数据写入和更新操作,从数据库常用的读取为主. 主从复制过程: 1.主服务器上面的任何修改都会通过自己的 I/O tread(I/O 线程)保存在二进制日志 Binary log 里面. 2.从服务器上面也启动一个 I/O thread,通过配置好的用户名和密码, 连接到主服务器上面请求读取二进制日志,然后把读取到的二进制日志写到本地的一个Realy log(中继日志)里面. 3.

随机推荐