Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use RESET MASTER and RESET SLAVE in mysql. It is very detailed and has a certain reference value. Friends who are interested must finish it!

[1] RESET MASTER parameter

Function description: delete all binglog log files, empty the log index files, and restart all new log files. It is used to initialize the master library binlog when building the master-slave library for the first time.

The tests are as follows:

Before deletion

[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 location of 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 has been deleted and binlog has been recorded since 000001

Note: when the database wants to clean up the binlog file, it can be deleted through the operating system or by running reset master. However, if it is currently the master database and the master-slave database is normal, you must not delete it in this way.

[usage scenario] when the master-slave database is built for the first time, it is used to initialize the binglog operation of the master database

[II] RESET SLAVE

Function description: used to delete relaylog log files of SLAVE database and re-enable new relaylog files

Log in from the 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. This statement will be used for a clean startup, deleting the master.info file and relay-log.info file as well as all relaylog files and re-enabling a new relaylog file.

Usage scenario: when the original master-slave relationship is destroyed, the direct connection to the slave library will report an error of ERROR 1201 after reinitialization. After running reset slave, you can reconfigure the master-slave connection.

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 be found in the MySQL error log

The above is all the contents of the article "how to use RESET MASTER and RESET SLAVE in mysql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.