MySQL semi-synchronous replication + MMM architecture, mysql synchronous mmm Architecture

MySQL semi-synchronous replication + MMM architecture, mysql synchronous mmm Architecture
Introduction

The previous article introduced the implementation method of the MMM architecture, but the replication of the MMM solution in the previous article is asynchronous replication, the main problem of asynchronous replication is that when the Master/Slave node has a delay, data will be lost if the master node fails, resulting in a Master/Slave switchover; mysql has added semi-synchronous replication to solve the problem of data loss in asynchronous replication. semi-synchronous replication has Versions later than 5.5, the principle of semi-synchronous replication is that the client must wait for the binlog response received from the slave database before committing the transaction (when multiple slave databases exist, only one slave database receives the response by default. bInlog, you can also configure that binlog must be received for each slave database, but this will have a great impact on the performance ), if the slave database fails or the binlog fails to be transmitted to the slave database in time (controlled by the rpl_semi_sync_master_timeout parameter, the default value is 10 S), mysql will automatically convert to asynchronous replication, when the slave database restarts, a new binglog is sent from the master database to the slave database, and the response is obtained within the specified time, mysql is automatically converted to semi-synchronous replication. Network requirements are relatively high.

Mysql version: 5.6

Solution: MMM + semi-synchronous Replication

 

I. Principles

5.6 semi-synchronous replication means that the dump thread sends the binlog record to the io thread of the slave database only after the client executes the commit primary database to write the binlog and refresh the data to the disk.

Ii. installation and configuration

The current configuration is based on the MMM solution. Because the MMM solution has two master nodes and one slave node, I configure semi-synchronous replication on the two master nodes, the original asynchronous replication is performed on the database. Semi-synchronous replication is implemented based on plug-ins. Different plug-ins are executed on the master and slave nodes.

1. Check whether dynamic plug-ins are supported

You must check whether dynamic plug-ins are supported for both the master and slave nodes.

2. Install dynamic plug-ins

Both master and slave instances are executed.

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

Note: Because of my current dual-master mode, both the master and slave instances need to execute the master-slave plug-ins. If only the master-slave mode is used, the master and slave plug-ins can be executed from the slave plug-ins.

Check whether the plug-in is installed

3. Enable semi-synchronous Replication

SET GLOBAL rpl_semi_sync_master_enabled = 1;SET GLOBAL rpl_semi_sync_slave_enabled = 1;

In the same way, both the master and slave databases perform the above two operations at the same time. Note that the global variables must be enabled.

4. Restart the io thread

1. Slave database execution

 STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;

2. master database execution

 STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;

5. Configure Parameters

show variables like '%rpl_semi%';

Rpl_semi_sync_master_enabled, rpl_semi_sync_slave_enabled

Indicates whether the semi-synchronous replication of the master and slave nodes is enabled. Because I am in dual-master mode, I assume the master node and the slave node. Therefore, the slave parameter is included in this table.

Rpl_semi_sync_master_timeout

This parameter is used to set the delay time of semi-synchronous replication. When the binlog of the master database to the slave database has not been returned within 10 seconds, it is automatically converted to asynchronous replication. This time can be adjusted, the default value is 10 S.

Note: For slave only, there are two states: "rpl_semi_sync_slave_enable", "rpl_semi_sync_trace_level", and the other four are displayed as the master. Because I have configured the dual-master mode, all master and slave nodes exist.

6. Status Parameters

show status like '%rpl_%';

These status parameters are important and are usually used for analysis on semi-synchronous replication.

Rpl_semi_sync_master_clients: there are multiple slave databases for semi-synchronous replication. For the current master, only backup is the slave database for semi-synchronous replication, while slave is asynchronous replication, so here is 1;

Rpl_semi_sync_master_net_avg_wait_time: The average response time for the master to wait for the slave database. The unit is subtle.

Rpl_semi_sync_master_net_wait_time: the total time the master waits for the slave database to respond. The unit is subtle.

Rpl_semi_sync_master_net_waits: Total number of times the master waits for the slave database to respond

Rpl_semi_sync_master_no_times: Number of times the master node disables semi-synchronous replication, that is, the number of times when the master/Slave latency exceeds the specified time to convert to asynchronous replication.

Rpl_semi_sync_master_no_tx: The number of times the master node has not been successfully submitted by the slave. It can also be understood as not the number of times the semi-sync replication was synchronized.

