The theory of MySQL replication

First,MySQL replication Overview

MySQL supports two ways of replicating: row-based replication and statement-based replication (logical replication). Both of these methods are recorded on the main library

Binary logs, and the way in which the backup logs are replayed to achieve asynchronous data replication, works like this:

There may be inconsistencies in the data at the same point in time between the primary and standby repositories. Replication typically does not increase the cost of the main library, primarily by enabling binary logs to open

Pin. Replication allows reading to be directed to the repository for better read expansion, but for write operations, unless properly designed, it is not appropriate to copy to

Extended write operations. In the schema of a master Repository multi-repository, write operations are executed multiple times, when the performance of the entire system depends on the slowest part of writing.

Replication-Resolved issues (Purpose): Data distribution, load balancing, backup, high availability and failover, MySQL upgrade test and so on.

Second,the principle of MySQL replication

1. Statement-based replication:

The main library records those queries that cause data changes, and when the repository reads and replays these events, it actually only performs the SQL is executed again.

Benefits: Easy to implement, more compact in binary log events. Logical replication can work in a variety of situations when the primary and standby modes are not the same. Easy to understand,

Problems can be well positioned.

Cons: There are some that cannot be copied correctly SQL, for example, when the current_user () function is used. The update must be serial, which requires more

Lock. Stored procedures and triggers may also be problematic when using statement-based replication patterns.

2. row-based replication:
row-based replication records the actual data in binary logs.

benefit: You can copy each row correctly. No need to replay the query to update the main library data, more efficient. Reduce the use of locks.

Cons: It's hard to make point-in-time restores, overhead sometimes big and sometimes small. Unable to determine which SQL was executed . Problems are difficult to locate. Unable to process such as in

Repository modification table. Schema such a situation.

no that pattern is perfect for all situations, MySQL is able to dynamically switch between the two modes of replication. By default, statement-based

Replication mode, but if the discovery statement cannot be copied correctly, switch to row-based replication.

3. Copy the files used:

(1) binary files, relay binaries (file names can be configured in the mysql configuration file)

(2)mysql-bin.index: Record all binary log file names and cannot be deleted,MySQL relies on this file to identify binary log files.

(3)mysql-relay-bin.index: Log the index file of all trunk logs, also cannot delete.

(4)master.info: Save the information required to connect to the main library, in plain text format, and record the password of the copied user.

(5)relay-log.info: Records the binary log and trunk log coordinates of the current standby copy.

4. send a system event to another repository:

The Log_slave_updates option allows the repository to become the main repository for other servers. When this option is set,MySQL logs events that it has performed to

Its own binary log so that its repository can retrieve and execute events from its log. Schematic diagram is as follows:

Note:

when copying When the SQL thread reads the relay log, it discards events that are logged in the event with the same server ID and the same ID as the server itself , thus breaking the replication process

The Infinite Loop.

5. Copy Filter:

The Copy filter option allows you to replicate only a subset of the data on the server. There are two ways to filter: To filter events logged to the binary log on the main library,

Events in the relay log are filtered on the standby library. Schematic diagram is as follows:

Note:

unless it is a last resort, do not use replication filtering because it is easy to interrupt replication and cause problems, which can be extremely inconvenient when disaster recovery is required.

Third, replication topology
replication can be established between any primary and standby, with only one restriction: Each repository can have only one main library. The basic principles of various topologies:

(1) a MySQL Standby instance can have only one main library.

(2) Each repository must have a unique server ID.

(3) A master library can have multiple repositories.

(4) If the log_slave_updates option is turned on, a standby library can propagate data changes from its main library to other repositories.

1. a master Repository multi-repository:

This configuration is useful when there is a small amount of write and bulk reading. You can spread the read over multiple repositories until the repository is too burdensome for the main library .

Or the bandwidth between the primary and standby becomes a bottleneck.

2. Master - master replication in active - Active mode :
also called dual Master or two-way replication, consisting of two servers, each configured as the other's main and standby repositories.

The biggest problem with this configuration is how to resolve conflicts, for example, two servers modifying a row of records at the same time, or on both servers to a

inserts data into the table in the Auto_increment column.

allowing writing to two servers is a far greater hassle than the benefits it brings.

3. Master - master replication in active - passive mode :

Configure a server in active - Active mode master - master replication to be a read-only passive server.

This approach makes it easy to switch between active and passive servers repeatedly because the configuration of the server is symmetric, which makes failover and failback easy.

4. Master - main structure with repository:

Add a standby library for each main library in primary-master replication.

The advantage of this configuration is that redundancy is added, and the problem of site single point failure can be eliminated for different geo-location replication topologies.

5. Ring Copy:
each server is a repository of servers before it, and is the main repository of the servers behind it. Fully dependent on each available node on the ring, greatly increasing

Increases the chances of the system failing. If a node is removed from the ring, the event initiated by the node is trapped in an infinite loop.

You can reduce the risk of circular replication by adding a standby to each node.

6. Main Library, distribution main library and standby library:

when the repository is large enough, it can cause a heavy load on the main library. This topology uses a single repository dedicated to the distribution of replication, removing the load from the main library.

to avoid making a real query on the distribution main library, you can modify its table to be the Blackhole storage engine. If the main library is close to full load, it should not be

Establish Ten more than one backup repository. You can save some main library bandwidth by setting up Slave_compressed_protocol. Other purposes can be achieved by distributing the main library, for example

such as filtering and rewriting rules for binary log events.

One of the major drawbacks of using the distribution Master Library is the inability to use a repository instead of the main library, because the distribution of the main library causes the repositories to be

Binary log coordinates are not the same.

7. Tree or pyramid shape:

reduces the burden on the main library, but its disadvantage is that any errors in the middle tier will affect multiple servers, the more the intermediate level, the more difficult to handle the failure,

More complex.

The theory of MySQL replication