How to reset/clear and MySQL replication

Here i’m going to explain, step’s to reset/clear MySQL (Master-Slave) replication.

Two way of RESET is must be handle, so that it will reset/clear properly.

Tested Environment.

Both Remote Server OS CentOS release 6.5 Server (Final)
Both Remote Server MySQL Version 5.5.37
Master Server IP 83.170.117.116
Slave Server IP 83.170.117.139

Slave Server Reset

First i’m going to Reset the Slave server Replication.

[email protected] [~]# mysql -u root -p
Enter Password : ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.37-cll MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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.

Stop the Slave server, Using below commands.

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Use the below command to reset the slave configuration.

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

Now, check the slave status, its shows empty.

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

Checking the mirroring database, yes its there.

mysql> show databases;
+-------------------------+
| Database                |
+-------------------------+
| cloudana_cloud          |
| emailnew_phplist10      |
| emailnew_phplist11      |
| emailnew_phplist12      |
| emailnew_phplist13      |
| daygeek_daygeek         |
| mysql                   |
+-------------------------+
42 rows in set (0.20 sec)

I’m going to drop the database.

mysql> drop database daygeek_daygeek;
Query OK, 33 rows affected (0.36 sec)

When you drop the database, it will not drop the user, check the below command.

mysql> select user,host from mysql.user where user='daygeek_daygeek';
+-----------------+-----------+
| user            | host      |
+-----------------+-----------+
| daygeek_daygeek | localhost |
+-----------------+-----------+
1 row in set (0.00 sec)

I’m going to drop the user.

mysql> drop user 'daygeek_daygeek'@'localhost';
Query OK, 0 rows affected (0.01 sec)

If you done any activity on mysql, you should flush the privileges. So that it will take effect.

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Now, i’m again checking whether the user was dropped or not. Yes, its dropped.

mysql> select user,host from mysql.user where user='daygeek_daygeek';
Empty set (0.00 sec)

Finally logout from database server.

mysql> exit
Bye

And remove the lines which you added in my.cnf file.

[email protected] [~]# nano /etc/my.cnf
  GNU nano 2.0.9                                           File: /etc/my.cnf

[mysqld]
local-infile=0
open_files_limit=9068
innodb_file_per_table=1
default-storage-engine=MyISAM


#Replication
#server-id = 2
#master-host=83.170.117.116
#master-connect-retry=60
#master-user=slave_daygeek
#master-password=daygeek123
#replicate-do-db=daygeek_daygeek
#relay-log = /var/lib/mysql/mysql-relay-bin
#relay-log-index = /var/lib/mysql/mysql-relay-bin.index
#log-error = /var/lib/mysql/mysql.err
#master-info-file = /var/lib/mysql/mysql-master.info
#relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
#log-bin = /var/lib/mysql/mysql-bin

Finally restart the mysql services.

[email protected] [~]# service mysql restart
Shutting down MySQL...... SUCCESS!
Starting MySQL. SUCCESS!

Now, you have successfully Reset Slave server configuration.

Master Server Reset

Here i’m going to reset the Master server for Replication.

[email protected] [~]# mysql -u root -p
Enter Password : *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 41428
Server version: 5.1.73-cll MySQL Community Server (GPLv2)

Copyright (c) 2000, 2013, 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.

Navigate the mysql database and delete the user.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Delete the replication user which you created earlier.

mysql> delete from user where User="slave_daygeek";
Query OK, 1 row affected (0.00 sec)

If you done any activity on mysql, you should flush the privileges. So that it will take effect.

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Finally logout from database server.

mysql> exit
Bye

And remove the lines which you added in my.cnf file.

[email protected] [~]# nano /etc/my.cnf
  GNU nano 2.0.9                                           File: /etc/my.cnf

[mysqld]
innodb_file_per_table=1
open_files_limit=20000
local-infile=0

#Replication
#server-id = 1
#binlog-do-db=daygeek_daygeek
#relay-log = /var/lib/mysql/mysql-relay-bin
#relay-log-index = /var/lib/mysql/mysql-relay-bin.index
#log-error = /var/lib/mysql/mysql.err
#master-info-file = /var/lib/mysql/mysql-master.info
#relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
#log-bin = /var/lib/mysql/mysql-bin

Finally restart the mysql services.

[email protected] [~]# service mysql restart
Shutting down MySQL...... SUCCESS!
Starting MySQL. SUCCESS!

That’s it. Now, you have successfully cleared the mysql replication to both mysql server

We are preparing all articles in-depth to understand by all level/stage Linux administrators. If the article is useful for you, then please spend less than a minute to share your valuable comments in our commenting section.

Please stay tune with us…Good Luck.

Magesh Maruthamuthu

Love to play with all Linux distribution

You may also like...