MySQL master-slave synchronization-Gtid

【A Hundred Refinements to Become a Demon】MySQL master-slave synchronization-Gtid

Server preparation

IP Node Configuration System version
191.168.117.143 master 2c2g40g centos 7.9
192.168.117.142 slave 2c2g40g centos 7.9

Environment preparation

The following operations need to be performed on both machines

Turn off firewall

systemctl stop firewalld & amp; & amp; systemctl disable firewalld

setenforce 0 & amp; & amp; sed -i 's/SELINUX=.*/SELINUX=disabled/g' /etc/selinux/config

Install commonly used software

yum install -y wget vim net-tools bash-completion

Update software

yum update
Download mysql installation source
https://dev.mysql.com/downloads/repo/yum/

image-20230919092155207

Click Download according to the system version

Click to download the rpm package or right-click to copy the link

image-20230919092456556

wget https://dev.mysql.com/get/mysql80-community-release-el7-10.noarch.rpm

rpm -ivh mysql80-community-release-el7-10.noarch.rpm

image-20230919093121744

Configure installation source
cd /etc/yum.repos.d/
vim mysql-community.repo

image-20230919093455820

Configure according to the required version number

image-20230919093759357

I installed 8.0 directly here, so there is no need to modify it.

Install mysql
yum -y install mysql-community-server

image-20230919094404877

Master node operation

Modify my.cnf configuration
vim /etc/my.cnf

[mysqld]
log_bin=mysql-bin
server_id=1
binlog_format=ROW
gtid_mode=on
enforce_gtid_consistency=on

Parameter explanation
log_bin=mysql-bin This parameter enables binary logging

server_id=1 per MySQL This parameter specifies the identifier of the current MySQL server. In MySQL replication and clustering environments, each server should have a unique identifier for data synchronization and identification of different servers.

binlog_format=ROW This parameter defines the format of the binary log, "ROW". This means that the binary log will record row-level details for each change, including the actual contents of the data row that was modified. This format is commonly used for replication and advanced data recovery operations.

gtid_mode=on This parameter enables GTID mode

enforce_gtid_consistency=on This parameter is used to enforce GTID consistency. When enabled, MySQL will ensure that transactions submitted on the master server are executed on the slave server to ensure consistent data synchronization.

image-20230919131905461

Start the database and configure auto-start at boot
systemctl start mysqld & amp; & amp; systemctl enable mysqld
Get MySQL initialization password
grep password /var/log/mysqld.log

image-20230919094601606

Login to database

mysql -p'password' ##Note that special characters must be enclosed in single quotes

image-20230919094745766

Modify user permissions and slave copy users
alter user 'root'@'localhost' identified with mysql_native_password by 'Jianren@123';
grant all privileges on *.* to 'root'@'localhost';
create user 'slave'@'%' identified with mysql_native_password by 'Jianren@123';
grant replication slave on *.* to 'slave'@'%';
flush privileges;

Parameter explanation
with mysql_native_password Modify the user's authentication plug-in to prevent slave node login synchronization users from being unable to connect

image-20230919132513220

Slave slave node operation

Configure local domain name resolution
vim /etc/hosts

192.168.117.143 mysql-master

image-20230919101555481

Ping to test whether there is connectivity

ping mysql-master

image-20230919101713673

Modify my.cnf configuration

 vim /etc/my.cnf

server-id=2
gtid_mode=on
enforce_gtid_consistency=on

image-20230919132748487

Start mysql and configure auto-start at boot
systemctl start mysqld & amp; & amp; systemctl enable mysqld
Modify mysql initialization password and permissions
grep password /var/log/mysqld.log

image-20230919101925512

Login to database

mysql -p'password' ##Note that special characters must be enclosed in single quotes

Configure passwords and permissions

alter user 'root'@'localhost' identified by 'Jianren@123';
grant all privileges on *.* to 'root'@'localhost' ;
flush privileges;
exit ;

image-20230919133023766

Test whether the user can log in to the master node

mysql -uslave -pJianren@123 -h mysql-master
show databases;
exit;

image-20230919102551422

Configuring master-slave replication

Log in as root user

mysql -p'Jianren@123'

edit

edit

CHANGE MASTER TO
MASTER_HOST='mysql-master',
MASTER_USER='slave',
MASTER_PASSWORD='Jianren@123',
MASTER_AUTO_POSITION=1;

Parameter explanation
CHANGE MASTER TO
MASTER_HOST='mysql-master', master node IP or domain name
MASTER_USER='slave', the synchronization user created by the master node
MASTER_PASSWORD='Jianren@123', synchronize user passwords
MASTER_AUTO_POSITION=1; Automatically locate the current binary log position of the master server.

save

;

image-20230919133250352

image-20230919133223052

Start master-slave replication

start slave ;

View status

 show slave stauts \G

Both threads are yes, indicating success.

image-20230919133650235

Test status

Enter in the master node mysq

create database test1;
use test1;
create table usertb (username varchar(10)not null, password varchar(20)not null);
insert into usertb values ('user1','123');
select * from usertb;

image-20230919134005938

Enter in slave node mysq

show databases;
use test1;
select * from usertb;

You can see that it has been synchronized

image-20230919134123716

At this point, the gtid method has also been deployed.

Delete test library master node operation

drop database test1;