Shulou(Shulou.com)06/02 Report--

This article mainly explains the "MySQL manual master-slave synchronization unlocked table implementation process", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "MySQL manual master-slave synchronization does not lock the table of the implementation process"!

Sometimes when the inconsistency between master and slave synchronization in MySQL is serious, manual synchronization is needed.

First, let's talk about how to do this when the table is locked:

The following is a brief process:

1. Lock the table on the main library first

FLUSH TABLES WITH READ LOCK

two。 Backup data

Mysqldump-uroot-p-hlocalhost > mysql.bak.sql

3. Unlock the main library

Unlock tables

4. View the binlog file and location of the main library

Show master status;, find File and Position.

5. Use the scp command to move files to the slave library

Scp mysql.bak.sql root@192.168.128.101:/tmp/

6. Stop synchronization on the slave library

Mysql > stop slave

7. Import data

Mysql > source / tmp/mysql.bak.sql

8. Set the start file and start location from the synchronization of the library

Change master to master_host = 'main library host', master_user = 'synchronous user name', master_port=3306, master_password='', master_log_file = 'file', master_log_pos= obtained in step 3 position obtained in step 3

9. Start synchronization from the library and check

Mysql > start slave

Mysql > show slave status\ G View:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes 、

The method of not locking the table:

There is roughly no need to perform steps 1 and 3, adding parameters when the data is exported in the second.

Mysqldump-skip-lock-tables-single-transaction-flush-logs-hex-blob-master-data=2-A

-- skip-lock-tables does not lock the table

-- master-data=2 generates both the binlog file name and location at the beginning of the exported file when it is exported. This is very important. So step 4 does not need to be performed, and the file and location of the binlog can be obtained here.

Single-transaction makes the exported data a consistent snapshot by encapsulating the export operation in a transaction

Finally, replace the binlog file and location in step 8 with the annotated file and position found at the beginning of the exported file.

Thank you for your reading, the above is the content of "MySQL manual master-slave synchronization unlocked table realization process". After the study of this article, I believe you have a deeper understanding of the realization process of MySQL manual master-slave synchronization unlocked table, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.