Reset master and reset slave use scenarios and descriptions

"Preface" in the configuration of master and slave often use these two statements, at the beginning of the time is not clear the use of these two statements and use of the scene.

After the test collated the following documents, hope to be helpful to everyone;

"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;

Note that reset master differs from the two places in purge binary log
1. Reset Master Deletes all the Binlog files recorded in the log index file and creates a new log file starting value starting at 000001, whereas the purge binary log command does not modify the values of the order in which records are binlog.
2. Reset master cannot be used in a master-slave relationship that has any slave running. Because the Reset Master command is not supported at slave run time, reset master records master's Binlog from 000001, slave the master log is the latest binlo of the main library at reset Master G, the specified Binlog file cannot be found with the error from the library.

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;

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.

You must use the Stop slave command to stop the replication process before using reset slave.

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 lo G

Summary: If it is necessary to delete the MySQL binlog and relaylog files, then through the operation of the operating system or purge commands can be, but related to MySQL master and subordinate configuration when the need to use reset master and reset Slave to solve the problem;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

The writer: JOHN, a public company DBA, spends his spare time focusing on the technical management of the database and applying technology from a management perspective.

Technology blog: Hunter Notes database Technology Group: 367875324 (Please note database type)

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++