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

Editor to share with you how to configure and monitor MySQL 5.5 semi-synchronous replication. I hope you will get something after reading this article. Let's discuss it together.

In MySQL's replication environment, data synchronization is asynchronous by default, and the Master node writes events to the binary log, but does not know when the Slave node gets and applies these events; if the Master node goes down, committed transactions may not be transferred to the Slave node.

Semi-synchronous replication is supported in MySQL 5.5 and above.

This function is implemented through plug-ins and has the following features:

When a Slave node connects to a Master node, it indicates whether it is in a semi-synchronous state

If the Master node has semi-synchronous replication enabled, there must be at least one semi-synchronous Slave node. When a thread in the Master node commits a transaction, the transaction does not commit immediately, but is in a blocking state, waiting for at least one Slave node to send a response message to the Master node, or until the timeout occurs

Only when the Slave node writes the transaction events received from the Master node to the relay log and synchronizes to disk, it will send response information to the Master node.

When no Slave node responds and times out, the Master node becomes asynchronous. When at least one Slave node is in a semi-synchronous state, the Master node returns to a semi-synchronous replication state.

Semi-synchronous replication must be enabled on both Master and Slave nodes. If the Master node does not enable semi-synchronous replication, or if all Slave nodes do not enable semi-synchronous replication, the Master node uses asynchronous replication.

-- displays the directory where the plug-in is located

Mysql > show variables like 'plugin_dir'

+-+

| | Variable_name | Value |

+-+

| | plugin_dir | / software/lib/plugin/ |

+-+

1 row in set (0.00 sec)

-- check the contents of the plug-in directory

[root@localhost ~] # cd / software/lib/plugin/

You have new mail in / var/spool/mail/root

[root@localhost plugin] # ls

Adt_null.so auth_socket.so daemon_example.ini libdaemon_example.so qa_auth_client.so qa_auth_server.so semisync_slave.so

Auth.so auth_test_plugin.so debug mypluglib.so qa_auth_interface.so semisync_master.so

-- check out the semi-synchronous plug-ins, respectively, master and slave plug-ins

[root@localhost plugin] # ll / software/lib/plugin/semisync_*

-rwxr-xr-x. 1 root mysql 175571 Apr 11 06:46 / software/lib/plugin/semisync_master.so

-rwxr-xr-x. 1 root mysql 93818 Apr 11 06:46 / software/lib/plugin/semisync_slave.so

-- load the plug-in on the Master node

Mysql > INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'

Query OK, 0 rows affected (0.24 sec)

Mysql > select plugin_name,plugin_type,plugin_status from plugins where plugin_name='rpl_semi_sync_master'

+-+

| | plugin_name | plugin_type | plugin_status | |

+-+

| | rpl_semi_sync_master | REPLICATION | ACTIVE | |

+-+

1 row in set (0.14 sec)

-- load the plug-in on the Slave node

Mysql > INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'

Query OK, 0 rows affected (0.57 sec)

Mysql > show plugins

+-+

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

+-+

| | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | |

| | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | |

| | mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL | |

| | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | |

| | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | |

| | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | |

| | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | |

| | FEDERATED | ACTIVE | STORAGE ENGINE | NULL | GPL | |

| | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | |

| | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | |

| | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | |

| | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | |

| | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | |

| | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | |

| | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | |

| | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | |

| | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | |

| | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | |

| | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | |

| | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | |

| | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | |

| | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | |

| | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | |

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

+-+

24 rows in set (0.03 sec)

-- important parameters are as follows:

Rpl_semi_sync_master_enabled controls whether synchronous replication of Master nodes is enabled or not

Rpl_semi_sync_slave_enabled controls whether synchronous replication of Slave nodes is enabled or not

Rpl_semi_sync_master_timeout controls the response time of the Master node waiting for the Slave node, in milliseconds. The default value is 10000 milliseconds, or 10 seconds.

-- Master node to view the relevant parameters of semi-synchronous replication

Mysql > show variables like 'rpl_semi%'

+-+ +

| | Variable_name | Value |

+-+ +

| | rpl_semi_sync_master_enabled | OFF |

| | rpl_semi_sync_master_timeout | 10000 | |

| | rpl_semi_sync_master_trace_level | 32 | |

| | rpl_semi_sync_master_wait_no_slave | ON |

+-+ +

4 rows in set (0.01sec)

-- Master node, turn on the semi-synchronous replication feature, and set the timeout to 3 seconds

Mysql > set global rpl_semi_sync_master_enabled=1

