MySQL master-slave synchronization-binlog

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/

Click Download according to the system version

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

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

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

Configure installation source

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

Configure according to the required version number

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

Install mysql

yum -y install mysql-community-server

Master node operation

Modify my.cnf configuration

vim /etc/my.cnf

[mysqld]
log-bin=mysql-bin
server-id=1

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

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

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

View binlog start log

show master status \G

Slave slave node operation

Configure local domain name resolution

vim /etc/hosts

192.168.117.143 mysql-master

Ping to test whether there is connectivity

ping mysql-master

Modify my.cnf configuration

 vim /etc/my.cnf

server-id=2

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

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 ;

Test whether the user can log in to the master node

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

Configuring master-slave replication

mysql -p'Jianren@123'

edit

\e

CHANGE MASTER TO
MASTER_HOST='mysql-master',
MASTER_USER='slave',
MASTER_PASSWORD='Jianren@123',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=157;

Parameter explanation
MASTER_HOST='mysql-master' The host IP of the master can also be a local domain name
MASTER_USER='slave' Synchronization user created by master
MASTER_USER='slave' master creates synchronized user password
MASTER_LOG_FILE='mysql-bin.000003' The binlog file name viewed by the master
MASTER_LOG_POS=157; masetr checks the log location of binlog

save

Start master-slave replication

start slave ;

View status

 show slave stauts \G

Both threads are yes, indicating success.

Test status

Enter in the master node mysq

create database test1;

Enter in slave node mysq

show databases;

You can see that it has been synchronized