• reset masert； reset slave； reset slave all；

    一：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 开始，而是从你指定的序号开始，例如：

    1. RESET MASTER TO 1234;
    2.  
    3. SHOW BINARY LOGS;
    4. +-------------------+-----------+-----------+
    5. | Log_name          | File_size | Encrypted |
    6. +-------------------+-----------+-----------+
    7. | source-bin.001234 |       154 | No        |
    8. +-------------------+-----------+-----------+

    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; 不会清除复制参数)，示例如下：

    1. mysql> show slave status\G;
    2. *************************** 1. row ***************************
    3.                Slave_IO_State: Waiting for master to send event
    4.                   Master_Host: 127.0.0.1
    5.                   Master_User: repl
    6.                   Master_Port: 3306
    7.                 Connect_Retry: 60
    8.               Master_Log_File: 3306-bin.000003
    9.           Read_Master_Log_Pos: 682
    10.                Relay_Log_File: relay_3308-channel_1.000004
    11.                 Relay_Log_Pos: 411
    12.         Relay_Master_Log_File: 3306-bin.000003
    13.              Slave_IO_Running: Yes
    14.             Slave_SQL_Running: Yes
    15.               Replicate_Do_DB: 
    16.           Replicate_Ignore_DB: 
    17.            Replicate_Do_Table: 
    18.        Replicate_Ignore_Table: 
    19.       Replicate_Wild_Do_Table: 
    20.   Replicate_Wild_Ignore_Table: 
    21.                    Last_Errno: 0
    22.                    Last_Error: 
    23.                  Skip_Counter: 0
    24.           Exec_Master_Log_Pos: 682
    25.               Relay_Log_Space: 880
    26.               Until_Condition: None
    27.                Until_Log_File: 
    28.                 Until_Log_Pos: 0
    29.            Master_SSL_Allowed: No
    30.            Master_SSL_CA_File: 
    31.            Master_SSL_CA_Path: 
    32.               Master_SSL_Cert: 
    33.             Master_SSL_Cipher: 
    34.                Master_SSL_Key: 
    35.         Seconds_Behind_Master: 0
    36. Master_SSL_Verify_Server_Cert: No
    37.                 Last_IO_Errno: 0
    38.                 Last_IO_Error: 
    39.                Last_SQL_Errno: 0
    40.                Last_SQL_Error: 
    41.   Replicate_Ignore_Server_Ids: 
    42.              Master_Server_Id: 1
    43.                   Master_UUID: 34d1b759-24ee-11ed-82c4-fa163efcbfd5
    44.              Master_Info_File: mysql.slave_master_info
    45.                     SQL_Delay: 0
    46.           SQL_Remaining_Delay: NULL
    47.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
    48.            Master_Retry_Count: 86400
    49.                   Master_Bind: 
    50.       Last_IO_Error_Timestamp: 
    51.      Last_SQL_Error_Timestamp: 
    52.                Master_SSL_Crl: 
    53.            Master_SSL_Crlpath: 
    54.            Retrieved_Gtid_Set: 
    55.             Executed_Gtid_Set: 34d1b759-24ee-11ed-82c4-fa163efcbfd5:1-5,
    56. 88cac428-24ee-11ed-9ec5-fa163efcbfd5:1-2
    57.                 Auto_Position: 1
    58.          Replicate_Rewrite_DB: 
    59.                  Channel_Name: channel_1
    60.            Master_TLS_Version: 
    61. *************************** 2. row ***************************
    62.                Slave_IO_State: Waiting for master to send event
    63.                   Master_Host: 127.0.0.1
    64.                   Master_User: repl
    65.                   Master_Port: 3307
    66.                 Connect_Retry: 60
    67.               Master_Log_File: 3307-bin.000001
    68.           Read_Master_Log_Pos: 1182
    69.                Relay_Log_File: relay_3308-channel_2.000003
    70.                 Relay_Log_Pos: 411
    71.         Relay_Master_Log_File: 3307-bin.000001
    72.              Slave_IO_Running: Yes
    73.             Slave_SQL_Running: Yes
    74.               Replicate_Do_DB: 
    75.           Replicate_Ignore_DB: 
    76.            Replicate_Do_Table: 
    77.        Replicate_Ignore_Table: 
    78.       Replicate_Wild_Do_Table: 
    79.   Replicate_Wild_Ignore_Table: 
    80.                    Last_Errno: 0
    81.                    Last_Error: 
    82.                  Skip_Counter: 0
    83.           Exec_Master_Log_Pos: 1182
    84.               Relay_Log_Space: 800
    85.               Until_Condition: None
    86.                Until_Log_File: 
    87.                 Until_Log_Pos: 0
    88.            Master_SSL_Allowed: No
    89.            Master_SSL_CA_File: 
    90.            Master_SSL_CA_Path: 
    91.               Master_SSL_Cert: 
    92.             Master_SSL_Cipher: 
    93.                Master_SSL_Key: 
    94.         Seconds_Behind_Master: 0
    95. Master_SSL_Verify_Server_Cert: No
    96.                 Last_IO_Errno: 0
    97.                 Last_IO_Error: 
    98.                Last_SQL_Errno: 0
    99.                Last_SQL_Error: 
    100.   Replicate_Ignore_Server_Ids: 
    101.              Master_Server_Id: 2
    102.                   Master_UUID: 88cac428-24ee-11ed-9ec5-fa163efcbfd5
    103.              Master_Info_File: mysql.slave_master_info
    104.                     SQL_Delay: 0
    105.           SQL_Remaining_Delay: NULL
    106.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
    107.            Master_Retry_Count: 86400
    108.                   Master_Bind: 
    109.       Last_IO_Error_Timestamp: 
    110.      Last_SQL_Error_Timestamp: 
    111.                Master_SSL_Crl: 
    112.            Master_SSL_Crlpath: 
    113.            Retrieved_Gtid_Set: 
    114.             Executed_Gtid_Set: 34d1b759-24ee-11ed-82c4-fa163efcbfd5:1-5,
    115. 88cac428-24ee-11ed-9ec5-fa163efcbfd5:1-2
    116.                 Auto_Position: 1
    117.          Replicate_Rewrite_DB: 
    118.                  Channel_Name: channel_2
    119.            Master_TLS_Version: 
    120. 2 rows in set (0.00 sec)
    121.  
    122. ERROR: 
    123. No query specified
    124.  
    125. mysql> reset slave all for channel 'channel_2';
    126. ERROR 3081 (HY000): This operation cannot be performed with running replication threads; run STOP SLAVE FOR CHANNEL 'channel_2' first
    127. mysql> stop slave for channel 'channel_2';
    128. Query OK, 0 rows affected (0.00 sec)
    129.  
    130. mysql> reset slave all for channel 'channel_2';
    131. Query OK, 0 rows affected (0.00 sec)
    132.  
    133. mysql> show slave status\G;
    134. *************************** 1. row ***************************
    135.                Slave_IO_State: Waiting for master to send event
    136.                   Master_Host: 127.0.0.1
    137.                   Master_User: repl
    138.                   Master_Port: 3306
    139.                 Connect_Retry: 60
    140.               Master_Log_File: 3306-bin.000003
    141.           Read_Master_Log_Pos: 682
    142.                Relay_Log_File: relay_3308-channel_1.000004
    143.                 Relay_Log_Pos: 411
    144.         Relay_Master_Log_File: 3306-bin.000003
    145.              Slave_IO_Running: Yes
    146.             Slave_SQL_Running: Yes
    147.               Replicate_Do_DB: 
    148.           Replicate_Ignore_DB: 
    149.            Replicate_Do_Table: 
    150.        Replicate_Ignore_Table: 
    151.       Replicate_Wild_Do_Table: 
    152.   Replicate_Wild_Ignore_Table: 
    153.                    Last_Errno: 0
    154.                    Last_Error: 
    155.                  Skip_Counter: 0
    156.           Exec_Master_Log_Pos: 682
    157.               Relay_Log_Space: 880
    158.               Until_Condition: None
    159.                Until_Log_File: 
    160.                 Until_Log_Pos: 0
    161.            Master_SSL_Allowed: No
    162.            Master_SSL_CA_File: 
    163.            Master_SSL_CA_Path: 
    164.               Master_SSL_Cert: 
    165.             Master_SSL_Cipher: 
    166.                Master_SSL_Key: 
    167.         Seconds_Behind_Master: 0
    168. Master_SSL_Verify_Server_Cert: No
    169.                 Last_IO_Errno: 0
    170.                 Last_IO_Error: 
    171.                Last_SQL_Errno: 0
    172.                Last_SQL_Error: 
    173.   Replicate_Ignore_Server_Ids: 
    174.              Master_Server_Id: 1
    175.                   Master_UUID: 34d1b759-24ee-11ed-82c4-fa163efcbfd5
    176.              Master_Info_File: mysql.slave_master_info
    177.                     SQL_Delay: 0
    178.           SQL_Remaining_Delay: NULL
    179.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
    180.            Master_Retry_Count: 86400
    181.                   Master_Bind: 
    182.       Last_IO_Error_Timestamp: 
    183.      Last_SQL_Error_Timestamp: 
    184.                Master_SSL_Crl: 
    185.            Master_SSL_Crlpath: 
    186.            Retrieved_Gtid_Set: 
    187.             Executed_Gtid_Set: 34d1b759-24ee-11ed-82c4-fa163efcbfd5:1-5,
    188. 88cac428-24ee-11ed-9ec5-fa163efcbfd5:1-2
    189.                 Auto_Position: 1
    190.          Replicate_Rewrite_DB: 
    191.                  Channel_Name: channel_1
    192.            Master_TLS_Version: 
    193. 1 row in set (0.00 sec)
    194.  
    195. ERROR: 
    196. No query specified
    197.  
    198. mysql> CHANGE MASTER TO
    199.     ->     MASTER_HOST = '127.0.0.1',
    200.     ->     MASTER_PORT = 3307,
    201.     ->     MASTER_USER = 'repl',
    202.     ->     MASTER_PASSWORD = 'root',
    203.     ->     MASTER_AUTO_POSITION = 1
    204.     ->     FOR CHANNEL 'channel_2';
    205. Query OK, 0 rows affected, 1 warning (0.03 sec)
    206.  
    207. mysql> start slave for channel 'channel_2';
    208. Query OK, 0 rows affected (0.01 sec)
    209.  
    210. mysql> show slave status\G;
    211. *************************** 1. row ***************************
    212.                Slave_IO_State: Waiting for master to send event
    213.                   Master_Host: 127.0.0.1
    214.                   Master_User: repl
    215.                   Master_Port: 3306
    216.                 Connect_Retry: 60
    217.               Master_Log_File: 3306-bin.000003
    218.           Read_Master_Log_Pos: 682
    219.                Relay_Log_File: relay_3308-channel_1.000004
    220.                 Relay_Log_Pos: 411
    221.         Relay_Master_Log_File: 3306-bin.000003
    222.              Slave_IO_Running: Yes
    223.             Slave_SQL_Running: Yes
    224.               Replicate_Do_DB: 
    225.           Replicate_Ignore_DB: 
    226.            Replicate_Do_Table: 
    227.        Replicate_Ignore_Table: 
    228.       Replicate_Wild_Do_Table: 
    229.   Replicate_Wild_Ignore_Table: 
    230.                    Last_Errno: 0
    231.                    Last_Error: 
    232.                  Skip_Counter: 0
    233.           Exec_Master_Log_Pos: 682
    234.               Relay_Log_Space: 880
    235.               Until_Condition: None
    236.                Until_Log_File: 
    237.                 Until_Log_Pos: 0
    238.            Master_SSL_Allowed: No
    239.            Master_SSL_CA_File: 
    240.            Master_SSL_CA_Path: 
    241.               Master_SSL_Cert: 
    242.             Master_SSL_Cipher: 
    243.                Master_SSL_Key: 
    244.         Seconds_Behind_Master: 0
    245. Master_SSL_Verify_Server_Cert: No
    246.                 Last_IO_Errno: 0
    247.                 Last_IO_Error: 
    248.                Last_SQL_Errno: 0
    249.                Last_SQL_Error: 
    250.   Replicate_Ignore_Server_Ids: 
    251.              Master_Server_Id: 1
    252.                   Master_UUID: 34d1b759-24ee-11ed-82c4-fa163efcbfd5
    253.              Master_Info_File: mysql.slave_master_info
    254.                     SQL_Delay: 0
    255.           SQL_Remaining_Delay: NULL
    256.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
    257.            Master_Retry_Count: 86400
    258.                   Master_Bind: 
    259.       Last_IO_Error_Timestamp: 
    260.      Last_SQL_Error_Timestamp: 
    261.                Master_SSL_Crl: 
    262.            Master_SSL_Crlpath: 
    263.            Retrieved_Gtid_Set: 
    264.             Executed_Gtid_Set: 34d1b759-24ee-11ed-82c4-fa163efcbfd5:1-5,
    265. 88cac428-24ee-11ed-9ec5-fa163efcbfd5:1-2
    266.                 Auto_Position: 1
    267.          Replicate_Rewrite_DB: 
    268.                  Channel_Name: channel_1
    269.            Master_TLS_Version: 
    270. *************************** 2. row ***************************
    271.                Slave_IO_State: Waiting for master to send event
    272.                   Master_Host: 127.0.0.1
    273.                   Master_User: repl
    274.                   Master_Port: 3307
    275.                 Connect_Retry: 60
    276.               Master_Log_File: 3307-bin.000001
    277.           Read_Master_Log_Pos: 1182
    278.                Relay_Log_File: relay_3308-channel_2.000002
    279.                 Relay_Log_Pos: 411
    280.         Relay_Master_Log_File: 3307-bin.000001
    281.              Slave_IO_Running: Yes
    282.             Slave_SQL_Running: Yes
    283.               Replicate_Do_DB: 
    284.           Replicate_Ignore_DB: 
    285.            Replicate_Do_Table: 
    286.        Replicate_Ignore_Table: 
    287.       Replicate_Wild_Do_Table: 
    288.   Replicate_Wild_Ignore_Table: 
    289.                    Last_Errno: 0
    290.                    Last_Error: 
    291.                  Skip_Counter: 0
    292.           Exec_Master_Log_Pos: 1182
    293.               Relay_Log_Space: 623
    294.               Until_Condition: None
    295.                Until_Log_File: 
    296.                 Until_Log_Pos: 0
    297.            Master_SSL_Allowed: No
    298.            Master_SSL_CA_File: 
    299.            Master_SSL_CA_Path: 
    300.               Master_SSL_Cert: 
    301.             Master_SSL_Cipher: 
    302.                Master_SSL_Key: 
    303.         Seconds_Behind_Master: 0
    304. Master_SSL_Verify_Server_Cert: No
    305.                 Last_IO_Errno: 0
    306.                 Last_IO_Error: 
    307.                Last_SQL_Errno: 0
    308.                Last_SQL_Error: 
    309.   Replicate_Ignore_Server_Ids: 
    310.              Master_Server_Id: 2
    311.                   Master_UUID: 88cac428-24ee-11ed-9ec5-fa163efcbfd5
    312.              Master_Info_File: mysql.slave_master_info
    313.                     SQL_Delay: 0
    314.           SQL_Remaining_Delay: NULL
    315.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
    316.            Master_Retry_Count: 86400
    317.                   Master_Bind: 
    318.       Last_IO_Error_Timestamp: 
    319.      Last_SQL_Error_Timestamp: 
    320.                Master_SSL_Crl: 
    321.            Master_SSL_Crlpath: 
    322.            Retrieved_Gtid_Set: 
    323.             Executed_Gtid_Set: 34d1b759-24ee-11ed-82c4-fa163efcbfd5:1-5,
    324. 88cac428-24ee-11ed-9ec5-fa163efcbfd5:1-2
    325.                 Auto_Position: 1
    326.          Replicate_Rewrite_DB: 
    327.                  Channel_Name: channel_2
    328.            Master_TLS_Version: 
    329. 2 rows in set (0.00 sec)
    330.  
    331. ERROR: 
    332. No query specified
    333.  
    334. mysql> reset slave all;
    335. ERROR 3081 (HY000): This operation cannot be performed with running replication threads; run STOP SLAVE FOR CHANNEL 'channel_1' first
    336. mysql> stop slave;
    337. Query OK, 0 rows affected (0.00 sec)
    338.  
    339. mysql> reset slave all;
    340. Query OK, 0 rows affected (0.00 sec)
    341.  
    342. mysql> show slave status\G;
    343. Empty set (0.00 sec)
    344.  
    345. ERROR: 
    346. No query specified

  • 相关阅读:
    3. 运行时间
    【MySQL】2.3.4-NULL字段如何参与运算(空值参与运算)
    【python】—— python的基本介绍并附安装教程
    anaconda3报错Can‘t find libdevice directory解决方案
    芯片的发展史和具体用途以及结构是什么样的
    组合数学笔记-特殊计数数列
    数据结构与算法 | 第一章：概论
    SpringBoot SpringBoot 开发实用篇 4 数据层解决方案 4.10 MongoDB 基础操作
    力扣精选算法100道——提莫攻击（模拟专题）
    如何经营咖啡加盟店和独立咖啡店？用好国外成功经营咖啡馆四大法则
  • 原文地址：https://blog.csdn.net/shaochenshuo/article/details/126745731