Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to configure semi-synchronous replication in MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

MySQL installation

Install via APT, official guidance document address:

Https://dev.mysql.com/downloads/repo/apt/

1. Download mysql-apt-config_0.8.3-1_all.deb

2. Install deb

A Quick Guide to Using the MySQL APT Repository:

Https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/

> sudo dpkg-I mysql-apt-config_0.8.3-1_all.deb

3. Update source

> sudo apt-get update

4. Install MySQL

> sudo apt-get install mysql-server

5. Follow the prompts to select the installation and set the password.

In addition, it also needs to be set up, bind the IP and open the firewall port 3306, here is mainly to learn MySQL semi-synchronous replication, do not explain the installation of MySQL, if in doubt, welcome to leave a message.

Introduction to MySQL semi-synchronous replication

(1) by default, the replication function of MySQL is asynchronous, and asynchronous replication can provide the best performance. When the master sends the binlog log to the slave database, this action ends and does not verify whether the slave database has been received. This process means that when the master server or slave server fails, it is possible that the binlog log sent from the master server may not be received from the master server. This will cause data inconsistency between the master server and the slave server, and even cause data loss during recovery.

Note:

Semi-synchronous replication mode must be turned on on both the master server and the slave server, otherwise the master server defaults to asynchronous replication mode.

(2) Asynchronous replication case:

MySQL5.6 Database Master-Slave (Master/Slave) synchronous installation and configuration

(3) in order to solve the above possible errors, MySQL 5.5 introduces a semi-synchronous replication mode. This mode ensures that the binlog log file sent by the master server is received from the server and written into its own relay log relay log, and then a feedback is given to the master server telling the master server that it has been received, and then the main service thread returns to the current session to inform the operation that the operation is complete.

(4) when a timeout occurs, the master server temporarily switches to asynchronous replication mode until at least one slave server receives a message from the server in time.

(5) self-repair of relay logs:

Starting from MySQL version 5.5.X, the relay_log_recovery parameter has been added. This parameter is used to automatically discard all outstanding relay-log and retrieve logs from the master if the relay.log is corrupted and part of the relay log is not processed after the relay-log goes down from the database, thus ensuring the integrity of the relay-log. This feature is off by default. If you set the value of relay_log_recovery to 1, you can enable this feature on the slave slave library. It is recommended to enable it.

(6) switching between semi-synchronous replication and asynchronous replication:

The working principle of semi-synchronous replication is that when slave receives the binlog log from the IO_ Thread thread of the library, it needs to give a confirmation to the master master library. If the rpl_semi_sync_master_timeout=10000 (10 seconds) does not receive an acceptance confirmation signal from the slave slave library for more than 10 seconds, it will automatically switch to the traditional asynchronous replication mode.

MySQL semi-synchronous replication configuration

First, you need to install two MySQL. Here are:

Master:192.168.1.227

Slave:192.168.1.224

The original database looks like this:

I. Master configuration

(1) install the semi-synchronous replication plug-in in the Master database:

Mysql > INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'

(2) set semi-synchronous replication to be enabled on Master:

Mysql > SET GLOBAL rpl_semi_sync_master_enabled = 1

(3) modify the mysqld.cnf configuration file:

The figure above indicates the file path for the MySQL configuration.

[mysqld] log-bin=mysql-binserver_id = 10086server_id_bits = 33rpl_semi_sync_master_enabled=1rpl_semi_sync_master_timeout=1000

(4) modify the MySQL server-uuid configuration file:

Root@xuliugen:/var/lib/mysql# pwd/var/lib/mysql

Modify the auto.cnf file, the value format of server-uuid is fixed, in order to distinguish it from Slave.

[auto] server-uuid=8d90feb7-1a88-11e7-9d11-000c298a546f

(5) check whether the configuration is successful:

Mysql > SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE'% semi%'

(6) Parameter description:

1. Rpl_semi_sync_master_enabled = 1, indicating that semi-synchronous replication mode has been enabled on master

2. Rpl_semi_sync_master_timeout = 10000, which means that if the waiting time of the master database in a transaction exceeds 10000 milliseconds, it will be degraded to asynchronous replication mode instead of waiting for slave slave database. If the master library detects that the slave slave is restored again, it will automatically switch back to semi-synchronous replication mode.

3. Rpl_semi_sync_master_wait_no_slave, indicating whether master is allowed to wait for an acknowledgement signal from slave after each transaction is committed. The default is ON, which means that every transaction waits. If OFF, semi-synchronous replication mode will not be enabled after slave catches up. It needs to be enabled manually.

4. Rpl_semi_sync_master_trace_level = 32, which refers to the debugging level used to enable semi-synchronous replication mode. The default is 32.

As you can see, when configuring Master, only 1 is set, and everything else is set by default.

II. Slave configuration

(1) install the semi-synchronous replication plug-in in the Slave database:

Mysql > INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'

(2) set semi-synchronous replication to be enabled on Slave:

Mysql > SET GLOBAL rpl_semi_sync_slave_enabled = 1

(3) modify the mysqld.cnf configuration file:

[mysqld] log-bin=mysql-binserver_id=10089server_id_bits = 32rpl_semi_sync_slave_enabled = 1

(4) modify the MySQL server-uuid configuration file:

Root@xuliugen:/var/lib/mysql# pwd/var/lib/mysql

Modify the auto.cnf file, the value format of server-uuid is fixed, in order to distinguish it from Slave.

[auto] server-uuid=8d90feb7-1a88-11e7-9d11-000c298a123f

Make sure it's not the same as Master's server-uuid!

(5) check whether the configuration is successful:

Mysql > SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE'% semi%'

(6) Parameter description:

1. Rpl_semi_sync_slave_enabled = 1, indicating that semi-synchronous replication mode has been enabled on slave

2. Rpl_semi_sync_slave_trace_level = 32, which refers to the debugging level used to enable semi-synchronous replication mode. The default is 32.

Specify Master for Slave

(1) restart Master in command line mode

Root@xuliugen:~# service mysql restart

(2) restart Slave in command line mode

Root@xuliugen:~# service mysql restart

(3) check the Master status:

Mysql > show master status\ Gbomber * 1. Row * * File: mysql-bin.000004 Position: 154Binlog_Do_DB: Binlog_Ignore_DB:Executed_Gtid_Set:1 row in set (0.02 sec) mysql >

Note:

File: mysql-bin.000004Position: 154

Very important, need to use later!

(4) specify Master for Slave:

1. First turn off the semi-synchronous replication of Slave

Mysql > stop slave;Query OK, 0 rows affected (0.01 sec)

2. Specify Master for Slave

Mysql > change master to master_host='192.168.1.227',master_user='root',master_password='123456',master_log_file='mysql-bin.000004', master_log_pos=154;Query OK, 0 rows affected, 2 warnings (0.02 sec)

Where:

Master_host='192.168.1.227',Master main library IP address master_user='root',Master main database account master_password='123456',Master main database root user password master_log_file='mysql-bin.000004', Master main library binlog file master_log_pos=154,Master main library binlog file position

Note:

After MySQL version 5.6, you can use GTID when performing master-slave replication. You no longer need to find binglog and pos points. You only need to know the IP, port, account and password of the master server to automatically find and synchronize points. When you turn on the GTID feature, you no longer need to use binlog and pos. For more information, please consult the relevant materials to learn.

3. Enable semi-synchronous replication of Slave

Mysql > start slave;Query OK, 0 rows affected (0.01 sec)

Test whether semi-synchronous replication is successful or not

Fifth, how to gracefully turn off Slave synchronization information

At some point, a server is no longer used as a Slave, so we need to know its synchronization information, which is generally used:

Mysql > stop slave # first close mysql > reset slave # clear Slave synchronization information

However, when it is displayed again through show master status\ G:

Mysql > show master status\ G

The synchronization message still appears, because the execution of reset slave only deletes the master.info and relay-log.info files, but the synchronization information is still there. if someone is executing the start slave command to turn on the synchronization function, the result will be synchronized again from scratch, which may also result in data loss.

How to make it cleaner? Use the following command:

Mysql > stop slave # first close mysql > reset slave all# clear Slave synchronization information

Execute show master status\ G again and there will be no more information.

VI. Simulation of some abnormal scenes

(1) switching between semi-synchronous replication and asynchronous replication:

The above has explained why there is a switch from semi-synchronous replication to asynchronous replication, so now simulate a scenario to demonstrate.

The scene is as follows:

1. Turn off slave synchronization and stop IO from receiving binlog logs

Mysql > stop slave

This shuts down the IO thread, waits for 10 seconds, and if master does not receive a confirmation from slave, it switches to asynchronous replication mode:

The figure above shows that slave has disabled semi-synchronous replication mode and enabled it again:

Mysql > start slave

At this point, it has been restored to semi-synchronous replication mode.

(2) synchronous error report case demonstration:

We first delete the database table ufind from the slave library, and then delete the library ufind from master again, and the synchronization will report an error:

Last_Error: Error 'Can't drop database' ufind'; database doesn't exist' on query. Default database: 'ufind'. Query: 'DROP DATABASE `ufind`'

At this point, view the semi-synchronous status:

It is enabled, so the semi-synchronous replication mode is not converted into asynchronous replication mode. It can be seen that the semi-synchronous replication mode is directly related to IO_Thread, but not to SQL_THREAD.

That is, slave gives an acknowledgement to the master master library after receiving the binary log from the library, but he doesn't care whether the relay-log relay log is finished.

After reading the above, do you have any further understanding of how to configure semi-synchronous replication in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.