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

Asynchronous replication (Asynchronous Replication)

Mysql default replication is asynchronous, and the master database will immediately return the result to the client after executing the things submitted by the client, and does not judge whether the slave library has accepted and processed it, so there will be a problem. If the master database crash at this time, the committed things on the master database may not have been transferred to the slave database. If at this time, the master is forced to promote to the slave, it may lead to data loss on the new master.

Fully synchronous replication (Fully Synchronous Replication)

When the master library executes a thing, all slave libraries execute the thing before returning it to the client. Because you need to wait for all slaves to execute this thing before returning, fully synchronous replication has a serious impact on performance.

Semi-synchronous replication (Semisynchronous Repalication)

Between asynchronous replication and full synchronous replication, the main library does not return to the client immediately after executing the transaction committed by the client, but waits for at least one received from the library and written to the relay log before returning to the client. Compared with asynchronous replication, semi-synchronous replication improves the security of data, and it also causes a certain degree of delay.

There are two types of semi-synchronization:

(1) official semi-synchronization

That is, when the parameter rpl_semi_sync_master_wait_point = after_commit

Principle:

The client sends a request, which is analyzed by the SQL of mysql, processed by the storage engine, written to binlog, then submitted by the storage engine layer, written to the relaylog of the slave library, and finally returned to the client.

Disadvantages:

(1) after the storage commit is submitted, other sessions can read the updated data, but the client does not have it yet. You need to wait for the log to be received from the library before returning the result.

(2) after the storage commit, if the master crash becomes available and the slave database has not received the relaylog, the master-slave switch will result in less new master data than the original master data, that is, part of the data will be lost.

(2) enhanced semi-synchronization (also known as community semi-synchronization, lossless lossless semi-synchronization, etc.)

That is, the parameter rpl_semi_sync_master_wait_point = after_sync (parameter default)

Principle:

The client sends a request, which is analyzed by mysql, processed by the storage engine, written to binlog, then written to the relaylog of the slave library, submitted by the storage engine layer, and finally returned to the client.

Advantages:

The master library first sends the update to the relaylog of the slave library, then submits it, and returns the result to the client. Even after the master crash after the storage commit, the log has been written to the relaylog, and the data of the slave library is the same as that of the master database.

To use semi-synchronous replication, the following conditions must be met:

1. MySQL 5.5 and above

two。 Variable have_dynamic_loading is YES

3. Asynchronous replication already exists

4. Multi-source replication is not supported

1. Basic environment

Masterslave database version 5.7.165.7.16IP192.168.91.16192.168.91.22serverid330616330622 port number 33063306

two。 Master-slave configuration

Master:

Settings in the configuration file:

Server-id = 330616

Binlog_format = row

Log-bin = / data/mysql3306/logs/mysql-bin

Skip_slave_start=1

Log-slave-updates=0

Gtid_mode=ON

Enforce-gtid-consistency=ON

Slave:

Settings in the configuration file:

Server-id = 330622

Binlog_format = row

Relay-log=relay-bin

Relay-log-index=relay-bin.index

Skip_slave_start=1

Log_slave_updates=0

Gtid_mode=on

Enforce-gtid-consistency=on

3. The main library creates users

Master1:

Create user rep@'192.168.91.%' identified by '147258'

Grant replication slave on *. * to rep@'192.168.91.%'

4. From library change master to

Slave:

Change master to

Master_host='192.168.91.16'

Master_port=3306

Master_user='rep'

Master_password='147258'

Master_auto_position=1

5. Install the plug-in

Master:

Install plugin rpl_semi_sync_master soname 'semisync_master.so'

Show plugins

You can also load the plug-in by downloading the service restart in the configuration file:

Plugin-load=rpl_semi_sync_master=semisync_master.so

Slave:

Install plugin rpl_semi_sync_slave soname 'semisync_slave.so'

Show plugins

You can also load the plug-in by downloading the service restart in the configuration file:

Plugin-load=rpl_semi_sync_slave=semisync_slave.so

6. Edit configuration file

Master

# semi

Rpl_semi_sync_master_enabled=1

Rpl_semi_sync_master_timeout=1000 # 1 second

Take effect dynamically:

Set global rpl_semi_sync_master_enabled=1

Set global rpl_semi_sync_master_timeout=1000

Slave

# semi

Rpl_semi_sync_slave_enabled=1

Take effect dynamically:

Set global rpl_semi_sync_slave_enabled=1

Under some highly available architectures, if you need master-slave switching to continue to use semi-synchronization, both master and slave need to load plug-ins and add configuration files:

# plugins

Plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

# semi

Rpl_semi_sync_master_enabled=1

Rpl_semi_sync_master_timeout=1000 # 1 second

Rpl_semi_sync_slave_enabled=1

7. Parameters and statu

Variables:

Rpl_semi_sync_master_enabled = ON

Indicates that semi-synchronous replication mode is enabled on master

Rpl_semi_sync_master_timeout = 10000

The default is 10000 milliseconds, equal to 10 seconds. This parameter is dynamically adjustable, which means that if the waiting time of the master library exceeds 10 seconds in a transaction, it will be degraded to asynchronous replication mode instead of waiting for the SLAVE slave library. If the primary library detects that SLAVE is restored from the library again, it will automatically return to semi-synchronous replication mode again.

Rpl_semi_sync_master_trace_level = 32

Used to debug level when semi-synchronous replication mode is turned on. Default is 32.

