十三、 MySQL主从同步

本文主要是介绍十三、 MySQL主从同步，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

准备实验环境

准备实验环境

主机名	主机IP	xx
admin	192.168.4.9	客户端
node1	192.168.4.11	Master节点
node2	192.168.4.12
node3	192.168.4.13
node4	192.168.4.14
node5	192.168.4.15

准备实验环境

1. 创建5台node节点
2. 使用scp命令将安装包拷贝至node节点
3. 为所有node节点安装mysql服务
4. 启动mysql服务
5. 设置密码为'Yue3314526!'

示例

[root@node1 ~]# tar -xf mysql-5.7.36-1.el7.x86_64.rpm-bundle.tar 
[root@node1 ~]# yum -y install *.rpm
[root@node1 ~]# systemctl start mysqld
[root@node1 ~]# grep "password" /var/log/mysqld.log
[root@node1 ~]# mysql -uroot -p"yb/EtXtWq3jD"
mysql> alter user root@'localhost' identified by "Yue3314526!";

基本结构模式

MySQL主从同步

主从同步介绍
- 实现数据自动同步的数据结构
- 主服务器：接受客户端访问连接
- 从服务器：自动同步主服务器数据

拓扑结构

主服务器: 192.168.4.11
从服务器: 192.168.4.12
客户端: 192.168.4.9

主从同步原理

Master
  - 启动binlog日志
  
Slave
  - Slave_IO: 复制master主机binlog日志文件里的SQL命令到本机的relay-log文件里
  - Slave_SQL: 执行本机relay-log文件里的SQL语句，实现与Master数据一致

配置主从同步(一主一从)

具体要求如下

数据库服务器"192.168.4.11"配置为主数据库服务器
数据库服务器"192.168.4.12"配置为从数据库服务器
客户端"192.168.4.9"测试配置

配置主服务器192.168.4.11

启动binlog日志、授权用户、查看binlog日志信息

# 启动binlog日志
[root@node1 ~]# vim /etc/my.cnf
[mysqld]
server_id=11
log_bin=masert11

[root@node1 ~]# systemctl restart mysqld
[root@node1 ~]# mysql -uroot -p'Yue3314526!' -e "show master status"
[root@node1 ~]# ls /var/lib/mysql/masert11.*

# 添加授权用户
mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";

配置从服务器192.168.4.12

- 设置server_id
- 确保与主服务器数据一致
- 指定主库信息
- 启动slave程序
- 查看状态信息

设置server_id

[root@node2 ~]# vim /etc/my.cnf
[mysqld]
server_id=12

[root@node2 ~]# systemctl restart mysqld

确保与主服务器数据一致

在主服务器上备份数据，将备份文件拷贝给从服务器
在从服务器使用备份文件恢复数据
在从服务器查看备份文件中的binlog日志信息

主库备份数据

[root@node1 ~]# mysqldump -uroot -p'Yue3314526!' --master-data --all-databases > /root/alldb.sql
[root@node1 ~]# scp /root/alldb.sql root@192.168.4.12:/opt/

# --master-data 记录当前备份数据对应的日志消息
# 大概就是记录当前时间的偏移量，防止搭建期间插入的数据未能记录

从服务器恢复数据

[root@node2 ~]# mysql -uroot -p'Yue3314526!' < /opt/alldb.sql
[root@node2 ~]# mysql -uroot -p'Yue3314526!' -e "select count(*) from db2.user"

[root@node2 ~]# grep "masert" /root/db2.sql 
CHANGE MASTER TO MASTER_LOG_FILE='masert11.000001', MASTER_LOG_POS=441;
# --master-data记录了一个"MASTER_LOG_POS"的偏移量，主从同步将从偏移量"MASTER_LOG_POS"开始同步，防止缺漏

指定主服务器信息

mysql> change master to
    -> master_host="192.168.4.11",				# 主库IP地址
    -> master_user="repluser",					# 主库授权用户
    -> master_password="123qqq...A",			# 授权用户密码
    -> master_log_file="masert11.000001",		# 主库日志文件
    -> master_log_pos=441;						# 日志偏移量

