MySQL 5.5 semi-synchronous Replication

I. Tutorial purpose:

Mysql also supports semi-synchronous replication in master-slave replication. mysql replication is asynchronous because of poor synchronization performance. After the master database distributes events, you must wait until the slave database completes copying data and receives the normal response from the slave database before proceeding to the next step. When the slave database lags behind the master database in asynchronous mode, the master database is unknown. Therefore, google patch semi-sync replication is introduced after mysql5.5. Two plug-ins are available: semisync_master.so and semisync_slave.so. Semi-Sync: In a master-slave architecture, the master database only waits for one slave database to copy the data and return a normal response. This means that the synchronization is complete and the next step is implemented, that is, asynchronous and high-speed, and synchronization security. Once the master database waits for the response from the slave database to time out, semi-synchronous replication is automatically downgraded to asynchronous replication. Semi-synchronous replication requires at least one slave database in the same data center as the master database, ensuring sufficient bandwidth and fast data replication. Because it has a great impact on write performance, you need to wait for synchronization confirmation.

About MySQL semi-sync Replication

SSL-based semi-synchronous MySQL Replication

MySQL Master/Slave, semi-sync, Master/Master Replication

Implementation of semi-synchronous master-slave architecture for MySQL Server replication

MySQL Semi-synchronous Replication (Semi-synchronous Replication)

Install semi-synchronous replication in MySQL5.5 Configuration

Ii. Experiment steps

1. mysql-supported modules/plug-ins

[Root @ master ~] # Ls-t/usr/local/mysql/lib/plugin/

Debug export mypluglib. so qa_auth_interface.so qa_auth_client.so adt_null.so daemon_example.ini semisync_master.so export qa_auth_server.so auth_test_plugin.so auth_socket.so auth.

2. Installation of the semi-sync Module

2.1 master database

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

Mysql> show global variables like '% semi %'; server variable

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

| Variable_name | Value |

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

| Rpl_semi_sync_master_enabled | OFF |

| Rpl_semi_sync_master_timeout | 10000 | the connection timeout value of the Master/Slave database is automatically downgraded to asynchronous mode. Millisecond

| Rpl_semi_sync_master_trace_level | 32 |

| Rpl_semi_sync_master_wait_no_slave | ON | you have to wait until the slave database is not available.

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

Mysql> set global rpl_semi_sync_master_enabled = 1;

Mysql & gt; set global rpl_semi_sync_master_timeout = 1000;

Mysql> show global status like '% semi % ';

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

| Variable_name | Value |

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

| Rpl_semi_sync_master_clients | 1 | 1 slave Node

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

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

Mysql> USE school;

Mysql> create table tb2 (name CHAR (20 ));

Mysql> show global status like '% semi % ';

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

| Variable_name | Value |

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

| Rpl_semi_sync_master_clients | 1 |

| Rpl_semi_sync_master_net_avg_wait_time | 800 | waiting for the report time returned from the slave server

| Rpl_semi_sync_master_net_wait_time | 800 |

| Rpl_semi_sync_master_net_waits | 1 |

| 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 | 715 | wait for the report time returned by the slave transaction

| Rpl_semi_sync_master_tx_wait_time | 715 |

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

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

Mysql> insert into tb2 VALUES ('Tom '), ('Jerry'), ('jack ');

Mysql> show global status like '% semi % ';

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

| Variable_name | Value |

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

| Rpl_semi_sync_master_clients | 1 |

| Rpl_semi_sync_master_net_avg_wait_time | 1518 |

| Rpl_semi_sync_master_net_wait_time | 3037 |

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

| Rpl_semi_sync_master_tx_wait_time | 715 |

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

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

 

Slave Database

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

Mysql> show global variables like '% semi % ';

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

| Variable_name | Value |

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

| Rpl_semi_sync_slave_enabled | OFF |

| Rpl_semi_sync_slave_trace_level | 32 |

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

Mysql> set global rpl_semi_sync_slave_enabled = 1;

Mysql> stop slave;

Mysql> start slave;

Mysql> show global status like '% semi % ';

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

| Variable_name | Value |

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

| Rpl_semi_sync_slave_status | ON |

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

Permanent configuration editing in my. cnf of Master and Slave:

# OnMaster

[Root @ master ~] # Vim/etc/my. cnf

[Mysqld]

Rpl_semi_sync_master_enabled = 1

Rpl_semi_sync_master_timeout = 1000 #1 second

Verify

Mysql> show global variables like '% semi % ';

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

| Variable_name | Value |

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

| Rpl_semi_sync_master_enabled | ON |

| Rpl_semi_sync_master_timeout | 1000 |

| Rpl_semi_sync_master_trace_level | 32 |

| Rpl_semi_sync_master_wait_no_slave | ON |

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

 

# OnSlave

[Root @ slave ~] # Vim/etc/my. cnf

[Mysqld]

Rpl_semi_sync_slave_enabled = 1

Verify

Mysql> show global variables like '% semi % ';

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

| Variable_name | Value |

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

| Rpl_semi_sync_slave_enabled | ON |

| Rpl_semi_sync_slave_trace_level | 32 |

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

This article permanently updates the link address: