Master slave replication of MySQL “solutions for existing data in master database” and “several replication modes”

Time:2020-10-19

Master slave replication of MySQL

Master slave replication architecture of MySQL

At present, MySQL supports two types of replication:

1. Traditional way:
The bin log based on the master database copies the log events and event locations to the slave database, and then the slave database is applied to achieve the purpose of master-slave synchronization.

2. Gtid mode (recommended for MySQL > = 5.7)
In gtid based replication, the slave database will inform the master database of the gtid values of the executed transactions, and then the master database will return the list of gtids of all unexecuted transactions to the slave database, and can ensure that the same transaction is executed only once in the specified slave database.

There are many types of MySQL replication:

1. Asynchronous replication
A master database, one or more slave databases, data asynchronously synchronized to the slave database.

2. Synchronous replication
The unique replication mode in MySQL Cluster.

3. Semi synchronous replication
On the basis of asynchronous replication, it is ensured that at least one slave library has received and recorded the transaction before it is submitted.

4. Delay replication
On the basis of asynchronous replication, the data synchronization delay time of master database and slave database is set artificially, that is to ensure that the data delay is at least this parameter.

The following is based on binary log pointsAsynchronous replication:

Master slave replication principle of MySQL:

After configuring master-slave in mysql,As long as we write to the master nodeThis operation will be saved to the binary log (bin log) log of MySQL. When the slave is connected to the master, the master machine will start the binlog dump thread for the slave. When the master binlog changes, the dump thread of the master will notify the slave and send the corresponding binlog content to the slave. The slave node will create two threads, one I / O thread and one SQL thread, when the master-slave synchronization is started.

I / O thread: the thread is linked to the master machine. When the binlog of the master machine is sent to the slave, the IO thread will write the log content to the local relay log.
SQL thread: this thread reads the contents of the relay log and performs corresponding operations on the slave database according to the contents of the relay log.
Possible problems: when there are quite a number of write requests, the slave data may be inconsistent with the master data, which is caused by the short delay in the log transmission process, or the large number of write commands, and the system speed does not match.
This is roughly the principle of master-slave synchronization of MySQL. The two log files, binlog and relay log, play a role in this process.


1、 To configure the master-slave database server parameters:

  1. Master server parameters:
[mysqld]
log-bin = /www/server/data/mysql-bin
binlog_format = mixed
server-id = 100
#expire_ logs_ Days = 10 ා log expiration time
#max_ binlog_ Size = 200m ා the maximum capacity of the log, which can not be set. There is a default value. After setting, MySQL cannot be restarted. I encounter a situation
binlog_do_db = test
#binlog_ Do_ DB specifies the database to record binary logs, that is, the name of the database to be copied. If multiple databases are copied, you can set this option repeatedly

2. Slave server parameters:

[mysqld]
log-bin = /www/server/data/mysql-bin
binlog_format = mixed
server-id = 200
#expire_ logs_ Days = 10 ා log expiration time
#max_ binlog_ Size = 200m ා the maximum capacity of the log, which can not be set. There is a default value. After setting, MySQL cannot be restarted. I encounter a situation

relay_log = /www/server/data/relay-bin
#Specify relay_ Log storage path and file prefix. If not specified, the host name is used as the prefix by default

read_only = on
skip_slave_start = on

#The following two parameters store the master-slave replication information in InnoDB table. By default, the master-slave replication information is stored in the file system. If the slave server is down, it is easy to have different file records and actual synchronization information. If stored in the table, the consistency of data records can be ensured through InnoDB's crash recovery mechanism
master_info_repository = TABLE
relay_log_info_repository = TABLE

2、 Create a replication account on the master server:

  1. Replication slave permission needs to be set:
Create user 'account' @'2.7.4.5 'identified by' password ';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'1.1.1.1';   
Flush privileges; # refresh permissions

3、 Operation on slave server:

  1. View the binlog file name and binlog offset of the master
Enter show master status; 
View the binlog file name and binlog offset of the master

Master slave replication of MySQL

2. Configure the slave server:

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='1.1.1.1',
    ->     MASTER_ User ='account number ',
    ->     MASTER_ Password ='password ',
    ->     MASTER_LOG_FILE='mysql-bin.00001',
    ->     MASTER_LOG_POS=66;
#Notice the master here_ log_ File is the file name of binlog. Enter mysql-bin.00001 in the above figure, and everyone's may be different.
#Notice the master here_ log_ POS is the binlog offset. Enter 66 in the figure above. Everyone's may be different.

4、 The data of the master database and the salve database are consistent (the master database already has a data solution)

The data of the master-slave database should be consistent, otherwise the master-slave synchronization will have bugs

  1. Solutions for existing data in the main database:

