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

This article mainly introduces "how to achieve MySQL asynchronous replication and semi-synchronous replication". In daily operations, I believe many people have doubts about how to achieve MySQL asynchronous replication and semi-synchronous replication. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "how to achieve MySQL asynchronous replication and semi-synchronous replication". Next, please follow the editor to study!

Asynchronous replication

In asynchronous replication (async replication), Master does not care whether Slave receives binary logs, so Master does not have any dependency on Slave. You can think of Master and Slave as two separate servers, and the data will eventually be consistent through binary logs.

Asynchronous replication performs best because it has little overhead on the database itself, unless the master-slave latency is so large that Dump Thread needs to read a large number of binary log files.

If the business does not have high requirements for data consistency, and can tolerate data loss, or even a large number of data loss, when a failure occurs, asynchronous replication is recommended for best performance (for example, businesses like Weibo, although it requires extremely high performance, data loss is usually tolerated). But often the core business system is most concerned about data security, such as monitoring business, alarm system.

Planning for an asynchronous replication environment:

Master (docker), port 3310

Slave (docker), port 3311

Configuration of master

Profile my.cnf

$sudo cat / home/mysql/docker-data/3311/conf/my.cnf# For advice on how to change settings please see# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html[mysqld]## Remove leading # and set to the amount of RAM for the most important data# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M## Remove leading # to turn on a very important data integrity option: logging# changes to the binary log between backups.# log_bin## Remove leading # to set options mainly useful for reporting servers.# The server defaults are faster for transactions and fast SELECTs.# Adjust sizes as needed Experiment to find the optimal values.# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2M#datadir=/home/mysql/docker-data/3307/data#socket=/home/mysql/docker-data/3307/mysql.sockcharacter_set_server=utf8init_connect='SET NAMES utf8'# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0#log-error=/home/mysql/docker-data/3307/logs/mysqld.log#pid-file=/home/mysql/docker-data / 3307/mysqld.pidlower_case_table_names=1 # whether the table name is lowercase server-id=1403311log-bin=mysql-bin # enable binlogbinlog-format=ROW # binlog format auto_increment_increment=1 # self-increasing step size Suitable for primary master replication. In order to avoid id conflicts, the step size is set to the number of master auto_increment_offset=1 # self-increasing offset, and the offset of each master for master replication needs to be inconsistent # binlog-do-db=mstest # databases to be synchronized # binlog-ignore-db=mysql # databases to be ignored # rpl_semi_sync_master_enabled=1#rpl_semi_sync_master_timeout=10000

Start mysql:

$docker run-- name mysql3310-p 3310 MYSQL_ROOT_PASSWORD=root 3306-- privileged=true-ti-e MYSQL_ROOT_PASSWORD=root-e MYSQL_DATABASE=order-e MYSQL_USER=user-e MYSQL_PASSWORD=pass-v / home/mysql/docker-data/3310/conf:/etc/mysql/conf.d-v / home/mysql/docker-data/3310/data/:/var/lib/mysql-v / home/mysql/docker-data/3310/logs/:/var/log/mysql-d mysql:5.7

Create a user for synchronization:

Mysql > GRANT REPLICATION SLAVE,FILE,REPLICATION CLIENT ON *. * TO 'repluser'@'%' IDENTIFIED BY' 123456The query OK, 0 rows affected, 1 warning (0.01 sec) mysql > FLUSH PRIVILEGES;Query OK, 0 rows affected (0.01 sec)

View the binary log of master on master:

Mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | mysql-bin.000003 | 1147 | +- -+ 1 row in set (0.00 sec)

View the list of processes on master:

Mysql > show processlist +-+ -+-+ | Id | User | Host | db | Command | Time | State | Info | + -+-- +-+ | 2 | root | | localhost | order | Query | 0 | starting | show processlist | | 6 | repluser | 172.17.0.1 Query 48450 | NULL | Binlog Dump | 448 | Master has sent all binlog to slave | Waiting for more updates | NULL | +-+ Configuration of-+-+ 2 rows in set (0.00 sec) slave

The configuration file my.cnf is the same as the configuration of master, except that the server-id field needs to be unique.

Start mysql:

$docker run-- name mysql3311-p 3311 privileged=true-- privileged=true-ti-e MYSQL_ROOT_PASSWORD=root-e MYSQL_DATABASE=order-e MYSQL_USER=user-e MYSQL_PASSWORD=pass-v / home/mysql/docker-data/3311/conf:/etc/mysql/conf.d-v / home/mysql/docker-data/3311/data/:/var/lib/mysql-v / home/mysql/docker-data/3311/logs/:/var/log/mysql-d mysql:5.7

