Implementation of mysql semi-synchronous replication _ MySQL

Implementation of mysql semi-synchronous replication bitsCN.com

The differences between mysql semi-synchronous replication and asynchronous replication are shown in the preceding architecture diagram: when mysql asynchronous replication occurs, after the Mysql Master Server transfers its Binary Log through the replication thread, the Mysql Master Sever automatically returns the data to the client, regardless of whether the binary log is received on the slave. In the semi-synchronous replication architecture, when the master sends its binlog to the slave, it must ensure that the slave has received the binary log before returning the data to the client. Two architectures are compared: asynchronous replication ensures a fast response structure for users, but does not ensure that binary logs are indeed on slave; semi-synchronous replication provides a slightly slower response to the customer's request, but it can ensure the integrity of binary logs.

Configure a master-slave architecture for semi-synchronous replication:

Master server whose 192.168.1.141 is mysql

192.168.1.142 is the slave server of mysql.

1. configure the mysql master server

Edit/etc/my. cnf and provide the following configuration

Log_bin = index

Server_id = 1

Authorize on the master server

# Mysql> grant replication slave, replication client on user @ '192. 168.1.142 'identified by "192 ";

# Mysql> flush privileges;

2. configure mysql slave service

Edit/etc/my. cnf and provide the following configuration

Server_id = 10

Relay_log = relay

Read_only = on

Skip-slave-start = 1

Enter the mysql command line interface

# Mysql> change master to MASTER_HOST = "192.168.1.141", MASTER_USER = "user", MASTER_PASSWORD = "123456", MASTER_LOG_FILE = "index.000004", MASTER_LOG_POS = 429;

# Mysql> start slave;

If you can see Slave_IO_Running: Yes and Slave_ SQL _Running: Yes, the master-slave configuration is successful.

To use the semi-synchronous replication function of mysql, you need to install plug-ins for mysql. The plug-ins supported by mysql by default are in/usr/local/mysql/lib/plugin /, two shared libraries semisync_master.so and semisync_slave.so are the key to mysql semi-synchronous replication.

3. set semi-synchronous replication

Run the following code under the command line interface of the mysql master server:

# Mysql> install plugin rpl_semi_sync_master SONAME 'semisync _ master. so ';

# Mysql> show variables like "% semi %"; (if you see the newly added semi variable, the module is successfully installed)

| Rpl_semi_sync_master_enabled | OFF | whether to enable semi-synchronous replication, which is disabled by default

| Rpl_semi_sync_master_timeout | 10000 | waiting for the slave server to tell the received timeout time. if the time is reached and it is not received, it is automatically downgraded to asynchronous

| Rpl_semi_sync_master_trace_level | 32 | running level

| Rpl_semi_sync_master_wait_no_slave | ON | whether to wait when there is no slave. by default, you also need to wait.

# Mysql> set global rpl_semi_sync_master_enabled = 1;

# Mysql & gt; set global rpl_semi_sync_master_timeout = 1000;

Run the following code in the command line interface of the mysql slave server:

# Mysql> install plugin rpl_semi_sync_slave SONAME 'semisync _ slave. so ';

# Mysql> show variables like "% semi %"; (if you see the newly added semi variable, the module is successfully installed)

# Mysql> set global rpl_semi_sync_slave_enabled = 1;

# Stop slave;

# Start slave;

Finally, write common configuration parameters in the configuration file:

192.168.1.141:

[Mysqld]

Rpl_semi_sync_master_enabled = 1

Rpl_semi_sync_master_timeout = 1000

192.168.1.142:

[Mysqld]

Rpl_semi_sync_slave_enabled = 1

4. view the status of semi-sync replication

Run the following command on 192.168.1.141:

Mysql> show status like "% semi % ";

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

| Variable_name | Value |

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

| Rpl_semi_sync_master_clients | 1 | number of semi-synchronous replication clients

| Rpl_semi_sync_master_net_avg_wait_time | 555 | average wait time (default: millisecond)

| Rpl_semi_sync_master_net_wait_time | 1665 | total waiting time

| Rpl_semi_sync_master_net_waits | 3 | number of waits

| Rpl_semi_sync_master_no_times | 0 | number of times that semi-sync replication is disabled

| Rpl_semi_sync_master_no_tx | 0 | number of times slave submissions failed to be received

| Rpl_semi_sync_master_status | ON | indicates whether the current mode is asynchronous or semi-synchronous, and on indicates semi-synchronous.

| Rpl_semi_sync_master_timefunc_failures | 0 | number of times the time function call failed

| Rpl_semi_sync_master_tx_avg_wait_time | 575 | average transaction transmission time

| Rpl_semi_sync_master_tx_wait_time | 1725 | total transfer time of a transaction

| Rpl_semi_sync_master_tx_waits | 3 | waiting times

| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |

| Rpl_semi_sync_master_wait_sessions | 0 | the number of sessions currently waiting for the slave Reply

| Rpl_semi_sync_master_yes_tx | 3 | the number of times slave transaction replies are successfully received

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

5. cancel the plug-in of semi-sync replication

192.168.1.141:

# Mysql> uninstall plugin rpl_semi_sync_master;

# Mysql> show status like "% semi %"

192.168.1.142:

# Mysql> uninstall plugin rpl_semi_sync_slave;

# Mysql> show status like "% semi %"

BitsCN.com