mysql主从数据库数据不同步分析方法-工具盒子

mysql主从数据库数据不同步分析方法

当出现主从数据库数据不同步时，就可以理解为出现了复数据制故障，一般在从库上分析故障原因，主要是从库上的两个线程出现问题；

主从异常情况有哪些？面试题
	（1）从库 IO线程
		1-连接
		2。 防火墙
		3。连接数达到限制
	（2）从库SQL 线程异常
	（3）排查 手动连接

一、从库线程异常分析-IO

确认线程是否出现问题：

# 确认线程运行状态
Slave_IO_Running: Yes
-- 常见异常状态：connecting、no，具体问题情况分析需要查看以下信息；
Last_IO_Errno: 0
Last_IO_Error:
-- 根据以上错误编码和错误信息说明，可以判断产生IO线程异常的原因；

确认线程相关工作职能：（从底层角度分析IO异常原因）

① IO线程主要用于连接主数据库服务；

以上工作职能失败就会导致线程状态为：connecting

可能导致异常原因：

连接地址、端口、用户、密码信息不对可能会导致连接异常；
防火墙安全策略阻止连接建立、网络通讯配置异常影响连接建立；
到达数据库服务连接数上限，造成主从连接产生异常；

线程异常情况排查：

使用主从复制专用用户进行手工连接测试，核实主从复制用户是否可以远程连接登录数据库服务

# 正常远程连接测试
[root@baimeidashu-01 ~]# mysql -urepl -p123456 -h192.168.30.101 -P3307
mysql> 
-- 以上测试情况成功；

# 异常远程连接测试
[root@baimeidashu-01 ~]# mysql -urepl1 -p123456 -h192.168.30.101 -P3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'repl1'@'baimeidashu-01' (using password: YES)
[root@baimeidashu-01 ~]# mysql -urepl -p123123 -h192.168.30.101 -P3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'repl'@'baimeidashu-01' (using password: YES)
-- 以上测试情况失败，输出信息表示建立远程连接的用户名称或密码信息异常；
[root@baimeidashu-01 ~]# mysql -urepl -p123456 -h192.168.30.102 -P3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.30.102:3307' (113)
-- 异常测试情况失败，输出信息表示建立远程连接的地址信息异常；
[root@baimeidashu-01 ~]# mysql -urepl -p123456 -h192.168.30.101 -P3300
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.30.101:3300' (111)
-- 异常测试情况失败，输出信息表示建立远程连接的端口信息异常；

线程异常情况模拟：

# 在从库上临时取消主从关系
[root@baimeidashu-01 ~]# mysql -S /tmp/mysql3308.sock
mysql> stop slave;
mysql> reset slave all;
-- 此次表示主从关系已经解除；

# 在从库上模拟连接错误情况
# 模拟用户名称或者密码信息错误
mysql> CHANGE MASTER TO
  MASTER_HOST='192.168.30.101',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='binlog.000003',
  MASTER_LOG_POS=681,
  MASTER_CONNECT_RETRY=10;
-- 模拟从库连接主库时，密码信息出现错误；
mysql> start slave;
-- 再次启动主从同步功能
# 模拟连接地址信息输入错误
CHANGE MASTER TO
  MASTER_HOST='192.168.30.103',
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='binlog.000003',
  MASTER_LOG_POS=681,
  MASTER_CONNECT_RETRY=10;
-- 模拟从库连接主库时，地址信息出现错误；
mysql> start slave;
-- 再次启动主从同步功能
# 模拟连接会话超过上限情况
[root@baimeidashu-01 ~]# mysql -S /tmp/mysql3307.sock
-- 在主库上进行连接上限配置
mysql> select @@max_connections;
+---------------------------+
| @@max_connections |
+---------------------------+
|                               151 |
+---------------------------+
1 row in set (0.00 sec)
-- 连接会话数默认为支持151人同时并发连接，超过上限数值后随即拒绝建立连接，但其中还给管理员预留了一个；
mysql> set global max_connections=3;
-- 模拟调整连接数信息
[root@baimeidashu-01 ~]# mysql -S /tmp/mysql3308.sock
mysql> stop slave;
-- 停止从库建立连接
[root@baimeidashu-01 ~]# mysql -uroot  -S /tmp/mysql3307.sock
-- 模拟将连接跑满（重复建立4个连接）
[root@baimeidashu-01 ~]#  mysql -uroot  -S /tmp/mysql3307.sock
ERROR 1040 (HY000): Too many connections
-- 再建立新的连接报错
mysql> start slave;
-- 从库重新建立连接