The first scheme is to choose to ignore the data before the main database without processing. This scheme is only suitable for unimportant and dispensable data, and can tolerate the inconsistency of master-slave database data in business.

The second scheme is to backup the data of the master database, then import the data exported from the master database to the slave database, and then turn on the master-slave replication to ensure the consistency of the master-slave database data.

Here is the operation of the second scheme:

  • Lock the main database and only read and write is not allowed. The purpose of this is to prevent new data insertion during or after the backup, which results in inconsistent backup data and master data.
  • mysql> flush tables with read lock;

2. Through the full standby initialization on the MySQL master server, the data on the slave server:

[[email protected] data]# cd /data/db_backup/
[[email protected] db_backup]#  mysqldump -uroot -p --master-data=1 --single-transaction --routines --triggers --events  --all-databases > all.sql
Enter password:

3. Unlock the master database:

unlock tables;

4. Import all data into slave database to ensure the consistency of master-slave data


5、 Start master slave synchronization:

From the server MySQL command line
Enter start slave;
Turn on slave synchronization
Then enter show slave status / g;
View slave node status

Master slave replication of MySQL


6、 Precautions:

If the IO thread is in the connecting state all the time, you can check whether the two machines can’t connect with each other. If they can, it may be that the slave account and password are wrongly written. Close the slave again, enter the above configuration command and then open the slave.

If the SQL thread is in no state, it may be caused by data inconsistency between the slave database and the master database, or the transaction is rolled back. If it is the latter, close it firststop slaveAnd then view the binlog and position of the master, then enter the configuration command, and then enter theset GLOBAL SQL_SLAVE_SKIP_COUNTER=1;, and try againstart slave;If the former is passed, check whether there is a table that has not been synchronized, and whether there are tables that exist in the master database but not in the slave database, and then synchronize them and reconfigure them again.

Could not find first log file name in binary log index file

If you find this problem by viewing the slave database status, check the master database status and write the file and position fields in the master library to the slave library configuration by executing the following SQL statements in the slave library.

change master to master_log_file='mysql-bin.000001',master_log_pos=3726

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

If this error occurs when starting the slave, it is mainly because the relay log used by the previous slave is saved. You can execute the following statement to start the slave.

reset slave;
start slave;

7、 Summary:

1. MySQL introduces gtid (global transaction IDS) from 5.6 (MySQL > = 5.7 is improved in 5.7, which is recommended) to make the configuration, monitoring and management of its replication function easier to implement and more robust. The configuration method is basically the same as the log based master-slave replication, so there is no duplication. MySQL 5.7 recommends using gtid to configure master-slave replication.

2. Due to the high concurrency, the master-slave replication of MySQL will lead to the delay of master-slave synchronization. MySQL has two mechanisms in this area, one isSemi synchronous replicationTo solve the problem of data loss in the main databaseParallel replicationTo solve the problem of master-slave synchronization delay. Specific solutions the next article discusses…

MySQL master master master semi synchronization

1. Overview of semi synchronization

First of all, learn about several replication of MySQL

Asynchronous replication
MySQL replication is asynchronous replication by default. The master writes the event to the binlog and commits the transaction. It does not know whether the slave is received or processed;
Disadvantage: there is no guarantee that all transactions will be received by all slaves.
synchronous copy
The master commits the transaction, and will not return the client transaction execution completion information until the transaction has been committed in all slave nodes;
Disadvantages: completing a transaction can cause delays.
Semi synchronous replication
When the semi synchronous replication function is enabled on the master, at least one slave can enable its function. When the master commits a transaction to the slave, and the transaction has been written to the relay log and flushed to the disk, the slave will inform the master that it has received the transaction. If the master commits the transaction and the transaction is blocked and the waiting time-out occurs, and the master is not informed that it has been received within a certain period of time, the master will automatically switch to the asynchronous replication mechanism;
Note: the semi synchronous replication function only works when it is enabled on the master and slave. If only one side is enabled, it is still asynchronous replication.

The main master semi synchronization is described in detail below

Through the semi synchronous plug-in of MySQL, the semi synchronization is set between the two databases to realize the main master semi synchronization architecture.

Compared with semi synchronization, the advantage of this method is that the two databases are in a completely equal position, which makes it easy to switch automatically.

It should be noted that read only is recommended to make only one database writable at the same time. If two databases write at the same time, if the synchronization is inconsistent, the recovery data needs to compare the two databases.

Automatic switching can use keepalived to drift VIP

Master slave replication of MySQL

Synchronization process

1. The main database records the event of data update to the binary log before each transaction is ready to commit. MySQL records binary logs in the order in which transactions are committed. After logging binary logs, the master database tells the storage engine that the transaction is ready to commit

