Shulou(Shulou.com)06/01 Report--

Before MySQL5.5, the replication of MySQL was an asynchronous operation, and there was a certain delay between the data of the master database and the slave database. There is a hidden danger: when a transaction is written to the main library and

When the Binlog log pushed by the master database is not available to the slave database, the master database goes down. For example, the master database may lose the transaction Binlog on the master database due to disk corruption, memory failure, and so on. In this case, the slave database may lose this transaction, resulting in inconsistency between the master and the master.

In order to solve this problem, MySQL5.5 introduces semi-synchronous replication mechanism. In the case of asynchronous replication before MySQL5.5, after the main library has completed the Commit commit operation, write to the main library

After the Binlog log, you can successfully return to the client without waiting for the Binlog log to be delivered to the slave library.

In semi-synchronous replication, in order to ensure that every Binlog transaction on the master database can be reliably copied to the slave database, the master database does not give timely feedback to the front each time the transaction is committed.

The application user waits for one of the slave libraries to receive the Binlog transaction and successfully writes the relay log before the master database returns the Commit operation to the client successfully. Semi-synchronous replication guarantee

After the transaction is successfully committed, there are at least two log records, one on the Binlog log of the master database and the other on the relay log Relay Log of at least one slave database.

The integrity of the data is guaranteed.

In semi-synchronous replication mode, if any link of the main library fails before the transaction commits, the transaction commit is not successful and the corresponding Binlog log of the transaction is not received from the library.

The master-slave data is consistent; if the slave database downtime or network failure in the process of sending the Binlog log from the master database to the slave database, the Binlog is not transmitted to the slave database in time

The transaction of the main library will wait for a period of time (the length of time is determined by the number of seconds set by rpl_semi_sync_master_timeout), if Binlog cannot successfully push to the slave during this period.

Library, MySQL automatically adjusts replication to asynchronous mode, and the transaction returns the submission result to the client normally

Semi-synchronization largely depends on the network condition between master and slave libraries. The smaller the round-trip delay RTT, the better the real-time performance of slave libraries. The popular saying is that the faster the network between the master and the slave, the more solid it is from the library.

When.

Semi-synchronous mode is implemented as a plug-in of MySQL5.5. Different plug-ins are used for main library and reopening. Installation is relatively simple, based on asynchronous replication in the previous section.

Semi-synchronous replication plug-in is fine.

(1) first of all, judge whether the MySQL server supports dynamically adding plug-ins:

MariaDB [(none)] > select @ @ have_dynamic_loading

+-- +

| | @ @ have_dynamic_loading |

+-- +

| | YES |

+-- +

1 row in set (0.01 sec)

(2) after confirming the support for adding plug-ins, check whether there is a plug-in in the installation directory of MySQL. Generally, there is a main library plug-in in the $MYSQL_HOME/lib/plugin directory by default.

Semisync_master.so and slave plug-in semisync_slave.so

[root@www ~] # ls / usr/local/mysql/lib/plugin/semisync_*

-rwxr-xr-x 1 root root 510355 Jun 13 09:44 / usr/local/mysql/lib/plugin/semisync_master.so

-rwxr-xr-x 1 root root 427031 Jun 13 09:45 / usr/local/mysql/lib/plugin/semisync_slave.so

Install the plug-in semisync_master.so on the main library:

MariaDB [(none)] > install plugin rpl_semi_sync_master soname 'semisync_master.so'

Query OK, 0 rows affected (0.07 sec)

Install semisync_slave.so from the library:

MariaDB [(none)] > install plugin rpl_semi_sync_slave soname 'semisync_slave.so'

Query OK, 0 rows affected (0.08 sec)

After the installation is complete, you can see the plug-in you just installed from the plugin table:

MariaDB [(none)] > select * from mysql.plugin

+-+ +

| | name | dl |

+-+ +

| | rpl_semi_sync_master | semisync_master.so |

+-+ +

1 row in set (0.00 sec)

MariaDB [(none)] > select * from mysql.plugin

+-+ +

| | name | dl |

+-+ +

| | rpl_semi_sync_slave | semisync_slave.so |

+-+ +

1 row in set (0.00 sec)

In other words, after the installation is complete, MySQL will record the plug-in you just installed in the system table plugin, and the plug-in will be loaded automatically the next time you restart.

(3) you need to configure parameters on the master and slave libraries to enable semi-synchronous semi-sync. The default setting of semi-synchronization is disabled.

Configure global parameters on the main library:

MariaDB [(none)] > set global rpl_semi_sync_master_enabled=1

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)] > set global rpl_semi_sync_master_timeout=30000

Query OK, 0 rows affected (0.00 sec)

