Mysql configuration master-slave replication-GTID mode

Table of Contents

master-slave replication

Definition of master-slave replication

The principle of master-slave replication

Advantages of master-slave replication

Master-slave replication form

Master-slave replication mode

Type of master-slave replication

GTID mode

The concept of GTID

Advantages of GTID

The principle of GTID

GTID configuration

Mysql main server

?edit

Mysql slave server

?edit

Master-slave replication

Definition of master-slave replication

It refers to copying data from one Mysql server (master node) to one or more Mysql servers (slave nodes). All database instances, specific database instances or specific tables in the master node server will be copied to the slave nodes. in the server.

Principle of master-slave replication

Data synchronization is achieved through log-based replication. When data changes occur on the master server, these changes are written to the binary log (Binary Log). The slave server connects to the master server, requests binary logs from the master server, and applies these logs to its own database.

Advantages of master-slave replication

Improve read performance: By setting up a slave server (Slave), read operations can be distributed to the master server (Master) and slave servers, thereby improving the overall read performance. The master server is responsible for processing write operations, and the slave server is responsible for processing read operations, thereby reducing the load on the master server and improving the throughput of the entire system.
Data redundancy and backup: Through master-slave replication, the data on the slave server is a redundant copy of the master server. When the master server fails, the slave server can still provide services, and services can be quickly restored by promoting a slave server to the new master server. In addition, the slave server can also be used for regular backup operations to ensure data security and recoverability.
High availability: Through master-slave replication, database failover and high availability can be achieved. When the master server fails, a slave server can be promoted to the new master server manually or automatically to continue to provide database services, thereby achieving rapid failure recovery.
Data analysis and report generation: Since the slave server can handle read operations, it can be used for database data analysis and report generation. This avoids additional load on the main server while providing real-time data analysis and reporting services.
Data distribution and cross-regional deployment: Master-slave replication can be used to distribute data to slave servers in different geographical locations, thereby achieving cross-regional data access and deployment. This is very useful for global applications and multi-region disaster recovery.
Architecture extensions. The business volume is getting larger and larger, and the frequency of I/O access is too high, which cannot be satisfied by a single machine. At this time, multi-database storage is used to reduce the frequency of disk I/O access and improve the I/O performance of a single machine.

Master-slave replication form

One master and one slave;
One master, many slaves;
Many masters and one slave;
Dual-master replication;
Cascade replication.

Master-slave replication mode

asynchronous mode (default);
Semi-synchronous mode;
Full sync mode.

Type of master-slave replication

Binary log based replication (available in three forms)
1. Replication based on SQL statements;
2. row-based replication;
3. Mixed mode copy.
Replication based on GTID (Mysql version >= 5.7 and above recommended)

GTID mode

Concept of GTID

GTID is the global transaction ID: global transaction identifier;
GTID is a one-to-one correspondence for a transaction and is a globally unique ID;
A GTID is only executed once on a server to avoid data confusion or master-slave inconsistency caused by repeated execution;
GTID is used to replace the traditional replication method, and MASTER_LOG_FILE + MASTER_LOG_POS is no longer used to start replication. Instead, use MASTER_AUTO_POSTION=1 to start copying;
It is supported starting from MySQL-5.6.5 and will be improved after MySQL-5.6.10;
On the traditional slave side, binlog does not need to be turned on, but in GTID, the binlog on the slave side must be turned on in order to record the executed GTID (mandatory).

GTID’s advantages

Easier implementation of failover, no need to look for log_file and log_pos as before;
Easier to build master-slave replication;
More secure than traditional copying;
GTID is continuous without holes, ensuring data consistency and zero loss.

Principle of GTID

When a transaction is executed and submitted on the main library side, a GTID is generated and recorded in the binlog;
After the binlog is transferred to the slave and stored in the slave’s relaylog, the value of the GTID is read and the gtid_next variable is set, which tells the slave the next GTID value to be executed;
The sql thread obtains the GTID from the relay log, and then compares the binlog on the slave side to see whether the GTID exists;
If there is a record, it means that the transaction with the GTID has been executed, and the slave will ignore it;
If there is no record, the slave will execute the GTID transaction and record the GTID in its own binlog. Before reading and executing the transaction, it will first check that other sessions hold the GTID to ensure that it is not executed repeatedly;
During the parsing process, it will be judged whether there is a primary key. If not, the secondary index will be used. If not, all scans will be used.

GTID configuration

Mysql main server

1. Add the following parameters in the my.ini configuration file of Mysql:

gtid_mode=ON
enforce_gtid_consistency=true
server_id=1
log-bin=mysql-bin
binlog_format=row
log-slave-updates=1

2. Restart Mysql and create a replication user

Use the administrator to open the cmd command window and execute the following command steps:

#Service Unavailable;

net stop mysql

#Start service;

net start mysql

#Connect to the database, replace Password with your own database password;

mysql -u root -pPassword

#Create a user for replication, set userName and Password by yourself;

CREATE USER ‘userName’@’%’ IDENTIFIED BY ‘Password’;

#Grant copy permission, userName is the user name created in the previous step;

GRANT REPLICATION SLAVE ON *.* TO ‘userName’@’%’;

#Get the status, remember the two parameters File and Position, you will need them later;

SHOW MASTER STATUS;

Mysql slave server

1. Add the following parameters in the my.ini configuration file of Mysql:

gtid_mode=ON
enforce_gtid_consistency=true
server_id=1
log-bin=mysql-bin
binlog_format=row
log-slave-updates=1

2. Restart Mysql and create a replication user

Use the administrator to open the cmd command window and execute the following command steps:

#Service Unavailable;

net stop mysql

#Start service;

net start mysql

#Connect to the database, replace Password with your own database password;

mysql -u root -pPassword

#Configure replication, serverIP is the IP address of the main server, userName and password are the users just created on the main server, file and position are the parameters returned by just checking the status of the main server;

CHANGE MASTER TO MASTER_HOST=’serverIP’, MASTER_USER=’userName’, MASTER_PASSWORD=’password’, MASTER_LOG_FILE=’file’, MASTER_LOG_POS=position;

#Start replication;

START SLAVE;

#Check the replication status. The values of Slave_IO_Running and Slave_SQL_Running in the output are Yes, indicating that replication is running normally;

SHOW SLAVE STATUS\G;

It’s over here. You can add, modify, and delete operations in the Mysql master server to test whether the data in the slave server is consistent. Note: The slave server only allows queries, and cannot perform operations such as adding, modifying, deleting, etc. to change data, otherwise the replication status will become invalid.