Shulou(Shulou.com)06/02 Report--

This article mainly explains "the replication principle and application of Mysql". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the replication principle and application of Mysql".

1. Overview of replication

Mysql supports two replication methods, row-based replication and statement-based replication. Both methods realize asynchronous data replication by recording the binary log on the main library and replaying the log in the standby database.

The data on the database at the same time may be inconsistent with the master database, and the delay between the master and backup cannot be guaranteed.

Replication usually does not increase the overhead of the primary library, mainly by enabling binary logging, but this overhead is necessary for backup or timely recovery from a crash.

Through replication, you can point the read operation to the standby library to get a better read extension. Write operations are usually written on the main library. In a master multi-standby library read architecture, write operations are performed multiple times, and the read performance of the whole system depends on the slowest read part of the write.

Common read replication scenarios:

Master-standby-standby: one standby library copy data from the master library, and the other read and standby libraries copy data from the standby library.

Master-standby: all read and standby libraries copy data from the master library.

two。 Copy to solve the read problem

Data distribution: distribute data in different read data centers.

Load balancing: through Mysql replication, read operations can be distributed to multiple servers to optimize intensive applications, and it is very convenient to achieve basic load balancing by simply modifying the code.

Backup: replication is a supplement to backup, but replication is not a backup and cannot be canceled.

High availability and failover: avoid a single point of failure, well designed but fail over

MySql upgrade test: ensure that reads and writes are implemented as expected.

3. How replication works

3.1 record data changes in a binary log (Binary Log) on the main library.

3.2 the standby database copies the logs of the main database to its own relay log (Relay Log).

3.3 the standby database reads the events of the relay log and replays them on top of the standby data.

This replication architecture decouples acquisition and playback events, allowing the two processes to proceed asynchronously. But it also limits the replication process, and the most important thing is that queries running concurrently on the primary database can only be serialized on the standby database. Because there is only one SQL thread to replay the events in the relay log.

Thank you for your reading, the above is the content of "replication principle and Application of Mysql". After the study of this article, I believe you have a deeper understanding of the replication principle and application of Mysql, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.