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 mysqld@inst1.service
[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.