Shulou(Shulou.com)06/01 Report--

When it comes to semi-synchronous replication, we need to talk about three methods of replication:

Asynchronous replication:

For asynchronous replication, after the Master host writes events to the binlog log, it does not guarantee that all events have been replicated to the slave host, so if there is a network delay between Master and Slave, it will cause temporary data inconsistency; if Master fails and the data has not been replicated, it will cause data loss; but it also has advantages, which is more efficient than the other two replication methods.

Semi-synchronous replication:

For semi-synchronous replication, the Master host sends an event to the Slave host and triggers a wait until one of the Slave nodes (if there are multiple Slave) returns information about the success of the data replication to the Master, thus enhancing data consistency, but some performance is lost due to the acknowledgement overhead of the Master host. In addition, except that semi-synchronous replication does not need to wait for all Slave hosts to acknowledge the receipt of events, semi-synchronous data replication does not require those events to be fully executed. Therefore, it is still possible to see the occurrence of data replication delay on Slave hosts. If Slave does not return the successful replication information due to network delay and other reasons, it exceeds the timeout (rpl_semi_sync_master_timeout) set by Master. Semi-synchronous replication is degraded to asynchronous replication, and then data replication continues.

Synchronous replication:

For synchronous replication, the Master host sends an event to the Slave host and triggers a wait until all Slave nodes (if there is more than one Slave) return information about successful data replication to the Master. This kind of replication is the safest, but at the same time, it is also the least efficient.

Summary:

The above three replication methods have no absolute advantages and disadvantages. According to their own business efficiency requirements and requirements for data consistency and security, we should choose by ourselves.

Installation of semi-synchronous replication plug-in: (the author uses the 5.6.16-log version of online MySQL)

1. First confirm whether dynamic loading of plug-ins is supported: (semi-synchronous replication is achieved through plug-ins provided by MySQL)

Master and Slave hosts all need to confirm

Mysql > show variables like 'have_dynamic_loading'

+-+ +

| | Variable_name | Value |

+-+ +

| | have_dynamic_loading | YES |

+-+ +

2. Install the plug-in:

Master host:

Mysql > install plugin rpl_semi_sync_master soname 'semisync_master.so'

Query OK, 0 rows affected (0.08 sec)

When you view the list of plug-ins, the last one is the semi-synchronous replication plug-in that you just installed:

Mysql > show plugins

+-+

| | Name | Status | Type | Library | License | |

+-+

| | rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL | |

+-+

Slave host:

Mysql > install plugin rpl_semi_sync_slave soname 'semisync_slave.so'

Query OK, 0 rows affected (0.07 sec)

Mysql > show plugins

+-+

| | Name | Status | Type | Library | License | |

+-+

| | rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL | |

+-+

Note: if the plug-in cannot be installed successfully when installing the plug-in, first check whether there is a corresponding plug-in in the MySQL plug-in directory.

Mysql > show variables like'% plugin%'

+-- +

| | Variable_name | Value |

+-- +

| | plugin_dir | / usr/local/mysql//lib/plugin/ |

+-- +

1 row in set (0.00 sec)

3. Enable semi-synchronous replication:

Master host:

Mysql > set global rpl_semi_sync_master_enabled=1

Query OK, 0 rows affected (0.00 sec)

Mysql > show global variables like 'rpl_semi_sync_master_enabled'

+-+ +

| | Variable_name | Value |

+-+ +

| | rpl_semi_sync_master_enabled | ON |

+-+ +

1 row in set (0.00 sec)

View the Master error log to display semi-synchronous replication information:

Cat master.err

2019-05-14 15:50:50 1465 [Note] Semi-sync replication initialized for transactions.

2019-05-14 15:50:50 1465 [Note] Semi-sync replication enabled on the master.

Slave host:

Mysql > set global rpl_semi_sync_slave_enabled=1

Query OK, 0 rows affected (0.01 sec)

Mysql > show global variables like 'rpl_semi_sync_slave_enabled'

+-+ +

