MySQL复制问题和解决
目录
环境介绍
一，主库执行delete，从库没有该数据
模拟故障
修复故障
二，主库执行insert，从库已存在该数据
模拟故障
故障恢复
三，主库执行update，从库没有该数据
模拟故障
故障恢复
四，从库relay log文件损坏/缺失
故障模拟
故障恢复
环境介绍
数据库1主2从，mysql版本是v5.19
表结构
一，主库执行delete，从库没有该数据
模拟故障
1.1， 在slave1中奖id = 1的数据delete from hero2 where id = 1
1.2, 在master上执行该数据的delete from hero2 where id = 1
1.3, slave1 上show slave status\G查看
delete from hero2 where id = 1;master上执行后
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.129
Master_User: repl
Master_Port: 3358
Connect_Retry: 60
Master_Log_File: mysql-bin.000038
Read_Master_Log_Pos: 463
Relay_Log_File: mha2-relay-bin.000020
Relay_Log_Pos: 313
Relay_Master_Log_File: mysql-bin.000038
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: 1032
Last_Error: Could not execute Delete_rows event on table test.hero2; Can't find record in 'hero2', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000038, end_log_pos 432修复故障
由于master上删一条记录，而slave上找不到，那么slave上直接跳过即可；
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.01 sec)或者在my.cnf文件中添加slave-skip-errors = 1032 并重启生效，可以跳过所有这种情况的同步错误
二，主库执行insert，从库已存在该数据
模拟故障
2.1 slave 上insert id = 5的数据
2.2 master 上 insert id = 5的数据
2.3 从库上执行show slave status查看同步失败
主从同步异常报错信息
ysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.129
Master_User: repl
Master_Port: 3358
Connect_Retry: 60
Master_Log_File: mysql-bin.000039
Read_Master_Log_Pos: 469
Relay_Log_File: mha2-relay-bin.000023
Relay_Log_Pos: 360
Relay_Master_Log_File: mysql-bin.000039
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: 1062
Last_Error: Could not execute Write_rows event on table test.hero2; Duplicate entry '5' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000039, end_log_pos 438故障恢复
从库已经有该记录，导致这条记录无法顺利插入，从而导致主从同步异常；这种情况就需要在从库上将多余的记录给delete。
报错信息：Write_rows event on table test.hero2; Duplicate entry '5' for key 'PRIMARY'
报错日志中显示了表名和主键id，所以在slave上执行下面语句：
delete from hero2 where id = 5;
stop slave; start slave; 三，主库执行update，从库没有该数据
模拟故障
3.1 slave上将id = 2的数据delete
mysql> delete from hero2 where id = 2;
Query OK, 1 row affected (0.00 sec)3.2 master上执行update hero2 set age = 200 where id = 2;
mysql> update hero2 set age = 200 where id = 2;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 03.3 slave上查看主从同步状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.129
Master_User: repl
Master_Port: 3358
Connect_Retry: 60
Master_Log_File: mysql-bin.000039
Read_Master_Log_Pos: 757
Relay_Log_File: mha2-relay-bin.000024
Relay_Log_Pos: 360
Relay_Master_Log_File: mysql-bin.000039
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: 1032
Last_Error: Could not execute Update_rows event on table test.hero2; Can't find record in 'hero2', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000039, end_log_pos 726故障恢复
根据报错信息中的mysql-bin.000039, end_log_pos 726，在主库上查看binlog
[root@mha1 mysql]# mysqlbinlog --no-defaults -vv --base64-output=decode-rows mysql-bin.000039 | grep -A 20 'end_log_pos 726'
#241221 11:27:47 server id 10129 end_log_pos 726 CRC32 0xb8c2e5cc Update_rows: table id 221 flags: STMT_END_F
### UPDATE `test`.`hero2`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='dragon' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
### @3=1001 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='dragon' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
### @3=200 /* INT meta=0 nullable=0 is_null=0 */
# at 726
#241221 11:27:47 server id 10129 end_log_pos 757 CRC32 0x3db3f01e Xid = 60
COMMIT/*!*/;将变更前的数据整理为insert，并插入slave中：然后stop slave; start slave即可；
mysql> insert into `test`.`hero2` values(2,'dragon',1001);
Query OK, 1 row affected (0.05 sec)
mysql> stop slave; start slave;
Query OK, 0 rows affected (0.00 sec)
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.10.129
Master_User: repl
Master_Port: 3358
Connect_Retry: 60
Master_Log_File: mysql-bin.000039
Read_Master_Log_Pos: 757
Relay_Log_File: mha2-relay-bin.000025
Relay_Log_Pos: 360
Relay_Master_Log_File: mysql-bin.000039
Slave_IO_Running: Yes
Slave_SQL_Running: Yes四，从库relay log文件损坏/缺失
故障模拟
4.1 slave上执行stop slave sql_thread;
4.2 master上执行dml操作，从而产生binlog
4.3 slave上删除relay-log
[root@mha2 mysql]# ls mha2-relay-bin.*
mha2-relay-bin.000030 mha2-relay-bin.000032 mha2-relay-bin.000034 mha2-relay-bin.000036 mha2-relay-bin.000038
mha2-relay-bin.000031 mha2-relay-bin.000033 mha2-relay-bin.000035 mha2-relay-bin.000037 mha2-relay-bin.index
[root@mha2 mysql]# \rm mha2-relay-bin.*
[root@mha2 mysql]# ls mha2-relay-bin.* 4.4 stat slave sql_thread; 并查看主从状态，报错1593
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.10.129
Master_User: repl
Master_Port: 3358
Connect_Retry: 60
Master_Log_File: mysql-bin.000042
Read_Master_Log_Pos: 194
Relay_Log_File: mha2-relay-bin.000031
Relay_Log_Pos: 360
Relay_Master_Log_File: mysql-bin.000039
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: Could not open log file
Skip_Counter: 0
Exec_Master_Log_Pos: 1563
Relay_Log_Space: 32126695
Until_Condition: None4.5 查看主库数据
mysql> select count(1) from hero2;
+----------+
| count(1) |
+----------+
| 1835008 |
+----------+
1 row in set (0.59 sec)查看从库数据
mysql> select count(1) from hero2;
+----------+
| count(1) |
+----------+
| 11 |
+----------+
1 row in set (0.00 sec)
此时许多数据在relay-log里面丢失了
故障恢复
根据Relay_Master_Log_File: mysql-bin.000039 和Exec_Master_Log_Pos: 1563
来重新搭建主从关系
[root@mha2 server]# sh install_mysqlv57.sh -m 192.168.10.129 mysql-bin.000039 1563
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
slave info:
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: 192.168.10.129
Master_User: repl
Master_Port: 3358
Connect_Retry: 60
Master_Log_File: mysql-bin.000039
Read_Master_Log_Pos: 17543128
Relay_Log_File: mha2-relay-bin.000002
Relay_Log_Pos: 10045
Relay_Master_Log_File: mysql-bin.000039
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: 待Seconds_Behind_Master = 0后查看表的数据量确认和主库保持一致，恢复完成
mysql> select count(1) from hero2;
+----------+
| count(1) |
+----------+
| 1835008 |
+----------+
1 row in set (0.59 sec)
mysql>