reset masert； reset slave； reset slave all；

渔夫数据库笔记

已于 2022-09-07 16:36:49 修改

阅读量2.2k

点赞数 1

分类专栏： Mysql 文章标签：数据库 mysql 服务器

于 2022-09-07 15:39:11 首次发布

本文链接：https://blog.csdn.net/shaochenshuo/article/details/126745731

版权

Mysql 专栏收录该内容

220 篇文章 9 订阅

订阅专栏

一：reset master (需要RELOAD 权限)

1. reset master 命令

1) 5.7 及一下版本

reset master;

2) 8.0 版本

RESET MASTER [TO binary_log_file_index_number]

# 8.0 开始 reset master 可以通过 to 指定一个合法的序号(该需要不能超过binlog序号的最大值)，这样reset master后binlog序号不是从.000001 开始，而是从你指定的序号开始，例如：

RESET MASTER TO 1234;

SHOW BINARY LOGS;
+-------------------+-----------+-----------+
| Log_name          | File_size | Encrypted |
+-------------------+-----------+-----------+
| source-bin.001234 |       154 | No        |
+-------------------+-----------+-----------+

2."reset master;" 做了哪些动作
1)对于启用binlog的实例，"reset master;" 会删除所有binlog index file中记录的binlog，重置 binlog index file，重新创建一个新的binlog(序号从000001开始，如果想从指定的需要开始，可以使用 reset master to 155; 这样重置后binlog序号从.000155开始)
2)对于启用 GTID 模式的实例，reset slave 会把 gtid_purged， gtid_executed 变量设置为空，mysql.gtid_executed 表也会被清空
3)如果你的架构是主从模式正在运行，在主库和从库上都不要轻易执行"reset master;" 否则从库复制会出现异常

二. "reset slave;"

reset slave(需要RELOAD权限)是各版本Mysql都有的功能，必须在stop slave之后使用。主要做：
1. 删除master.info和relay-log.info文件；
2. 删除所有的relay log（包括还没有应用完的日志），创建一个新的relay log文件；
3. 会把 change master 指定的 MASTER_DELAY 参数设置为0
4. reset slave 不会清空 gtid_executed 和 gtid_purged 变量，也不会清空 mysql.gtid_executed 表(如果想清空 GTID相关信息得使用 reset master)

5.mysql 5.6.7之后，reset slave会隐式提交事务。
6. 清理复制信息

6.1 5.7.24 及以上版本

1) master-info-repository = TABLE

NOTE1：master-info-repository 指定为 TABLE 的情况下，"reset slave;" 命令不会清理保存在mysql.slave_master_info表中的复制参数

NOTE2：不会清理内存中保存的复制信息，show slave status\G; 依然有输出，"start slave;" 可以恢复复制。

2) master-info-repository = FILE

Master_Info_File: /app/mysql/mysql3308/data/master.info

NOTE1："reset slave;" 删除master.info（start slave;后会创建master.info）

NOTE2：不会清理内存中保存的复制信息，show slave status\G; 依然有输出，"start slave;" 可以恢复复制。"reset slave;" 后重启实例复制参数丢失需要重新 change master

6.2 5.7.24 以下版本

1) master-info-repository = TABLE

NOTE1：master-info-repository 指定为 TABLE 的情况下，"reset slave; "命令会清理保存在mysql.slave_master_info表中的复制参数，这时重启实例会丢失复制信息，需要重新change master

NOTE2：不会清理内存中保存的复制信息，show slave status\G; 依然有输出，"start slave;" 可以恢复复制。

2) master-info-repository = FILE

Master_Info_File: /app/mysql/mysql3308/data/master.info

NOTE1："reset slave;" 删除master.info（"start slave;"后会创建master.info）

NOTE2：不会清理内存中保存的复制信息，"show slave status\G; "依然有输出，"start slave;" 可以恢复复制。"reset slave;" 后重启实例复制参数丢失需要重新 change master

三：reset slave all; 命令

从Mysql 5.6.3 开始，reset slave 多了一个all参数。如果不加all参数，那么所有的连接信息仍然保留在内存中，包括主库地址、端口、用户、密码等。这样可以直接运行start slave命令而不必重新输入change master to命令，而运行show slave status也仍和没有运行reset slave一样，有正常的输出。但如果加了all参数，那么这些内存中的数据也会被清除掉，运行show slave status就输出为空了。

1)清除内存中的复制参数

2)清除mysql.slave_master_info表中的复制参数(如果master-info-repository = TABLE)

3)删除/app/mysql/mysql3308/data/master.info(如果master-info-repository = FILE)

7. 对于多源复制(5.7开始支持多源复制) 与 reset slave; reset slave all;

1)使用多源复制时，从库 master_info_repository 参数必须为 TABLE(对应主库的master_info_repository 参数无特殊要求)