Query OK, 0 rows affected (0.00 sec)

Mysql > set global rpl_semi_sync_master_timeout=3000

Query OK, 0 rows affected (0.00 sec)

Mysql > show variables like 'rpl_semi%'

+-+ +

| | Variable_name | Value |

+-+ +

| | rpl_semi_sync_master_enabled | ON |

| | rpl_semi_sync_master_timeout | 3000 | |

| | rpl_semi_sync_master_trace_level | 32 | |

| | rpl_semi_sync_master_wait_no_slave | ON |

+-+ +

4 rows in set (0.00 sec)

If you want the parameters to take effect after the database restart, you need to add the parameters to the configuration file by adding Master and Slave nodes, respectively.

-- Slave node to turn on semi-synchronous replication feature

Mysql > show variables like 'rpl_semi%'

+-+ +

| | Variable_name | Value |

+-+ +

| | rpl_semi_sync_slave_enabled | OFF |

| | rpl_semi_sync_slave_trace_level | 32 | |

+-+ +

2 rows in set (0.02 sec)

Mysql > set global rpl_semi_sync_slave_enabled=1

Query OK, 0 rows affected (0.00 sec)

Mysql > show variables like 'rpl_semi%'

+-+ +

| | Variable_name | Value |

+-+ +

| | rpl_semi_sync_slave_enabled | ON |

| | rpl_semi_sync_slave_trace_level | 32 | |

+-+ +

2 rows in set (0.00 sec)

After configuring the system variable, restart the IO_THREAD thread of the Slave node

Mysql > stop slave io_thread

Query OK, 0 rows affected (0.01 sec)

Mysql > start slave io_thread

Query OK, 0 rows affected (0.00 sec)

-- Monitoring of semi-synchronous replication environment

-- related parameters

Rpl_semi_sync_master_clients configures the number of Slave nodes for semi-synchronous replication. This parameter will not take effect until the Master node's semi-synchronous replication plug-in is installed.

Rpl_semi_sync_master_status indicates whether the semi-synchronous replication feature of the Master node is enabled. If the value of this parameter is ON, the semi-synchronous replication plug-in has been enabled and the commit response has occurred; if the value of this parameter is OFF, the semi-synchronous replication plug-in is not enabled, or

The Master node returns to asynchronous replication mode because the commit response timed out

Rpl_semi_sync_slave_status indicates whether the semi-synchronous replication feature of the Slave node is enabled. If the value of this parameter is ON, the semi-synchronous replication plug-in is turned on and the I / O thread of the Slave node is running. This parameter is only when the Slave node installs the semi-synchronous replication plug-in

It will take effect.

The number of committed transactions for which the Rpl_semi_sync_master_no_tx was not successfully responded to by the Slave node

The number of committed transactions for which the Rpl_semi_sync_master_yes_tx was successfully responded by the Slave node

The total time in microseconds that the Rpl_semi_sync_master_net_wait_time Master node waits for the Slave node to reply. This parameter will take effect only if the Master node has installed the semi-synchronous replication plug-in.

Average time that a Rpl_semi_sync_master_net_avg_wait_time Master node waits for a reply from a Slave node (in microseconds)

-- monitors the status of semi-synchronous replication on the Master node

Mysql > show status like'% semi_sync%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_master_clients | 1 | |

| | Rpl_semi_sync_master_net_avg_wait_time | 752 | |

| | Rpl_semi_sync_master_net_wait_time | 1505 | |

| | Rpl_semi_sync_master_net_waits | 2 | |

| | Rpl_semi_sync_master_no_times | 0 | |

| | Rpl_semi_sync_master_no_tx | 0 | |

| | Rpl_semi_sync_master_status | ON |

| | Rpl_semi_sync_master_timefunc_failures | 0 | |

| | Rpl_semi_sync_master_tx_avg_wait_time | 0 | |

| | Rpl_semi_sync_master_tx_wait_time | 0 | |

| | Rpl_semi_sync_master_tx_waits | 0 | |

| | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | |

| | Rpl_semi_sync_master_wait_sessions | 0 | |

| | Rpl_semi_sync_master_yes_tx | 2 | |

+-+ +

14 rows in set (0.00 sec)

-- monitors the status of semi-synchronous replication on the Slave node

Mysql > show status like'% semi%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_slave_status | ON |

+-+ +

1 row in set (0.00 sec)

After reading this article, I believe you have some understanding of "how to configure and monitor MySQL 5.5 semi-synchronous replication". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

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.