当前位置： 首页 > article >正文

Mysql8主从复制（兼容低高版本）

article 2025/1/6 16:29:48

Mysql主从复制

理论知识

  1. 主从复制必要前提
主从复制必要的条件：
主库开启binlog日志（设置log-bin参数）
主从server-id不同
从库服务器能连同主库
  1. 实现原理
原理：实现整个主从复制，需要由slave服务器上的IO进程和Sql进程共同完成；要实现主从复制，首先必须打开Master端的binary log（bin-log）功能，因为整个MySQL 复制过程实际上就是Slave从Master端获取相应的二进制日志，然后再在自己本地（slave端）按照执行日志中所记录的顺序，全部操作一遍。

---在主库上把有数据更改的（DDL DML DCL）sql语句都记录到二进制日志（Binary Log）中。
---备库的I/O线程将主库上的日志复制到自己的中继日志（Relay Log）中。
---备库的SQL线程读取中继日志中的事件，将其重放到备库数据库之上。
  1. binlog的三种类型
binlog有三种模式：statement模式、mixed模式和row模式。
statement模式记录的是SQL语句
row模式记录的是每一行数据的变化
mixed模式是自动组合 STATEMENT 和 ROW 模式，按照最优方式来记录日志。
Binlog日志的开启和关闭可以通过设置MySQL的配置文件实现。

配置步骤

两台机器时间一致
对主库已有的数据库不会进行自动同步。
主从同步之前，主库上已有数据库备份，需要在从库上手动导入同步

操作系统IPmysql版本主从类型
Anolis OS8.9192.168.200.81mysql8.4主
Anolis OS8.9192.168.200.83mysql8.4从

需要注意：mysql 版本不同，有些命令是不同的

  1. show master status; 不能用了

查看主节点binlog的命令

# mysql 8.4版本前使用这条命令查看
show master status; 

# MySQL 8.4版本后使用这条命令查看
SHOW BINARY LOG STATUS;
  1. change master to不能用了

从节点配置主节点信息的命令

# MSQL 8.23前
CHANGE MASTER TO MASTER_HOST='192.168.200.81', MASTER_USER='nomax', MASTER_PASSWORD='nomax', MASTER_LOG_FILE='binlog.000003', MASTER_LOG_POS=158;
# MSQL 8.23后
CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.200.81', SOURCE_USER='nomax', SOURCE_PASSWORD='nomax', SOURCE_LOG_FILE='binlog.000003', SOURCE_LOG_POS=158;
  1. start slave不能用了
# 开启同步
start replica ; #8.0.22之后 
start slave ; #8.0.22之前

#停止同步
stop replica ; #8.0.22之后 
stop slave ; #8.0.22之前

#清空之前的主从复制配置信息
reset replica ; #8.0.22之后 
reset slave ; #8.0.22之前
  1. show slave status不能用了

查看从节点状态的

# 查看状态,\G表示行转列，便于查看
show replica status\G ; #8.0.22之后 
show slave status\G ; #8.0.22之前

一、主节点配置(在192.168.200.81操作)

主从节点配置的差异：由于后续需要演示主从切换，所以无论是主从节点，都需要提前开启binlog和relaylog。故而这里主从配置基本一致，具体配置选项差异只有：server_id、read-only选项

编辑/etc/my.cnf

#==================== 主从同步配置=========================
#节点id编号，各个mysql的server_id需要唯一
server_id=1
#指定binlog和binglog index的文件名
log_bin=/data/log/mysql/mysql-bin
log_bin_index=/data/log/mysql/mysql-bin.index
#[可选]0（默认）表示读写（主机），1表示只读（从机）
read-only=0
#[可选]启用中继日志
relay-log=/data/log/mysql/mysql-relay
#[可选] 单个binlog最大的文件大小，默认是1G
max_binlog_size=500M
#[可选]设置binlog格式.STATEMENT,row,mixed
binlog_format=row
#[可选]设置日志文件保留的时长，单位是秒(默认不删除文件)
#binlog_expire_logs_seconds=6000
#[可选]设置不要复制的数据库
#binlog-ignore-db=test
#[可选]设置需要复制的数据库,默认全部记录。比如：binlog-do-db=atguigu_master_slave
#binlog-do-db=需要复制的主数据库名字

relay-log中继日志可以看主从复制之间的细节

log_bin_index，用来索引，主从复制时提供日志文件顺序

binlog_format=row

row哪里更改都会记录下来，不会出现无法正确复制的问题。很详细，缺点是日志量大

read-only=0 主的设置0读写，从的设置1只读

  • 修改配置后重启数据库:
systemctl restart mysql
or
service mysql restart

二、从节点配置(在192.168.200.83操作)

