Reset master and reset Slave use scenarios and instructions, and purge master-slave sync relationships

MySQL master-slave replication, you need to promote the main library from the library, you need to remove it from the library role, which can be performed by the reset SLAVE all clear from the library replication information, including connection information and binary file name, location. After this command is executed from the library, there will be no output using show slave status.
Original link

Reset SLAVE vs. reset SLAVE all:disconnecting A replication SLAVE is easier with MySQL 5.5+


Reference Link: http://www.tuicool.com/articles/ZJnUn2


mysql> stop Slave;
Queryok, 0 rowsaffected (0,00 sec)

mysql> Reset Slave All;
Queryok, 0 rowsaffected (0,04 sec)

Mysql> Show Slave Status\g
Emptyset (0,00 sec)
The purge slave synchronous replication relationship is really implemented at this time.


--------------------------------------------------------------------------------


"One" RESET Master parameter


Feature Description: Delete all binglog log files and empty the log index file to restart all new log files. For the first time to build the master and slave Library, the main library binlog initialization work;





The test is as follows:


Before deleting


[Root@mysql01 mysql]# pwd
/data/mysql




[Root@mysql01 mysql]# ls






Mysql> Show Master Status\g;
1. Row ***************************
file:mysql-bin.000025
position:107
binlog_do_db:
binlog_ignore_db:
1 row in Set (0.01 sec)




There are currently 25 Binlong logs and the position position is 107



Run Reset MASTER


mysql> Reset Master;
Query OK, 0 rows affected (0.03 sec)




Mysql> Show Master Status\g;
1. Row ***************************
file:mysql-bin.000001
position:107
binlog_do_db:
binlog_ignore_db:
1 row in Set (0.00 sec)












Shows that all binlog have been deleted and the Binlog is logged from 000001





Note: When the database is to clean the Binlog file, it can be deleted through the operating system, or reset master can be deleted. However, if you are currently the primary database, and the master and subordinate database is normal, you must not be deleted in this way.


"Use Scene" for the first time to build master-slave database, for the main library initialization binglog operation;




"Two" RESET SLAVE


Feature Description: Used to delete the Relaylog log file of the slave database and re-enable the new Relaylog file;


Login from database, not deleted before
Mysql> show Slave status\g;
1. Row ***************************
Slave_io_state:connecting to Master
master_host:192.168.47.167
Master_user:server
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000019
read_master_log_pos:12992
relay_log_file:mysql02-relay-bin.000004
Relay_log_pos:4
relay_master_log_file:mysql-bin.000019







Current Relaylog is 0004;





After deletion


mysql> stop Slave; Stop slave first.
Query OK, 0 rows affected (0.01 sec)


mysql> Reset Slave;
Query OK, 0 rows affected (0.04 sec)





Mysql> show Slave status\g;
1. Row ***************************
Slave_io_state:
master_host:192.168.47.167
Master_user:server
master_port:3306
Connect_retry:60
Master_log_file:
Read_master_log_pos:4
relay_log_file:mysql02-relay-bin.000001
Relay_log_pos:4









RESET slave will make slave forget the location information of the master-slave replication relationship. The statement will be used for a clean boot, which deletes the master.info and Relay-log.info files, as well as all the relay log files, and re-enable a new Relaylog file.


Use the scene: When the original master-slave relationship is destroyed, the direct connection from the library after reinitialization will report error 1201 errors, run reset slave, reconfigure the master-slave connection can be;


mysql> change MASTER to master_host= ' 192.168.0.167 ', master_user= ' test ', master_password= ' test ', master_log_file= ' Mysql-bin.000001 ', master_log_pos=176658;








ERROR 1201 (HY000): Could not initialize master info structure; More error messages can is found in the MySQL error log






Summary: If you need to delete the MySQL binlog and relaylog files, then the deletion of the operating system or purge commands can be, but when it comes to MySQL master and slave configuration, you need to use reset master and reset Slave to solve the problem