MySQL主从复制故障案例一

stop slave;set global sql_slave_skip_counter =1   表示忽略执行N各更新，N>0start  salve;

检查主从同步状态，恢复一致

方法二：

根据错误代码跳过指定错误

slave-skip-errors = 1032,1007 slave-skip-errors = all   谨慎使用all

对于错误代码，我们应使用slave服务器错误日志中的错误消息提供错误编号和show slave status的输出。

网络文章;

主从同步出现异常语句跳过错误处理

http://my.oschina.net/u/214948/blog/87846

本文出自 “crazy_sir” 博客，请务必保留此出处http://douya.blog.51cto.com/6173221/1795350

MySQL主从复制故障案例一

标签：主从   skip   

小编还为您整理了以下内容，可能对您也有帮助：

关于mysql 主从复制的错误

项目上 MySQL 还原 SQL 备份经常会碰到一个错误如下，且通常出现在导入视图、函数、存储过程、事件等对象时，其根本原因就是因为导入时所用账号并不具有SUPER 权限，所以无法创建其他账号的所属对象。ERROR 1227 (42000) : Access denied; you need (at least one of) the SUPER privilege(s) for this operation常见场景：1. 还原 RDS 时经常出现，因为 RDS 不提供 SUPER 权限；2. 由开发库还原到项目现场，账号权限等有所不同。

处理方式：

1. 在原库中批量修改对象所有者为导入账号或修改 SQL SECURITY 为 Invoker；2. 使用 mysqlmp 导出备份，然后将 SQL 文件中的对象所有者替换为导入账号。
二、问题原因我们先来看下为啥会出现这个报错，那就得说下 MySQL 中一个很特别的权限控制机制，像视图、函数、存储过程、触发器等这些数据对象会存在一个 DEFINER 和一个 SQL SECURITY 的属性，如下所示：

--视图定义CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW v_test




--函数定义CREATE DEFINER=`root`@`%` FUNCTION `f_test()` RETURNS varchar(100) SQL SECURITY DEFINER




--存储过程定义CREATE DEFINER=`root`@`%` PROCEDURE `p_test`() SQL SECURITY DEFINER




--触发器定义CREATE DEFINER=`root`@`%` trigger t_test




--事件定义CREATE DEFINER=`root`@`%` EVENT `e_test`

DEFINER：对象定义者，在创建对象时可以手动指定用户，不指定的话默认为当前连接用户；

SQL SECURITY：指明以谁的权限来执行该对象，有两个选项，一个为 DEFINER，一个为 INVOKER，默认情况下系统指定为 DEFINER；DEFINER：表示按定义者的权限来执行； INVOKER：表示按调用者的权限来执行。

如果导入账号具有 SUPER 权限，即使对象的所有者账号不存在，也可以导入成功，但是在查询对象时，如果对象的 SQL SECURITY 为 DEFINER，则会报账号不存在的报错。ERROR 1449 (HY000): The user specified as a definer ('root'@'%') does not exist




三、改写内容上述这个 DEFINER 问题，个人想到最简单的解决方式就是 mysqlmp 导出时直接摘除掉相关属性，但是 mysqlmp 本身并不提供对应参数，所以比较蛋疼，无论是原库走脚本变更或是备份后修改 SQL 文件都不是非常方便，尤其是触发器的 DEFINER，只能先 DROP 再 CREATE 才可以变更。只能看下是否可以从 mysqlmp 源码中去掉 DEFINER 定义。本次 mysqlmp 改写主要有 2 个目的：1. 摘取备份中视图、函数、存储过程、触发器等对象的 DEFINER 定义；2. 尝试加上比较简单的备份进度显示（原生 mysqlmp 的 verbose 参数不是非常清晰，想要实现 navicate 备份时的那种行数显示）。

改写好处：1. 可以避免还原时遇到 DEFINER 报错相关问题；2. 根据输出信息知道备份是否正常进行，防止备份中遇到元数据锁无法获取然后一直卡住的情况。

关于mysql 主从复制的错误