# 这个偏移量，参考db2.sql中记载的'MASTER_LOG_POS'偏移量，或者备份时定位

mysql> start slave;			# 启动slave进程

# Master的信息会自动保存到'/var/bin/mysql/master.info'文件
# 若更改主库信息时，应先执行'stop slave;'，修改后 再执行'start slave;'
# mysql> stop slave;		# 停止slave进程
# mysql> change master to master_log_file="masert11.000002";	# 修改一条
# mysql> start slave;		# 开启slave进程

查看Slave状态信息

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.4.11		# 主服务IP
                  Master_User: repluser			# 用户
                  Master_Port: 3306				# 主服务器端口
                Connect_Retry: 60
              Master_Log_File: masert11.000001
          Read_Master_Log_Pos: 441
               Relay_Log_File: node2-relay-bin.000002
                Relay_Log_Pos: 319
        Relay_Master_Log_File: masert11.000001
             Slave_IO_Running: Yes			# 重点 必须是Yee
            Slave_SQL_Running: Yes			# 重点 必须是Yes

排错

Slave_IO_Running: No	# 查看下面错误字段Last_IO_Error的提示

相关文件(存放在数据库目录下)

master.info					# 主库配置信息(主库的配置相关信息)
relay-log.info				# 中继日志配置信息
主机名-relay-bin.xxxxxx	  # 中继日志
主机名-relay-bin.index		  # 索引文件

删除这四个文件可以使从服务器的身份切换为独立服务器
# 特殊情况，可以删除这四个文件(重启mysqld)，进行重新配置(例: 中继日志与中继日志配置文件不符)

[root@node2 ~]# cat /var/lib/mysql/master.info

验证主从同步配置

在主服务器添加授权用户给客户端连接使用

mysql> create database db3;
mysql> grant all on db3.* to admin@"%" identified by "123qqq...A";
mysql> grant select,insert on db2.* to admin@"%";

# 如果之前实验有建立重复的admin用户，删掉
mysql> drop user admin@"192.168.4.%";

客户端使用授权用户连接主服务器，访问数据

[root@node2 ~]# mysql -h'192.168.4.11' -uadmin -p'123qqq...A'

mysql> show grants;

mysql> insert into db2.user(username) values("lisi");
mysql> select * from db2.user where username="lisi";

在从服务器主机查看数据(能看到和主服务器同样的数据)

[root@node2 ~]# mysql -uroot -p'Yue3314526' -e "select * from db2.user"

# 在实验环境中，出现了偏移量与文件不符合情况，重建主服务器的binlog日志，并重新配置从服务器的偏移量解决
# Read_Master_Log_Pos 与 Position 应该是相同的

扩展结构类型

MySQL一主多从结构

拓扑结构

从 < -- 主 -- > 从
        ↓
        从

客户端: admin(192.168.4.9)
主服务器: node1(192.168.4.11)
从服务器1: node2(192.168.4.12)
从服务器2: node3(192.168.4.13)

构建思路

配置主服务器
  - 启动binlog日志、授权用户、查看binlog日志信息
配置从服务器
  - 设置server_id
  - 确保与主服务器数据一致
  - 指定主库信息
  - 启动slave程序
  - 查看状态信息

实现过程

基于一主一从结构，再添加一个从服务器即可，不需要过多操作
这里就不进行详细说明了
# 但是你需要手动实现一遍

[root@node1 ~]# mysqldump -uroot -p'Yue3314526!' --master-data --all-databases > /root/alldb_two.sql
[root@node1 ~]# scp /root/alldb_two.sql root@192.168.4.13:/opt/

[root@node3 ~]# vim /etc/my.cnf
[mysqld]
server_id=13
[root@node3 ~]# systemctl restart mysqld
[root@node3 ~]# mysql -uroot -p'Yue3314526!' < /opt/alldb_two.sql

