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 testing and finishing the following documents, I hope to help you; ### "one" RESET Master parameter function Description: Delete all binglog log files, and empty the log index file, restart all new log files. For the first time to build master and slave library, the main library binlog initialization work; Note that reset master differs from the 1\ of two places in purge binary log. Reset master Deletes all 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 numeric 2\ of the order in which the records Binlog. 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: <span style= "color: #ff0000;" >mysql-bin.000025
</span>        Position: <span style= "color: #ff0000;" >107
</span>    binlog_do_db:
binlog_ignore_db:
1 row in Set (0.01 sec)

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

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;                
First stop slave
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 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)

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

Turn from: https://blog.csdn.net/yabingshi_tech/article/details/50736735