项目上 MySQL 还原 SQL 备份经常会碰到一个错误如下，且通常出现在导入视图、函数、存储过程、事件等对象时，其根本原因就是因为导入时所用账号并不具有SUPER 权限，所以无法创建其他账号的所属对象。ERROR 1227 (42000) : Access denied; you need (at least one of) the SUPER privilege(s) for this operation常见场景：1. 还原 RDS 时经常出现，因为 RDS 不提供 SUPER 权限；2. 由开发库还原到项目现场，账号权限等有所不同。

处理方式：

1. 在原库中批量修改对象所有者为导入账号或修改 SQL SECURITY 为 Invoker；2. 使用 mysqlmp 导出备份，然后将 SQL 文件中的对象所有者替换为导入账号。
二、问题原因我们先来看下为啥会出现这个报错，那就得说下 MySQL 中一个很特别的权限控制机制，像视图、函数、存储过程、触发器等这些数据对象会存在一个 DEFINER 和一个 SQL SECURITY 的属性，如下所示：

--视图定义CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`%` SQL SECURITY DEFINER VIEW v_test




--函数定义CREATE DEFINER=`root`@`%` FUNCTION `f_test()` RETURNS varchar(100) SQL SECURITY DEFINER




--存储过程定义CREATE DEFINER=`root`@`%` PROCEDURE `p_test`() SQL SECURITY DEFINER




--触发器定义CREATE DEFINER=`root`@`%` trigger t_test




--事件定义CREATE DEFINER=`root`@`%` EVENT `e_test`

DEFINER：对象定义者，在创建对象时可以手动指定用户，不指定的话默认为当前连接用户；

SQL SECURITY：指明以谁的权限来执行该对象，有两个选项，一个为 DEFINER，一个为 INVOKER，默认情况下系统指定为 DEFINER；DEFINER：表示按定义者的权限来执行； INVOKER：表示按调用者的权限来执行。

如果导入账号具有 SUPER 权限，即使对象的所有者账号不存在，也可以导入成功，但是在查询对象时，如果对象的 SQL SECURITY 为 DEFINER，则会报账号不存在的报错。ERROR 1449 (HY000): The user specified as a definer ('root'@'%') does not exist




三、改写内容上述这个 DEFINER 问题，个人想到最简单的解决方式就是 mysqlmp 导出时直接摘除掉相关属性，但是 mysqlmp 本身并不提供对应参数，所以比较蛋疼，无论是原库走脚本变更或是备份后修改 SQL 文件都不是非常方便，尤其是触发器的 DEFINER，只能先 DROP 再 CREATE 才可以变更。只能看下是否可以从 mysqlmp 源码中去掉 DEFINER 定义。本次 mysqlmp 改写主要有 2 个目的：1. 摘取备份中视图、函数、存储过程、触发器等对象的 DEFINER 定义；2. 尝试加上比较简单的备份进度显示（原生 mysqlmp 的 verbose 参数不是非常清晰，想要实现 navicate 备份时的那种行数显示）。

改写好处：1. 可以避免还原时遇到 DEFINER 报错相关问题；2. 根据输出信息知道备份是否正常进行，防止备份中遇到元数据锁无法获取然后一直卡住的情况。

mysql的主从复制，linux重启后遇到问题。

主机的mysql重启，但是你的从机mysql肯定是没重启才出现binlog索引不一致的现象，我认为是，从机mysql在主机重启之前slave_io_running线程始终保持和主机通信，传输binlog的更新。当主机挂掉之后，slave_io_running一直等待，状态变成NO，整个复制就会停掉，应该说是处于sleep状态，后来主机恢复，从机是没法辨别的，只能stop slave 然后再start，或者change master操作下，实在不行，只能重启从机的mysql了

MySQL中主从复制不同步?

我也遇到了这个问题，我是这样解决的：
第一show variables like '%server_id%';看看是不是没有设置成功
第二看看是不是数据库的版本不同，

MySQL中主从复制不同步?

我也遇到了这个问题，我是这样解决的：
第一show variables like '%server_id%';看看是不是没有设置成功
第二看看是不是数据库的版本不同，

mysql 主从复制报错,不知道是什么原因

1、从库上看到如下错误

