MYSQL user experience (8)-configuration and recovery of online master-slave replication cluster and master-master Configuration

Source: Internet
Author: User
MYSQL usage experience (8)-configuration and recovery of online master-slave replication cluster and online execution of master-slave configuration FLUSHTABLESWITHREADLOCK; UNLOCKTABLES; there is a master.info file under the slave database data folder, is unique from the machine. Reset slave Database: used to make the slave server forget its binary log in the master server

MYSQL usage experience (8)-configuration and recovery of online master-slave replication cluster and online execution of master-slave configuration flush tables with read lock; unlock tables; in the slave database data folder, there is a master.info file, which is unique on the machine. Reset slave Database: used to make the slave server forget its binary log in the master server

MYSQL usage experience (8)-configuration and recovery of online master-slave replication cluster and master-master Configuration
Online Master/Slave Configuration
Flush tables with read lock;
Unlock tables;

In the slave database data folder, there is a master.info file, which is unique on the slave machine.

Reset slave Database: used to make the slave server forget its replication location in the master server's binary log, which deletes the master.info and relay-log.info files, and all the relay logs, and start a new relay log. You can perform this operation from the top when you do not need the master or slave node.
Reset slave;

Reconfigure the master database (delete binlog)
Reset master;

View copy logs
Show binlog events in 'mysql-bin.000004 '\ G;



Master Configuration
Pay attention to configuration
[Mysqld]
# Step Value
Auto_increment_increment = 2
# Start offset
Auto_increment_offset = 1

# Enable the slave server to record the copied event to its own binary log
Log_slave_updates = 1

Automatically delete binlog
Set to automatically clear MySQL binlog and configure my. cnf:
Expire_logs_days = 10


Online Master/Slave recovery
1) First, lock the table of the master database.
Flush tables with read lock;

2) copy the physical files of the master database to the slave database to ensure the consistency of the data files. (We recommend that you use mysqldump to ensure data consistency)

3) record

Master_log_file
Master_log_pos

4) unlock the table in the master database. Although the configuration is not completed in the slave database, writing data does not matter. We have recorded the offset position of the bin-log.
Unlock tables;

5) New Master/Slave configurations in the slave Database



Additional reading

In the slave database data folder, there is a master.info file, which is unique on the slave machine.

Reset slave Database: used to make the slave server forget its replication location in the master server's binary log, which deletes the master.info and relay-log.info files, and all the relay logs, and start a new relay log. You can perform this operation from the top when you do not need the master or slave node.
Reset slave;

Online master/Master replication recovery
Same as above, lock table, copy the same physical file (mysqldump is recommended to ensure data consistency), and record 2

Master_log_file
Master_log_pos

Then unlock

Configure master-slave Synchronization

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.