MySQL semi-synchronous replication and mysql Synchronization

MySQL semi-synchronous replication and mysql Synchronization
1. Introduction to semi-synchronous Replication

What is the semi-synchronous replication mode? Here, we first understand the asynchronous replication mode, which is the default replication option for MySQL. Asynchronous replication means that the master database sends binlog logs to the slave database, and then there is no more. This exposes a problem. When the slave server fails, the data on the master-slave database server may be inconsistent.

To solve the problem above, MySQL5.5 introduces a semi-synchronous replication mode. Enabling this mode ensures that the slave database receives the binlog logs sent from the master database and writes them to its own relay log. Then, it reports the logs to the master database to inform that the replication has been completed.

When this mode is enabled, the master database will automatically switch to the asynchronous replication mode until at least one slave server receives the binlog of the master database and sends it back to the master database. Then, the primary database switches back to the semi-synchronous replication mode.

Note:

The semi-synchronous replication mode must be enabled on both the master server and slave server; otherwise, the asynchronous replication mode is used by default.

2. Environment Description

Two linux Virtual Hosts

Linux CentOS6.6 and MySQL 5.5

Ip: 192.168.95.11 (master), 192.168.95.12 (slave)

3. installation and configuration 3.1. Installation prerequisites

1. It must be MySQL5.5 or later.

2. MySQL must have the automatic loading function, that is, the have_dynamic_loading variable is YES (because we load and install this function plug-in MySQL)

Show variables like 'have _ dynamic_loading '; # Check whether automatic loading is enabled.

3. Master-slave replication has been configured and ing is working.

Master-slave replication configuration Tutorial: http://www.cnblogs.com/phpstudy2015-6/p/6485819.html

3.2 Installation

　　　　192.168.95.11 load and install:

1 mysql> install plugin rpl_semi_sync_master soname 'semisync _ master. so '; 2 3 mysql> show plugins; # Check whether the load is successful 4 5 mysql> set global rpl_semi_sync_master_enabled = 1; # enable semi-synchronous replication, Which is disabled by default

　　　　192.168.95.12 load and install

1 mysql> install plugin rpl_semi_sync_slave SONAME 'semisync _ slave. so '; 2 3 mysql> show plugins; # Check whether the load is successful 4 5 mysql> set global rpl_semi_sync_slave_enabled = 1; # enable semi-synchronous replication, 6 7 is disabled by default # restart the IO thread from the server, manually switch the asynchronous mode to the semi-synchronous Mode 8 9 mysql> stop slave IO_THREAD; 10 11 mysql> start slave IO_THREAD;

 

3.3 configuration file

1 rpl_semi_sync_master_enabled = 1 # Add the configuration file of the master database, indicating that semi-sync replication 2 3 rpl_semi_sync_slave_enabled = 1 will be automatically enabled when MySQL is started later.

4. View related parameters

1. Run the show variables like '% semi %' command ';

Master:

Rpl_semi_sync_master_enabled = ON indicates that semi-synchronous replication is enabled.

Rpl_semi_sync_master_timeout = 1000 default 1000 milliseconds, that is, 10 seconds timeout, will switch to asynchronous replication

Rpl_semi_sync_master_wait_no_slave indicates whether to allow the master to wait for the slave to receive confirmation. The default value is ON

Rpl_semi_sync_master_trace_level = 32 indicates the debugging level used to enable semi-synchronous replication. The default value is 32.

　Slave:

Rpl_semi_sync_slave_enabled = ON indicates that the semi-synchronous replication mode has been started ON slave.

Rpl_semi_sync_slave_trace_level = 32 indicates the debugging level used to enable semi-synchronous replication. The default value is 32.

　　2. Run the show status like '% semi %' command on the master and slave nodes ';

Master

Rpl_semi_sync_master_status indicates whether the master server uses asynchronous or semi-synchronous replication.

Rpl_semi_sync_master_client indicates the number of slave Servers configured as semi-synchronous replication.

Rpl_semi_sync_master_yes_tx indicates the number of successful Submissions confirmed from the server.

Rpl_semi_sync_master_no_tx indicates the number of failed submissions from the server.

Slave:

Rpl_semi_sync_slave_status indicates that semi-synchronous replication is enabled from the server.

5. Test

When the simulated slave fails and the master waits for 10 seconds but does not receive the feedback signal, it is switched to the asynchronous replication mode to continue the execution.

First, create database aa synchronously

1. Run stop slave on slave and disable master-slave replication.

2. The master Creates table tab1 In the aa database and does not receive the feedback. Wait for 10 seconds (Rpl_semi_sync_master_timeout = 1000) and continue the execution.

Master:

　　　　

Slave:

　　　　

3. The master creates tab2 in the database and runs it directly without waiting for feedback.

[When the feedback times out, the master switches to the asynchronous replication mode. This mode is asynchronous and does not need to wait]

4. Run start slave on slave, synchronize data, create tab1 and tab2, report to master, and switch to semi-synchronous replication.

　　　

5. Run stop slave on slave and disable master-slave replication.

6. The master Creates table tab3 in the database. Wait 10 seconds to receive the slave feedback signal. Wait for the timeout and switch to the asynchronous replication mode to continue the execution.

[In Step 4, data synchronization has been fed back to the master, and the master is in semi-synchronous replication mode]

　　　

6. Summary

The performance and concurrency of the semi-synchronous replication mode are lower than that of the asynchronous replication mode, because each replication requires feedback, but there is one more step.

To be honest, I still don't quite understand why semi-synchronous replication can maintain data integrity? If the slave fails, the master node does not return to asynchronous replication. It is no different from the previous full asynchronous mode. In addition, even if there is more information feedback, what is the role of feedback in addition to letting the master confirm? If the slave fails and no feedback is provided, the master still has no further processing method.

After all, I am still a little white. I don't know much about mysql. If you know anything, please give me some advice.

Reference books:

The path to MySQL management-performance optimization, high availability and monitoring

(The above are some of your own opinions. If you have any shortcomings or errors, please point them out)

Author: Yi ye Fengfeng

Disclaimer: This blog post is original and only represents the point of view or conclusion I have summarized at a certain time in my work and study. When reprinting, please provide the original article link clearly on the article page.