Reset master and reset slave usage scenarios and instructions

Source: Internet
Author: User

"One" RESET Master parameter

Function Description: Delete all the Binglog log files and empty the log index file and start all new log files again. For the first time to build the master-slave library, the main library binlog initialization work;

The test is as follows:

Before deleting

[Email protected] mysql]# pwd
/data/mysql


[[email protected] 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 location 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)

Show that all binlog have been removed and Binlog start from 000001

Note: When the database cleans up the Binlog file, it can be deleted by the operating system, or it can be deleted by running reset master. However, if you are currently the primary database and the master and slave databases are normal, you must not delete them in this way.

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


"Two" RESET SLAVE

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

log in from database, before deleting

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

The 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 clean startup, it deletes the Master.info file and the Relay-log.info file, and all the relay log files and re-enables a new Relaylog file.

Usage Scenario: When the original master-slave relationship is destroyed, the direct connection from the library after reinitialization will report error 1201 errors, after running 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 MySQL Binlog and relaylog files, then the removal of the operating system or purge command can be, but the MySQL master and slave configuration will need to use reset master and reset Slave Problem Solving

Reset master and reset slave usage scenarios and instructions

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.