Set the information of master in slave:

# master_log_file and master_log_pos take the values shown in show master status above: mysql > change master to master_host='172.23.252.98',master_port=3310,master_user='repluser',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=1147

Open slave and start the SQL and IO threads:

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

View the status of slave:

Mysql > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.54.214 Master_User: repluser Master_Port: 3310 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 1147 Relay_Log_File: 2da789531bf3-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1147 Relay_Log_Space: 534 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_ Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1403311 Master_UUID: cd2eaa0a-7a59-11ec-b3b4-0242ac110002 Master_Info_File: / var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log Waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:1 row in set (0.00 sec)

Only the new data is replicated, and the stock data needs to be synchronized manually using a tool such as mysqldump.

View the list of processes on slave:

Mysql > show processlist +-+-- + -+ | Id | User | Host | db | Command | Time | State | Info | +- +-- +-+ | 4 | root | localhost | order | Query | 0 | starting | show Processlist | | 7 | system user | | NULL | Connect | 533 | Waiting for master to send event | NULL | | 8 | system user | | NULL | Connect | 127 | Slave has read all relay log | Waiting for more updates | NULL | +-+ -+-+ 3 rows in set (0.00 sec) semi-synchronous replication

Semi-synchronous replication is a semi-synchronous replication mechanism before MySQL version 5.7.

Semi-synchronous replication requires at least N Slave to receive binary logs during Master transaction commit, which ensures that the data in at least N Slave servers is complete when Master downtime occurs.

Semi-synchronous replication is not a built-in function of MySQL, but to install a semi-synchronous plug-in, enable semi-synchronous replication, and set N Slave to accept binary logs successfully.

Disadvantages: suppose user1 inserts a piece of data in the main database and is waiting for the data to be returned. User2 can query this data at this time. If master dies at this time, user2 cannot find this data again, resulting in a phenomenon similar to phantom reading.

Enhanced semi-synchronous replication

Enhanced semi-synchronous replication solves the disadvantage of semi-synchronous replication. WAIT and ACK occur before the transaction is committed, so even if Slave does not receive the binary log, Master is down. Because the last transaction has not been committed, the data itself is not visible to the outside world, and there is no problem of loss.

Therefore, for any business that requires data consistency, such as e-commerce's core order business, banking, insurance, securities and other businesses closely related to funds, it is necessary to use enhanced semi-synchronous replication. In this way, the data is safe and guaranteed, and even if there is a downtime, the slave machine also has a complete data.

The enhanced semi-synchronous replication environment is based on asynchronous replication.

To install the plug-in, it is recommended that both master and slave be installed, as there will be a master-slave switching scenario:

# mastermysql > install plugin rpl_semi_sync_master soname 'semisync_master.so';# slavemysql > install plugin rpl_semi_sync_slave soname' semisync_slave.so'

Ensure that the plug-in is installed successfully:

Mysql > show plugins | rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL | | rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL | +-- +-- -+ 46 rows in set (0.00 sec)

Start semi-synchronization on slave first:

Mysql > set global rpl_semi_sync_slave_enabled = {0 | 1}; # 1: enable, 0: disable

Start the semi-synchronization on master again:

Mysql > set global rpl_semi_sync_master_enabled = {0 | 1}; # 1: enable, 0: prohibit # mysql > set global rpl_semi_sync_master_timeout = 10000; # unit is ms, default is 10s

Restart io_thread from the library:

Why are the parameters of mysql > stop slave io_thread;mysql > start slave io_thread; semi-synchronous not recommended to be written to the configuration file

If the parameter is written into the configuration file, the instance will enter semi-synchronous mode immediately after startup. If the instance that is disconnected for a long time is restarted, it may cause the main database to be dragged down.

If the slave library is disconnected for a long time, after reconnecting, you have to wait for all transactions to be followed, and then manually turn on the semi-synchronous mode, instead of switching directly after startup to prevent the impact on the master library.

Query main database status information mysql > 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 | 0 | Rpl_semi_sync_master_no_times | 0 | Rpl_semi_ Sync_master_no_tx | 0 | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | Rpl_semi_sync_master_tx_avg_wait_time | 0 | Rpl_semi_sync_master_tx_wait_time | 0 | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | Rpl_semi_sync_master_wait_sessions | 0 | Rpl_semi_sync_master_yes_tx | 0 | | Rpl_semi_sync_slave_status | ON | +-- -+-+ 15 rows in set (0.00 sec)

