MySQL主从同步详解

MySQL主从同步概述

• 实现不同MySQL服务器之间数据实时同步的解决方案
• 通过主从同步可以实现数据备份的作用

MySQL主从同步原理

MySQL主从同步结构模式

• 一主一从
• 一主多从
• 链式复制
• 互为主从(扩展实验)

MySQL主从同步搭建

搭建步骤

- master(主服务器)
​ 1）开启binlog日志
​ 2）授权主从同步用户
​ 3）备份已有数据
- slave1(从服务器)
​ 1）设置server_id，可不开启binlog日志
​ 2）还原数据(实现主从结构前保证服务器基础数据统一)
​ 3）搭建主从关系

一主一从

实验环境

使用模板机克隆实验虚拟机（配置如下信息）

主机名	IP地址	角色
master	192.168.8.100	主服务器
slave1	192.168.8.101	从服务器1

配置master主机

[root@template ~]# hostnamectl set-hostname master					#配置主机名
[root@master ~]# nmcli connection modify ens160 ipv4.method manual ipv4.addresses 192.168.8.100/24 ipv4.gateway 192.168.8.254 ipv4.dns 192.168.8.254 connection.autoconnect yes 
[root@master ~]# nmcli connection up ens160

配置slave主机

[root@template ~]# hostnamectl set-hostname slave1
[root@slave1 ~]# nmcli connection modify ens160 ipv4.method manual ipv4.addresses 192.168.8.101/24 ipv4.gateway 192.168.8.254 ipv4.dns 192.168.8.254 connection.autoconnect yes 
[root@slave1 ~]# nmcli connection up ens160

2台主机均使用WindTerm远程链接

将master和slave1搭建成MySQL主从结构

master主机

[root@master ~]# dnf -y install mysql-server mysql
[root@master ~]# systemctl start mysqld
[root@master ~]# systemctl enable mysqld

master主机指定server_id，开启binlog日志

[root@master ~]# vim /etc/my.cnf.d/mysql-server.cnf
#...此处省略1万字，在第4行下方写入，不要写行号！...13 [mysqld]14 server_id=10015 log_bin=master#...此处省略1万字...

 [root@master ~]# systemctl restart mysqld				#重启动mysqld服务[root@master ~]# ls /var/lib/mysql/master.*			#验证是否成功
/var/lib/mysql/master.000001  /var/lib/mysql/master.index

[root@master ~]# mysqladmin -uroot password '123qqq...A'	#修改密码

用户授权（用户slave1，密码为slavepwd，这个用户用于从服务器连接主服务器同步数据）

• 使用 mysql_native_password 插件验证该用户的密码
• REPLICATION SLAVE 表示使用户拥有向主服务器复制的权限

mysql> CREATE USER 'slave1'@'%' IDENTIFIED with mysql_native_password BY 'slavepwd';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%';

查看日志信息

mysql> SHOW MASTER STATUS ;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| master.000002 |      984 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

备份master主机上的数据（虽然现在没有多余的数据，但工作中一定会有旧数据）

#备份已有数据
[root@master ~]# mysqldump -hlocalhost -uroot -p'123qqq...A' -A > ab1.sql#同步备份文件
[root@master ~]# scp ab1.sql 192.168.8.101:/root				

slave1主机

• 运行数据库服务
• 指定 server_id
• 指定主服务器信息
• 启动 slave 进程
• 查看状态
• 需要先将master上的数据手动还原至slave主机
• 确保master主机和slave主机UUID是不相同的，因为都是从模板克隆的裸机，所以这里可以不用考虑该问题

slave1主机安装mysql

[root@slave1 ~]# dnf -y install mysql-server mysql 

slave1主机修改server_id

[root@slave1 ~]# vim /etc/my.cnf.d/mysql-server.cnf
...此处省略1万字...13 [mysqld]14 server_id=10115 datadir=/var/lib/mysql...此处省略1万字...
[root@slave1 ~]# systemctl restart mysqld						#重启服务

slave1主机设置密码

[root@slave1 ~]# mysqladmin -uroot password '123qqq...A'		#修改密码

还原master主机备份过来的数据

[root@slave1 ~]# mysql  -uroot -p'123qqq...A' <  /root/ab1.sql 	#数据还原

slave1指定主服务器信息

