MySQL 5.6.x semi-sync replication Configuration

MySQL 5.6.x semi-sync replication Configuration

Environment
Master Database: CentOS6.5 x64 192.168.0.65 mysql-5.6.29
Slave Database: CentOS6.5 x64 192.168.0.66 mysql-5.6.29

Connection: MySQL 5.6 master-slave synchronization configuration case

The concept of semi-synchronous replication:
MySQL 5.5.x and later versions support semi-synchronous replication. When the Server Load balancer host is connected to the Master node, you can check whether the Server Load balancer instance is in semi-synchronous replication. When the semi-synchronous replication function is enabled on the Master, at least one Slave should be enabled. At this time, a thread will be blocked when committing a transaction on the Master until it knows that a Server Load balancer with semi-synchronous replication enabled has received all events of the transaction or waits for timeout. When a transaction event has been written to its relay-log and refreshed to the disk, Slave will notify you that it has been received. If the wait times out, that is, the Master has not been notified that it has been received, the Master will be automatically converted to an asynchronous replication mechanism. When at least one semi-synchronous Slave catches up, the Master and its Slave are automatically converted to the semi-synchronous replication mechanism. The semi-synchronous replication function must be enabled on both the Master and Slave nodes. The semi-synchronous replication function will take effect. Otherwise, the half-synchronous replication function is still asynchronous replication when only one side is enabled.

I. semi-synchronous master server configuration 1. mysql master server configuration (1) Add semi-automatic configuration file parameters to the master server

# Vi/etc/my. cnf

[Mysqld]
Log-bin = master-bin
Log-bin-index = master-bin.index
Binlog_format = mixed
Server-id = 1
Expire-logs-days = 7
Rpl_semi_sync_master_enabled = 1
Rpl_semi_sync_master_timeout = 1000 #1 second

(2) install the semi-sync configuration plug-in

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

(3) configure the semi-sync plug-in

Mysql> show global variables like '% rpl % ';
+ ------------------------------------ + ------- +
| Variable_name | Value |
+ ------------------------------------ + ------- +
| Rpl_recovery_rank | 0 |
| 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) Enable the semi-sync plug-in

Mysql> set global rpl_semi_sync_master_enabled = 1;

(5) configuration timeout

Mysql> set rpl_semi_sync_master_timeout 1000;

Mysql> show global status like 'rpl _ semi % ';
+ ------------------------------------------ + ------- +
| Variable_name | Value |
+ ------------------------------------------ + ------- +
| Rpl_semi_sync_master_clients | 0 |
| 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 |
+ ------------------------------------------ + ------- +
14 rows in set (0.00 sec)

Other performance and related parameters:

2. Restart the master server to make the configuration file take effect.

# Service mysqld restart

Ii. Semi-sync slave server configuration 1. mysql slave server configuration (1) Add the following semi-sync configuration.

# Vi/etc/my. cnf

[Mysqld]
Log-bin = mysql-bin
Binlog_format = mixed
Server-id = 11
Relay-log = slave-relay-bin
Relay-log-index = slave-relay-bin.index
Replicate_wild_ignore_table = mysql. %
Rpl_semi_sync_slave_enabled = 1

(2) install the semi-sync configuration plug-in

Mysql> install plugin rpl_semi_sync_slave soname 'semisync _ slave. so ';
Query OK, 0 rows affected (0.00 sec)

(3) configure the semi-sync plug-in

Mysql> show global variables like '% rpl % ';
+ --------------------------------- + ---------- +
| Variable_name | Value |
+ --------------------------------- + ---------- +
| Rpl_semi_sync_slave_enabled | ON |
| Rpl_semi_sync_slave_trace_level | 32 |
| Rpl_stop_slave_timeout | 31536000 |
+ --------------------------------- + ---------- +
3 rows in set (0.00 sec)

(4) Enable the semi-sync plug-in

Mysql> set global rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

Mysql> show global variables like '% rpl % ';
+ --------------------------------- + ---------- +
| Variable_name | Value |
+ --------------------------------- + ---------- +
| Rpl_semi_sync_slave_enabled | ON |
| Rpl_semi_sync_slave_trace_level | 32 |
| Rpl_stop_slave_timeout | 31536000 |
+ --------------------------------- + ---------- +
3 rows in set (0.00 sec)

(5) restart the synchronization process

Mysql> stop slave;
Query OK, 0 rows affected (0.02 sec)

Mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

Mysql> show slave status \ G;
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.65
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000005
Read_Master_Log_Pos: 120
Relay_Log_File: testdb-relay-bin.000012
Relay_Log_Pos: 284
Relay_Master_Log_File: master-bin.000005
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: mysql. %
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 120
Relay_Log_Space: 622
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_ SQL _Errno: 0
Last_ SQL _Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: c8bb22a1-024e-11e6-a1e8-000c29225fa0
Master_Info_File:/usr/local/mysql-5.6.29-linux-glibc2.5-x86_64/data/master.info
SQL _Delay: 0
SQL _Remaining_Delay: NULL
Slave_ SQL _Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_ SQL _Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)

2. Check that one slave server is connected to the master server.

Mysql> show global status like 'rpl _ semi % ';
+ ------------------------------------------ + ------- +
| 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 |
+ ------------------------------------------ + ------- +
14 rows in set (0.00 sec)

3. Restart the slave server to make the configuration file take effect.

# Service mysqld restart

Implement master-slave synchronization between two MySQL Databases

MySQL master-slave synchronization in Linux -- Add a new slave Database

Use XtraBackup to build MySQL master-slave synchronization without stopping the table and locking it

MySQL master-slave synchronization configuration records

Master-slave synchronization configuration of MySQL Databases in Linux

This article permanently updates the link address: