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=100 15 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=101 15 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=102 15 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 开发
将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=server 23 address=192.168.8.100 #指定主服务器地址 24 port=3306 25 [server2] 26 type=server 27 address=192.168.8.102 #指定从服务器地址 28 port=3306 ...#指定监控用户maxscalemon，用于登录后端服务器，检查服务器的运行状态和主从状态 47 [MariaDB-Monitor] 48 type=monitor 49 module=mariadbmon 50 servers=server1,server2 #上边的定义的主机 51 user=maxscalemon #指定监控用户 52 password=123qqq...A #指定监控用户的密码 53 monitor_interval=2s ... 86 #[Read-Only-Service] #只读服务不需要，这段全部注释 87 #type=service 88 #router=readconnroute 89 #servers=server1 90 #user=service_user 91 #password=service_pw 92 #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 ...#读写分离配置信息，默认端口号为4006124 [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)
总结
掌握MySQL主从同步部署流程
一主一从
一主多从
掌握MySQL主从复制模式特点
掌握MaxScale读写分离技术以应用场景
熟练搭建MaxScale读写分离集群
ChatGPT扩展
如何使用MyCAT完成读写分离？