####指定主服务器信息
#MASTER_HOST=       				指定主服务器的IP地址
#MASTER_USER=       				指定主服务器授权用户 
#MASTER_PASSWORD=   				指定授权用户的密码
#MASTER_LOG_FILE=   				指定主服务器binlog日志文件(到master上查看)
#MASTER_LOG_POS=   					指定主服务器binlog日志偏移量(去master上查看)
mysql> CHANGE MASTER TO -> MASTER_HOST="192.168.8.100",				#指定自己主服务器master的IP地址-> MASTER_USER="slave1", -> MASTER_PASSWORD="slavepwd",-> MASTER_LOG_FILE="master.000001",-> MASTER_LOG_POS=655;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> START SLAVE;					#启动SLAVE进程
mysql> SHOW SLAVE STATUS \G			#查看主从同步状态

验证主从同步

主服务器master写入数据验证

mysql> CREATE DATABASE sre;			#新建sre库

从服务器slave1写入数据验证

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sre                |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

主服务器master写入数据验证

mysql> CREATE TABLE sre.t1(id INT,name CHAR(10));
mysql> INSERT INTO sre.t1 VALUES(1,"Sam");
mysql> INSERT INTO sre.t1 VALUES(2,"Jack");

从服务器slave1写入数据验证

mysql> SELECT * FROM sre.t1;
+------+------+
| id   | name |
+------+------+
|    1 | Sam  |
|    2 | Jack |
+------+------+

一主多从

主机名	IP地址	角色
master	192.168.8.100	主服务器
slave1	192.168.8.101	从服务器1
slave2	192.168.8.102	从服务器2

使用模板机克隆slave2虚拟机

[root@template ~]# hostnamectl set-hostname slave1
[root@slave2 ~]# nmcli connection modify ens160 ipv4.method manual ipv4.addresses 192.168.8.102/24 ipv4.gateway 192.168.8.254 ipv4.dns 192.168.8.254 connection.autoconnect yes 
[root@slave2 ~]# nmcli connection up ens160

master主机

master主机需要重新完全备份（因为相对与上次备份已经产生了新数据）

[root@master ~]# mysqldump -uroot -p'123qqq...A' -A > ab2.sql
[root@master ~]# scp ab2.sql 192.168.8.102:/root

slave2主机

slave2主机操作

[root@slave2 ~]# dnf -y install mysql-server mysql

slave2主机修改server_id

[root@slave2 ~]# vim /etc/my.cnf...此处省略1万字...13 [mysqld]14 server_id=10215 datadir=/var/lib/mysql...此处省略1万字...
[root@slave2 ~]# systemctl restart mysqld		#重启服务
[root@slave2 ~]# systemctl enable mysqld		#将服务设置为开机自启

slave2主机设置密码

[root@slave2 ~]# mysqladmin -hlocalhost -uroot password '123qqq...A'

还原master主机备份过来的数据（使用ab2.sql）

[root@slave2 ~]# mysql  -uroot  -p'123qqq...A' <  /root/ab2.sql		#数据还原

slave2指定主服务器信息

• MASTER_LOG_FILE和MASTER_LOG_POS指定为master主机现使用的文件（SHOW MASTER STATUS查看）

• master主机查看（每个人都不一样，以自己的为准）

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| master.000001 |     1599 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

####slave2指定主服务器信息
#MASTER_HOST=       				指定主服务器的IP地址
#MASTER_USER=       				指定主服务器授权用户 
#MASTER_PASSWORD=   				指定授权用户的密码
#MASTER_LOG_FILE=   				指定主服务器binlog日志文件(到master上查看)
#MASTER_LOG_POS=   					指定主服务器binlog日志偏移量(去master上查看)
mysql> CHANGE MASTER TO -> MASTER_HOST="192.168.8.100",				#指定自己主服务器master的IP地址-> MASTER_USER="slave1", -> MASTER_PASSWORD="slavepwd",-> MASTER_LOG_FILE="master.000001",-> MASTER_LOG_POS=1599;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> START SLAVE;					#启动SLAVE进程
mysql> SHOW SLAVE STATUS \G			#查看主从同步状态

验证主从同步

master测试数据写入

mysql> CREATE TABLE sre.t2(id INT,name CHAR(10),male ENUM("male","female"));
mysql> INSERT INTO sre.t2 VALUES(1,"Sam","male");
mysql> INSERT INTO sre.t2 VALUES(2,"Janner","female");

