当前位置： 代码网 > it编程>数据库>Mysql > Mysql8主从复制解读(兼容低高版本)

Mysql8主从复制解读(兼容低高版本)

2025年02月21日 • Mysql •我要评论
mysql主从复制理论知识主从复制必要前提主从复制必要的条件：主库开启binlog日志（设置log-bin参数）主从server-id不同从库服务器能连同主库实现原理原理：实现整个主从复制，需要由sl

mysql主从复制

理论知识

主从复制必要前提

  • 主从复制必要的条件：
  • 主库开启binlog日志（设置log-bin参数）
  • 主从server-id不同
  • 从库服务器能连同主库

实现原理

原理：实现整个主从复制，需要由slave服务器上的io进程和sql进程共同完成；要实现主从复制，首先必须打开master端的binary log（bin-log）功能，因为整个mysql 复制过程实际上就是slave从master端获取相应的二进制日志，然后再在自己本地（slave端）按照执行日志中所记录的顺序，全部操作一遍。

  • ---在主库上把有数据更改的（ddl dml dcl）sql语句都记录到二进制日志（binary log）中。
  • ---备库的i/o线程将主库上的日志复制到自己的中继日志（relay log）中。
  • ---备库的sql线程读取中继日志中的事件，将其重放到备库数据库之上。

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 版本不同，有些命令是不同的

show master status; 不能用了

查看主节点binlog的命令

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

# mysql 8.4版本后使用这条命令查看
show binary log status;

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;

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之前

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: yesreplica_sql_running: yeslast_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;

查看是否是只读模式

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;`**

 查看

mysql> show replica status\g

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

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。

赞 (0)

相关文章:

版权声明：本文内容由互联网用户贡献，该文观点仅代表作者本人。本站仅提供信息存储服务，不拥有所有权，不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 2386932994@qq.com 举报，一经查实将立刻删除。

发表评论

验证码：
最近更新的文章
推荐阅读
大家感兴趣的文章
Copyright © 2017-2025  代码网 保留所有权利. 粤ICP备2024248653号
站长QQ:2386932994 | 联系邮箱：2386932994@qq.com