mysql> show slave statusG;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 172.18.10.11                  Master_User: rep                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000021          Read_Master_Log_Pos: 273               Relay_Log_File: mysql-relay-bin.000051                Relay_Log_Pos: 336        Relay_Master_Log_File: mysql-bin.000021             Slave_IO_Running: Yes            Slave_SQL_Running: No                从库SQL线程已经停止              Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 1007              报错（错误代码1007）                   Last_Error: Error ‘Can‘t create database ‘shaw‘; database exists‘ on query. Default database: ‘shaw‘. Query: ‘create database shaw‘                 Skip_Counter: 0          Exec_Master_Log_Pos: 190              Relay_Log_Space: 721              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: NULLMaster_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 1007               Last_SQL_Error: Error ‘Can‘t create database ‘shaw‘; database exists‘ on query. Default database: ‘shaw‘. Query: ‘create database shaw‘  Replicate_Ignore_Server_Ids:              Master_Server_Id: 11 row in set (0.00 sec)

2、查看错误代码，确定错误类型

3、根据错误提示和错误代码，我们可以确定，从库已经有"shaw",这个库，导致同步主库上的"shaw"库时，发生冲突

4、解决方法（从库上操作）

mysql> stop slave;Query OK, 0 rows affected (0.00 sec)mysql> set global sql_slave_skip_counter=1;Query OK, 0 rows affected (0.00 sec)mysql> start slave;Query OK, 0 rows affected (0.00 sec)

5、再次查看状态（从库）

mysql> show slave statusG;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 172.18.10.11                  Master_User: rep                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000021          Read_Master_Log_Pos: 273               Relay_Log_File: mysql-relay-bin.000052                Relay_Log_Pos: 253        Relay_Master_Log_File: mysql-bin.000021             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: 273              Relay_Log_Space: 721              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:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 11 row in set (0.00 sec)

6、测试主从同步是否正常

mysql> show databases;    主库数据+--------------------+| Database           |+--------------------+| information_schema || gamedb             || mysql              || performance_schema || shaw               || test               |+--------------------+

mysql> show databases;    从库数据+--------------------+| Database           |+--------------------+| information_schema || gamedb             || mysql              || performance_schema || shaw               || test               |+--------------------+

