MySQL monitoring optimization (ii) master-slave replication

The basic problem with replication is to keep one server's data synchronized with other servers. Data from one master server can be synchronized to multiple slave servers. And from the server can also be configured as the main library of another server. There can be many different combinations between the main library and the library.

MySQL supports two replication scenarios: statement-based replication (statement-based replication) and row-based replication (row-based replication). Statement-based replication already exists in MySQL3.23, and it is used in a more replicated manner. Row-based replication is introduced by MySQL5.1. These 2 replication methods are performed by logging the primary server's binary log and reproducing from the server (replay). All of them are asynchronous. In other words, the data from the server is not always up to date.

Replication typically does not increase the cost of the main library, primarily the overhead of enabling binary logging, but is necessary for the purpose of recovering the backup ring from the crash in time. Replication can point a read operation to a library for better read expansion, but for write operations, it is not appropriate to extend by replication.

1. mysql Replication use

• Distribution data

MySQL usually does not cause much pressure on the bandwidth. Therefore, the data can be distributed in different geographical locations, and the data distribution across the room is realized.

• Load Balancing

MySQL replication allows you to distribute read operations across multiple servers for optimized read-intensive applications.

• Backup

Replication is helpful for backups, but it is not a backup from the server.

• High Availability and failover

Replication avoids MySQL failure in the application. So 隓 transfer can significantly reduce downtime and even give users no sense.

• Test MySQL Version upgrade

A common approach is to upgrade from the server to a new version of MySQL, and then use it to test the query, to ensure that no exception before upgrading the master server

2, Case: Customer complaints: Modified xx information, prompted to modify the success. View or old data again. What's going on?

Most likely caused by a database master-slave delay

3, MySQL master-slave delay delay problem how to deal with?

1. Occasional delay: Control the Write speed, peak load shifting.

2. Frequency delay: Split database for multi-point write

3. Last resort: Upgrade from library disk hardware to SSD

4. What is the process of MySQL master-slave replication? As shown

3 Threads Complete replication:

• Main Library 1 threads responsible for logging database change logs
• 1 threads from the library are responsible for pulling the change log of the main library
• 1 threads from the library are responsible for executing the change log for the main library
• The decoupling of acquisition events and replay events is implemented, which allows for asynchronous.
• Replication bottlenecks: Primary library parallel (multi-threaded) writes and serial (single-threaded) writes from libraries can cause a master-slave delay.

MySQL monitoring optimization (ii) master-slave replication