MySQL lock table and non-lock table set master-slave replication method

Source: Internet
Author: User
Tags scp command

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

Related Article

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.