Sometimes MySQL master-slave synchronization is more serious when it requires manual synchronization.
Let's talk about how to operate in the case of a lock table:
The following is the brief process
1. Lock the table for the main library first
FLUSH TABLES with READ LOCK;
2. Backing up data
Mysqldump-uroot-p-hlocalhost > Mysql.bak.sql
3. Unlocking 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 the file to the slave library
SCP Mysql.bak.sql [Email protected]:/tmp/
6. Stop syncing from the library
mysql> stop Slave;
7. Import data
Mysql> Source/tmp/mysql.bak.sql
8. Set the sync start file and start location from the library
Change Master to Master_host = ' Main Library host ', Master_user = ' sync username ', master_port=3306, master_password= ' ', master_log_file = ' 3rd step Obtained in the 3rd step of the file ', master_log_pos= position;
9. Start the synchronization from the Library and check
mysql> start slave;
Mysql> Show slave STATUSG View:
Slave_io_running:yes
Slave_sql_running:yes
Methods of not locking tables
It is generally not necessary to perform steps 1th and 3rd, and add parameters when exporting data at 2nd
Mysqldump--skip-lock-tables--single-transaction--flush-logs--hex-blob--master-data=2-a
--skip-lock-tables not lock the table
--master-data=2 generates Binlog file name and location at the beginning of the exported file at the time of export. This is important. So the 4th step does not need to be executed, binlog files and locations can be obtained from here.
--single-transaction The exported data is a consistent snapshot by encapsulating the export operation within a single transaction
Finally, the 8th step of the Binlog file and the location of the exported file to find the beginning of the document with the annotated file and position just fine.
MySQL lock table and non-lock table set master-slave replication method