MySQL replication (1) Principles and architecture

Replicated schemas

The basic problem with MySQL replication is to keep the data of one server synchronized with other servers, which has the following characteristics:

1) Asynchronous: This means that data from the repository at the same point in time may not be consistent with the main library, and that there is no guarantee of delay between them;

2 Backward Compatibility: That is, the backup version can be higher than the master library version, but not lower than the main library version

The following is a MySQL replication architecture diagram:

Roughly divided into three steps:

1 The main library all data changes are logged to the binary log

2) Reserve Library Copy the binary log of the main library to the repository through the I/O thread

3 the Standby library reads and applies the relay log through the SQL thread

The above replication architecture allows I/O threads and SQL threads to be asynchronous, however, the process of replication is limited, and the most important point is that queries running concurrently on the main library can only be serialized on the repository because there is only one SQL thread, which is the performance bottleneck for many workloads.

The principle of replication

MySQL replication can be divided into statement-based replication and row-based replication, statement-based replication (i.e., logical replication) is the advantage of having a master library executed on a standby, which is simple and convenient, but does not perform well, and there are many problems with replication triggers and stored procedures. Row-based replication (i.e., physical replication) refers to the retrieval of data from the binary log of the main library, which is relatively efficient, but more complex.

Since there is no way to be perfect for all cases, MySQL can dynamically switch between the two ways of replicating, using statement-based replication by default, and switching to row-based replication if the statement cannot be replicated correctly.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/