Important parameters description:

Rpl_semi_sync_master_clients: the number of connected Slave that supports and registers semi-synchronous replication

The number of times Rpl_semi_sync_master_no_times:master turned off semi-synchronous replication

The number of times Rpl_semi_sync_master_no_tx:master submitted without receiving a reply from slave can be understood as the number of times master waited for a timeout, that is, the number of unsuccessful submissions in semi-synchronous mode.

Rpl_semi_sync_master_status:ON is active (semi-synchronous) and OFF is inactive (asynchronous), indicating whether the primary server uses asynchronous replication mode or semi-synchronous replication mode

Average wait time spent by Rpl_semi_sync_master_tx_avg_wait_time:master on each transaction

The number of times Rpl_semi_sync_master_tx_waits:master waited for success, that is, the number of times master did not wait for a timeout, that is, the number of times it was successfully submitted

The number of successful replies received by Rpl_semi_sync_master_yes_tx:master from slave, that is, the number of successful submissions in semi-synchronous mode.

Query main database parameter information mysql > show global variables like'% sync%' +-+-+ | Variable_name | Value | +-- -+-+ | binlog_group_commit_sync_delay | 0 | | binlog_group_commit_sync_no_delay_count | 0 | innodb_flush_sync | ON | | innodb_sync_array_size | 1 | | innodb_sync_spin_loops | | 30 | | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 10000 | | 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 | sync_binlog | 1 | sync_frm | ON | | sync_master_ Info | 10000 | sync_relay_log | 10000 | | sync_relay_log_info | 10000 | +-- +-- + 18 rows in set (sec)

Important parameters description:

Rpl_semi_sync_master_enabled: (main library) whether to start semi-synchronization

Rpl_semi_sync_master_timeout: wait for milliseconds to become asynchronous replication. Default is 10000ms.

Rpl_semi_sync_master_wait_point:5.7 defaults to AFTER_SYNC (enhanced semi-synchronous replication, lossless copy mode). After receiving the reply from slave, commit. The optional value is AFTER_COMMIT. After the master is submitted, the data is synchronized to slave, and then master waits for the slave reply. The reply is returned to the client successfully.

You can execute stop slave on the slave side to test what happens on the master side.

Execute the following sql on master:

Mysql > insert into t_order values (3, "C"); Query OK, 1 row affected (10.05 sec)

You will find that this statement will block for 10 seconds (corresponding to the above parameter rpl_semi_sync_master_timeout), and then execute successfully. Finally, take a look at the log description of master:

2022-01-25T02:31:57.016430Z 4 [Note] Start binlog_dump to master_thread_id (4) slave_server (1403312), pos (mysql-bin.000005) 2022-01-25T02:31:57.016515Z 4 [Note] Start asynchronous binlog_dump to slave (server_id: 1403312), pos (mysql-bin.000005) 2022-01-25T02:33:32.183819Z 2 [Note] Semi-sync replication initialized for transactions.2022-01-25T02:33:32.183865Z 2 [Note] Semi-sync replication enabled on the master.2022-01-25T02:33:32.184004Z 0 [Note] Starting ack receiver thread2022-01-25T02:33:59.644444Z 5 [Note] While initializing dump thread for slave with UUID, found a zombie dump thread with the same UUID. Master is killing the zombie dump thread (4). 2022-01-25T02:33:59.644612Z 5 [Note] Start binlog_dump to master_thread_id (5) slave_server (1403312), pos (mysql-bin.000005, 154) 2022-01-25T02:33:59.644632Z 4 [Note] Stop asynchronous binlog_dump to slave (server_id: 1403312) 2022-01-25T02:33:59.644727Z 5 [Note] Start semi-sync binlog_dump to slave (server_id: 1403312), pos (mysql-bin.000005 2022-01-25T02:38:02.847879Z 0 [ERROR] mysqld: Got an error reading communication packets2022-01-25T02:38:27.228952Z 2 [Warning] Timeout waiting for reply of binlog (file: mysql-bin.000005, pos: 684), semi-sync up to file mysql-bin.000005, position 419.2022-01-25T02:38:27.229063Z 2 [Note] Semi-sync replication switched OFF.2022-01-25T02:39:47.230189Z 5 [Note] Stop semi-sync binlog_dump to slave (server_id: 1403312)

You can see that semi-synchronous is turned off and becomes asynchronous mode.

At this point, the study on "how to achieve MySQL asynchronous replication and semi-synchronous replication" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.