# 查看主从同步状态情况
# 用户名称或者密码错误异常情况查看：
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Last_IO_Errno: 1045
Last_IO_Error: error connecting to master 'repl@192.168.30.101:3307' - retry-time: 10 retries: 9 message: Access denied for user 'repl'@'baimeidashu-01' (using password: YES)
-- IO线程运行状态为connecting，并且显示IO线程错误码，以及IO线程错误原因说明；
# 地址信息输入异常情况查看：
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master 'repl@192.168.30.103:3307' - retry-time: 10 retries: 1 message: Can't connect to MySQL server on '192.168.30.103:3307' (113)
-- IO线程运行状态为connecting，并且显示IO线程错误码，以及IO线程错误原因说明；
# 连接上限超出限制情况查看：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Last_IO_Errno: 1040
Last_IO_Error: error connecting to master 'repl@192.168.30.101:3307' - retry-time: 10 retries: 7 message: Too many connections
-- IO线程运行状态为connecting，并且显示IO线程错误码，以及IO线程错误原因说明；

线程异常情况处理：

# 主从搭建过程出现异常：
[root@baimeidashu-01 ~]# mysql -S /tmp/mysql3308.sock
mysql> stop slave;
mysql> reset slave all;
-- 对主从关系进行重置；
CHANGE MASTER TO
  MASTER_HOST='192.168.30.101',
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='binlog.000003',
  MASTER_LOG_POS=681,
  MASTER_CONNECT_RETRY=10;
-- 模拟从库重新连接主库；
mysql> drop database baimeidashu;
mysql> start slave;
-- 再次启动主从同步功能

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
-- 此时主从异常情况修复；

② IO线程主要用于进行日志信息请求，以及接收日志信息，并将日志信息进行保存（落地）；

以上工作职能失败就会导致线程状态为：no

可能导致异常原因：

IO线程在请求日志信息失败，有可能日志信息被无意清理了；
IO线程在请求日志信息失败，有可能主从配置的标识信息重复冲突了；

线程异常情况模拟：

在进行异常情况模拟前，核实确认好主从同步状态是否正常

# 异常情况模拟-01：主库日志信息被清理了
# 在从库上核实同步状态情况
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.30.101
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 341
Relay_Log_File: baimeidashu-01-relay-bin.000005
Relay_Log_Pos: 506

# 在主库上清理日志文件信息
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+------------------+-----------+-------------------+-----------------------+-------------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+-------------------+-----------------------+-------------------------+
| binlog.000001 |         156 |                           |                                 |                                   |
+------------------+-----------+-------------------+-----------------------+-------------------------+
1 row in set (0.00 sec)
-- 模拟主库已经将日志信息清理

# 查看主从同步状态情况
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Errno: 13114
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event 'binlog.000004' at 156, the last event read from './binlog.000004' at 341, the last byte read from './binlog.000004' at 341.'
-- IO线程运行状态为no，并且显示IO线程错误码，以及IO线程错误原因说明；

# 异常情况模拟-02：主从server_id server_uuid信息配置相同了
# 查看主从服务server_id信息
mysql> select @@server_id;
+-----------------+
| @@server_id |
+-----------------+
|                     7 |
+-----------------+
1 row in set (0.00 sec)
mysql> select @@server_id;
+-----------------+
| @@server_id |
+-----------------+
|                      8 |
+-----------------+
1 row in set (0.00 sec)
-- 核实确认主库server_id为7，从库server_id为8；

# 模拟修改从服务server_id信息
mysql> stop slave;
mysql> set global server_id=7;
mysql> start slave;

# 查看主从同步状态情况
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Errno: 13117
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
-- IO线程运行状态为no，并且显示IO线程错误码，以及IO线程错误原因说明；

线程异常情况处理：

# 主从搭建过程出现异常修复：
[root@baimeidashu-01 ~]# mysql -S /tmp/mysql3308.sock
mysql> stop slave;
mysql> reset slave all;
-- 对主从关系进行重置；
CHANGE MASTER TO
  MASTER_HOST='192.168.30.101',
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='binlog.000001',
  MASTER_LOG_POS=156,
  MASTER_CONNECT_RETRY=10;
-- 模拟从库重新连接主库；
mysql> start slave;
-- 再次启动主从同步功能

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
-- 此时主从异常情况修复；

# 主从搭建过程出现异常修复：
mysql> set global server_id=8;
mysql> stop slave;
mysql> start slave;

*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
-- 此时主从异常情况修复；

二、从库线程异常分析-SQL

确认线程是否出现问题：

# 确认线程运行状态
Slave_SQL_Running: Yes
-- 常见异常状态：no，具体问题情况分析需要查看以下信息；
Last_SQL_Errno: 0
Last_SQL_Error:
-- 根据以上错误编码和错误信息说明，可以判断产生SQL线程异常的原因；

确认线程相关工作职能：（从底层角度分析SQL异常原因）

