MySQL主从同步详解

🕗 发布于 2024-12-10 10:27 mysql android adb

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 开发
- 下载地址 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=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
 ...
#读写分离配置信息，默认端口号为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)

原文地址：https://blog.csdn.net/weixin_68522070/article/details/144338283

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：克服大规模语言模型限制，构建新的应用方法——LangChain
下一篇：代码随想录第40天

.NET(C#) 如何配置用户首选项及保存用户设置
.NET(C#) 如何配置用户首选项及保存用户设置
阅读更多2024-12-14
【最新】北大数字普惠金融指数数据集-省市县（2011-2023年）
郭峰,王靖一,王芳,孔涛,张勋,程志云.测度中国数字普惠金融发展:指数编制与空间特征[J].经济学(季刊),2020,19(04):1401-1418.时间跨度：省级和城市级指数时间跨度为2011-2
阅读更多2024-12-14
GESP202412 四级【Recamán】题解（AC）
a11ak−1−kkakak−1−kak−1k小杨想知道 Recamán 数列的前n项从小到大排序后的结果。手动计算非常困难，小杨希望你能帮他解决这个问题。
阅读更多2024-12-14
IDEA遇到EasyConnect中的网络资源无法访问的问题
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。原文链接：https://blog.csdn.net/wanshanyu_/article/de
阅读更多2024-12-14
双目摄像头标定方法
此时已经完成标定，左下角为反投影误差，右边为外参可视化。将双目左右目拍的图像上传（左右目最好不少于20张）此时回到主页面，即可看到成功导出。把这些误差大的删除即可。
阅读更多2024-12-14
Servlet、omcat服务器架构与工作原理
Servlet是运行在服务器端的Java程序，它的主要职责之一是接收并处理来自客户端（如浏览器）的HTTP请求。当客户端发送一个请求到服务器时，Servlet可以解析请求中的信息，例如请求的URL路径
阅读更多2024-12-14
Vue生命周期钩子函数：深入解析与实践
作为高级Vue前端开发人员，对Vue组件的生命周期钩子函数有着深刻的理解是至关重要的。生命周期钩子函数是指在Vue组件的创建、更新、销毁等过程中，Vue自动调用的一系列方法。通过这些钩子函数，我们可以
阅读更多2024-12-14
安卓开发--使用android studio发布APP
app发布
阅读更多2024-12-14
数据结构与算法学习笔记----拓扑排序
@ author: 明月清了个风。
阅读更多2024-12-14
python 将数据保存到现有的Excel文件的新工作表
out_file = ‘query.xlsx’df1 = pd.DataFrame(out_data)若直接写入：df1.to_excel(out_file, index=False, sheet_n
阅读更多2024-12-14

MySQL主从同步详解

文章目录

MySQL主从同步概述

MySQL主从同步原理

MySQL主从同步结构模式

MySQL主从同步搭建

搭建步骤

一主一从

实验环境

master主机

slave1主机

验证主从同步

一主多从

master主机

slave2主机

验证主从同步

MySQL主从同步复制模式

读写分离技术

MaxScale简介

部署MaxScale服务器

授权用户

master主机操作

slave主机操作

启动服务

测试读写分离服务

相关文章