| | Variable_name | Value |

+-+ +

| | rpl_semi_sync_slave_enabled | ON |

+-+ +

1 row in set (0.00 sec)

Note:

To prevent these dynamic semi-synchronous replication parameters from becoming invalid after the server is restarted, you need to write them to the configuration file to make them permanent:

Master host configuration:

[mysqld]

Rpl_semi_sync_master_enabled = 1

Rpl_semi_sync_master_timeout = 1000

Slave host configuration:

[mysqld]

Rpl_semi_sync_slave_enabled = 1

4. Monitor semi-synchronous replication through MySQL status variable on Master host.

Mysql > show global status like 'rpl%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_master_clients | 0 | |

| | Rpl_semi_sync_master_net_avg_wait_time | 4803 | |

| | Rpl_semi_sync_master_net_wait_time | 4803 | |

| | Rpl_semi_sync_master_net_waits | 1 | |

| | Rpl_semi_sync_master_no_times | 1 | |

| | Rpl_semi_sync_master_no_tx | 1 | |

| | Rpl_semi_sync_master_status | ON |

| | Rpl_semi_sync_master_timefunc_failures | 0 | |

| | Rpl_semi_sync_master_tx_avg_wait_time | 1975 | |

| | Rpl_semi_sync_master_tx_wait_time | 1975 | |

| | Rpl_semi_sync_master_tx_waits | 1 | |

| | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | |

| | Rpl_semi_sync_master_wait_sessions | 0 | |

| | Rpl_semi_sync_master_yes_tx | 1 | |

+-+ +

14 rows in set (0.00 sec)

Parameter meaning. For more information, please see the official website of MySQL.

Note:

If you have configured semi-synchronous, Slave can also synchronize data normally when Master changes data, but when monitoring semi-synchronous replication, it is found that Rpl_semi_sync_master_ status value is OFF, but rpl_semi_sync_master_enabled is ON, indicating that the asynchronous replication method was still used before. In this case, you need to restart Slave to use semi-synchronous replication to take effect.

View the system variables related to semi-synchronous replication:

Mysql > show global variables like 'rpl_semi%'

+-+ +

| | Variable_name | Value |

+-+ +

| | rpl_semi_sync_master_enabled | ON |

| | rpl_semi_sync_master_timeout | 10000 | time for Master to switch to asynchronous replication mode after waiting for Slave confirmation message timed out after submitting the event |

| | rpl_semi_sync_master_trace_level | 32 | |

| | rpl_semi_sync_master_wait_no_slave | ON |

+-+ +

4 rows in set (0.00 sec)

Parameter meaning. For more information, please see the official website of MySQL.

Semi-synchronous data replication can be degraded to asynchronous replication when waiting for a confirmation timeout to occur:

1. There is a problem in simulating the network on Slave.

Ifdown eth0 (third-party clients, such as Xshell, etc., will be cut off after the network is turned off)

2.1 Update a piece of data on Maser:

Mysql > update edusoho_e.t1 set xname='MySQL' where id=39

Query OK, 1 row affected (10.04 sec) because Master needs to wait for the confirmation message of Slave, so it takes 10 seconds, which also indicates that semi-synchronous replication is successful.

Rows matched: 1 Changed: 1 Warnings: 0

2.2 looking at the Master error log, you can see that semi-synchronous replication has been degraded (although the data can still be replicated to Slave)

Cat master.err

2019-05-14 15:59:39 1465 [Warning] Timeout waiting for reply of binlog (file: mysql-bin.000004, pos: 2860), semi-sync up to file mysql-bin.000004, position 2606

2019-05-14 15:59:39 1465 [Note] Semi-sync replication switched OFF

Remember to turn on the network card of Slave

Ifup eth0

Uninstall the semi-synchronous replication plug-in:

If you no longer need semi-synchronous replication, you can uninstall the semi-synchronous replication plug-in:

Mysql > uninstall plugin rpl_semi_sync_master; (Slave host is rpl_semi_sync_slave)

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.