##节点id编号，各个mysql的server_id需要唯一
server_id=2
#指定binlog和binglog index的文件名
log_bin=/data/mysql/binlog
log_bin_index=/data/mysql/binlog.index
#[可选]启用中继日志
relay-log=/data/mysql/mysql-relay
#[可选] 单个binlog最大的文件大小，默认是1G
max_binlog_size=500M
#[可选]设置binlog格式.STATEMENT,row,mixed
binlog_format=row
#[可选]0（默认）表示读写（主机），1表示只读（从机）
read-only=1
# #[可选]设置日志文件保留的时长，单位是秒(默认不删除文件)
# #binlog_expire_logs_seconds=6000
# #[可选]设置不要复制的数据库
# #binlog-ignore-db=test
# #[可选]设置需要复制的数据库,默认全部记录。比如：binlog-do-db=atguigu_master_slave
# #binlog-do-db=需要复制的主数据库名字

主从节点配置的差异：开启binlog和relaylog,具体配置选项差异只有：server_id、read-only选项

在从节点登录主节点服务器获取服务器公钥

防止后续出现从节点连接主节点服务器报无公钥的错误

mysql -u nomax -pnomax -h 192.168.200.81 -P3306 --get-server-public-key
  • 修改配置后重启数据库:
systemctl restart mysql
or
service mysql restart

三、创建用于主从同步的用户(主从都要创建)

root 用户也可以但是不安全

  1. MySQL8新版本
#创建nomax用户
CREATE USER'nomax'@'%' IDENTIFIED BY'nomax';
#给nomax用户授予数据同步的权限
GRANT replication slave on *.* to 'nomax'@'%';
#刷新权限
flush privileges;
  1. mysql5 老版本
GRANT REPLICATION SLAVE ON *.*  TO  'nomax'@'%'  identified by 'nomax';
flush privileges;

参数解析

新版本需要先创建用户

老版本权限用户可一起生成

GRANT replication slave：授予改用户从库中进行复制的权限

ON *.*：第一个 * 代表数据库名，第二个 * 代表表名，*.* 意味着所有数据库中的所有表。

TO 'nomax' ：用户名字

'@'%' ：表示用户可以在任何主机连接数据库

identified by 'nomax' ：为用户设置nomax的密码

  1. 查询确认创建的用户
SELECT User FROM mysql.user;

四、开启主从复制

  1. 查看主节点binlog执行位置（主节点192.168.200.81来执行以下命令）
# mysql8.4的
SHOW BINARY LOG STATUS; 
# mysql8及其以前的
SHOW BINARY STATUS;

mysql> SHOW BINARY LOG STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000013 |      158 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
  1. 从节点开启主节点同步操作（从节点192.168.200.83来执行以下命令）：
#从节点配置连接主的信息
CHANGE REPLICATION SOURCE TO
#主节点的IP
SOURCE_HOST='192.168.200.81',    
#主节点的端口号
SOURCE_PORT=3306,      
#主节点的用户
SOURCE_USER='nomax',      
#主节点的密码
SOURCE_PASSWORD='nomax',   
#通过 SHOW BINARY LOG STATUS;查看
SOURCE_LOG_FILE='mysql-bin.000013',  
SOURCE_LOG_POS=158;

#开启从节点备份
start replica;

#查看从节点的状态
show replica status \G;

方便复制版本
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.200.81',
SOURCE_PORT=3306,
SOURCE_USER='nomax',
SOURCE_PASSWORD='nomax', 
SOURCE_LOG_FILE='mysql-bin.000013',  
SOURCE_LOG_POS=158;

start replica;

show replica status \G;

CHANGE REPLICATION SOURCE TO SOURCE_HOST=‘192.168.200.81’, SOURCE_LOG_FILE=‘mysql-bin.000013’, SOURCE_LOG_POS=158, SOURCE_PORT=3306, SOURCE_USER=‘nomax’, SOURCE_PASSWORD=‘nomax’;

确保下面四项参数正确

Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Last_IO_Error:
Last_SQL_Error:

在这里插入图片描述

报错

Last_IO_Error: Error connecting to source 'nomax@192.168.200.81:3306'. This was attempt 10/10, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

在这里插入图片描述

  1. 原因：根据上一张密码传输方式的第3条，该插件发现连接未加密，因此需要使用RSA加密来传输密码。但是，服务器不会将公用密钥发送给客户端，并且客户端未提供公用密钥，因此它无法加密密码并且连接失败

解决方法：

  1. 使用复制用户请求服务器公钥
mysql -u nomax -pnomax -h 192.168.200.81 -P3306 --get-server-public-key

在这种情况下，服务器将RSA公钥发送给客户端，后者使用它来加密密码并将结果返回给服务器。插件使用服务器端的RSA私钥解密密码，并根据密码是否正确来接受或拒绝连接。

重新在从库配置change masrer to并且start slave，复制可以正常启动：

#停止主从复制
#清空之前的主从复制配置信息
stop replica;
reset replica;
#从新配置主从复制
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.200.81',
SOURCE_PORT=3306,
SOURCE_USER='nomax',
SOURCE_PASSWORD='nomax', 
SOURCE_LOG_FILE='mysql-bin.000013',  
SOURCE_LOG_POS=158;