MariaDB [pancou] > show variables like'% rpl_semi%'

+-+ +

| | Variable_name | Value |

+-+ +

| | rpl_semi_sync_master_enabled | ON |

| | rpl_semi_sync_master_timeout | 30000 | |

| | rpl_semi_sync_master_trace_level | 32 | |

| | rpl_semi_sync_master_wait_no_slave | ON |

+-+ +

4 rows in set (0.00 sec)

From the same configuration parameters on the library:

MariaDB [(none)] > set global rpl_semi_sync_slave_enabled=1

Query OK, 0 rows affected (0.00 sec)

MariaDB [pancou] > 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)

Note that since the previously configured replication is asynchronous, restart the I / O thread on the slave library (not required if it is a newly configured semi-synchronous replication):

MariaDB [(none)] > stop slave io_thread;start slave io_thread

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Now that the semi-synchronous configuration is complete, we can verify it below. You can see some of the status values of the current semi-synchronous replication through the show status command on the main library:

MariaDB [(none)] > show status like'% 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 | |

+-+ +

14 rows in set (0.00 sec)

Focus on the following three status values:

Rpl_semi_sync_master_status: the value is ON, indicating that semi-synchronous replication is currently on.

Rpl_semi_sync_master_yes_tx: a value of 0, indicating that the master library is semi-synchronously copied to the slave library if there is no transaction at present.

Rpl_semi_sync_master_no_tx: a value of 0 indicates that there are currently 0 transactions that are not responding in time from the library in semi-synchronous mode (remember to give values only, there will be a comparison later).

Perform a transaction and check again:

MariaDB [pancou] > create table emp (emp_id smallint,emp_name varchar (10))

Query OK, 0 rows affected (0.10 sec)

MariaDB [pancou] > insert into emp values (1), (2) Jerry`)

Query OK, 2 rows affected (0.01sec)

Records: 2 Duplicates: 0 Warnings: 0

MariaDB [pancou] > show status like'% semi_sync%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_master_clients | 1 | |

| | Rpl_semi_sync_master_net_avg_wait_time | 1496 | |

| | Rpl_semi_sync_master_net_wait_time | 2992 | |

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

| | Rpl_semi_sync_master_tx_wait_time | 2407 | |

| | Rpl_semi_sync_master_tx_waits | 2 | |

| | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | |

| | Rpl_semi_sync_master_wait_sessions | 0 | |

| | Rpl_semi_sync_master_yes_tx | 2 | |

+-+ +

14 rows in set (0.00 sec)

At this point, you will find that the Rpl_semi_sync_master_yes_ TX value has changed to 2, that is, the create and insert transactions have been semi-synchronously copied to the slave library.

Increase the Rpl_semi_sync_master_yes_tx cardinality to 2 and confirm it from the library:

MariaDB [pancou] > select * from emp

+-+ +

| | emp_id | emp_name |

+-+ +

| | 1 | tom |

| | 2 | jerry |

+-+ +

2 rows in set (0.00 sec)

Try the scenario where the network is abnormal. After waiting for the rpl_semi_sync_master_timeout millisecond timeout, the main database will automatically switch to an asynchronous replication scenario.

(1) first of all, confirm that half-synchronization on the main database will wait for a 30-second timeout

MariaDB [pancou] > show variables like'% semi%time%'

+-+ +

| | Variable_name | Value |

+-+ +

| | rpl_semi_sync_master_timeout | 30000 | |

+-+ +

1 row in set (0.00 sec)

(2) simulate slave downtime or network failure by iptables command on the master library:

The second day

MariaDB [pancou] > set global rpl_semi_sync_master_enabled=1

Query OK, 0 rows affected (0.00 sec)

Show variables like 'rpl_semi_%'

+-+ +

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

+-+ +

4 rows in set (0.01sec)

MariaDB [pancou] > show status like'% semi_sync%'

+-+ +

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

MariaDB [pancou] > delete from emp where emp_id=2

Query OK, 1 row affected (0.01sec)

MariaDB [pancou] > show status like'% semi_sync%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_master_clients | 1 | |

| | Rpl_semi_sync_master_net_avg_wait_time | 1494 | |

| | Rpl_semi_sync_master_net_wait_time | 1494 | |

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

+-+ +

14 rows in set (0.00 sec)

On the slave library

Iptables-I INPUT-s 192.168.110.128-j DROP

(3) execute a transaction on the main database and commit it by default, and the commit operation on the main database will be blocked for 30 seconds:

MariaDB [pancou] > insert into emp values (2djerry`)

-wait.

A new thread that opens a main library

MariaDB [(none)] > show processlist\ G

* * 1. Row *

Id: 7

User: root

