Shulou(Shulou.com)05/31 Report--

This article mainly explains "the principle of mysql master-slave synchronization mechanism". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn the principle of mysql master-slave synchronization mechanism.

1. Binlog+pos synchronization (default is asynchronous)

Read the binlog of the main library from the server through the IO process and write it to relay_log

Read the relay_log of the slave library from the server through the sql running process and write it to the bin_Log of the slave library for synchronization

We generally use row mode in production because some functions may report errors when using mix and state mode.

2. Semi-synchronization (5.5 start)

Semi-synchronization means that users write data to mysql, write to the main database first, and then generate binlog logs. The main library waits for the binlog log to be fetched from the library, if the binlog log is not obtained from the library for more than 10 seconds. The main library is automatically converted to asynchronous, and later the user writes data to generate binlog logs, waiting for the user to pick it up. If the master database is not fetched, it is not managed.

Between asynchronous replication and full synchronous replication, the main library does not return to the client immediately after executing the transaction committed by the client, but waits for at least one received from the library and written to the relay log before returning to the client. Compared with asynchronous replication, semi-synchronous replication improves the security of data, and it also causes a certain degree of delay, which is at least one TCP/IP round trip time. Therefore, semi-synchronous replication is best used in low-latency networks.

When not specified, the default is asynchronous replication.

3. GTID synchronization (starting on 5.6.10)

The UUID+ transaction ID is used as the global transaction identity, UUID is the identifier of each machine, and the transaction ID records the latest transaction, and replication is no longer enabled using binlog+pos. Instead, master_auto_postion=1 is used to automatically match GTID breakpoints for replication.

4. Group replication (starting at 5.7.17)

Based on the defect of traditional asynchronous replication and semi-synchronous replication-the problem of data consistency can not be guaranteed, MySQL officially launched group replication in version 5.7.17, that is, MGR.

A replication group is formed by several nodes, and the submission of a transaction must be resolved and approved by most of the nodes in the group (N / 2 + 1) before it can be submitted. A replication group is composed of three nodes, and the Consensus layer is the consistency protocol layer. During the transaction commit, the inter-group communication occurs, and the transaction is passed by the two nodes' resolution (certify), so that the transaction can finally be committed and respond.

Semi-synchronous opening method and prerequisites:

To use semi-synchronous replication, the following conditions must be met:

1. MySQL 5.5 and above

two。 Variable have_dynamic_loading is YES

3. Asynchronous replication already exists

1. Load the plug-in first, and install the plug-in separately from the master and slave.

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'

2. Check whether the plug-in is installed successfully.

1 、 show plugins

Rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL

2. Mysql > SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE'% semi%'

+-+ +

| | PLUGIN_NAME | PLUGIN_STATUS |

+-+ +

| | rpl_semi_sync_master | ACTIVE |

+-+ +

1 row in set (0.00 sec)

3. Start semi-synchronous replication (can also be written in the configuration file)

SET GLOBAL rpl_semi_sync_master_enabled = 1

SET GLOBAL rpl_semi_sync_slave_enabled = 1

4. Restart the IO thread on the slave

Mysql > STOP SLAVE IO_THREAD

Mysql > START SLAVE IO_THREAD

5. Check whether semi-synchronization is running correctly.

Mysql > show status like 'Rpl_semi_sync_master_status'

Mysql > show status like 'Rpl_semi_sync_slave_status'

If it is shown as ON, it will start normally.

At this point, I believe that everyone on the "mysql master-slave synchronization mechanism principle" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.