start replica;
show replica status \G;

五、主从同步验证

  • 也可以用navicat去试试，俩IP的数据库都连上进行创建库表啥的操作
  1. 主节点上进行建库、建表、插入表数据操作
#创建数据库
mysql> create database test_db;
Query OK, 1 row affected (0.03 sec)
#查看数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
5 rows in set (0.06 sec)
#切换数据库
mysql> use test_db;
Database changed
#创建表
mysql> CREATE TABLE `t_test` (
    ->   `id` int(11) NOT NULL,
    ->   `age` int(11) DEFAULT NULL,
    ->   `score` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected, 3 warnings (0.10 sec)

#插入表数据
mysql> INSERT INTO `t_test` VALUES (1, 2, 1);
Query OK, 1 row affected (0.01 sec)
#插入表数据
mysql> INSERT INTO `t_test` VALUES (222, 22, 19);
Query OK, 1 row affected (0.01 sec)
#查看表
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| t_test            |
+-------------------+
1 row in set (0.00 sec)
#查看表数据
mysql> select * from t_test;
+-----+------+-------+
| id  | age  | score |
+-----+------+-------+
|   1 |    2 |     1 |
| 222 |   22 |    19 |
+-----+------+-------+
2 rows in set (0.00 sec)

检查从节点192.168.200.83是否也都同步成功：
如下所示，从节点也都自动完成了主节点上所进行的相关操作~

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
5 rows in set (0.00 sec)
#切换数据库
mysql> use test_db;
Database changed
#查看表
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| t_test            |
+-------------------+
1 row in set (0.00 sec)
#查询表数据
mysql> select * from t_test;
+-----+------+-------+
| id  | age  | score |
+-----+------+-------+
|   1 |    2 |     1 |
| 222 |   22 |    19 |
+-----+------+-------+
2 rows in set (0.00 sec)

至此，主从同步部署完成

六、故障切换

mysql主从，主节点宕机，如何进行切换

  1. 在从节点执行（版本以8.4为例，之前的命令不同参考本文理论知识模块命令注意）
mysql> stop replica;
mysql> reset replica;
  1. 查看是否是只读模式
mysql> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

只读模式需要修改my.cnf文件，注释read-only=1并重启mysql服务。
或者不重启使用命令临时关闭只读，但下次重启后失效：set global read_only=off;

  1. 查看
mysql> show replica status\G
  1. 在程序中将原来主库IP地址改为现在的从库IP地址，测试应用连接是否正常

stop replica;
mysql> reset replica;


2. 查看是否是只读模式

mysql> show variables like ‘read_only’;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| read_only | ON |
±--------------±------+
1 row in set (0.00 sec)


**只读模式需要修改my.cnf文件，注释read-only=1并重启mysql服务。**
**或者不重启使用命令临时关闭只读，但下次重启后失效：`set global read_only=off;`**

3. 查看

mysql> show replica status\G


4. **在程序中将原来主库IP地址改为现在的从库IP地址，测试应用连接是否正常**

http://www.kler.cn/a/465975.html

相关文章：

  • ChatGPT 主流模型GPT-4/GPT-4o mini的参数规模是多大？
  • 一文讲明白朴素贝叶斯算法及其计算公式（入门普及）
  • 智能工厂的设计软件 应用场景的一个例子：为AI聊天工具添加一个知识系统 之9 重新开始 之2 “三端架构”各自的“中间区”：三支决策的肯定/待定/否定
  • 《代码随想录》Day25打卡！
  • PHP 使用集合 处理复杂数据 提升开发效率
  • ESP32自动下载电路分享
  • 【AI部署】腾讯云每月1w小时免费GPU获取
  • DBSCAN 聚类 和 gmm 聚类 测试
  • Gitlab-runner 修改默认的builds_dir并使用custom_build_dir配置
  • Milvus×合邦电力:向量数据库如何提升15%电价预测精度
  • ASP.NET Core 中的响应压缩中间件
  • TensorFlow DAY1：基础语法
  • C++ 中如何优雅地返回一个递归闭包函数？
  • 聆听音乐 1.5.9 | 畅听全网音乐，支持无损音质下载
  • 【银河麒麟高级服务器操作系统实例】tcp半链接数溢出分析及处理全过程
  • 【go每日一题】golang异常、错误 {源码、实践、总结}
  • 探索Docker Compose：轻松管理多容器应用
  • RAID磁盘整列
  • cut-命令详解
  • 【Linux】传输层协议UDP
  • CDP集群安全指南-静态数据加密
  • 奇异值分解SVD
  • vue字符串的数字比较大小有问题
  • typescript安装后仍然不能使用tsc，如何解决
  • mask-R-cnn模型详解
  • overleaf写学术论文常用语法+注意事项+审阅修订