Mysql5.5 semi-sync installation and maintenance

Author: skate
Time: 2013/01/10

Mysql5.5 semi-sync replication installation and maintenance

MySQL's replication protocol is asynchronous. Although asynchronous efficiency and performance are good, it cannot guarantee master-slave data consistency (if the master crash, transactions that have been committed will not be transferred to any slave.) from mysql5.5, MySQL introduces the semi-sync function to ensure data consistency between the master and slave databases, semi-sync means that the master only needs to receive the returned information from one of the Server Load balancer instances, and then commit; otherwise, it needs to wait until it is switched to asynchronous mode and then submit.

 

Advantages:
When the Transaction Returns a successful client, the log must exist on at least two hosts.
MySQL semi-sync is suitable for small transactions, and the delay between the two hosts is small, semi-sync can achieve zero data loss when the performance is low.

 

Disadvantages:
An extra wait delay is added for completing a single transaction. The latency depends on the network performance.
Semi-sync is not a distributed transaction. After completing the transaction, the master database waits for the slave database to receive the transaction log (Netease has modified the source code so that it is in the same transaction)

When the backup database crash is used, the master database will disable the semi-sync feature after a certain wait timeout and downgrade it to normal asynchronous replication. This situation is relatively simple.

After the master database crash, there may be some transactions in the master database commit, but they have not been passed to any slave database. Let's just call such transactions as "wall transaction ". "Wall transaction" is not returned to the client, so the client initiating the transaction does not know whether the transaction has been completed. In this case, if the client does not perform a switchover, but continues to operate on the master database after the master database of crash is restored, the client will find that all the preceding "wall transaction" has been completed, you can continue to perform subsequent business processing. In another case, if the client fails to the slave database, the client will find that the previous "wall transaction" was not successful, you need to re-execute these transactions and then proceed with subsequent business processing.

Install

Install the semisync_master plug-in the master database:
Mysql> install plugin rpl_semi_sync_master soname 'semisync _ Master. so'; // Linux
Mysql> install plugin rpl_semi_sync_master soname 'semisync _ Master. dll '; // windows

Install semisync_slave plug-in the slave Database
Mysql> install plugin rpl_semi_sync_slave soname 'semisync _ slave. so'; // Linux
Mysql> install plugin rpl_semi_sync_slave soname 'semisync _ slave. so'; // windows

After the plug-in is installed for the first time, MySQL will record the plug-in to the system table MySQL. plugin. The system will automatically load the plug-in at the next startup, without executing the above command again.

 

Brief installation:
Master Installation Process

1. Install plugin rpl_semi_sync_master soname 'semisync _ Master. so ';
2. The value of show variables like 'rpl _ semi_sync_master_enabled 'is on, indicating that it is enabled; otherwise, check the cause of failure.
3. Set global rpl_semi_sync_master_timeout = 100000 (for observation );
4. Set global rpl_semi_sync_master_wait_no_slave = 1 (default value, indicating that even if there is no slave, it will wait until the expiration time ends)
 
Slave Installation Process

1. Install plugin rpl_semi_sync_slave soname 'semisync _ slave. so ';
2. The show variables like 'rpl _ semi_sync_slave_enabled 'value is on, indicating that it is enabled; otherwise, the cause of failure is checked.

 

To enable semi-sync, you need to add the following record in the configuration file my. CNF of the Master/Slave database to enable semi-sync.

The following parameters are added to the master database:

$ VI my. CNF
...
Rpl_semi_sync_master_enabled = 1
Rpl_semi_sync_master_timeout = 1000
...

Added the following on the slave database:

$ VI my. CNF
...
Rpl_semi_sync_slave_enabled = 1
...

If you consider master-slave switchover, you need to install the two plug-ins on both master and slave. You need to add both the master and slave parameters in the configuration file my. CNF.

After the master and slave nodes are started and the slave thread starts dumping logs of the master database, semi-sync replication is enabled. The preceding configuration (rpl_semi_sync_master_timeout = 1000) indicates that the master database is in
In a secondary transaction, if the wait time exceeds 1000 milliseconds, It is downgraded to normal mode and no longer waiting for the standby database. If the master database detects again and the slave database recovers, it will automatically return to the semi-sync status again.

Parameters on the Master/Slave Database
Mysql> show variables like '% rpl_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 |
| Rpl_semi_sync_slave_trace_level | 32 |
+ ------------------------------------ + ------- +
7 rows in SET (0.00 Sec)

 

Slave database parameters:
Mysql> show variables like '% rpl_semi % ';
+ ------------------------------------ + ------- +
| Variable_name | value |
+ ------------------------------------ + ------- +
| Rpl_semi_sync_master_wait_no_slave | on |
| Rpl_semi_sync_slave_enabled | on |
| Rpl_semi_sync_slave_trace_level | 32 |
+ ------------------------------------ + ------- +
6 rows in SET (0.00 Sec)

Mysql>

If two plug-ins are installed on the master, the master parameters are as follows (slave is the same ):

Mysql> show variables like '% rpl_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 |
| Rpl_semi_sync_slave_enabled | on |
| Rpl_semi_sync_slave_trace_level | 32 |
+ ------------------------------------ + ------- +

