MySQL Learning note 12 semi-synchronous replication

1.1.1. Related Concepts

by default, MySQL uses asynchronous replication, that is, the transaction execution on master and the copy operation on the slave do not need to be synchronized,master You do not have to wait for The replication thread on the slave to complete other transactions by receiving the Binary Log .

when semi-synchronous replication occurs,the transaction execution on master and the copy operation on the slave need to be synchronized on a step,andmaster needs to wait slave the replication thread on the receive is complete Binary Log .

asynchronous replication is relatively efficient, but under certain conditions, it is possible to cause data inconsistency between master and slave . Semi-synchronous replication can reduce the probability of inconsistent data. Semi-synchronous replication supports only single source replication and does not support multi-source replication.

1.1.2. Deploying semi-synchronous replication

To establish a semi-synchronous replication environment , you need to do the following when you have established a master-slave replication environment.

(1) in the Master Install the semi-synchronous copy plug-in.

Mysql> Show variables like '%semi% ';

Empty Set (0.02 sec)

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ' semisync_master.so ';

Query OK, 0 rows affected (0.05 sec)

Mysql> Show variables like '%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_for_slave_count | 1 |

| Rpl_semi_sync_master_wait_no_slave | On |

| Rpl_semi_sync_master_wait_point | After_sync |

+-------------------------------------------+------------+

6 rows in Set (0.00 sec)

where the rpl_semi_sync_master_timeout variable controls the time-out of the half-sync, in milliseconds, the default is 10000, which is Seconds.

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)

(2) in the slave Install the semi-synchronous copy plug-in.

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ' semisync_slave.so ';

Query OK, 0 rows affected (0.03 sec)

Mysql> Show variables like '%semi% ';

+---------------------------------+-------+

| variable_name | Value |

+---------------------------------+-------+

| rpl_semi_sync_slave_enabled | OFF |

| Rpl_semi_sync_slave_trace_level | 32 |

+---------------------------------+-------+

2 rows in Set (0.00 sec)

Mysql> SELECT Plugin_name, Plugin_status

From INFORMATION_SCHEMA. PLUGINS

WHERE plugin_name like '%semi% ';

+---------------------+---------------+

| Plugin_name | Plugin_status |

+---------------------+---------------+

| Rpl_semi_sync_slave | ACTIVE |

+---------------------+---------------+

1 row in Set (0.00 sec)

(3) in the Master enable semi-synchronous replication on the

in the MySQL configuration file on master:

[Mysqld]

Rpl_semi_sync_master_enabled=1

rpl_semi_sync_master_timeout=10000

"Additional configuration required for asynchronous replication is omitted here"

can use The SQL command dynamically enables semi-synchronous replication.

mysql> set global rpl_semi_sync_master_enabled=1;

Query OK, 0 rows Affected (0.00 sec)

(4) in the slave enable semi-synchronous replication on the

in the MySQL configuration file on the slave:

[Mysqld]

Rpl_semi_sync_slave_enabled=1

"Additional configuration required for asynchronous replication is omitted here"

can use The SQL command dynamically enables semi-synchronous replication.

mysql> stop Slave;

Query OK, 0 rows affected, 1 Warning (0.00 sec)

mysql> set global rpl_semi_sync_slave_enabled=1;

Query OK, 0 rows Affected (0.00 sec)

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

The semi-synchronous replication environment is established successfully.

1.1.3. Monitor the status of semi-synchronous replication

(1) View Master semi-synchronous replication on the.

mysql> SHOW VARIABLES like ' rpl_semi_sync% ';

+-------------------------------------------+------------+

| variable_name | Value |

+-------------------------------------------+------------+

| rpl_semi_sync_master_enabled | On |

| Rpl_semi_sync_master_timeout | 10000 |

| Rpl_semi_sync_master_trace_level | 32 |

| Rpl_semi_sync_master_wait_for_slave_count | 1 |

| Rpl_semi_sync_master_wait_no_slave | On |

| Rpl_semi_sync_master_wait_point | After_sync |

+-------------------------------------------+------------+

6 rows in Set (0.01 sec)

mysql> SHOW STATUS like ' rpl_semi_sync% ';

+--------------------------------------------+-------+

| variable_name | Value |

+--------------------------------------------+-------+

| rpl_semi_sync_master_clients | 1 |

| Rpl_semi_sync_master_net_avg_wait_time | 0 |

| Rpl_semi_sync_master_net_wait_time | 0 |

| Rpl_semi_sync_master_net_waits | 0 |

| 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 | 0 |

+--------------------------------------------+-------+

Rows in Set (0.00 sec)

among them, Rpl_semi_sync_master_clients indicates the number of slave that have been connected through a semi-synchronous replication .

RPL_SEMI_SYNC_MASTER_YES_TX indicates the number of slave that have successfully sent a semi-synchronous ACK .

RPL_SEMI_SYNC_MASTER_NO_TX indicates the number of slave that did not successfully send a semi-synchronous ACK .

(2) View slave the state of the semi-synchronous replication on.

mysql> SHOW VARIABLES like ' rpl_semi_sync% ';

+---------------------------------+-------+

| variable_name | Value |

+---------------------------------+-------+

| rpl_semi_sync_slave_enabled | On |

| Rpl_semi_sync_slave_trace_level | 32 |

+---------------------------------+-------+

2 rows in Set (0.00 sec)

mysql> SHOW STATUS like ' rpl_semi_sync% ';

+----------------------------+-------+

| variable_name | Value |

+----------------------------+-------+

| Rpl_semi_sync_slave_status | On |

+----------------------------+-------+

1 row in Set (0.00 sec)

among them, Rpl_semi_sync_slave_status indicates that the semi-synchronous replication on the slave has started successfully.

MySQL Learning note 12 semi-synchronous replication