① SQL线程主要用于回放执行relay log日志信息，即执行相关数据同步SQL语句信息；

以上工作职能失败就会导致线程状态为：no（研究SQL线程故障，实质就是在研究SQL语句为什么无法执行）

可能导致异常原因：（从库数据或设置异常导致）

- 创建的对象已经存在，涉及到的对象可能有库、表、用户、索引...;

- 插入（insert）的操作对象有异常、修改（update alter）的操作对象有异常、删除（delete drop）的操作对象有异常；

- 由于数据库设置的约束信息，与执行的SQL语句产生冲突问题；

- 在数据库不同版本之间进行数据同步时，可能出现配置冲突问题（比如：5.6可以识别时间为0字段，5.7不能识别时间为0字段）

可能造成异常情况：

- 在进行主从配置时，指定的位置点出现错误（change master to）；

- 在进行主从配置前，从库被写入相应的数据信息了，与主库同步数据产生冲突（误连接从库进行操作了）；

- 在从库工作繁忙状态时，从库宕机了，业务恢复后可能出现异步同步数据错乱（主库操作创建表操作没同步，同步了插入表操作）；

- 在进行主从切换时（假设进行的是手工切换），没有正确操作锁定源主库和binlog日志信息；（画图说明）

导致切换前主库数据没有完全同步，切换后从库数据（原主库）比主库数据（原从库）信息更全；

- 在应用数据库双主结构时，没有正确使用（经常导致相互同步数据，主键或唯一键冲突）（画图说明）

若企业创建必须使用双主架构，实现双写机制，可以使用全局序列机制，实现主键或唯一键的统一分配；

线程异常情况模拟：

在进行异常情况模拟前，核实确认好主从同步状态是否正常；

# 异常情况模拟：模拟误连接从库做写入操作，与主库产生冲突
# 在从库上核实同步状态情况
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.30.101
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 338
Relay_Log_File: baimeidashu-01-relay-bin.000003
Relay_Log_Pos: 321
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

# 在从库上创建数据信息（模拟误连接从库执行操作情况）
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)

# 在主库上创建数据信息（实现主库创建数据与从库一致）
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)

# 查看主从同步状态情况
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_SQL_Errno: 1007
Last_SQL_Error: Error 'Can't create database 'test1'; database exists' on query. Default database: 'test1'. Query: 'create database test1'
-- SQL线程运行状态为no，并且显示SQL线程错误码，以及SQL线程错误原因说明；

线程异常处理原则：

- 主从同步出现数据同步异常，一切数据信息以主库为准；

- 尽量不要使用双主架构，避免数据信息双写，造成的数据同步异常；

- 主从同步数据信息时候，可以设置从库只读，避免从库误写入冲突；

# 从库出现SQL线程异常处理
# 处理方案01：将冲突操作进行回退
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_SQL_Errno: 1007
Last_SQL_Error: Error 'Can't create database 'test1'; database exists' on query. Default database: 'test1'. Query: 'create database test1'
-- 此时SQL线程出现异常，需要根据异常错误代码和错误原因描述处理问题；

mysql> drop database test1;
-- 删除从库产生冲突的数据库信息，从而实现冲突情况的回退机制；

mysql> start slave;
-- 冲突异常问题回退后，可以重新启动主从关系

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
-- 主从关系已经恢复正常；

# 处理方案02：跳过主从同步异常错误，以从库数据为准（不建议使用，除非配合pt-checksum/pt-sync工具使用）
mysql> create database test2;
Query OK, 1 row affected (0.00 sec)
-- 从库创建数据test2，重新SQL线程异常情况
mysql> create database test2;
Query OK, 1 row affected (0.00 sec)
-- 主库创建数据test2，重新SQL线程异常情况
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_SQL_Errno: 1007
Last_SQL_Error: Error 'Can't create database 'test2'; database exists' on query. Default database: 'test2'. Query: 'create database test2'
-- 此时SQL线程出现异常，需要根据异常错误代码和错误原因描述处理问题；

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> set global sql_slave_skip_counter=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
-- 先停止主从同步关系，实现跳过错误提示步骤，最后重新建立主从连接；（一般都是配合工具进行修复）
-- 在主键冲突导致的问题，跳过错误时要非常小心，建议将跳过的错误的ID数值需要先抓取出来，否则可以会丢失操作事件

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
-- 主从关系已经恢复正常；

# 处理方案03：可以设定跳过指定的错误编码
mysql> select @@slave_skip_errors;
+---------------------------+
| @@slave_skip_errors |
+---------------------------+
| OFF                                |
+---------------------------+
1 row in set, 1 warning (0.00 sec)
-- 这种应用风险比较大，不建议生产环境使用