6 rows in SET (0.00 Sec)

Mysql>

 

Parameter description:
Rpl_semi_sync_master_timeout = 1000: indicates that the master database is in a transaction. If the wait time exceeds 1000 milliseconds, the master database is downgraded to normal mode and no longer waiting for the slave database.
If the master database detects again and the slave database recovers, it will automatically return to the semi-sync status again. During the test, you can set a larger value for observation.

Rpl_semi_sync_master_wait_no_slave = 1: indicates that even if there is no slave, it will wait until the expiration time ends, which is the default value,

These two variables can be set online, but if they are not written in my. CNF, the database restart parameter restores the default value.
Set global rpl_semi_sync_master_timeout = 100000
Set global rpl_semi_sync_master_wait_no_slave = 1

Verify whether semi-sync is enabled for Master/Slave.
Check whether the semi-sync feature is enabled after the master is installed. (If the value of rpl_semi_sync_master_enabled is on, the feature is enabled. Otherwise, check the cause of failure)

Mysql> show variables like '% rpl_semi_sync_master_enabled % ';
+ ------------------------------ + ------- +
| Variable_name | value |
+ ------------------------------ + ------- +
| Rpl_semi_sync_master_enabled | on |
+ ------------------------------ + ------- +
1 row in SET (0.00 Sec)

Check whether semi-sync is enabled after Server Load balancer is installed. (If rpl_semi_sync_slave_enabled is set to on, the Server Load balancer is enabled. Otherwise, check the cause of failure)
Mysql> show variables like '% rpl_semi_sync_slave_enabled % ';
+ ----------------------------- + ------- +
| Variable_name | value |
+ ----------------------------- + ------- +
| Rpl_semi_sync_slave_enabled | on |
+ ----------------------------- + ------- +
1 row in SET (0.00 Sec)

Mysql>

View the semi-sync running status of Master/Slave

Check whether semi-sync is enabled on slave.
Mysql> show status like '% rpl_semi % ';
+ ------------------------------------------ + ------- +
| Variable_name | value |
+ ------------------------------------------ + ------- +
| Rpl_semi_sync_slave_status | on |
+ ------------------------------------------ + ------- +
15 rows in SET (0.00 Sec)

Mysql>

Check whether semi-sync is enabled on the master.
Mysql> show status like '% rpl_semi % ';
+ ------------------------------------------ + ------- +
| Variable_name | value |
+ ------------------------------------------ + ------- +
| Rpl_semi_sync_master_clients | 2 | # Number of slave semi-sync Databases
| Rpl_semi_sync_master_net_avg_wait_time | 1209 | # average wait time for the standby database to respond after the transaction is committed
| Rpl_semi_sync_master_net_wait_time | 213811529 | # Total number of requests waiting for network response
| Rpl_semi_sync_master_net_waits | 176714 | # total network wait time
| Rpl_semi_sync_master_no_times | 0 | # drops from semi-sync to normal status several times
| Rpl_semi_sync_master_no_tx | 0 | # number of transactions that the standby database does not respond to in a timely manner
| Rpl_semi_sync_master_status | on | # enable semi-sync on the master database
| Rpl_semi_sync_master_timefunc_failures | 0 | # Number of times the time function does not work properly
| Rpl_semi_sync_master_tx_avg_wait_time | 1618 | # enable semi-sync, and the average waiting time is returned for the transaction.
| Rpl_semi_sync_master_tx_wait_time | 156225399 | # Total time the transaction waits for the standby database to respond
| Rpl_semi_sync_master_tx_waits | 96501 | # Total number of times the transaction waits for the standby database to respond
| Rpl_semi_sync_master_wait_pos_backtraverse | 1006 | # change the number of currently waiting for the minimum binary log
| Rpl_semi_sync_master_wait_sessions | 0 | # currently, several threads are waiting for the standby database to respond.
| Rpl_semi_sync_master_yes_tx | 92171 | # Number of successful transactions in semi-Sync Mode
+ ------------------------------------------ + ------- +
15 rows in SET (0.00 Sec)

Mysql>

Master Status parameter descriptions
Rpl_semi_sync_master_tx_avg_wait_time: the average waiting time for a transaction because semi_sync is enabled.
Rpl_semi_sync_master_net_avg_wait_time: average network wait time after the transaction enters the waiting queue

 

Based on the above two status values, we can know how much the network consumption of semi-sync is and how much extra consumption is incurred for a transaction.

 

Rpl_semi_sync_master_status: indicates whether the current semi-sync works properly.
Rpl_semi_sync_master_no_times: indicates that semi-Sync has timed out and failed for a period of time. This counter records the number of such failures.

 

Refer:
Http://www.orczhou.com/index.php/2011/06/mysql-5-5-semi-sync-replication-setup-config/
Http://www.mysqlops.com/2011/10/24/mysql-semi-replicatio.html
Http://code.google.com/p/google-mysql-tools/wiki/SemiSyncReplication
Http://www.orczhou.com/index.php/2011/07/why-and-how-mysql-5-5-semi-sync-replication/

 

 

------ End -----