Mysql传统复制空库搭建过程中reset slave以及reset slave all对复制的影响
主库信息
- IP:192.168.1.21
- 版本:5.7.18
- 空库搭建主从
从库信息
- IP:192.168.1.128
- 版本:5.6.36
- 空库搭建主从
操作过程
主库上操作
- 赋予复制账户:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.128' IDENTIFIED by 'repl';
- 查看master信息:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000022 | 612 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从库上操作
- 直接指定新的master:
root@localhost : (none) 05:16:09> change master to master_host='192.168.1.21',master_user='repl',master_password='repl',master_log_file='mysql-bin.000022',master_log_pos=612;
ERROR 1776 (HY000): Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.
#报错是因为128这台mysql server之前是使用gtid复制指向另一台mysql主库;而且没有经过reset slave清理相关数据,直接指向新的master肯定也会有问题。
- 关闭gtid设置:
[root@host-192-168-1-128 ~]# service mysql stop
Shutting down MySQL.... SUCCESS!
[root@host-192-168-1-128 ~]# vim /etc/my.cnf 在配置文件中关闭gtid
#gtid-mode=on # GTID only
#enforce-gtid-consistency=true # GTID only
或者
enforce_gtid_consistency=OFF
gtid_mode=OFF
[root@host-192-168-1-128 ~]# service mysql start
Starting MySQL.. SUCCESS!
- 重新指定master,并开启slave:
root@localhost : (none) 05:19:34> change master to master_host='192.168.1.21',master_user='repl',master_password='repl',master_log_file='mysql-bin.000022',master_log_pos=612;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
root@localhost : (none) 05:20:21> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
root@localhost : (none) 05:20:37> reset slave;
Query OK, 0 rows affected (0.03 sec)
root@localhost : (none) 05:20:56> start slave;
Query OK, 0 rows affected (0.10 sec)
- 查看复制情况:
root@localhost : (none) 05:20:57> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.21
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000022
Read_Master_Log_Pos: 612
Relay_Log_File: mysql-relay-bin.000005
Relay_Log_Pos: 333
Relay_Master_Log_File: mysql-bin.000022
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: 1008
Last_Error: Error 'Can't drop database 'sbtest'; database doesn't exist' on query. Default database: 'sbtest'. Query: 'drop database sbtest'
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 964
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1008
Last_SQL_Error: Error 'Can't drop database 'sbtest'; database doesn't exist' on query. Default database: 'sbtest'. Query: 'drop database sbtest'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 12001
Master_UUID: 0a646c88-36e2-11e7-937d-fa163ed7a7b1
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 170516 05:20:58
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
# 注意Exec_Master_Log_Pos: 154
主库上查看
- 查看主库binlog中的events:
mysql> show binlog events in 'mysql-bin.000022';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000022 | 4 | Format_desc | 12001 | 123 | Server ver: 5.7.18-log, Binlog ver: 4 |
| mysql-bin.000022 | 123 | Previous_gtids | 12001 | 154 | |
| mysql-bin.000022 | 154 | Anonymous_Gtid | 12001 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000022 | 219 | Query | 12001 | 317 | drop database sbtest |
| mysql-bin.000022 | 317 | Anonymous_Gtid | 12001 | 382 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000022 | 382 | Query | 12001 | 612 | GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.128' IDENTIFIED WITH 'mysql_native_password' AS '*A424E797037BF97C19A2E88CF7891C5C2038C039' |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
# 对比slave的Exec_Master_Log_Pos: 154,确实会去执行drop database sbtest。问题来了:为什么我指定的master_log_pos=612但是却会执行这个位置之前的binlog日志?
解决方法
- 先关闭slave:
root@localhost : (none) 05:39:13> stop slave;
Query OK, 0 rows affected (0.02 sec)
- 清空master.info文件和relay-log.info 文件以及所有的relay log 文件,all也包括在内存中的信息:
root@localhost : (none) 05:39:17> reset slave all;
Query OK, 0 rows affected (0.08 sec)
- 重新指定master并开启slave:
root@localhost : (none) 05:39:26> change master to master_host='192.168.1.21',master_user='repl',master_password='repl',master_log_file='mysql-bin.000022',master_log_pos=612;
Query OK, 0 rows affected, 2 warnings (0.10 sec)
root@localhost : (none) 05:39:44> start slave;
Query OK, 0 rows affected (0.02 sec)
- 查看复制情况:
root@localhost : (none) 05:39:48> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.21
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000022
Read_Master_Log_Pos: 612
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 286
Relay_Master_Log_File: mysql-bin.000022
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: 612
Relay_Log_Space: 459
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: 0
Master_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: 12001
Master_UUID: 0a646c88-36e2-11e7-937d-fa163ed7a7b1
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
思路探讨
- reset slave删除master.info文件和relay-log.info 文件以及所有的relay log 文件并重新启用一个新的relaylog文件,但并不会清理存储于内存中的复制信息比如master host, master port, master user, or master password,也就是说如果没有使用change master 命令做重新定向,执行start slave 还是会指向旧的master 上面。
- 在5.5.16 版本以及以后,可以使用 RESET SLAVE ALL 来完全的清理复制连接参数信息。
In MySQL 5.5.16 and later, you can instead use RESET SLAVE ALL to reset these connection parameters - 至于明明指定了master_log_pos=612,从库却执行了这之前的位置Exec_Master_Log_Pos: 154。
猜测:这应该和128这个从库之前指定的是另一个master(192.168.1.12),而这些旧的信息还保存在内存中导致的。 - 当时出现错误的第一反应是用skip去跳过错误,问了小波之后说尝试下reset slave all,才能进一步去了解这其中的区别。
参考资料
https://www.percona.com/blog/2013/04/17/reset-slave-vs-reset-slave-all-disconnecting-a-replication-slave-is-easier-with-mysql-5-5/
https://dev.mysql.com/doc/refman/5.5/en/reset-slave.html