Detailed explanation of mysql master-slave synchronization principle, configuration and delay

This article introduces the master-slave synchronization principle, master-slave synchronization configuration, and master-slave synchronization delay of mysql. First, let’s understand what master-slave synchronization is. Master-slave synchronization, as the name implies, is also called master-slave replication, which is used to establish a The database environment is exactly the same as the main database. Master-slave synchronization allows data to be copied from one database server to other servers to ensure that the data in the master database and the data in the slave database are consistent.

• The cluster is a shared storage, which is data-sharing. There is no sharing in master-slave replication. Each machine is an independent and complete system, which is nothing-sharing.

The principle of master-slave synchronization

• There are three main ways to implement master-slave replication after mysql5.6 :
  

  1. Asynchronous replication

  2. Fully synchronous replication

  3. Semi-synchronous replication
  

• Master-slave synchronization schematic
  

1. When the update event (update, insert, delete) of the master database is written binary-log .

2. The slave library creates an I/O thread, which connects to the main library and requests the main library to send the update records in the binlog to the slave library. The main library creates a binlog The dump thread thread sends the contents of the binlog to the slave library. The I/O thread of the slave library reads the updates sent by the output thread of the main library and copies these updates to the local relay log file.

3. From The library creates a SQL thread. This thread reads the update events written to the relay log from the library I/O thread and executes them.

Implementation of master-slave synchronization (asynchronous replication, databases on different servers)

1. Configure the main database to open binary-log

vim /etc/my.cnf

在[mysqld]下添加

server-id=1(用来标识不同的数据库)log-bin=master-bin(打开bin-log并配置文件名为master-bin)log-bin-index=master-bin.index(区分不同的log-bin文件)

Restart the database: systemctl restart mariadb.service

2. Configure the slave database to open relay-log

vim /etc/my.cnf

在[mysqld]下添加

server-id=2relay-log=slave-relay-bin(打开relay-log并配置文件名为slave-relay-bin)

relay-log-index=slave-relay-bin.index

Restart the database: systemctl restart mariadb.service

3. Connect two databases

In the main database: Create user repl, which is required for each slave server An account name and password for the master database to connect to the master server.

CREATE USER 'repl'@'114.116.77.213' IDENTIFIED BY '12312';GRANT REPLICATION SLAVE ON *.* TO 'repl'@'114.116.77.213' IDENTIFIED BY '12312';

In the slave database:

change master to master_host='47.106.78.106',master_user='repl',master_password='12312',master_log_file='master-bin.000001',master_log_pos=0;

Start synchronization: start slave;

4. Verification

Create a database in the master database, and then check the slave database

The role of master-slave synchronization

1. Do hot backup of data as a backup database. After the main database server fails, you can switch to the slave database to continue working to avoid data loss.

2. Read and write Separation allows the database to support greater concurrency.

Notes on master-slave synchronization

1. The master library can read and write data, while the slave library can only Read data, because when the slave library writes data, the positon will change, but the position of the main library will not change. When the main library writes data and changes the position, there may be a conflict.

2. When the binarylog file of the main library stores a lot of data, that is, when the position is very large, a new binarylog file will be split and the position is set to 0;

3. mysql of the master-slave library The versions can be different, but the mysql version of the slave library is higher than the version of the main library. If not, the statements of the main library may not be executed when reaching the slave library.
   Because mysql is backward compatible, that is It is said that the statements of the lower version are supported in the higher version, but some statements of the higher version are not supported in the lower version.

Interview related

(If you ask When it comes to database master-slave issues, you must ask the following questions):

1. What are the benefits of master-slave?

2. What is the principle of master-slave?

3. Do you know about the delay problem of reading from the database? How to solve?

4. What should I do if the master server crashes after being the master and slave?

5. The reason for the delay of master-slave synchronization

6. The reason for the delay of master-slave synchronization
   

Master-slave synchronization delay problem

1. Reasons for the delay of master-slave synchronization

We know that a server is open N links are provided for the client to connect, so there will be large concurrent update operations, but there is only one thread to read the binlog from the server. When a certain SQL takes a little longer to execute on the slave server or due to some Locking the table for each SQL will result in a large backlog of SQL on the master server and not being synchronized to the slave server. This leads to master-slave inconsistency, that is, master-slave delay.

2. Solution to master-slave synchronization delay

In fact, there is no one-stop solution to master-slave synchronization delay, because all SQL must be executed in the slave server , but if the main server continues to have update operations written continuously, then once a delay occurs, the possibility of aggravating the delay will be greater. Of course we can take some mitigation measures.

a. We know that because the master server is responsible for update operations, it has higher security requirements than the slave server. All settings can be modified, such as sync_binlog=1, innodb_flush_log_at_trx_commit = 1, etc., but slave does not need to For such high data security, you can set sync_binlog to 0 or turn off binlog, innodb_flushlog, innodb_flush_log_at_trx_commit can also be set to 0 to improve the execution efficiency of SQL. This can greatly improve efficiency. The other is to use a better hardware device than the main library as the slave.

b. That is, a slave server is used as a backup without providing queries. When its load is reduced, the efficiency of executing the SQL in the relay log will naturally be higher.

c. The purpose of adding slave servers is to disperse the reading pressure, thereby reducing the server load.

Related recommendations:

MYSQL master-slave synchronization delay principle analysis and solutions

MYSQL master-slave synchronization delay principle

The above is the detailed content of Detailed explanation of mysql master-slave synchronization principle, configuration and delay. For more information, please follow other related articles on the PHP Chinese website!