Construction of MySQL master-slave database

Time:2022-5-21

Note: ensure that the same version of the database is installed on the master-slave database. Because the roles of the master-slave database may be interchanged and the probability of error may be reduced, it is recommended to install the latest stable version when possible. The version used in this article is 5.7.36

Build main library

1. Create the account used by master database replication

create user [email protected]'ip' identified by 'password';

grant replication slave on *.* to [email protected]'ip';

2. Check whether the permissions are correct

show grants for [email protected]'ip';
Construction of MySQL master-slave database

image.png

3. Modificationmaster dataMy.my profile library CNF, start binlog and set server ID. to modify these two parameters, you need to restart the database service

[mysqld]
server-id=1
log-bin=/var/log/mysql/localhost-binlog

#The tables that need to be synchronized are not configured to synchronize all databases by default
binlog-do-db=db_a
binlog-do-db=db_b
binlog-do-db=db_c
......

4、Back up the data of the master database to the slave database, it is recommended to set the read lock to be valid to ensure that there is no database write operation, so as to obtain a consistent snapshot

flush tables with read lock

After the backup of the primary database is completed, the write operation can be resumed

unlock tables

5. The purpose of querying the current binary log name and offset value on the main database is toFrom databaseAfter startup, data recovery starts from this point

show master status
Construction of MySQL master-slave database

image.png

6. Start or restart the primary database service

Build slave Library

1. Modify my CNF, add the server ID parameter. Note that the value must be unique and cannot be the same as the server ID of the main database:

[mysqld]
server-id=2

#Specify the database to be synchronized. If not configured, all databases will be synchronized by default
replicate-do-db=sql_inform

skip-slave-start

On the slave library, use the — skip slave start option to start the slave database, or in my The skip slave start configuration is added to CNF, so that the replication from the database server will not be started immediately, which is convenient for further configuration.

2. Set the slave database server accordingly
change master to
master_ Host = ‘primary database server IP’,
master_ Port = database port,
master_ User = ‘master data replication user’,
master_ Password = ‘master data copy user password’,
master_ log_ File = ‘current binary log name of master data’,
master_ log_ POS = current binary log offset value of master data;
List:

change master to 
master_host = '192.168.1.102',
master_port = 9802,
master_user = 'slavauser',
master_password = '123456',
master_log_file = 'localhost-binlog.000009',
master_log_pos = 2329;

3. On the slave library, start the slave thread

start slave

At this time, execute the show processlist command from the library to display processes similar to the following:

Construction of MySQL master-slave database

image.png

This indicates that the slave has connected to the master and started to accept and execute logs. You can also execute the show slave status command on the slave database to check whether the two important fields are both yes

Construction of MySQL master-slave database

image.png

You can also perform an update operation on the master database to observe whether it is synchronized to the slave database.

If one of the above operations fails,Please check the error log on the slave database, conduct troubleshooting.

Multithreaded replication (Extended)

mysql5. 7. It brings a new multi-threaded replication technology, which solves the problem that the data under the same schema of the master is changed and the slave database cannot be applied concurrently.

1. From library my CNF is additionally configured with the following parameters

[mysqld]
#Multithreaded replication (from Library)
#The timestamp of the commit time of the replication dependent master database
slave_parallel_type=LOGICAL_CLOCK
#Number of execution threads
slave_parallel_workers=4
#During the replication process, ensure the consistency with the transaction submission order of the main database
slave_preserve_commit_order=ON
relay_log_recovery=ON
master_info_repository=table
relay_log_info_repository=table

Enhanced semi synchronous replication (Extended)

The replication described above is an asynchronous operation, and there will inevitably be a certain delay between the data of the master database and the slave database. There is a hidden danger: when a transaction is written and committed in the master database, but the slave database does not get the binlog log of the master database, the master database will be unexpectedly shut down due to disk damage, memory failure, breakpoints and other reasons, resulting in the loss of the binlog of the transaction on the master database. At this time, the slave database will lose this transaction, resulting in the inconsistency between the master and the slave database.

In traditional semi synchronous replication, after the master database writes data to binlog and performs the commit operation, it will always wait for the ACK from the slave database, that is, after writing the relaylog from the database, drop the data and return it to the master database message to inform the master database that the front-end application operation can be returned successfully. In this way, there will be a problem that the master database has actually committed the transaction to the transaction engine layer, The application can see that the data has changed and is just waiting to return. If the master database goes down at this time, the slave database may not be able to write to the relaylog, and the data of the master and slave databases will be inconsistent.

The purpose of enhancing semi synchronization is to solve this problem. Fine tuning is made, that is, after the master database writes data to binlog, it starts to wait for the response ack from the slave database,Until at least one slave database writes the relaylog, drops the data to the disk, and then returns to the main database message, notify the master database that it can perform the commit operation, and then the master database starts to commit to the transaction engine layer. At this time, the application can see that the data has changed. The general flow of enhanced semi synchronous replication is shown in figure 30-9.

Construction of MySQL master-slave database

image.png

In the semi synchronous replication mode, if the slave database fails to transmit binlog to the slave database in time due to downtime or network failure when transmitting binlog logs to the slave database, the transactions on the master database will wait for a period of time (the length of time is determined by the milliseconds set by the parameter rpl_semi_sync_master_timeout). If binlog cannot be successfully sent to the slave database within this period of time, MySQL will automatically adjust the replication to asynchronous mode, The transaction returns the commit result to the client normally.

Semi synchronous replication largely depends on the network between master and slave libraries. The smaller the round-trip delay RTT, the better the real-time performance of slave libraries. Generally speaking, the faster the network between master and slave databases, the more real-time the slave database is.

