4

Today my question is about MySQL replication cleanup.

I used mysqldump with the --master-info --all-databases tag and restored it to a new host to be used as a replication slave.

After restore, I see some artifacts of slave information from the master. This is the third host in a replication chain.

I issued reset slave for channel 'xxxxx'; which returned Query OK, 0 rows affected (0.00 sec). When I later query using show slave status for channel 'xxxxx';, I still see information for this replication channel appearing.

How can I cleanup this replication channel such that it never accidentally starts, as well as cleanup the output of show slave status \G to only show the intended replication channel?

4
  • I think you should have dumped the Relay Slave, not the original Master. That is dump B to make C in this topology: A->B->C
    – Rick James
    Commented Feb 7, 2017 at 1:14
  • That would typically be the case, however in my scenario I have multi master replication. A1 A2 -> B -> C, simpler to backup from B. Commented Feb 7, 2017 at 1:20
  • A1 <-> A2 -> B. Dump A2 to create B. A1 <-> A2 -> B -> C. Dump B to create C. When you want a new slave of the same master, stop the slave, copy the disk, change server_id, start both slaves.
    – Rick James
    Commented Feb 7, 2017 at 2:03
  • That is the exact scenario that I am working with and have described. There's an artifact of the "previous" master-slave when it moves to the new slave. Commented Feb 7, 2017 at 4:37

3 Answers 3

6

reset slave all for channel '[name]'

3
0

Do NOT run 'reset slave all for channel' without saving your current replication data. It will clear all existing replication data, not only the channel you want to remove. If you don't save the replication data, it will be terrible.

The correct way are:

  1. Stop slave;

  2. show slave status\G

    (save all replication setting, and Master_Log_File, Exec_Master_Log_Pos data)

  3. change master to ....

    (add channels one by one from scratch)

-1

You can delete the single channel by deleting the entry from the table mysql.slave_master_info.

For example: delete from slave_master_info where channel_name='';

And then use the query to show slave status running status something like below:

 SELECT 
    channel_name AS Channel_Name,
    smi.host AS Master_Host,
    smi.user_name AS Master_User,
    smi.port AS Master_Port,
    smi.master_log_name AS Master_Log_File,
    smi.master_log_pos AS Read_Master_Log_Pos,
    ssi.master_log_pos AS Exec_Master_Log_Pos,
    rcs.service_state AS Slave_IO_Running,
    rss.service_state AS Slave_SQL_Running,
    t.processlist_time AS Seconds_Behind_Master,
    rcs.last_error_number AS Last_IO_Errno,
    rcs.last_error_message AS Last_IO_Error,
    rss.last_error_number AS Last_SQL_Errno,
    rss.last_error_message AS Last_SQL_Error,
    tc.processlist_state AS Slave_IO_State,
    t.processlist_state AS Slave_SQL_Running_State
FROM
    mysql.slave_master_info smi
        JOIN
    mysql.slave_relay_log_info ssi USING (channel_name)
        JOIN
    performance_schema.replication_connection_status rcs USING (channel_name)
        LEFT JOIN
    performance_schema.replication_applier_status_by_worker rss USING (channel_name)
        LEFT JOIN
    performance_schema.threads t ON (rss.thread_id = t.thread_id)
        LEFT JOIN
    performance_schema.threads tc ON (rcs.thread_id = tc.thread_id)

Note: DO NOT USE show slave status\G for chanel which are running on the instance, because this command will show all channel.So use the above query.

1
  • Suggesting people run DELETE queries in the mysql database for whatever reason is incredibly bad advice.
    – dland
    Commented Nov 22, 2022 at 14:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.