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:
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:
|GRANT REPLICATION SLAVE,FILE ON . TO 'replication'@'192.168.106.%'|
|1.||IDENTIFIED BY 'mysqlpassword';|
Alibaba Clouder - May 7, 2018
Alibaba Clouder - August 1, 2017
Alibaba Clouder - September 10, 2018
Michelle - July 25, 2018
Alibaba Clouder - October 22, 2018
Alibaba Clouder - February 24, 2018
An on-demand database hosting service for MySQL with automated monitoring, backup and disaster recovery capabilitiesLearn More
A secure solution to migrate TB-level or PB-level data to Alibaba Cloud.Learn More
A premium, serverless, and interactive analytics serviceLearn More
Supports data migration and data synchronization between data engines, such as relational database, NoSQL and OLAPLearn More
More Posts by Alibaba Clouder