Note: round trip delay RTT (round trip time) is an important performance index in computer network. It represents the total time from the sending end sending data to the sending end receiving the confirmation from the receiving end.

1. Install semisync on the main library_ master. So plug-in

install plugin rpl_semi_sync_master SONAME 'semisync_master.so';

2. Installing semisync on the slave Library_ slave. So plug-in

install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';

View installed plug-ins

select * from mysql.plugin;
Construction of MySQL master-slave database

image.png

3. In the main library my The configuration parameters in CNF enable semi synchronization, which is not enabled by default

[mysqld]
#Semi synchronous configuration (Main Library)
rpl_semi_sync_master_enabled=on
#Response time of transactions on master database waiting for slave database
rpl_semi_sync_master_timeout=1000
#Enhanced semi synchronization
rpl_semi_sync_master_wait_point=AFTER_SYNC

4. From library my Configure parameters in CNF and turn on semi synchronization

[mysqld]
#Turn on semi synchronization (from Library)
rpl_semi_sync_slave_enabled=ON

5. Due to the previous configuration, the I / O thread from the library needs to be restarted (not if it is a newly configured semi synchronous replication)

stop slave
start slave

This completes the semi synchronous configuration. Show status like ‘% semi’ on the main database_ The sync% ‘command can see some states of the current semi synchronous replication

Construction of MySQL master-slave database

image.png

Focus on the following three status values:
Rpl_ semi_ sync_ master_ Status: the value is on, which indicates that semi synchronous replication is currently on.

Rpl_ semi_ sync_ master_ Yes: a value of 6 indicates that 6 transactions in the master database are copied to the slave database through semi synchronization.

Rpl_ semi_ sync_ master_ No: a value of 1 indicates that in the semi synchronous mode, there are 1 transactions that the slave library does not respond in time.

Command set:

show master status;
show processlist;
start slave;
stop slave;
reset slave all;  // Reset all configuration contents of the slave library connected to the master library

change master to 
master_host = '192.168.1.102',
master_port = 9802,
master_user = 'repl',
master_password = 'Re123456..',
master_log_file = 'localhost-binlog.000007',
master_log_pos = 5665;

select @@have_ dynamic_ loading; //  Check whether dynamic add-on is supported
install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';
install plugin rpl_semi_sync_master SONAME 'semisync_master.so';
show status like '%semi_sync%'

Main library my CNF profile reference

[mysqld]
server-id=1
#Port
port=9802
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/mysql.sock
pid-file=/usr/local/mysql/mysql.pid
symbolic-links=0
log_timestamps=SYSTEM

#Table to synchronize
binlog-do-db=sql_inform
binlog-do-db=test

#Semi synchronous configuration (Main Library)
rpl_semi_sync_master_enabled=on
rpl_semi_sync_master_timeout=1000
#Enhanced semi synchronization
rpl_semi_sync_master_wait_point=AFTER_SYNC

#Startup log
log-error=/var/log/mysql/localhost-error.log

#Slow log
slow_query_log=ON
slow_query_log_file=/var/log/mysql/localhost-slow.log
#Timeout
long_query_time=5

#Binary log
log-bin=/var/log/mysql/localhost-binlog
log_bin_index=/var/log/mysql/localhost-binlog.index
#Binary log保留天数
expire_logs_days=7

#Query log
general_log=0
general_log_file=/var/log/mysql/localhost-query.log

#Password strength
plugin-load-add=validate_password.so
validate_password_length=8
validate_password_policy=MEDIUM

#Pattern
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

[mysql]
default-character-set=utf8

[mysqld_safe]
#log-error=/var/log/mariadb/mariadb.dlog
#pid-file=/var/run/mariadb/mariadb.pid
log-error=/var/log/mysql/localhost-error.log
pid-file=/usr/local/mysql/mysql.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

From library my CNF profile reference

[mysqld]
server-id=3
port=9803
datadir=/usr/local/mysql9803/data
socket=/usr/local/mysql9803/mysql.sock
pid-file=/usr/local/mysql9803/mysql.pid
log_timestamps=SYSTEM

#Multithreaded replication (from Library)
slave_ parallel_ type=LOGICAL_ Clock # replication depends on the timestamp of the commit time of the master database
slave_ parallel_ Workers = 4 # number of execution threads
slave_ preserve_ commit_ Order = on # during the replication process, ensure the consistency with the transaction submission order of the main database
relay_log_recovery=ON
master_info_repository=table
relay_log_info_repository=table

#Turn on semi synchronization (from Library)
rpl_semi_sync_slave_enabled=ON

#Error log
log-error=/var/log/mysql9803/localhost-error.log

#Slow log
slow_query_log=ON
slow_query_log_file=/var/log/mysql9803/localhost-slow.log
#Timeout
long_query_time=5

#Binary log
log-bin=/var/log/mysql9803/localhost-binlog
log_bin_index=/var/log/mysql9803/localhost-binlog.index
#Binary log保留天数
expire_logs_days=7

#Relay log
relay_log=/var/log/mysql9803/localhost-relay-bin
log_slave_updates=on

#Query log
general_log=0
general_log_file=/var/log/mysql9803/localhost-query.log

#Password strength
#plugin-load-add=validate_password.so
#validate_password_length=8
#validate_password_policy=MEDIUM

#Pattern
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

[mysql]
default-character-set=utf8

[mysqld_safe]
#log-error=/var/log/mariadb/mariadb.dlog
#pid-file=/var/run/mariadb/mariadb.pid
log-error=/var/log/mysql9803/localhost-error.log
pid-file=/usr/local/mysql9803/mysql.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d