Drop MySql Replication Slave Configuration

In a previous blog, we had created File Based Master Slave Replication Click here to read more. In this post, we will remove the same master slave configuration. Below are detailed steps:

Login to mysql slave instance and use command show slave status \G to get the current configuration.

[root@test-machine02 ~]# mysql -hlocalhost -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.114.230
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: binlog.000016
          Read_Master_Log_Pos: 66378595
               Relay_Log_File: relay_bin01.000039
                Relay_Log_Pos: 4
        Relay_Master_Log_File: binlog.000016
             Slave_IO_Running: No
            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: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 66378595
              Relay_Log_Space: 0
              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: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID: 13ecba9c-444a-11eb-a397-000c29f9d9e6
             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:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

Run the following commands in slave instance.

mysql>
mysql> STOP SLAVE;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST=' ';
Query OK, 0 rows affected (0.14 sec)

mysql> RESET SLAVE;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql>

Then you need to remove the Slave configuration from mysql config. file (my.cnf). You can comment out or remove parameters with line server, master, relay from my.cnf.

[root@test-machine02 data]# vi /etc/my.cnf
[mysqld@inst1]
user=mysql
basedir=/u01/mysql-8
datadir=/u01/mysql-8/data
socket=/u01/mysql-8/mysql.sock
pid_file=/u01/mysql-8/mysqld.pid

innodb_directories="/u01/mysql-8;/u01/mysql"

server_id = 2
log_bin = /u01/mysql-8/data/binlog
log_error = /u01/mysql-8/data/mysqld.log

#relay_log = /u01/mysql-8/data/relay_bin01
#relay_log_index = /u01/mysql-8/data/relay_bin.index

#relay_log_recovery = on

#master_info_file = /u01/mysql-8/data/master.info
#relay_log_info_file = /u01/mysql-8/data/relay_log.info

#master_info_repository = TABLE
#relay_log_info_repository = TABLE

#read_only = 1
#super_read_only = 1

Once the above changes are done in the config file (my.cnf) restart mysql slave instance to reload the new configuration and check the status of the slave using the command show slave status. Now the slave is disabled as it returns an empty set.

[root@test-machine02 data]# service mysqld@inst1 restart
Redirecting to /bin/systemctl restart [email protected]
[root@test-machine02 data]#

mysql> show slave status;
Empty set, 1 warning (0.01 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.