MySQL semi-synchronous replication of the database

1 Overview

Semi-synchronous replication refers to a subset of host synchronous replication, and another part of the host asynchronous replication

The logic of synchronization is that the user writes to the primary server, the primary service is logged to the binary file, the primary service is sent synchronously to the slave server, and after the server responds to the data synchronization, the host server sends the response to the user. This approach has a strong dependency on Io.

MySQL is a highly plug-in, semi-synchronous copy plugin is a total Google dedicated to MySQL plug-ins. The server that contains the plug-in is required for synchronous replication. Semi-synchronous plug-ins in the primary node and from the node are not the same, each using a proprietary plug-in, when the plug-in configuration is enabled, from the node will synchronize the way to synchronize the master server data, other non-configured plug-in machine for asynchronous synchronization mechanism replication. Synchronous replication here can set the time, within a certain time frame, if the slave server with plug-ins does not replicate the data from the primary server, the slave server will be downgraded to asynchronous replication.

The Pxc:percona XtraDB Cluster,percona plug-in is a multi-master, highly available, scalable solution that can be used in production environments. The implementation of the master-slave replication cluster, and the MySQL master-slave service replication logic is not the same, PXC implementation of multi-master cluster replication, each node is readable and writable. Through the cluster channel to synchronize information, do not rely on binary logs and relay logs, bitwise replication, the number of different steps, to achieve almost synchronous replication. Binary level check, you can find inconsistent data, but this program is not the official MySQL, this scheme may stop maintenance, when the volume of data is large, it is difficult to re-transfer the database.

This article does not introduce the use of the PXC tool, the main MySQL comes with the semi-synchronous copy installation plug-in to achieve the semi-synchronous function

2 Plug-in installation

View plug-ins for the current database installation

MariaDB [sunny]> Show plugins;

MySQL supports a variety of plug-ins, where semisync_master.so and semisync_slave.so under path/usr/lib64/mysql/plugin/achieve semi-synchronous replication and require installation to use the synchronous replication feature

Perform the help install under MySQL to see how the install is used

The installation syntax is as follows:

mysql> INSTALL PLUGIN plugin_name SONAME ' shared_library_name ';

which

Plugin_name is the plug-in name, inconsistent with the file name

Shared_library_name is the shared library name, which is the plug-in name that does not need to be appended with the. so suffix under/usr/lib64/mysql/plugin/.

The plug-in name and file name of the plugin can be learned through the document, there is no rule to find

Semi-synchronous replication:

Semisync_master.so main node Plug-in

Semisync_slave.so from the node plug-in

Master node:

INSTALL PLUGIN rpl_semi_sync_master SONAME ' semisync_master.so ';

MariaDB [mydb]> SHOW GLOBAL VARIABLES like ' rpl_semi% ';

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

| variable_name | Value |

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

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

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

MariaDB [mydb]> SET GLOBAL rpl_semi_sync_master_enabled=on;

From node:

INSTALL PLUGIN rpl_semi_sync_slave SONAME ' semisync_slave.so ';

MariaDB [mydb]> SHOW GLOBAL VARIABLES like ' rpl_semi% ';

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

| variable_name | Value |

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

| rpl_semi_sync_slave_enabled | OFF |

| Rpl_semi_sync_slave_trace_level | 32 |

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

MariaDB [mydb]> STOP SLAVE io_thread;

MariaDB [mydb]> SHOW GLOBAL VARIABLES like ' rpl_semi% ';

MariaDB [mydb]> START SLAVE io_thread;

Judging method:

Master node:

MariaDB [mydb]> SELECT @ @global. rpl_semi_sync_master_clients;

3 Example for semi-synchronous replication

Step one, master-slave replication model configuration

Primary Server 71 Configuration

[[Email protected] ~] #vim/etc/my.cnf.d/server.cnf

[Server]

Skip_name_resolve = On

Innodb_file_per_table = On

Max_connections = 20000

Log_bin = Master-log

server_id = 1

Restart MySQL

