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

The following content mainly brings you a brief analysis of the definition of Mysql semi-synchronous replication and installation tutorials, the knowledge here is slightly different from books, are summed up by professional and technical personnel in the process of contact with users, have a certain experience sharing value, hope to bring help to the majority of readers.

1 Overview

Semi-synchronous replication means that some hosts replicate synchronously while others replicate asynchronously.

The logic of synchronization is that the user writes the operation to the master cloud server. After the master service records to the binary file, the master service will synchronously send it to the slave server. After the slave server responds to the data synchronization, the master server will send the response to the user. This approach is strongly dependent on IO.

Mysql is highly plug-in, and the semi-synchronous replication plug-in is a plug-in co-dedicated to mysql by google. Only the server with this plug-in can replicate synchronously. The plug-ins of semi-synchronous replication are different in the master node and the slave node, and they respectively use proprietary plug-ins. When the plug-in configuration is enabled, the slave node will synchronize the master server data in a synchronous way. Other machines without configured plug-ins are replicated by asynchronous synchronous mechanism. Synchronous replication can be set here, and within a certain time range, if the slave server with the plug-in does not copy the data of the master server, the slave server will be downgraded to asynchronous replication.

PXC:Percona XtraDB Cluster,percona plug-in is a multi-host, highly available, scalable solution that can be used in a production environment. The master-slave replication cluster is realized, which is different from the master-slave service replication logic of mysql. PXC implements multi-master cluster replication, and each node is readable and writable. Synchronization information through the cluster channel, does not rely on binary logs and relay logs, bit-by-bit replication, less non-synchronization, to achieve almost synchronous replication. Data inconsistencies can be found when checking at the binary level, but this solution is not officially owned by mysql. This solution may stop maintenance. When the amount of data is large, it is very difficult to transfer the database again.

This article does not introduce the use of PXC tools, the main mysql comes with semi-synchronous replication installation plug-ins can achieve semi-synchronous function.

2 plug-in installation

View the plug-ins currently installed in the database

MariaDB [sunny] > show plugins

Mysql supports a variety of plug-ins. Among them, semisync_master.so and semisync_slave.so under path / usr/lib64/mysql/plugin/ implement semi-synchronous replication, which requires installation to use synchronous replication.

Execute help install under mysql to view the usage of install

The installation syntax is as follows:

Mysql > INSTALL PLUGIN plugin_name SONAME 'shared_library_name'

Among them

Plugin_name is the plug-in name, which is not consistent with the file name.

Shared_library_name is the name of the shared library, that is, the name of the plug-in that does not need a .so suffix under / usr/lib64/mysql/plugin/.

Know the plug-in name and file name corresponding to the plug-in through the documentation, there is no rule to find.

Semi-synchronous replication:

Plug-in for semisync_master.so master node

Plug-in for semisync_slave.so slave node

Primary 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

Slave 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

The method of judgment:

Primary node:

MariaDB [mydb] > SELECT @ @ global.rpl_semi_sync_master_clients

3 examples to realize semi-synchronous replication

Step 1, master-slave replication model configuration

Primary server 71 configuration

[root@CentOS7A ~] # 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

[root@CentOS7A ~] # systemctl restart mariadb

Authorize an account with copy permission

MariaDB [sunny] > grant replication client,replication slave on *. * to 'sunnycopy'@'192.168.1.%' identified by' Pass123456'

MariaDB [sunny] > flush privileges

Configuration from node 73

[root@CentOS7C ~] # 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

[root@CentOS7C ~] # systemctl restart mariadb

Connect to the slave server and start 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 main server with the following command

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

Or

MariaDB [sunny] > show mastert status

Check the location of the binary file from which to copy

Start the replication thread

MariaDB [(none)] > start slave

View statu

MariaDB [(none)] > show slave status\ G

At this point, the master-slave replication cluster setup is complete.

Step 2, configure it in semi-synchronous mode

Install the module on the master node 73

MariaDB [sunny] > install plugin rpl_semi_sync_master soname 'semisync_master'

After the installation is complete, the relevant server semi-synchronization parameters are generated on the primary node

Check out the following

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 parameters of the semi-synchronous replication status, the command is as follows

MariaDB [sunny] > show status like'% rpl%'

Install 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 3, set the parameter to enable

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

Enabled on primary 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 4, restart the replication thread from the node

Note that this is from the original master slave replication to semi-synchronous replication. The synchronization host has been set, that is, the command change master to has been executed. If there is no need to set the relevant parameters, then enable the replication thread.

Set on 73

MariaDB [(none)] > stop slave io_thread

MariaDB [(none)] > start slave io_thread

Confirm that the Rpl_semi_sync_slave_status status of the server is on. The view command is as follows.

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

Check the Rpl_semi_sync_master_clients parameter on the master server. Value is 1, indicating that one slave server has enabled semi-synchronous replication to connect to the master server.

MariaDB [sunny] > show status like'% rpl%'

Check that at the primary node, the state will change after the data is inserted

Do the following 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, and the change will occur. The command is as follows

MariaDB [test2] > show status like'% rpl%'

Semi-synchronous replication is configured here. Note that semi-synchronous is a double-edged sword, because it takes a certain amount of time and is relatively slow to wait for the completion of transaction synchronous replication transactions.

Build environment, it is recommended to use the keepalive tool to configure proxysql to be highly available to prevent a single point of failure

For the above brief analysis of the definition and installation tutorials of Mysql semi-synchronous replication, if you have more information, you can continue to pay attention to the innovation of our industry. If you need professional solutions, you can contact the pre-sale and after-sale ones on the official website. I hope this article can bring you some knowledge updates.

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.