Rpl_semi_sync_master_wait_for_slave_count=1

This parameter controls how many slave replies the master needs to wait before returning to the client. The default is 1.

Rpl_semi_sync_master_wait_point = AFTER_SYNC

Semi-synchronous mode

Status:

Rpl_semi_sync_master_clients

The current number of semi-synchronous replication slaves, if it is an one-master multi-slave architecture, does not include the number of asynchronous replication slaves.

Rpl_semi_sync_master_yes_tx

Indicates the successful use of semi-synchronous things

Rpl_semi_sync_master_no_tx

Things that indicate that semi-synchronization is not used are:

Example:

Master:

How to check whether semi-synchronization is enabled:

Root@localhost [testdb] > show global variables like'% semi%'

+-+ +

| | Variable_name | Value |

+-+ +

| | rpl_semi_sync_master_enabled | ON |-- indicates that the semi-synchronization on the main database has been enabled |

| | rpl_semi_sync_master_timeout | 1000 | |

| | rpl_semi_sync_master_trace_level | 32 | |

| | rpl_semi_sync_master_wait_for_slave_count | 1 | |

| | rpl_semi_sync_master_wait_no_slave | ON |

| | rpl_semi_sync_master_wait_point | AFTER_SYNC |

| | rpl_semi_sync_slave_enabled | ON |

| | rpl_semi_sync_slave_trace_level | 32 | |

+-+ +

Test inserts a piece of data:

Root@localhost [testdb] > insert into T1 values

Root@localhost [testdb] > show global status like'% semi%'

+-+ +

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

| | Rpl_semi_sync_master_no_times | 0 | |

| | Rpl_semi_sync_master_no_tx | 0 |-- indicates that no semi-synchronous transaction is used |

| | Rpl_semi_sync_master_status | ON |-- indicates that semi-synchronization is running |

| | Rpl_semi_sync_master_timefunc_failures | 0 | |

| | Rpl_semi_sync_master_tx_avg_wait_time | 2920 | |

| | Rpl_semi_sync_master_tx_wait_time | 2920 | |

| | 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 | 1 |-indicates the successful use of semi-synchronous transactions |

| | Rpl_semi_sync_slave_status | OFF |

+-+ +

Slave:

Root@localhost [(none)] > stop slave

Root@localhost [testdb] > show status like'% semi%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Rpl_semi_sync_slave_status | OFF |

+-+ +

Master:

Root@localhost [testdb] > insert into T1 values (5meme')

Query OK, 1 row affected (1.01 sec)-it takes 1.01 seconds to insert the first piece of data here, which is controlled by rpl_semi_sync_master_timeout=1000ms, because the timeout after 1 second does not use semi-synchronization and becomes a normal replication.

Root@localhost [testdb] > insert into T1 values

Query OK, 1 row affected (0.00 sec)-the later operations do not use semi-synchronization by default, so it is faster to use.

Root@localhost [testdb] > show global status like'% semi%'

+-+ +

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

| | Rpl_semi_sync_master_no_times | 1 | |

| Rpl_semi_sync_master_no_tx | 2 |-indicates that there are 2 transactions that do not use semi-synchronization. If this value is relatively large, then there may be a problem with the slave library and need to be optimized.

| | Rpl_semi_sync_master_status | OFF |

| | Rpl_semi_sync_master_timefunc_failures | 0 | |

| | Rpl_semi_sync_master_tx_avg_wait_time | 2920 | |

| | Rpl_semi_sync_master_tx_wait_time | 2920 | |

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

| | Rpl_semi_sync_slave_status | OFF |

+-+ +

Rpl_semi_sync_master_wait_for_slave_count=N

This parameter controls how many slave replies the master needs to wait before returning to the client. The default is 1.

Example:

Root@localhost [testdb] > set global rpl_semi_sync_master_wait_for_slave_count=2

Root@localhost [testdb] > show variables like'% semi%'

+-+ +

| | Variable_name | Value |

+-+ +

| | rpl_semi_sync_master_enabled | ON |

| | rpl_semi_sync_master_timeout | 1000 | |

| | rpl_semi_sync_master_trace_level | 32 | |

| | rpl_semi_sync_master_wait_for_slave_count | 2 | |

| | rpl_semi_sync_master_wait_no_slave | ON |

| | rpl_semi_sync_master_wait_point | AFTER_SYNC |

+-+ +

Root@localhost [testdb] > insert into T2 values

Query OK, 1 row affected (1.03 sec)-uses asynchronous replication mode after timeout

Summary:

1. In an one-master and multi-slave architecture, if semi-synchronous replication is to be enabled, it is not required that all slaves are semi-synchronous replication.

2. MySQL 5.7 greatly improves the performance of semi-synchronous replication.

(1) in version 5.6 semi-synchronous replication, dump thread undertakes two different and frequent tasks: sending binlog to slave and waiting for slave feedback, and these two tasks are serial, and dump thread must wait for slave to return before sending the next events transaction. Dump thread has become a bottleneck to improve the performance of the whole semi-synchronous. In high concurrency business scenarios, such a mechanism will affect the overall TPS of the database.

(2) in semi-synchronous replication version 5.7, an independent ack collector thread is created to receive feedback from slave. In this way, two threads on the master work independently, sending binlog to slave and receiving feedback from slave at the same time.

Reference link:

Https://dev.mysql.com/doc/refman/5.7/en/replication-semisync.html

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.