[root@node3 ~]# vim /opt/alldb_two.sql
CHANGE MASTER TO MASTER_LOG_FILE='masert11.000001', MASTER_LOG_POS=1422;	# 查看偏移量信息

[root@node3 ~]# mysql -uroot -p'Yue3314526!'
# ----- 进入mysql操作 -----

mysql> change master to
    -> master_host="192.168.4.11",
    -> master_user="repluser",
    -> master_password="123qqq...A",
    -> master_log_file="masert11.000001",
    -> master_log_pos=1422;

mysql> start slave;
mysql> show slave status \G;

验证测试

客户端访问主服务器(11)进行增删改查验证
在slave(12,13)可以查看到同样的数据

MySQL主从从结构

拓扑结构

主 <-- 从 <-- 从

客户端: admin(192.168.4.9)
主服务器: node1(192.168.4.11)
从服务器1: node2(192.168.4.12)
从服务器2: node4(192.168.4.14)

# node2这这个结构中即做node4节点的主服务器，也要做node1节点的从服务器
# 该实验沿用之前的一主一从配置进行扩展

配置主服务器(node2)

[root@node2 ~]# vim /etc/my.cnf
[mysqld]
server_id=12
log_bin=master12
log_slave_updates		# 允许级联更新(主从从必须指定)

[root@node2 ~]# systemctl restart mysqld

# 添加授权用户(由于node2是node1的完全备份后恢复库，所以该步骤可以省略)
# mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";

mysql> show master status;

[root@node2 ~]# mysqldump -uroot -p'Yue3314526!' --master-data --all-databases > /root/alldb.sql
[root@node2 ~]# scp alldb.sql root@192.168.4.14:/opt/

配置从服务器(node4)

[root@node4 ~]# vim /etc/my.cnf
[mysqld]
server_id=14
[root@node4 ~]# systemctl restart mysqld

[root@node4 ~]# mysql -uroot -p'Yue3314526!' < /opt/alldb.sql 

# 查看主(node2)节点的日志偏移量
[root@node2 ~]# mysql -uroot -p'Yue3314526!' -e "show master status"
Position: 154

# ---------- 登录node4MySQL服务进行操作 ------------

mysql> change master to
    -> master_host="192.168.4.12",
    -> master_user="repluser",
    -> master_password="123qqq...A",
    -> master_log_file="master12.000001",
    -> master_log_pos=154;

mysql> start slave;
mysql> show slave status \G;

验证效果

1. 对node1数据进行增删改查，然后对node2数据和node4数据节点数据查看进行验证

2. 在node1服务添加访问数据的授权
mysql> grant all on db_tes.* to yueyue@"%" identified by "123qqq...A";

3. 客户端远程连接node1，访问数据
[root@admin ~]# mysql -uyueyue -h"192.168.4.11" -p'123qqq...A'
mysql> show grants;
mysql> show databases;
mysql> create database db_tes;
mysql> create table db_tes.a(id int);
mysql> insert into db_tes.a values(10);

4. 客户端远程连接node2，node4，查看数据和表的情况，会看到和node1一样的数据
mysql> select * from db_tes.a;

MySQL双主结构(互为主从)

要求

把数据库node5和node6配置为MySQL主主结构

步骤如下
  - 创建两台新的虚拟机 并配置IP地址 node5("192.168.4.15/24"),node6("192.168.4.16/24")
  - 分别在2台数据库服务器安装软件、启动服务、管理初始登录密码
  - 配置数据库服务node5
  - 配置数据库服务node6
  - 把node6配置为node5的从服务器
  - 把node5配置为node6的从服务器
  - 分别在2台服务器查看slave进程的状态信息

# 注意 克隆镜像之后，MySQL实现主从必须保证uuid的不相同 (MySQL5.6之后引入了uuid的概念)
	- mysql> show variables like '%server_uuid%';
	- vim /var/lib/mysql/auto.cnf 
# 如果uuid相等导致报错，可以手动修改一下(长度不变)

