About MYSQL primary/secondary synchronization
What is master-slave replication of MYSQL
In primary/secondary replication, one server serves as the primary server, all data updates are performed on the primary server, and data on the primary server is synchronized to one or more secondary servers to ensure data consistency between the secondary server and the primary server.
Why master slave replication
- High availability. Master/slave replication implements hot backup of data, effectively avoiding data loss caused by single point of failure.
- High performance, can be used to achieve read and write separation, improve the concurrency performance of MYSQL service.
MYSQL master-slave replication principle
-
Master node binlog, the basis of master/slave replication is that the master database records all changes in the database to the binlog. Binlog is a file that holds all changes to the database structure or content from the moment the database server is started.
-
The log dump thread of the primary node reads the contents of the binlog and sends them to the secondary node when the binlog changes.
-
The binlog contents are received from the node I/O thread and written to the relay log file.
-
The SQL thread of the secondary node reads the contents of the relay log file and replay data updates to ensure consistency between the primary and secondary databases.
Note: The master and slave nodes use binglog file + position offset to locate the master and slave synchronization position. The slave node will save the offset it has received. If the slave node breaks down and restarts, it will automatically initiate synchronization from position.
MYSQL primary/secondary replication mode
1. Asynchronous mode (default)
In asynchronous mode, the master node commits the transaction immediately after executing the transaction submitted by the client and returns it to the client, regardless of whether the log dump thread successfully writes the transaction to binglog and sends it to the slave library. If the main thread of the transaction commits the transaction before the log dump thread writes the binlog, the system breaks down at this time, then binglog does not save the transaction just committed, resulting in data inconsistency between the master and slave.
Advantages: In asynchronous mode, the main thread performs synchronous operations without relationship, providing the best performance.
Disadvantages: Data inconsistency between master and slave may occur.
2. Semi-synchronous replication
In semi-synchronous mode, the master does not commit the transaction immediately after executing the transaction submitted by the client, but waits for the log dump thread to synchronize the transaction to the binlog and send it to the slave library, and at least one slave library is saved to its relay log successfully, then the master library commits the transaction and returns to the client.
Advantages: Compared with asynchronous mode, semi-synchronous mode ensures data synchronization reliability to a certain extent.
Disadvantages: Increased latency of the master library response client, which is at least one TCP/IP round trip time, i.e. the response time between binglog sending to the slave library and receiving the slave library.
3. Full synchronous replication
In fully synchronous mode, after the master library completes the transaction submitted by the client, the master library can return to the client only after the binlog is sent to the slave library and all the slave libraries complete the transaction successfully. The differences from semi-synchronous replication are as follows:
- In semi-synchronous mode, the master library waits for the binlog to be written to the relay log of the slave library and then returns. In fully synchronous mode, the slave library must wait for the transaction to be successfully executed.
- In semi-synchronization, the master library returns to the client after at least one slave library responds. In full synchronization, the master library must wait for all slave libraries to return.
Advantages: Compared with semi-synchronous replication, full synchronous replication has higher data consistency reliability
Disadvantages: When a transaction is executed, the master library must wait for all slave libraries to execute successfully before returning, which greatly improves the response time of the master library.
MYSQL primary/secondary replication setup
1. Environment preparation
Operating system: centos 7
MYSQL: MYSQL 8.0
Primary library IP address: 192.168.44.101
Secondary library IP address: 192.168.44.102
The method of installing mysql in Centos is not described here
2. Master library configuration
(1) Configure my.cnf
[mysqld]
server-id = 1
Copy the code(2) Restart the master library
systemctl restart mysqld
Log in to mysql and run show master status.
File: indicates the name of the binglog File. Each time the mysql service is restarted, a new binlog File will be generated. When the size of the File exceeds the limit (1 GB by default), a new binlog File will be generated.
Position: The offset of the binlog file, equal to the binglog file size (in bytes)
Binlog_Do_DB: database to be synchronized. If this parameter is not set, all databases, including the mysql default database, will be synchronized by default.
Binlog_Ignore_DB: database that does not need to be synchronized.
(3) For the sake of data security, add users dedicated to master and slave replication, and grant only the replication permission to create users
CREATE USER 'replication_user'@'%' IDENTIFIED BY '123456';
Copy the code(4) Grant the replication permission
GRANT replication slave on *.* to 'replication_user'@'%' WITH GRANT OPTION;
flush privileges;
Copy the code(5) Set the password authentication mode for the replicarion_user remote connection
ALTER USER 'replication_user'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Copy the code3. Configure the slave library
(1) Configure my.cnf
[mysqld]
server-id = 2
Copy the codeRestart the secondary library systemctl restart mysqld
(2) Specify the master library
Mysql > change master to master_host='192.168.44.101', master_user='replication_user', master_port=3306, master_password='123456', master_log_file='binlog.000004', master_log_pos=156;Copy the codeMaster_log_file: binlog file to be synchronized.
Master_log_pos: indicates the position from which synchronization starts.
In general, we need to ensure that the initial data of the master and slave libraries is consistent (manual synchronization can be done through dump), and then start automatic master/slave synchronization from this consistent state.
(3) Open the slave library
mysql> start slave;
Copy the code(4) Check the status of the secondary node
show slave status\G;
Copy the code
Slave_IO_Running: The IO thread from the library that receives binlogs sent by the master and writes them to the relay log relag log
Slave_SQL_Running: SQL thread from the library, used to read and execute binlogs from the relay log. Slave_IO_Running and Slave_SQL_Running: The status of both processes must be YES. If either process is NO, the replication will stop.
Master_Log_File: binlog file name of the master library to be synchronized.
Read_Master_Log_Pos: the synchronized location, the byte offset in the synchronized binlog file, which increases as the master/slave synchronization progresses.
Relay_Log_File: the trunk log file of the secondary library, which is buffered against the received binlog of the primary library. The SQL thread from the library continuously reads the binlog from the relay log and executes it.
Relay_Log_Pos: location offset read in the relay log.
Seconds_Behind_Master: the synchronization delay between the primary and secondary databases. A value of 0 indicates a normal condition. A positive value indicates that the synchronization delay has occurred.
4. Master/slave synchronization test
Create tables and insert data in the main library
CREATE DATABASE test CHARACTER SET utf8mb4;
USE test;
CREATE TABLE `person` (
` id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`age` int NOT NULL,
PRIMARY KEY (` id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the codeInsert two pieces of data:
INSERT INTO ` test `. ` person ` (` id `, ` name `, ` age `) VALUES (' 1 ', 'xiao Ming', '28'); INSERT INTO ` test `. ` person ` (` id `, ` name `, ` age `) VALUES (' 2 ', 'li hua', '30');Copy the codeQuery from the library
mysql> SELECT * FROM person; + + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | id name | age | + -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | xiaoming 28 | | | | | | 30 li hua 2 + + -- -- -- -- -- -- -- -- -- -- -- -- - + - + 2 rows in set (0.00 SEC)Copy the codeYou can see that the test database person table that we created and the data in the table were successfully synchronized to the slave library.