2. The slave library will start a worker thread, and the I / O thread will establish a common client connection with the master database, and then start a special binary dump thread on the master database. This binary dump thread will read the events in the binary log of the master database. If the thread catches up with the main database, it will enter the sleep state until the main library sends a signal to inform that there is a new event and wake up again

3. From the database, the SQL thread reads the events from the relay log and executes them on the slave database to complete the replication

Compared with asynchronous, semi synchronous can solve the problem of data loss, but it can’t solve the problem of handover

MySQL’s default replication is asynchronous replication

The master database will return the result to the client immediately after executing the transaction submitted by the client, and does not care whether the slave database has received and processed it

If the master database is abnormal, the transactions already committed by the master database may not be synchronized to the slave database. If the slave database is forced to be promoted to the master database, the data on the new master database will be incomplete

The recommended solution is to check the synchronization status when the writable database is switched

2. Configure the primary and secondary synchronization

Test environment: database mysql 5.7.20, centos7.2.1511 (DVD)

1. Prepare database

yum install mysql-community-server

mysql-community-server-5.7.20-1.el7.x86_64

Note: client will also be installed automatically when server is installed. However, if client is installed, server will not be installed

Create data directory without password

The catalog must not exist

Note: the multi instance management of MySQL in centos7 can be performed directly through systemctl. stay my.cnf In the configuration, the section must have the @ keyword to manage, so the instance name is also used to create the data directory here

mysqld –initialize-insecure –user=mysql –datadir=/var/lib/[email protected]

Master slave replication of MySQL

to configure my.cnf file
[ [email protected] ]Please note the @ sign in the section

datadir = /var/lib/ [email protected] #Data catalog

socket = /var/lib/[email protected]/[email protected]

port = 3320

pid-file = /var/run/[email protected]/[email protected]

log-error = /var/log/mysqld.log

skip-name-resolve = 1

#Bin log related configuration

log-bin = mysql-bin

binlog_cache_size = 128K

innodb_flush_log_at_trx_commit = 1

binlog_format = MIXED

expire_logs_days = 7

max_binlog_size = 500M

log-slave-updates = 0

slave-skip-errors = 1062,1032

#Relay log configuration

relay_log_recovery = 1

relay-log = [email protected]

#Configuration of database

user = mysql

language = /usr/share/mysql/english

default-storage-engine = InnoDB

character-set-server = UTF8

master_info_repository = TABLE

relay_log_info_repository = TABLE

tmpdir = /var/tmp

#Set read only to be readable and writable

read-only = 0

#After mysql5.7, you must have a server ID to start

server-id = 1
Next, you can start the database

systemctl start [email protected]

2. Create users

The way we create the data directory is without a password. So you can log in directly

Note: only use the sock file to log in

mysql -uroot -S /var/lib/[email protected]/[email protected]

Let’s set the password of the root account first. Create another one to configure semi synchronization

SET password='admin';
grant all on *.* to 'root'@'127.0.0.1' identified by 'admin' with grant option;
flush privileges;

Log out and log in again to see if the root password is created successfully

grant replication client, replication slave on *.* to 'SemiSync'@'%' identified by 'admin';
#Remote synchronization account requires reload and super permissions

3. Install the semi synchronous plug-in

INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so’

INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’

You can view the installed plug-ins through show plugins

Modify after installing the plug-in my.cnf Configuration file, add the following options to the file

#Enable binlog and start semi synchronization

sync_binlog = 1

rpl_semi_sync_master_enabled = 1

rpl_semi_sync_slave_enabled = 1

Repeat the steps for the other node to create an identical instance

Note: Server_ ID needs to be different, and the semi synchronous plug-in also needs to be installed, because we are configuring the primary and primary semi synchronization

4. Configure semi synchronization

Set 192.168.184.54 as the main and 192.168.184.40 as the slave

First, execute show master status on 54 to view the binlog file and location, as shown below

Master slave replication of MySQL

Note that in this step, make sure that no data is written, otherwise it will cause out of sync. The table is usually locked here to prevent new data from being generated

Then set the slave on 40

Master slave replication of MySQL

Use show slave status to view the semi synchronous status.

Master slave replication of MySQL

If the last two lines in the figure (without intercepting the complete command output) are not yes, you can select the last line of the output command_ IO_ Error or last_ SQL_ Error option to view the error prompt. Then according to the prompt processing

At this point, one-way semi synchronization is configured. Next, set the same semi synchronization on another node. In this way, the master master semi synchronization is formed

Now you can test data synchronization

Recommended Today

Summary of recent use of gin

Recently, a new project is developed by using gin. Some problems are encountered in the process. To sum up, as a note, I hope it can help you. Cross domain problems Middleware: func Cors() gin.HandlerFunc { return func(c *gin.Context) { //Here you can use * or the domain name you specify c.Header(“Access-Control-Allow-Origin”, “*”) //Allow header […]