slave1验证数据是否同步

mysql> USE sre;
mysql> SHOW TABLES;						#多出了sre.t2表
mysql> SELECT * FROM sre.t2;
+------+--------+--------+
| id   | name   | male   |
+------+--------+--------+
|    1 | Sam    | male   |
|    2 | Janner | female |
+------+--------+--------+
2 rows in set (0.00 sec)

slave2验证数据是否同步

mysql> USE sre;
mysql> SHOW TABLES;						#多出了sre.t2表
mysql> SELECT * FROM sre.t2;
+------+--------+--------+
| id   | name   | male   |
+------+--------+--------+
|    1 | Sam    | male   |
|    2 | Janner | female |
+------+--------+--------+
2 rows in set (0.00 sec)

MySQL主从同步复制模式

MySQL主从同步复制模式指的是主服务器(执行写操作的服务器)什么时候将SQL命令的执行结果返还给客户端

分为三种情况：

• 异步复制(默认)

  • 主节点在执行写操作后，将写操作的日志异步发送到从节点。主节点不会等待从节点的同步完毕，直接讲结果返回给客户端，因此主节点可以以较高的速度执行写操作，而从节点可能会有一定的延迟。由于异步复制的延迟，如果主节点故障或数据丢失，可能会造成从节点数据与主节点不一致。

• 全同步复制

  • 在全同步复制中，主节点在执行写操作后，等待所有从节点全部同步完数据之后，在将结果返回至客户端这样可以确保主节点和从节点的数据一致性，但会影响主节点的写操作速度，因为主节点需要等待从节点的确认。

• 半同步复制

  • 在半同步复制中，主节点在执行写操作后，等待至少一个从节点同步完数据再将结果返回至客户端。这样可以提高主节点的写操作速度，同时保证主节点和至少一个从节点的数据一致性。但如果从节点故障或延迟高，可能会造成主节点的等待时间增加。

读写分离技术

• 使用模板机克隆实验虚拟机（配置如下信息，配置IP地址信息，这里不再重复演示）
• master和slave1继续沿用上方主从关系

主机名	IP地址	角色
master	192.168.8.100	主服务器
slave2	192.168.8.102	从服务器
maxscale	192.168.8.99	读写分离服务器

MaxScale简介

• MaxScale 代理软件
  • 由 MySQL 的兄弟公司 MariaDB 开发
  • 下载地址 https://downloads.mariadb.com/files/MaxScale
• 将maxscale-24.02.1-1.rhel.8.x86_64.rpm 上传至maxscale主机的/root

部署MaxScale服务器

maxscale主机安装maxscale

[root@maxscale ~]# dnf -y install lrzsz
[root@maxscale ~]# dnf -y localinstall maxscale-24.02.1-1.rhel.8.x86_64.rpm 

maxscale主机修改修改读写分离服务配置文件

[root@maxscale ~]# cp /etc/maxscale.cnf  /etc/maxscale.cnf.bak #先备份，以防改错
[root@maxscale ~]# vim /etc/maxscale.cnf						#修改主配置文件
...12 [maxscale]13 threads=auto
...
#指定要代理的数据库服务器，[server2]部分需要自己手工定义21 [server1]22 type=server23 address=192.168.8.100					#指定主服务器地址24 port=330625 [server2]26 type=server27 address=192.168.8.102					#指定从服务器地址28 port=3306 
...
#指定监控用户maxscalemon，用于登录后端服务器，检查服务器的运行状态和主从状态47 [MariaDB-Monitor]48 type=monitor49 module=mariadbmon50 servers=server1,server2					#上边的定义的主机51 user=maxscalemon						#指定监控用户52 password=123qqq...A						#指定监控用户的密码53 monitor_interval=2s...86 #[Read-Only-Service]					#只读服务不需要，这段全部注释87 #type=service88 #router=readconnroute89 #servers=server190 #user=service_user91 #password=service_pw92 #router_options=slave
...
#定义读写分离服务器配置99 [Read-Write-Service]
100 type=service
101 router=readwritesplit
102 servers=server1,server2				#指定读写分离服务器	
103 user=maxscalerouter					#指定路由用户
104 password=123qqq...A					#指定路由用户密码			...
#只读服务配置信息加上注释
118 #[Read-Only-Listener]
119 #type=listener
120 #service=Read-Only-Service
121 #protocol=mariadbprotocol
122 #port=4008...
#读写分离配置信息，默认端口号为4006
124 [Read-Write-Listener]
125 type=listener
126 service=Read-Write-Service
127 protocol=mariadbprotocol
128 port=4006

