Community Blog How to Synchronize Master-Slave Data for MySQL

How to Synchronize Master-Slave Data for MySQL

In this article, you will get some information on the synchronization of master and slave databases in MySQL.

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.

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 for data synchronization between the primary and standby databases. This feature guarantees both the performance and data consistency.

The process of master-slave synchronization in MySQL 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.

For details, you can go to this tutorial.

Related Blog Posts

How to Configure MySQL Master-Slave Replication on Ubuntu 16.04

A MySQL Master-Slave replication uses separate Alibaba Cloud ECS instances to offer a clustered environment for your database. Data is live-copied to multiple computers for backup service, analysis or for scaling out and creating different nodes for multiple applications.

In this guide, we will show you how to setup and configure MySQL Master-Slave replication on your Ubuntu 16.04 server running on Alibaba Cloud.

To MGR or Not MGR? Review of MySQL Group Replication

On December 12, 2016, Oracle released exciting news to the MySQL circle. It officially launched version 5.7.17 of MySQL, which includes the long-awaited MySQL Group Replication (MGR). This article provides insights on the basics of MGR and Galera and ponders the question of whether or not MongoDB will become the next Galera.

Related Market Products

MySQL5.7 (Windows2008)

Websoft9 MySQL is a pre-configured, ready to run image for running MySQL on Alibaba Cloud.MySQL is the world's most popular open source database.

MySQL5.6 (CentOS7.4)

Websoft9 MySQL is a pre-configured, ready to run image for running MySQL on Alibaba Cloud.MySQL is the world's most popular open source database.

Related Documentation

Commonly used SQL statements for MySQL

This document lists some of the commonly used SQL statements. For more information about the SQL statements including command parameters and restrictions, see MySQL 5.7 Reference Manual.

Synchronize data from an ApsaraDB RDS for MySQL database to an Alibaba Cloud Elasticsearch instance, and query and analyze data

Alibaba Cloud provides you with a wide range of cloud storage and database services. If you want to analyze and search data stored in these services, use Data Integration to replicate the data to Alibaba Cloud Elasticsearch, and then query or analyze the data. Data Integration allows you to replicate data at a minimum interval of five minutes.

Related Products

ApsaraDB RDS for MySQL

MySQL is one of the most popular open-source databases in the world. As a key component of the open-source software bundle LAMP (Linux, Apache, MySQL, and Perl/PHP/Python), MySQL has been widely applied to different scenarios.

Alibaba Cloud Elasticsearch

Alibaba Cloud Elasticsearch is a cloud-based Service that offers built-in integrations such as Kibana, commercial features, and Alibaba Cloud VPC, Cloud Monitor, and Resource Access Management. With Pay-As-You-Go billing, Alibaba Cloud Elasticsearch costs 30% less than self-built solutions and saves you the hassle of maintaining and scaling your platform.

Related Course

MySQL for Beginners - Basic Queries

This course is the first class of Alibaba Cloud MySQL for Beginners series. It mainly introduces the usage of SELECT statement and its clauses,It also describes how to use warning and error messages to troubleshoot problems in MySQL and how to set SQL mode and how the SQL mode affects the intepretation of SQL syntax.

0 0 0
Share on

Alibaba Clouder

2,606 posts | 737 followers

You may also like