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

This article introduces the master-slave synchronization principle of MySQL, master-slave synchronization configuration, master-slave synchronization delay, first of all, we first understand what is master-slave synchronization, master-slave synchronization, as the name implies also known as the main copy, used to establish a database with the primary database environment exactly the same. Master-Slave synchronization allows data to be replicated from one database server to another, enabling data from the primary database to be consistent with data from the database.

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

Principle of Master-slave synchronization

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

  1. Asynchronous replication

  2. Full synchronous replication

  3. Semi-synchronous replication
  

• master-Slave synchronization schematic diagram
  

1. When the update event (update, INSERT, delete) of the primary database is written to Binary-log.

2. Create an I/O thread from the library that thread attached to the main library and request the main library to send the update records from the Binlog to the library. The main library creates a Binlog dump thread thread that sends the Binlog content to the i/from the library, from the library The o thread reads the updates sent by the output thread of the main library and copies the updates to the local relay log file.

3. Create a SQL thread from the library that reads the update event written to relay log from the library I/O thread and executes.

Implementation of Master-Slave synchronization (asynchronous replication, database on different servers)

1. Configure the primary database to open Binary-log

VIM/ETC/MY.CNF add server-id=1 (used to identify different databases) under [Mysqld] Log-bin=master-bin (open bin-log and configure file name Master-bin) log-bin-index= Master-bin.index (distinguish between different log-bin files)

Restart database: Systemctl Restart Mariadb.service

2. Configure open from Database Relay-log

VIM/ETC/MY.CNF add Server-id=2relay-log=slave-relay-bin under [mysqld] (open relay-log and configure file name Slave-relay-bin) Relay-log-index=slave-relay-bin.index

Restart database: Systemctl Restart Mariadb.service

3. Connection of two databases

In the main database: Create user repl, each slave server needs to use the primary database one account name and password to connect to the primary 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 From 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 sync: start slave;

4. Verification

Create a database in the primary database and then view it from the database

The role of master-slave synchronization

1. Do the hot standby data, as a backup database, the primary database server failure, you can switch to continue to work from the database to avoid data loss.

2. Read and write separation, so that the database can support greater concurrency.

Considerations for Master-Slave synchronization

1. The main library can read and write data, but only the data from the library, because when the data is written from the library Positon will change, but the position of the main library will not change, when the main library write data changes position there may be conflicts.

2. When the main library's Binatylog file stores a lot of data, that is, position is very large, it will split a new Binarylog file, position set to 0;

3. The MySQL version of the master-slave library can be different, but the MySQL version from the library is higher than the main library version, if not, then the main library statement from the library may not be executed.
   Because MySQL is backwards compatible, that is, the lower version of the statement is supported in the higher version, but some statements in the high version are not supported in the lower version.

Interview related

(If you ask the database master-slave problem, you must ask the following questions):

1. What are the advantages of master and slave?

2. What is the principle of master and slave?

3. Do you understand the latency problem of reading from the database? How to solve?

4. What should I do if the boss hangs from the master server?

5. Reasons for the delay of master-slave synchronization

6. Reasons 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 open n links to the client to connect, so there will be a large concurrent update operation, but from the inside of the server to read the binlog of the thread only one, when a SQL to execute from the server for a little longer or because of a SQL to be locked table will cause, The primary server has a large backlog of SQL that has not been synchronized to the slave server. This leads to the inconsistency of the Lord, which is the master-slave delay.

2. Solution of Master-Slave synchronization delay

In fact, master-slave synchronization delay There is no way to make the enemy, because all the SQL must be executed from the server, but the master server if there is a constant stream of updates to write, then if there is a delay, then the likelihood of increased delay will be greater. Of course, we can do some mitigation measures.

A. We know that because the primary server is responsible for the update operation, his security requirements are higher than from the server, all the settings can be modified, such as Sync_binlog=1,innodb_flush_log_at_trx_commit = 1 settings, Slave does not need this high data security, can be said Sync_binlog set to 0 or close Binlog,innodb_flushlog, Innodb_flush_log_at_trx_commit Also can be set to zero to improve the efficiency of SQL execution This can greatly improve efficiency. The other is to use a better hardware device than the main library as a slave.

B. That is, one from the server when the degree of use as a backup, and do not provide a query, where his load down, execute relay log inside the SQL efficiency is naturally high.

C. Increase from the server, this is also a decentralized reading of pressure, thereby reducing server load.