reset slave
首先来看下当前master-slave情况
mysql> prompt u@h,p:d>\_ PROMPT set to 'u@h,p:d>\_' # master mydba@192.168.85.129,3306:test> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000007 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) # slave mydba@192.168.85.129,3307:test> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.85.129 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000007 Read_Master_Log_Pos: 120 Relay_Log_File: mysql-relay-bin.000011 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000007 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: 120 Relay_Log_Space: 15247 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: 6 Master_UUID: 02a05b2c-0557-11e7-bb02-000c29493a20 Master_Info_File: /usr/local/mysql3307/log/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，并查看同步情况
# slave mydba@192.168.85.129,3307:test> stop slave; Query OK, 0 rows affected (0.20 sec) mydba@192.168.85.129,3307:test> reset slave; Query OK, 0 rows affected (0.07 sec) mydba@192.168.85.129,3307:test> start slave; Query OK, 0 rows affected (0.05 sec) mydba@192.168.85.129,3307:test> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.85.129 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000007 Read_Master_Log_Pos: 120 Relay_Log_File: mysql-relay-bin.000003 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000001 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: 1051 Last_Error: Error 'Unknown table 'test.a'' on query. Default database: 'test'. Query: 'DROP TABLE `a` /* generated by server */' Skip_Counter: 0 Exec_Master_Log_Pos: 120 Relay_Log_Space: 498471 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: 1051 Last_SQL_Error: Error 'Unknown table 'test.a'' on query. Default database: 'test'. Query: 'DROP TABLE `a` /* generated by server */' Replicate_Ignore_Server_Ids: Master_Server_Id: 6 Master_UUID: 02a05b2c-0557-11e7-bb02-000c29493a20 Master_Info_File: /usr/local/mysql3307/log/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: 170719 15:35:08 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec)
Slave_IO_Running: I/O线程是否启动并且成功连接到master
Slave_SQL_Running: SQL线程是否启动
Master_Log_File：I/O线程当前正在读取的主服务器二进制日志文件的名称
Read_Master_Log_Pos：在当前的主服务器二进制日志中，I/O线程已经读取的位置
Relay_Log_File：SQL线程当前正在读取和执行的中继日志文件的名称
Relay_Log_Pos：在当前的中继日志中，SQL线程已读取和执行的位置
Relay_Master_Log_File：包含SQL线程最近执行的事件的主服务器二进制日志文件的名称
Exec_Master_Log_Pos：在主服务器二进制日志中(Relay_Master_Log_File)，SQL线程已执行的位置。主服务器的二进制日志中的(Relay_Master_Log_File,Exec_Master_Log_Pos)对应于在中继日志中的(Relay_Log_File,Relay_Log_Pos)
从上面结果可知，SQL线程已经停止；SQL线程已经执行的主服务器二进制日志文件的名称mysql-bin.000001，SQL线程已执行的位置120
主库查看二进制日志
# master mydba@192.168.85.129,3306:test> show binlog events in 'mysql-bin.000001'; +------------------+------+-------------+-----------+-------------+------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+-------------+-----------+-------------+------------------------------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 6 | 120 | Server ver: 5.6.35-log, Binlog ver: 4 | | mysql-bin.000001 | 120 | Query | 6 | 234 | use `test`; DROP TABLE `a` /* generated by server */ | | mysql-bin.000001 | 234 | Query | 6 | 319 | use `test`; flush general logs | | mysql-bin.000001 | 319 | Query | 6 | 404 | use `test`; flush general logs | | mysql-bin.000001 | 404 | Query | 6 | 501 | use `test`; create table aaa(a int) | | mysql-bin.000001 | 501 | Query | 6 | 617 | use `test`; DROP TABLE `aaa` /* generated by server */ | | mysql-bin.000001 | 617 | Query | 6 | 737 | use `test`; DROP TABLE `bittest` /* generated by server */ | | mysql-bin.000001 | 737 | Query | 6 | 853 | use `test`; DROP TABLE `tb1` /* generated by server */ | | mysql-bin.000001 | 853 | Query | 6 | 969 | use `test`; DROP TABLE `tb2` /* generated by server */ | | mysql-bin.000001 | 969 | Query | 6 | 1086 | use `test`; DROP TABLE `test` /* generated by server */ | | mysql-bin.000001 | 1086 | Query | 6 | 1187 | use `test`; create table credro(id int) | | mysql-bin.000001 | 1187 | Query | 6 | 1306 | use `test`; DROP TABLE `credro` /* generated by server */ | | mysql-bin.000001 | 1306 | Query | 6 | 1407 | use `test`; create table credro(id int) | | mysql-bin.000001 | 1407 | Query | 6 | 1526 | use `test`; DROP TABLE `credro` /* generated by server */ | | mysql-bin.000001 | 1526 | Stop | 6 | 1549 | | +------------------+------+-------------+-----------+-------------+------------------------------------------------------------+ 15 rows in set (0.00 sec) mydba@192.168.85.129,3306:test> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 1549 | | mysql-bin.000002 | 143 | | mysql-bin.000003 | 143 | | mysql-bin.000004 | 480108 | | mysql-bin.000005 | 143 | | mysql-bin.000006 | 14771 | | mysql-bin.000007 | 120 | +------------------+-----------+ 7 rows in set (0.00 sec)
Log_name=mysql-bin.000001，Pos=120对应的是语句是use `test`; DROP TABLE `a`;其后还有很多操作语句。
由此可知，stop slave->reset slave->start slave 它的连接信息没有丢失，只是同步的起始位置发生了变化，之前执行过的操作还需再次执行导致同步异常。
为了后续操作直接使用change master到master status位置
# slave mydba@192.168.85.129,3307:test> stop slave; Query OK, 0 rows affected (0.01 sec) mydba@192.168.85.129,3307:test> change master to -> master_host='192.168.85.129', -> master_port=3306, -> master_user='repl', -> master_password='repl', -> master_log_file='mysql-bin.000007', -> master_log_pos=120; Query OK, 0 rows affected, 2 warnings (0.04 sec) mydba@192.168.85.129,3307:test> start slave; Query OK, 0 rows affected (0.02 sec)
reset slave all
首先来看下当前master-slave情况
# master mydba@192.168.85.129,3306:test> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000007 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.01 sec) # slave mydba@192.168.85.129,3307:test> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.85.129 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000007 Read_Master_Log_Pos: 120 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000007 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: 120 Relay_Log_Space: 456 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: 6 Master_UUID: 02a05b2c-0557-11e7-bb02-000c29493a20 Master_Info_File: /usr/local/mysql3307/log/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 all，并查看同步情况
# slave mydba@192.168.85.129,3307:test> stop slave; Query OK, 0 rows affected (0.00 sec) mydba@192.168.85.129,3307:test> reset slave all; Query OK, 0 rows affected (0.01 sec) mydba@192.168.85.129,3307:test> start slave; ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO
使用change master to
mydba@192.168.85.129,3307:test> change master to -> master_host='192.168.85.129', -> master_port=3306, -> master_user='repl', -> master_password='repl', -> master_log_file='mysql-bin.000007', -> master_log_pos=120; Query OK, 0 rows affected, 2 warnings (0.02 sec) mydba@192.168.85.129,3307:test> start slave; Query OK, 0 rows affected (0.01 sec)
参考网上的说法reset slave和reset slave all所做的操作如下
1、删除master.info和relay-log.info文件；
2、删除所有的relay log(包括还没有应用完的日志)，创建一个新的relay log文件；
如果不加all参数，那么所有的连接信息仍然保留在内存中，包括主库地址、端口、用户、密码等。这样可以直接运行start slave命令而不必重新输入change master to命令，而运行show slave status也仍和没有运行reset slave一样，有正常的输出(ShanFish补充：运行show slave status有输出，但里面的取值有变化，部分同步会出现异常)。但如果加了all参数，那么这些内存中的数据也会被清除掉，运行show slave status输出为空。
如果主服务器部分二进制日志删除(expire_logs_days)，比如mysql-bin.000001被删除，那么reset slave后再启动会指向哪个位置？