分别为node5与node6开启binlog日志

[root@node5 ~]# vim /etc/my.cnf
[mysqld]
server_id=15
log_bin=master15
[root@node5 ~]# systemctl restart mysqld

[root@node6 ~]# vim /etc/my.cnf
[mysqld]
server_id=16
log_bin=master16
[root@node6 ~]# systemctl restart mysqld

配置node6为node5的从服务器

[root@node5 ~]# mysql -uroot -p'Yue3314526!'

# 查看node5上MySQL的偏移量
mysql> show master status;

# node5服务器创建授权账户
mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";

[root@node6 ~]# mysql -uroot -p'Yue3314526!'

mysql> change master to
    -> master_host="192.168.4.15",
    -> master_user="repluser",
    -> master_password="123qqq...A",
    -> master_log_file="master15.000001",
    -> master_log_pos=154;

mysql> start slave;

mysql> show slave status \G

配置node5为node6的从服务器

mysql> show master status;
mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";

[root@node5 ~]#  mysql -uroot -p'Yue3314526!'

mysql> change master to
    -> master_host="192.168.4.16",
    -> master_user="repluser",
    -> master_password="123qqq...A",
    -> master_log_file="master16.000001",
    -> master_log_pos=154;

mysql> start slave;

mysql> show slave status \G;

主从同步复制模式

类型

异步复制模式(默认)Asynchronous replication

主库执行完一次事务后，立即将结果返给客户端，并不关心从库是否已经接收并处理。

全同步复制模式Fully synchronous replication

当主库执行完一次事务，且所有从库都执行了该事务后才将结果返回给客户端。

关键字: MySQL组同步

半同步复制模式Semisynchronous replication

介于异步复制和全同步复制之间。
主库在执行完一次事务后，等待至少一个从库接收到并写到"relay log"中才将结果返回给客户端。

半同步复制功能由模块提供

半同步复制模式

说明

该实验沿用了之前的MySQL双主结构(互为主从)
因为每个node服务器既是主库，又是从库，所以下列模块配置都要进行配置

查看是否允许动态加载模块

# 默认允许
mysql> show variables like "have_dynamic_loading";

命令行加载插件

使用数据库管理员root用户访问服务

# install plugin 插件类型 SONAME "插件名"

# 主服务器上执行
mysql> install plugin rpl_semi_sync_master SONAME "semisync_master.so";

# 从服务器上执行
mysql> install plugin rpl_semi_sync_slave SONAME "semisync_slave.so";

mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like "%semi%";
 - plugin_status: ACTIVE
 - plugin_status: ACTIVE

启用半同步复制

半同步复制，默认是关闭的

# 主服务器执行
mysql> set global rpl_semi_sync_master_enabled=1;

# 从服务器执行
mysql> set global rpl_semi_sync_slave_enabled=1;

mysql> show variables like "rpl_semi_sync_%_enabled";

永久启用半同步复制(写入配置文件)

修改主配置文件/ect/my.cnf

在[mysqld]下方添加

# 该配置是当一个服务器即为主又为从的情况下进行的配置方法

plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled=1;
rpl_semi_sync_slave_enabled=1;

# 配置完成后，可以重启MySQL服务 然后查看相关配置状态进行判断
  - show variables like "rpl_semi_sync_%_enabled";

# 该配置是在主从分开的情况下分别对应情况写入配置文件

# 主服务器
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1;

# 从服务器
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1;

这篇关于十三、 MySQL主从同步的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

MySql教程

十三、 MySQL主从同步

准备实验环境

基本结构模式

MySQL主从同步

配置主从同步(一主一从)

扩展结构类型

MySQL一主多从结构

MySQL主从从结构

MySQL双主结构(互为主从)

主从同步复制模式

类型

半同步复制模式

前端开发

后端开发

移动端开发

数据库

服务器运维

人工智能

区块链

游戏开发

网站运营

大数据/云计算

软件工程

软件/开发工具使用

资讯