MySQL Semi-synchronous replication

L Installation Configuration:

Slightly

L Machine Distribution

Ip	Role	Use
192.168.15.3	Master	Main Library
192.168.15.4	Slave1	Standby Library
192.168.15.5	Slave2	Standby Library

L Basic MySQL master-slave configuration

The MySQL replication process is divided into three steps:

Master will change the record to binary log (binary logs). These recording processes are called binary log events, binary logs event;

The slave copies the binary log events of master to its trunk log (relay log);

Slave redo the events in the trunk log and apply the changes to your own database. MySQL replication is asynchronous and serialized

Master configuration

1) Assigning Replication permissions

Both the main library and the slave library need to be executed

mysql> Grant Replication client,replication slave on * * to ' replck1 ' @ ' 192.168.15.% ' identified by "replck1#!123";

mysql> flush Privileges;

2) Dump the data in master into Slave1 slave2

/opt/app/mysql/bin/mysqldump-uroot-p '--opt--default-character-set=utf8 \

--triggers-r--master-data=2--hex-blob--single-transaction--no-autocommit--all-databases > All.sql

Install on each machine

[Email protected]_03 ~]# yum-y Install openssh-clients

Import

[Email protected]_04 ~]#/opt/app/mysql/bin/mysql </tmp/all.sql

[Email protected]_05 data]#/opt/app/mysql/bin/mysql </tmp/all.sql

After import, go to master purge log

mysql> Reset Master;

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

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

|      mysql-bin.000001 |              120 |                  |                   | |

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

1 row in Set (0.00 sec)

Specify in Slave1 slave2

Mysql> Change Master to

Master_host= ' 192.168.15.3 ',

Master_user= ' Replck1 ',

Master_password= ' replck1#!123 ',

master_port=3306,

Master_log_file= ' mysql-bin.000001 ',

master_log_pos=120;

Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> start slave;

Create a Iot2 library in master and discover that it is synchronized

Turn on Half-sync

1, install the plugin on master:
Install plugin rpl_semi_sync_master soname ' semisync_master.so ';
SET GLOBAL Rpl_semi_sync_master_enabled=on;
In the MY.CNF configuration file, add rpl_semi_sync_master_enabled = 1

2, in the slave add-ons:
Install plugin rpl_semi_sync_slave soname ' semisync_slave.so ';
SET GLOBAL rpl_semi_sync_slave_enabled =on;
In the MY.CNF configuration file, add rpl_semi_sync_slave_enabled = 1

Parameters:
When all slave crashes or disconnects, even master does not have slave (impossible). Thus slave accepts a transaction that does not have a master, resulting in m not receiving acknowledgment information for s return. M will always wait. Here are 2 parameters to set:
On master:
rpl_semi_sync_master_enabled: indicates that the semi-synchronous copy mode is turned on on master.
rpl_semi_sync_master_timeout: in order to prevent the semi-synchronous replication from receiving an acknowledgement from the s, it is possible to set a timer to set a timeout that exceeds this time value without receiving information, then switch to asynchronous replication and perform the operation. (the default is 10000 milliseconds, equal to 10 seconds, this parameter is dynamically adjustable, indicating that the main library is in a transaction, if the wait time exceeds 10 seconds, then it is degraded to asynchronous replication mode, no longer waiting for slave from the library.) If the main library is detected again and slave is recovered from the library, it will automatically return to the semi-synchronous replication mode again. ）
Rpl_semi_sync_master_wait_no_slave: When a transaction is committed, but Master does not have a slave connection, m cannot receive any acknowledgment information, but M will continue to wait within the time limit. If there is no slave link, it switches to asynchronous replication. (whether to allow master to wait for slave to receive the acknowledgement signal after each transaction commits.) The default is on, and every transaction waits. If off, the slave will not turn on the semi-synchronous mode and need to be turned on manually after catching up. ）
On slave:
Rpl_recovery_rank: When slave is down from the library, if the relay-log is damaged, causing some of the relay logs to not be processed, all the relay-log that are not executed are automatically discarded, and the logs are retrieved from MASTER again, ensuring The integrity of the relay-log. This feature is turned off by default, and the value of Relay_log_recovery is set to 1 o'clock, which will be enabled on slave from the library.

In master

Mysql> Show variables like ' rpl% ';
+------------------------------------+----------+
| 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 |
| Rpl_stop_slave_timeout | 31536000 |
+------------------------------------+----------+
5 rows in Set (0.00 sec)

In the slave

Mysql> Show 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.01 sec)

State:
How do I know it is a semi-synchronous copy?

Rpl_semi_sync_master_clients: Describes the number of linked slave that support and register semi-synchronous replication.
Rpl_semi_sync_master_status:on is active (semi-synchronous), off is inactive (asynchronous), either he is not enabled or has reverted to the asynchronous replication state. Used to indicate that the primary server is using an asynchronous complex
mode, or semi-synchronous replication mode.
RPL_SEMI_SYNC_MASTER_YES_TX: Number of successful submissions.
RPL_SEMI_SYNC_MASTER_NO_TX: The number is not successfully submitted.
The semi-synchronous replication state on the Rpl_semi_sync_slave_status:slave, on indicates that it is already enabled, and off indicates inactivity.

Copy a normal test

mysql> INSERT into test1 values (5, ' D5 ');
Query OK, 1 row Affected (0.00 sec)

Mysql> show global status like ' rpl_semi% ';
+--------------------------------------------+-------+
| variable_name | Value |
+--------------------------------------------+-------+
| rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 7022 |
| Rpl_semi_sync_master_net_wait_time | 21066 |
| Rpl_semi_sync_master_net_waits | 3 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 4 |
| Rpl_semi_sync_master_status | On |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 3146 |
| Rpl_semi_sync_master_tx_wait_time | 6293 |
| 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 |
+--------------------------------------------+-------+
Rows in Set (0.00 sec)

Stop slave1 and insert it in master

mysql> INSERT INTO test1 values (6, ' D6 ');
Query OK, 1 row affected (10.01 sec)

The state at this time

Mysql> show global status like ' Rpl_semi_sync_master_status ';
+-----------------------------+-------+
| variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | OFF |
+-----------------------------+-------+
1 row in Set (0.00 sec)

Re-open slave view Master

Mysql> show global status like ' Rpl_semi_sync_master_status ';
+-----------------------------+-------+
| variable_name | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_status | On |
+-----------------------------+-------+
1 row in Set (0.00 sec)

Insert

mysql> INSERT INTO test1 values (7, ' D7 ');
Query OK, 1 row affected (0.06 sec)

No problem

MySQL Semi-synchronous replication