Master-Slave Synchronization for MySQL

Abstract

MySQL is the world's most popular open-source database. As an important part of LAMP, a combination of open-source software (Linux + Apache + MySQL + Perl/PHP/Python), MySQL is widely used in various applications. Chinese Internet forum system Discuz! and blogging platform WordPress, which swept the Internet in the Web 2.0 era, are both built based on a MySQL-based underlying architecture. In the Web 3.0 era, leading Internet companies such as Alibaba, Facebook, and Google have all built mature and large database clusters based on the more flexible MySQL.

Alibaba ApsaraDB for MySQL has consistently provided excellent performance and throughput during many November 11 shopping festivals, withstanding highly concurrent and massive data traffic. Additionally, Alibaba ApsaraDB for MySQL provides a wide range of advanced features, such as optimized read/write splitting, data compression, and intelligent optimization.

Read/write splitting enables the master database to handle INSERT, UPDATE, and DELETE operations on transactions, and the slave database to handle SELECT queries. Read/write splitting constitutes an important means for improving performance in large-scale and high-concurrency systems. The synchronization of master and slave databases in MySQL is the foundation of read/write splitting, making the management of said synchronization very important.

RDS for MySQL data synchronization mode

RDS for MySQL 5.1 synchronizes data between the primary and standby databases asynchronously. This mode boasts high performance but with a certain possibility of data inconsistency between the primary and standby databases.

RDS for MySQL 5.5 adopts a semi-synchronous mode for data synchronization between the primary and standby databases. This mode decreases the write performance but greatly lowers the possibility of data inconsistency. If you have high requirements on data reliability, such as for finance applications, we recommend RDS for MySQL Version 5.5 or above.

RDS for MySQL 5.6 adopts GTID (new in MySQL 5.6) for data synchronization between the primary and standby databases. This feature guarantees both the performance and data consistency.

Role of MySQL master-slave synchronization:

1.Data distribution
2.Load balancing
3.Copy
4.High availability and error tolerance

The process of master-slave synchronization is as follows:

1.The master server verifies the connection.
2.The master server opens a thread for the slave server.
3.The slave server notifies the master server of the master server log's offset bit.
4.The master server checks whether the value is smaller than the offset bit of the current binary log.
5.If so, it notifies the slave server to fetch the data.
6.The slave server keeps fetching data from the master server until all the data has been obtained. Then the slave server and master server thread enter sleep mode simultaneously.
7.When there is an update to the master server, the master server thread is activated and pushes the binary log to the slave server, signaling the slave server thread to run.
8.The slave server SQL thread executes the binary log and then sleeps.

Process of establishing MySQL master-slave synchronization:

(1) Master-slave synchronization environment

OS: CentOS 64-bit
MySQL version: MySQL 5.1
Master server IP address: 192.168.106.1
Slave server IP address: 192.168.106.2

(2) Create the synchronization account on the master server

When setting the ACL, ensure that your password is not too simple:

(3) Change the slave server configuration file

server-id = 2

replicate-wild-ignore-table=mysql.%

log-slave-updates #This option can be enabled as needed.

(4) Get a snapshot version from the slave server

If you use MyISAM or MyISAM and InnoDB at the same time, run the following command on your master server to export a snapshot of your server:

mysqldump -uroot -p --lock-tables --events --triggers --routines --flush-logs --master-data=2 --databases test> db.sql

If you are only using InnoDB, use the following command:

mysqldump -uroot -p --single-transaction --events --triggers --routines --flush-logs --master-data=2 --databases test > db.sql

Here you must make note of the following parameters:

--single-transaction: This parameter only applies to InnoDB.
--Databases: Fill in the names of all the other databases other than MySQL in the field. Here I only have a test database.
--Master-data: This parameter records the location of the MySQL binary log when the snapshot is exported. The location will be used later.

(5) Restore the snapshot to the slave server

mysqldump -uroot -p -h 192.168.106.2 test < db.sql

After the snapshot is restored to the slave server, the data on the slave server is consistent with that on the master server.

(6) Synchronize data from the master server to the slave server using the "change master" command

Use the "grep" command to find the name and location of the binary log.

# grep -i "change master" db.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=106;

Generate the "CHANGE MASTER" statement and then execute the statement on the slave server.

STOP SLAVE;
CHANGE MASTER TO

MASTER_HOST='192.168.106.1',MASTER_USER='replication',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=106;

START SLAVE;

(7) By this point we have completed the establishment of the master-slave synchronization mechanism. To view the statuses of Slave_IO_Running and Slave_SQL_Running, we can use the command:

SHOW SLAVE STATUS;

If both of them are "Yes", the configuration is successful.

Note: Do not write the synchronized information to the configuration file as it will complicate management, especially when there is a change and the server needs to be restarted.