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

I have worked on the solution team for many years and have found that database replication is always misunderstood and even completely misunderstood by some people, so this article will review the concept of replication in the MySQL environment and clarify some misunderstandings about replication.

What is replication?

Replication: ensures that information is copied and purposefully populated into another environment, rather than just stored in one location (transactions based on the source environment). In more vernacular terms, use a secondary server on your infrastructure to read or use other management solutions.

The following figure shows an example of a MySQL replication environment.

If we narrow it down to MySQL, how many options do we have when copying?

Standard asynchronous replication

Asynchronous replication means that the transaction is completed entirely in the local environment and is not affected by the replication dependency itself. When the changes are complete, the primary server populates the binary log with data modifications or actual statements (the differences between row-based replication or statement-based replication will be discussed later). This dump thread reads the binary log and sends it to the slave IO thread. The slave station uses its IO thread to place it in its own preprocessing queue (called the relay log). The slave station uses SQL threads to perform each change on the slave database.

Semi-synchronous replication

Semi-synchronous replication means that the slave device and the master device communicate with each other to ensure the correct transmission of the transaction. The master device only populates the binlog and continues its session, and one of the slave devices confirms that the transaction is correctly placed in the relay log of the slave device.

Semi-synchronous replication ensures that transactions are replicated correctly, but does not guarantee that commits actually occur on the slave device.

It is important to note that semi-synchronous replication ensures that the master server waits to continue processing transactions in a particular session until at least one slave server acknowledges receipt of the transaction (or times out). This is different from asynchronous replication because semi-synchronous allows additional data integrity.

Keep in mind that semi-synchronous replication affects performance because it waits for round trips from the actual ACK from the slave station.

Group replication

This is a new concept introduced in MySQL Community Edition 5.7and GA is implemented in MySQL 5.7.17. This is a new plug-in for virtual synchronous replication.

Whenever a transaction is executed on a node, the plug-in tries to reach a consensus with other nodes and then returns it to the client. Although this solution is a completely different concept from standard MySQL replication, it is based on using binlog to generate and process log events.

The following is a sample architecture for group replication.

If you are interested in Group Replication, please refer to the following article:

Http://mysqlhighavailability.com/mysql-group-replication-its-in-5-7-17-ga/

Http://mysqlhighavailability.com/performance-evaluation-mysql-5-7-group-replication/

Percona XtraDB Cluster/ Galera Cluster

Another solution that allows information to be copied to other nodes is Percona XtraDB Cluster. This solution focuses on providing consistency, using the authentication process to ensure that transactions avoid conflicts and execute correctly. In this case, we are talking about a clustering solution, where each environment is constrained by the same data and there is communication between nodes to ensure consistency.

Percona XtraDB Cluster has several components:

Percona Server for MySQL

Percona XtraBackup is used to perform a snapshot of a running cluster (restoring or adding nodes).

Wsrep patches/Galera Library

The solution is almost synchronous and is comparable to group replication. However, it also has the ability to use multi-master replication. A solution like Percona XtraDB Cluster is a component that improves the availability of the database infrastructure.

Row-based replication and statement-based replication

With statement-based replication, the SQL query itself is written to the binary log. For example, the slave station executes exactly the same INSERT / UPDATE / DELETE statement.

This approach has many advantages and disadvantages:

Because the actual statements are recorded in the binary log, it is much easier to audit the database

Less data is transmitted over the line

Non-deterministic queries may cause actual damage in dependent environments

Some queries have performance disadvantages, such as SELECT-based INSERT

Statement-based replication is slow due to SQL optimization and execution

Row-based replication has been the default choice since MySQL 5.7.7 and has many advantages. Row changes are recorded in binary logs and do not require context information, eliminating the impact of non-deterministic queries.

Other benefits include:

Performance improvement of highly concurrent queries with a small number of row changes

Significant improvement in data consistency

Its disadvantages include:

If there is a query that modifies a large number of rows, the network traffic may be much greater

It is more difficult to audit changes to the database

In some cases, row-based replication may be slower than statement-based replication

Misunderstandings about copying

Replication is a cluster

Standard asynchronous replication is not a synchronous cluster. Keep in mind that standard and semi-synchronous replication do not guarantee that the environment serves the same dataset. When using Percona XtraDB Cluster, each server actually needs to handle each change separately. If not, remove the affected nodes from the cluster. Asynchronous replication does not have this failure security, and read operations can still be accepted in the event of inconsistency.

Theoretically, the environment should be comparable. However, there are many parameters that affect the efficiency and consistency of data transmission. As long as asynchronous replication is used, there is no guarantee that the transaction will occur correctly. Consumers can avoid this by enhancing the persistence of the configuration, but this can result in a performance penalty.

Use the pt-table-checksum tool to verify the consistency between the master server and the slave server.

If there is replication, there is no need for backup.

Yes, replication is a good solution to get an accessible copy of the dataset (such as reporting problems, reading queries, generating backups). However, it is not a substitute for backup solutions. With offsite backup, you can rebuild the environment in the event of a major disaster, user error, or other reason. Some people use delayed slaves, but it is not a substitute for appropriate disaster recovery procedures.

There is replication, so the environment will load balance transactions

Although running a secondary instance with the same dataset may improve the availability of the environment, you may still need to point the read query to the secondary server and the write query to the primary server. You can use the proxy tool or define this feature in your own application.

Replication slows down significantly.

Replication has little impact on the performance of the primary server. Peter Zaitsev discussed the potential impact of the slave server on the master server in an article. Keep in mind that writing to binary logs can affect performance, especially if you have many small transactions that are then dumped and received from the server.

Of course, there are many other parameters that may affect the performance of the actual master-slave settings.

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.