mysql> drop database shaw;        主库上删除数据Query OK, 0 rows affected (0.01 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || gamedb             || mysql              || performance_schema || test               |+--------------------+

mysql> show databases;        从库已经同步主库数据+--------------------+| Database           |+--------------------+| information_schema || gamedb             || mysql              || performance_schema || test               |+--------------------+

mysql主从复制-故障案例一

标签：

mysql 主从复制报错,不知道是什么原因

1、从库上看到如下错误

mysql> show slave statusG;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 172.18.10.11                  Master_User: rep                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000021          Read_Master_Log_Pos: 273               Relay_Log_File: mysql-relay-bin.000051                Relay_Log_Pos: 336        Relay_Master_Log_File: mysql-bin.000021             Slave_IO_Running: Yes            Slave_SQL_Running: No                从库SQL线程已经停止              Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 1007              报错（错误代码1007）                   Last_Error: Error ‘Can‘t create database ‘shaw‘; database exists‘ on query. Default database: ‘shaw‘. Query: ‘create database shaw‘                 Skip_Counter: 0          Exec_Master_Log_Pos: 190              Relay_Log_Space: 721              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: NULLMaster_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 1007               Last_SQL_Error: Error ‘Can‘t create database ‘shaw‘; database exists‘ on query. Default database: ‘shaw‘. Query: ‘create database shaw‘  Replicate_Ignore_Server_Ids:              Master_Server_Id: 11 row in set (0.00 sec)

2、查看错误代码，确定错误类型

3、根据错误提示和错误代码，我们可以确定，从库已经有"shaw",这个库，导致同步主库上的"shaw"库时，发生冲突

4、解决方法（从库上操作）

mysql> stop slave;Query OK, 0 rows affected (0.00 sec)mysql> set global sql_slave_skip_counter=1;Query OK, 0 rows affected (0.00 sec)mysql> start slave;Query OK, 0 rows affected (0.00 sec)

5、再次查看状态（从库）

mysql> show slave statusG;*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 172.18.10.11                  Master_User: rep                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000021          Read_Master_Log_Pos: 273               Relay_Log_File: mysql-relay-bin.000052                Relay_Log_Pos: 253        Relay_Master_Log_File: mysql-bin.000021             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: 273              Relay_Log_Space: 721              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:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 11 row in set (0.00 sec)

6、测试主从同步是否正常

mysql> show databases;    主库数据+--------------------+| Database           |+--------------------+| information_schema || gamedb             || mysql              || performance_schema || shaw               || test               |+--------------------+

mysql> show databases;    从库数据+--------------------+| Database           |+--------------------+| information_schema || gamedb             || mysql              || performance_schema || shaw               || test               |+--------------------+

mysql> drop database shaw;        主库上删除数据Query OK, 0 rows affected (0.01 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || gamedb             || mysql              || performance_schema || test               |+--------------------+

mysql> show databases;        从库已经同步主库数据+--------------------+| Database           |+--------------------+| information_schema || gamedb             || mysql              || performance_schema || test               |+--------------------+

mysql主从复制-故障案例一

标签：

故障分析 | MySQL 从机故障重启后主从同步报错案例分析

MySQL 从库所在主机故障重启后，sql_thread 线程报错：

通过报错信息可知，worker 线程在回放事务 '471c2974-f9bb-11eb-afb1-52540010fb89:88313207' 时，由于要插入的记录主键冲突报错。

主机重启前，主从同步正常，主机重启后，主从同步由于主键冲突报错，对比了冲突主键所在行记

录在主从库是一致的，初步分析事务 '471c2974-f9bb-11eb-afb1-52540010fb89:88313207' 在主机故

障前已经在从库进行了回放，那为何事务会重复回放呢？

在开启gtid模式下，如果指定 master_auto_position=1，start slave 时，从库会把

Retrieved_Gtid_Set 和 Executed_Gtid_Set 的并集发送给主库，主库将收到的并集和自己的

gtid_executed 比较，把从库 gtid 集合里缺失的事务全都发送给从库。

主机重启后，事务重复回放，表明 Retrieved_Gtid_Set 和 Executed_Gtid_Set 的并集中有 GTID 事务

丢失，导致重复获取事务执行引发主键冲突错误。Retrieved_Gtid_Set 和 Executed_Gtid_Set 均为内存变

量，MySQL 重启后，Retrieved_Gtid_Set 初始化为空值，从而推断出 Executed_Gtid_Set 有 GTID 事务丢

失。

Executed_Gtid_Set 来源于 gtid_executed 变量，gtid_executed 变量持久化介质有

mysql.gtid_executed 表和 binlog ，其中 mysql.gtid_executed 表是 MySQL 5.7 后引入的，在 MySQL 5.6 中，从库要使用 GTID ，必须要先设置 log_bin=on,log_slave_updates=on ，因为从库执行过的 GTID 只保留在 binlog 中。

gtid_executed 变量值陈旧，推断出 binlog 未实时持久化，我们看一下参数 sync_binlog :

通过以上分析，此次故障来龙去脉就清楚了：

Worker 线程报 1062 主键冲突错误 --> gtid_executed 信息陈旧 --> binlog 未实时持久化

搭建一主一从测试环境，通过 sysbench 模拟主库并发插入，从库主机暴力关机后，故障复现：

既然错误原因是事务重复执行，那跳过错误就好了，有如下两种方式，根据需要选取其中一种方式执行：

如果最新 binglog 丢失的 GTID 较多，手工执行比较繁琐，需要不断试错。可写一个存储过程批量执行：

待主从同步正常后，再取消参数 slave_skip_errors 设置重启 MySQL 。

MySQL：主从复制从库GTID大量空洞的问题

这是今天要到的一个问题，由朋友 杨长江 提交给我，版本5.7.17。如下：

已知的一个BUG是skip-slave-error参数设置问题，而且这个BUG再8.0.25依旧存在，参考如下：

而拿到朋友的参数文件后，发现并没有设置skip-slave-error参数，那么是其他什么BUG导致的呢？实际上这个BUG是5.7.23以下的版本，并且设置了replicate_wild_do_table等过滤规则会后对CREATE DATABASE/ALTER DATABASE/DROP DATABASE会过滤掉操作，并且从库的GTID也会被抛弃掉，这样就产生了大量的空洞。

稍微浏览修改，如BUG描述增加对database操作的判定。

如果使用较老的版本应该注意这个奇特的问题。