ERROR 3077 (HY000): To have multiple channels, repository cannot be of type FILE; Please check the repository configuration and convert them to TABLE.

2)reset slave all;

#因为多源复制 master_info_repository 必须指定为 TABLE,这时 reset slave不会清除任何复制参数(内存中和mysql.slave_master_info表中)，所以下面主要讲 reset slave all; 在多源复制的使用

reset slave/reset slave all 命令可以 for channel channel_name指定具体的 channel。如果reset slave all;没有指定 for channel 则会把所有channel 连接清空(reset slave; 不会清除复制参数)，示例如下：

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 3306-bin.000003
          Read_Master_Log_Pos: 682
               Relay_Log_File: relay_3308-channel_1.000004
                Relay_Log_Pos: 411
        Relay_Master_Log_File: 3306-bin.000003
             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: 682
              Relay_Log_Space: 880
              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: 1
                  Master_UUID: 34d1b759-24ee-11ed-82c4-fa163efcbfd5
             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 more updates
           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: 34d1b759-24ee-11ed-82c4-fa163efcbfd5:1-5,
88cac428-24ee-11ed-9ec5-fa163efcbfd5:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: channel_1
           Master_TLS_Version: 
*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: repl
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: 3307-bin.000001
          Read_Master_Log_Pos: 1182
               Relay_Log_File: relay_3308-channel_2.000003
                Relay_Log_Pos: 411
        Relay_Master_Log_File: 3307-bin.000001
             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: 1182
              Relay_Log_Space: 800
              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: 2
                  Master_UUID: 88cac428-24ee-11ed-9ec5-fa163efcbfd5
             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 more updates
           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: 34d1b759-24ee-11ed-82c4-fa163efcbfd5:1-5,
88cac428-24ee-11ed-9ec5-fa163efcbfd5:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: channel_2
           Master_TLS_Version: 
2 rows in set (0.00 sec)

ERROR: 
No query specified

mysql> reset slave all for channel 'channel_2';
ERROR 3081 (HY000): This operation cannot be performed with running replication threads; run STOP SLAVE FOR CHANNEL 'channel_2' first
mysql> stop slave for channel 'channel_2';
Query OK, 0 rows affected (0.00 sec)

mysql> reset slave all for channel 'channel_2';
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: 127.0.0.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 3306-bin.000003
          Read_Master_Log_Pos: 682
               Relay_Log_File: relay_3308-channel_1.000004
                Relay_Log_Pos: 411
        Relay_Master_Log_File: 3306-bin.000003
             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: 682
              Relay_Log_Space: 880
              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: 1
                  Master_UUID: 34d1b759-24ee-11ed-82c4-fa163efcbfd5
             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 more updates
           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: 34d1b759-24ee-11ed-82c4-fa163efcbfd5:1-5,
88cac428-24ee-11ed-9ec5-fa163efcbfd5:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: channel_1
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> CHANGE MASTER TO
    ->     MASTER_HOST = '127.0.0.1',
    ->     MASTER_PORT = 3307,
    ->     MASTER_USER = 'repl',
    ->     MASTER_PASSWORD = 'root',
    ->     MASTER_AUTO_POSITION = 1
    ->     FOR CHANNEL 'channel_2';
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> start slave for channel 'channel_2';
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 3306-bin.000003
          Read_Master_Log_Pos: 682
               Relay_Log_File: relay_3308-channel_1.000004
                Relay_Log_Pos: 411
        Relay_Master_Log_File: 3306-bin.000003
             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: 682
              Relay_Log_Space: 880
              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: 1
                  Master_UUID: 34d1b759-24ee-11ed-82c4-fa163efcbfd5
             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 more updates
           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: 34d1b759-24ee-11ed-82c4-fa163efcbfd5:1-5,
88cac428-24ee-11ed-9ec5-fa163efcbfd5:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: channel_1
           Master_TLS_Version: 
*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: repl
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: 3307-bin.000001
          Read_Master_Log_Pos: 1182
               Relay_Log_File: relay_3308-channel_2.000002
                Relay_Log_Pos: 411
        Relay_Master_Log_File: 3307-bin.000001
             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: 1182
              Relay_Log_Space: 623
              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: 2
                  Master_UUID: 88cac428-24ee-11ed-9ec5-fa163efcbfd5
             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 more updates
           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: 34d1b759-24ee-11ed-82c4-fa163efcbfd5:1-5,
88cac428-24ee-11ed-9ec5-fa163efcbfd5:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: channel_2
           Master_TLS_Version: 
2 rows in set (0.00 sec)

ERROR: 
No query specified

mysql> reset slave all;
ERROR 3081 (HY000): This operation cannot be performed with running replication threads; run STOP SLAVE FOR CHANNEL 'channel_1' first
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> reset slave all;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
Empty set (0.00 sec)

ERROR: 
No query specified