授权用户

• 根据/etc/maxscale.cnf配置要求，需要在master主机和slave主机授权用户
  • maxscalemon用户，密码为123qqq…A
  • maxscalerouter用户，密码为123qqq…A
  • 创建监控用户maxscalemon，用于登录后端服务器，检查服务器的状态
  • 创建路由用户maxscalerouter,检测客户端的用户名和密码在后端数据库中是否存在
  • REPLICATION SLAVE：该权限能够同步数据，查看从服务器上slave的状态；
  • REPLICATION CLIENT:该权限可以获取数据库服务的状态(数据库服务是否允许，主从是否正常)

master主机操作

授权maxscalemon用户

[root@master ~]# mysql -uroot -p'123qqq...A'
mysql> CREATE USER 'maxscalemon'@'%' IDENTIFIED WITH mysql_native_password BY '123qqq...A';
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'maxscalemon'@'%';

授权maxscalerouter用户

• 只是检查用户是否存在，所以此用户只需对mysql库下表有查询权限即可

mysql> CREATE USER 'maxscalerouter'@'%' IDENTIFIED WITH mysql_native_password BY '123qqq...A';
mysql> GRANT SELECT ON mysql.* TO 'maxscalerouter'@'%';

slave主机操作

• 由于已经设置了主从同步，所slave主机也可以不用操作，因为已经自动同步，如果未同步则手工创建

授权maxscalemon用户

[root@slave2 ~]# mysql -uroot -p'123qqq...A'
mysql> CREATE USER 'maxscalemon'@'%' IDENTIFIED WITH mysql_native_password BY '123qqq...A';
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'maxscalemon'@'%';

授权maxscalerouter用户

• 只是检查用户是否存在，所以此用户只需对mysql库下表有查询权限即可

mysql> CREATE USER 'maxscalerouter'@'%' IDENTIFIED WITH mysql_native_password BY '123qqq...A';
mysql> GRANT SELECT ON mysql.* TO 'maxscalerouter'@'%';

启动服务

maxscale主机操作

[root@maxscale ~]# systemctl restart maxscale
[root@maxscale ~]# systemctl enable maxscale

测试读写分离服务

master主机授权测试用户

[root@master ~]# mysql -uroot -p'123qqq...A'
mysql> CREATE USER 'sam'@'%' IDENTIFIED WITH mysql_native_password BY '123qqq...A';
mysql> GRANT ALL ON *.* TO 'sam'@'%';

maxscale充当客户端访问读写分离服务器

[root@maxscale ~]# dnf -y install mysql				#安装mysql连接命令
[root@maxscale ~]# mysql -h192.168.8.99 -P4006 -usam -p"123qqq...A"
mysql> CREATE DATABASE study;						#创建study库
mysql> CREATE TABLE study.t1(id INT,name VARCHAR(20));  	#创建表
mysql> INSERT INTO study.t1 VALUES(1,'tom');				#插入数据

master主机验证查看数据

[root@master ~]# mysql -uroot -p'123qqq...A'
mysql> SELECT * FROM study.t1;
+------+------+
| id   | name |
+------+------+
|    1 | tom  |
+------+------+

slave主机验证查看数据

[root@slave2 ~]# mysql -uroot -p''123qqq...A'
mysql> SELECT * FROM study.t1;
+------+------+
| id   | name |
+------+------+
|    1 | tom  |
+------+------+

slave主机插入数据，主服务器不会同步；

使用maxscale充当客户端访问验证，能看到slave插入的数据，代表查询操作是slave主机提供服务；

slave主机操作

mysql> INSERT INTO study.t1 VALUES(2,"jerry");			#插入数据

maxscale充当客户端主机访问验证

[root@maxscale ~]# mysql -h192.168.8.99 -P4006 -usam -p"123qqq...A"
mysql> SELECT * FROM study.t1;							#能查询到代表读写分离成功
+------+-------+
| id   | name  |
+------+-------+
|    1 | tom   |
|    2 | jerry |
+------+-------+
2 rows in set (0.00 sec)