[[Email protected] ~] #systemctl Restart MARIADB

Authorize accounts with copy rights

MariaDB [sunny]> Grant Replication client,replication slave on * * to ' sunnycopy ' @ ' 192.168.1.% ' identified by ' Pass1234 56 ';

MariaDB [sunny]> flush Privileges;

Configuring from Node 73

[[Email protected] ~] #vim/etc/my.cnf.d/server.cnf

[Server]

Skip_name_resolve = On

Innodb_file_per_table = On

Innodb_buffer_pool_size = 256M

max_connections = 2000

Relay_log = Relay-log

server_id = 2

Restart MySQL

[[Email protected] ~] #systemctl Restart MARIADB

Connect into the slave server, initiate replication from the server

MariaDB [(none)]> change master to master_host= ' 192.168.1.71 ', master_user= ' sunnycopy ', master_password= ' Pass1234 ' , master_log_file= ' master-log.000004 ', master_log_pos=245;

where Master_log_file and Master_log_pos can go to the master server with the following command

MariaDB [sunny]> show Binlog events in ' master-log.000004 ';

Or

MariaDB [sunny]> show Mastert status;

To see where to start copying from which binary file

Start the replication thread

MariaDB [(None)]> start slave;

View status

MariaDB [(none)]> show Slave status\g;

Here, the master-slave replication cluster setup is complete.

Step two, configure for semi-synchronous mode

Installing the module on Master node 73

MariaDB [sunny]> Install plugin rpl_semi_sync_master soname ' semisync_master ';

When the installation is complete, the relevant server semi-synchronization parameters are generated on the master node

See below

MariaDB [sunny]> 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 |

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

5 rows in Set (0.00 sec)

To view the relevant parameters of the semi-synchronous replication status, the command is as follows

MariaDB [sunny]> Show status like '%rpl% ';

Installing the plug-in from node 73

MariaDB [(none)]> Install plugin rpl_semi_sync_slave soname ' Semisync_slave ';

View parameters

MariaDB [sunny]> show global variables like '%rpl% ';

MariaDB [sunny]> Show status like '%rpl% ';

Step three, set the parameter to Enabled

Note that the rpl_semi_sync_master_enabled and rpl_semi_sync_slave_enabled on the master and slave nodes are disabled after the plug-in installation is complete and need to be enabled

Enabled on Master node 71

MariaDB [sunny]> SET @ @global. Rpl_semi_sync_master_enabled=on;

Enable from Node 73

MariaDB [(None)]> set @ @global. Rpl_semi_sync_slave_enabled=on;

Step four, restart the replication thread from the node

Note that this is from the original master-slave copy to semi-synchronous replication, has set up a synchronization host, that is, the command to perform change master to, if there is no need to set the relevant parameters, and then enable the replication thread

73 Settings on

MariaDB [(none)]> stop slave io_thread;

MariaDB [(None)]> start slave io_thread;

Verify that the Rpl_semi_sync_slave_status status from the server is on, and the following commands are viewed:

MariaDB [(None)]> show status like '%rpl% ';

View the rpl_semi_sync_master_clients parameter on the primary server, value is 1, indicating that 1 are already connected to the primary server by enabling the semi-synchronous replication from the server

MariaDB [sunny]> Show status like '%rpl% ';

Check, in the master node, after inserting the data, the state will change

Perform the following actions on 73

MariaDB [sunny]> CREATE DATABASE test2;

MariaDB [sunny]> Use Test2

MariaDB [test2]> CREATE TABLE class (ID int,major char (20));

Check the status of the relevant parameters, it will change, the command is as follows

MariaDB [test2]> Show status like '%rpl% ';

Semi-synchronous replication here the configuration is complete, note that the semi-sync is a double-edged sword, because in order to wait for transactional replication transactions to complete, it takes some time and is relatively slow.

Build environment, it is recommended to use the KeepAlive tool to configure Proxysql to be highly available to prevent single point of failure

MySQL semi-synchronous replication of the database