Host: localhost

Db: NULL

Command: Query

Time: 0

State: init

Info: show processlist

Progress: 0.000

* 2. Row * *

Id: 10

User: root

Host: localhost

Db: pancou

Command: Query

Time: 4

State: Waiting for semi-sync ACK from slave

Info: insert into emp values (2)

Progress: 0.000

* 3. Row * *

Id: 11

User: repl_user

Host: 192.168.110.130:52165

Db: NULL

Command: Binlog Dump

Time: 2037

State: Reading from net

Info: NULL

Progress: 0.000

3 rows in set (0.00 sec)

MariaDB [(none)] > show processlist\ G

* * 1. Row *

Id: 6

User: root

Host: localhost

Db: pancou

Command: Sleep

Time: 248

State:

Info: NULL

Progress: 0.000

* 2. Row * *

Id: 7

User: root

Host: localhost

Db: NULL

Command: Query

Time: 0

State: init

Info: show processlist

Progress: 0.000

2 rows in set (0.00 sec)

(4) after waiting for 30 seconds on the main database

Query OK, 1 row affected (10.00 sec)

MariaDB [pancou] > insert into emp values (2djerry`)

Query OK, 1 row affected (0.01sec)

Check the status value of semi-synchronization

MariaDB [pancou] > show status like'% semi_sync%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_master_clients | 0 | |

| | Rpl_semi_sync_master_net_avg_wait_time | 15016390 | |

| | Rpl_semi_sync_master_net_wait_time | 30032780 | |

| | Rpl_semi_sync_master_net_waits | 2 | |

| | Rpl_semi_sync_master_no_times | 1 | |

| | Rpl_semi_sync_master_no_tx | 2 | |

| | Rpl_semi_sync_master_status | OFF |

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

+-+ +

14 rows in set (0.00 sec)

Rpl_semi_sync_master_status | OFF

| Rpl_semi_sync_master_yes_tx | 1 was not completed by semi-synchronous replication just now, so semi-synchronization does not add up |

Rpl_semi_sync_master_no_tx | 2

After connecting to the host normally from the library in the previous scenario, confirm whether it will automatically switch back to semi-synchronous replication mode.

Iptables-D INPUT 1

Check the status on the slave library:

MariaDB [pancou] > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.110.128

Master_User: repl_user

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000005

Read_Master_Log_Pos: 1940

Relay_Log_File: www-relay-bin.000011

Relay_Log_Pos: 1024

Relay_Master_Log_File: mysql-bin.000005

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

(2) check the semi-synchronous replication status value

MariaDB [pancou] > show status like'% semi_sync%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_master_clients | 1 | |

| | Rpl_semi_sync_master_net_avg_wait_time | 10019692 | |

| | Rpl_semi_sync_master_net_wait_time | 30059077 | |

| | Rpl_semi_sync_master_net_waits | 3 | |

| | Rpl_semi_sync_master_no_times | 1 | |

| | Rpl_semi_sync_master_no_tx | 3 | |

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

+-+ +

14 rows in set (0.00 sec)

The Rpl_semi_sync_master_status value automatically changes from OFF to | ON, which means that when the master database detects that it is normal, the copy mode from the master library to the slave library is automatically switched to

Semi-synchronous mode

(3) do a test on the main library

MariaDB [pancou] > insert into emp values (3mai Jian bob')

Query OK, 1 row affected (0.01sec)

MariaDB [pancou] > show status like'% semi_sync%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_master_clients | 1 | |

| | Rpl_semi_sync_master_net_avg_wait_time | 7515331 | |

| | Rpl_semi_sync_master_net_wait_time | 30061326 | |

| | Rpl_semi_sync_master_net_waits | 4 |

| | Rpl_semi_sync_master_no_times | 1 | |

| | Rpl_semi_sync_master_no_tx | 3 | |

| | Rpl_semi_sync_master_status | ON |

| | Rpl_semi_sync_master_timefunc_failures | 0 | |

| | Rpl_semi_sync_master_tx_avg_wait_time | 742 |

| | Rpl_semi_sync_master_tx_wait_time | 742 |

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

+-+ +

14 rows in set (0.01 sec)

After committing a transaction, Rpl_semi_sync_master_yes_tx changes from 1 to 2, confirming that the transaction is semi-synchronous replication.

From the process of semi-synchronous replication, we will find that the "half" of semi-synchronous replication is reflected in that although the Binlog logs of the master library and the slave library are synchronized, the master library does not wait for the slave library to be applied.

The log returns the submission result, and this part of the operation is asynchronous, and the data of the slave database is not synchronized with the master database in real time, so it can only be called semi-synchronous rather than full real-time synchronization.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.