Rpl_semi_sync_master_status:Whether semi-synchronous replication is enabled only when the current server acts as the master database. If it is OFF, it means asynchronous replication, provided that the server currently queried is the master server.

Rpl_semi_sync_master_timefunc_failures: Number of times the master failed to call the time function, for example:gettimeofday().

Rpl_semi_sync_master_tx_avg_wait_time: average time for the master to wait for each transaction. The unit is subtle.

Rpl_semi_sync_master_tx_wait_time: the total time the master waits for the transaction. The unit is subtle.

Rpl_semi_sync_master_tx_waits: the total number of times the master waits for transactions.

Rpl_semi_sync_master_wait_pos_backtraverse: The number of binlogs written by the master node is different from the number of binlogs returned by the slave response.

Rpl_semi_sync_master_wait_sessions: The number of calls waiting for the slave response. This status can reflect the current Master/slave latency, including pressure.

Rpl_semi_sync_master_yes_tx: Number of successful responses submitted by the master node by the slave

Rpl_semi_sync_slave_status: Indicates whether semi-synchronous replication is enabled only when the current server is used as the slave database. on indicates that the replication is enabled.

Note: For slave, only the "rpl_semi_sync_slave_status" status is available. Other statuses are displayed as the master. Because I have configured the dual-master mode, all master and slave nodes exist.

Note: Pay attention to the status of the above colors.

Iii. Test

Simulate slave database downtime

stop slave;

Insert on master

The waiting time on the master database is exactly 10 S.

 

You can also obtain from the parameter value that the semi-sync replication failed.

After the slave database restarts the replication, the semi-synchronous replication is also enabled, and the insert record is also asynchronously copied and applied.

Note: To enable semi-synchronous replication, you must add it to the my. cnf file to ensure that half-synchronous replication is also enabled after the mysql service is restarted.

SET GLOBAL rpl_semi_sync_master_enabled = 1;SET GLOBAL rpl_semi_sync_slave_enabled = 1;

 

 

Refer:

Http://dev.mysql.com/doc/refman/5.6/en/replication-semisync-interface.html

The previous article on replication:

Master-slave replication: http://www.cnblogs.com/chenmh/p/5089919.html

Master replication: http://www.cnblogs.com/chenmh/p/5153184.html

Http://www.cnblogs.com/chenmh/p/5563778.html: MMM

SummarySemi-synchronous replication although the Master/Slave binlog is synchronized, the master only waits for the binlog to be written to the slave database and does not wait for the slave database to apply this part of binlog, the slave database applies the binlog operations asynchronously. Therefore, the master-slave data is not synchronized in real time, so it can only be called semi-synchronous replication. For businesses that use master and slave nodes before version 5.7, there will be large master-slave latency, especially when the master node is intensive writing, this is because the master node is concurrently written and the slave database application master's binlog is executed sequentially, leading to the slave node being unable to keep up with the master node. In 5.7, parallel replication was introduced, the so-called parallelism means placing irrelevant operations in a group so that theoretically parallel operations can be achieved from the database, thus increasing the speed at which binlog is applied to the master database, in addition, a new parameter "rpl_semi_sync_master_wait_point = AFTER_SYNC" is introduced in semi-synchronous replication of 5.7, which controls the binlog of the master database to respond to the slave database before flush disk, in version 5.6, binlog is sent only after the master database flush disk. If the master database is down for some reason, in this case, if the client does not receive a response from the slave database, the client may execute a new operation. At this time, the master and slave databases have switched the client to connect to the master database (previously the slave database) and perform the previous operation again, in this way, after the previous master database (now changed to slave database) is started, the slave database is re-applied. The data is written twice, so the binlog response is placed before the flush disk in 5.7 so that there will be no repeated data writes, this parameter is enabled by default. Therefore, the setting method is the same as that of 5.6, and no changes are required. If you change the value of this parameter to "rpl_semi_sync_master_wait_point = AFTER_COMMIT", then it becomes the same solution as 5.6. I will write an article about the new improvement related to the 5.7 replication later.

 

 

 

Note: Author: pursuer. chen Blog: http://www.cnblogs.com/chenmh All essays on this site are original. You are welcome to repost them. However, you must indicate the source of the article and clearly give the link at the beginning of the article. Welcome to discussion