java面试之Mysql主从同步

最新推荐文章于 2024-07-11 21:37:29 发布

扆梦楠

最新推荐文章于 2024-07-11 21:37:29 发布

阅读量491

点赞数

本文链接：https://blog.csdn.net/ymn159/article/details/119515262

版权

java面试专栏收录该内容

16 篇文章 0 订阅

订阅专栏

java面试之Mysql主从同步

1.主从同步

概述

MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。
MySQL主从复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。

原理

Master 数据库只要发生变化，立马记录到Binary log 日志文件中
Slave数据库启动一个I/O thread连接Master数据库，请求Master变化的二进制日志，同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件
Slave I/O获取到的二进制日志，保存到自己的Relay log 日志文件中
Slave 有一个 SQL thread定时检查Realy log是否变化，变化那么就更新数据

用途

读写分离，提供查询服务
实时灾备，用于故障切换
备份，避免影响业务
高可用
架构扩展

主从形式

一主一从

一主多从
在这里插入图片描述
一主一从和一主多从是最常见的主从架构，实施起来简单并且有效，不仅可以实现HA，而且还能读写分离，进而提升集群的并发能力。

多主一从 （从5.7开始支持）
在这里插入图片描述
多主一从可以将多个mysql数据库备份到一台存储性能比较好的服务器上。

双主复制
双主复制，也就是互做主从复制，每个master既是master，又是另外一台服务器的slave。这样任何一方所做的变更，都会通过复制应用到另外一方的数据库中。

级联复制
在这里插入图片描述
级联复制模式下，部分slave的数据同步不连接主节点，而是连接从节点。因为如果主节点有太多的从节点，就会损耗一部分性能用于replication，那么我们可以让3~5个从节点连接主节点，其它从节点作为二级或者三级与从节点连接，这样不仅可以缓解主节点的压力，并且对数据一致性没有负面影响。

主从问题及解决方法

主库宕机后，数据可能丢失
从库只有一个sql Thread，主库写压力大，复制很可能延时

解决方法：
半同步复制—解决数据丢失的问题
并行复制----解决从库复制延迟的问题

并行复制（MTS）

MySQL 5.6 并行复制原理

MySQL 5.6 版本也支持所谓的并行复制，但是其并行只是基于库。基于库的并行复制遇到单库多表使用场景发挥不出优势，另外对事务并行处理的执行顺序也是个大问题。

MySQL 5.7 并行复制原理

MySQL 5.7 是基于组提交的并行复制，MySQL 5.7 才可称为真正的并行复制。

MySQL 5.7 并行复制基于一个前提即所有已经处于 prepare 阶段的事务都是可以并行提交的。这些当然也可以在从库中并行提交，因为处理这个阶段的事务都是没有冲突的。在一个组里提交的事务一定不会修改同一行。

为了兼容 MySQL 5.6 基于库的并行复制，5.7 引入了新变量 slave-parallel-type，其可以配置的值有： DATABASE（默认值，基于库并行复制方式）、LOGICAL_CLOCK（基于组提交并行复制方式）。

MySQL 5.7 将组提交的信息存放在 GTID 中，为了避免用户没有开启 GTID 功能（gtid_mode=OFF），又引入了称之为 Anonymous_Gtid 的二进制日志 event 类型 ANONYMOUS_GTID_LOG_EVENT。通过 mysqlbinlog 工具分析 binlog 日志，last_committed 表示事务提交时上次事务提交的编号，如果事务具有相同的 last_committed，表示这些事务都在一组内，可以进行并行回放。

MySQL 8.0 并行复制原理

MySQL8.0 是基于 write-set 的并行复制。MySQL 会有一个集合变量来存储事务修改的记录信息（主键哈希值），所有已经提交的事务所修改的主键值经过 hash 后都会与那个变量的集合进行对比，来判断改行是否与其冲突，并以此来确定依赖关系，没有冲突即可并行。这样的粒度，就到了 row 级别了，此时并行的粒度更加精细，并行的速度会更快。

#sql设置sql线程数为10
set global slave_parallel_workers=10;

主从复制模式

MySQL 主从复制默认是异步的模式。

异步模式：主节点不会主动推送bin-log到从节点，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主节点如果崩溃掉了，此时主节点上已经提交的事务可能并没有传到从节点上，如果此时，强行将从提升为主，可能导致新主节点上的数据不完整。
半同步模式：介于异步和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay-log中才返回成功信息给客户端（只能保证主库的bin-log至少传输到了一个从节点上，但并不能保证从节点将此事务执行更新到db中），否则需要等待直到超时时间然后切换成异步模式再提交。相对于异步复制，半同步复制提高了数据的安全性，一定程度保证数据能成功备份到从库，同时也造成了一定程度的延迟，但是比全同步模式延迟要低，这个延迟最少是一个TCP/IP往返的时间。
全同步模式：当主库执行完一个事务,所有的从库都执行了该事务才返回给客户端
GTID复制：从Mysql5.6开始，GTID复制是完全基于事务的复制，即每个在主库上执行的事务都会被分配到一个唯一的全局ID并记录和应用在主库上。首先从服务器会告诉主服务器已经在从服务器执行完了哪些事务的GTID值，然后主库会把所有没有在从库上执行的事务，发送到从库上进行执行，并且使用GTID的复制可以保证同一个事务只在指定的从库上执行一次，这样可以避免由于偏移量的问题造成数据不一致。GTID=source_id:transaction_id，其中source_id就是执行事务的主库的server-uuid值，server-uuid值是在mysql服务首次启动生成的，保存在数据库的数据目录中，在数据目录中有一个auto.conf文件，这个文件保存了server-uuid值（唯一的）。而事务ID则是从1开始自增的序列，表示这个事务是在主库上执行的第几个事务，Mysql会保证这个事务和GTID是一比一的关系。

主从复制的方式

基于SQL语句的复制：binlog文件的格式是STATEMENT，每一条会修改数据的sql都会记录在binlog中。
基于行的复制：binlog文件的格式是ROW。
混合模式复制：对于一般的复制使用STATEMENT模式保存到binlog，对于STATEMENT模式无法复制的操作则使用ROW模式来保存，MySQL会根据执行的SQL语句选择日志保存方式。binlog文件的格式是MIXED

2.主从（一主一从）配置实践

GTID模式

MySQL5.6的GTID复制模式，slave必须开启binary log和log_slave_updates参数，否则启动就报错，因为需要在binary log找到同步复制的信息（UUID：事务号），注意，开启log_slave_updates参数，是把relay-log里的日志内容再记录到本地的binary log里。但在MySQL5.7里，官方做了调整，用一张gtid_executed系统表记录同步复制的信息（UUID：事务号），这样就可以不用开启log_slave_updates参数，减少了从库的压力。

#未开启gtid复制是查看mysql.gtid_executed为空！
mysql> desc gtid_executed;
+----------------+------------+------+-----+---------+-------+
| Field          | Type       | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------+-------+
| source_uuid    | char(36)   | NO   | PRI | NULL    |       |
| interval_start | bigint(20) | NO   | PRI | NULL    |       |
| interval_end   | bigint(20) | NO   |     | NULL    |       |
+----------------+------------+------+-----+---------+-------+

主节点配置

#进入主节点容器
docker exec -it 88bb8d47a27d /bin/bash
#配置my.cnf
vi /etc/mysql/my.cnf
#添加如下内容后保存
	[mysqld]
	server_id = 1
	log-bin= mysql-master-bin
	gtid-mode=on
	enforce-gtid-consistency=on
	basedir=/home/mysql/
	datadir=/home/mysql/data/
#重启mysql服务
service mysql restart
#启动主容器
docker start master

从节点配置

#进入从节点容器
docker exec -it slave  /bin/bash
#配置my.cnf
vi /etc/mysql/my.cnf
#添加如下内容后保存
	[mysqld]
	basedir=/yinzhengjie/softwares/mysql/
	datadir=/yinzhengjie/softwares/mysql/data/
	log-bin=yinzhengjie-mysql-bin
	server-id=2
	gtid-mode=on
	enforce-gtid-consistency=on
#重启mysql服务
service mysql restart
#启动从容器
docker start slave
# 连接mysql
docker exec -it slave mysql -u root -p
STOP SLAVE;
RESET SLAVE ALL;
#从节点上连接主节点
change master to master_host='172.17.0.2',master_user='root',master_password='123456',master_port=3306,MASTER_AUTO_POSITION=1;
#开启主从复制
start slave;
#查询主从同步状态,SlaveIORunning 和 SlaveSQLRunning 都是Yes，说明主从复制已经开启。
show slave status \G;

验证

show master status;
+-------------------------+----------+--------------+------------------+--------                   ----------------------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+-------------------------+----------+--------------+------------------+--------                   ----------------------------------+
| mysql-master-bin.000004 |     1392 |              |                  | 0cb04f26-85d0-11ea-8547-0242ac110002:1-5 |

同步过程省略，在从库中查询到SELECT * FROM mysql.gtid_executed;　
在这里插入图片描述
GTID复制的限制条件

异步模式

#拉取docker镜像
docker pull mysql:5.7
#查看镜像
docker images 
#创建mysql主从容器，Master对外映射的端口是3339，Slave对外映射的端口是3340。
docker run -p 3339:3306 --name master -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
docker run -p 3340:3306 --name slave -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
#查看运行的容器
docker ps

用mysql可视化工具连接，mysql的主机地址是docker所在的宿主ip，如下图所示
在这里插入图片描述

有可能出现连接不上的情况，试试以下方案解决

#进入主节点容器（从节点容器类似）
docker exec -it master bash 
#进入MySQL客户端
mysql -u root -p 
#修改密码
ALTER USER 'root'@'%' IDENTIFIED BY '123456'; 
#刷新权限
flush privileges;

普通模式

主节点配置

#进入主节点容器
docker exec -it 88bb8d47a27d /bin/bash
#配置my.cnf
vi /etc/mysql/my.cnf
#添加如下内容后保存
	[mysqld]
	#启动MySQL二进制日志
	log-bin=mysql-master-bin
	#SQL语句复制的方式
	binlog_format=mixed
	#服务器唯一标识
	server_id=1
	#同步的数据库
	binlog-do-db=ddm
	#为0表示支持可读写
	read-only=0
#重启mysql服务
service mysql restart
#启动主容器
docker start master
#再一次进入主节点容器
docker exec -it master bash
# 连接mysql
mysql -u root -p
#从节点如果要同步主节点的数据,主节点需要开通权限
grant replication slave, replication client on *.* to 'root'@'*' identified by '123456';
#查看master状态,记录File 和 Position的数据
show master status;
#退出mysql
exit;
#退出主容器
exec
#查看主容器ip,每个容器都有ip，此ip和docker所在ip不是一回事
docker inspect master|grep IPAddress

从节点配置

#进入从节点容器
docker exec -it slave bash 
#配置my.cnf, vim: command not found
vi /etc/mysql/my.cnf
#添加如下内容后保存
	[mysqld]
	#服务器唯一标识
	server_id=2
	#启动MySQL二进制日志，以备slave作为其它slave的master时使用
	log-bin=mysql-slave-bin
	#SQL语句复制的方式
	binlog_format=mixed
	#relay_log配置中继日志
	relay_log=mysql-relay-bin
	#为1表示只读
	read-only=1
#重启mysql服务
service mysql restart
#启动从容器
docker start slave
#再一次进入从节点容器
docker exec -it slave bash
# 连接mysql
mysql -u root -p
#从节点上连接主节点
change master to master_host='172.17.0.2',master_user='root',master_password='123456',master_port=3306,master_log_file='mysql-master-bin.000001',master_log_pos=457,master_connect_retry=30;
#开启主从复制
start slave;
#查询主从同步状态,SlaveIORunning 和 SlaveSQLRunning 都是Yes，说明主从复制已经开启。
show slave status \G;

vim: command not found

#同步/etc/apt/sources.list 和 /etc/apt/sources.list.d 中源的索引
#有时连接不上，多试几次
apt-get update
#安装vim
apt-get install vim

或以下方案

#更新apt-get源
mv /etc/apt/sources.list /etc/apt/sources.list.bak && \
echo "deb http://mirrors.163.com/debian/ jessie main non-free contrib" >/etc/apt/sources.list && \
echo "deb http://mirrors.163.com/debian/ jessie-proposed-updates main non-free contrib" >>/etc/apt/sources.list && \
echo "deb-src http://mirrors.163.com/debian/ jessie main non-free contrib" >>/etc/apt/sources.list && \
echo "deb-src http://mirrors.163.com/debian/ jessie-proposed-updates main non-free contrib" >>/etc/apt/sources.list
#更新apt-get
apt-get update
#安装vim
apt-get install vim

验证同步过程
在主节点建立ddm数据库，然后创建表，插入、修改、删除记录测试主从同步过程，如下图所示：
在这里插入图片描述

3.主从（一主多从）配置实践

搭建两台从机器

#停止容器
docker stop mysqlmaster
#commit该docker容器
docker commit mysqlmaster new_image
#启动刻画的容器slave2,slave3
docker run --name slave2 -p 3341:3306 new_image
docker run --name slave3 -p 3342:3306 new_image

slave2从节点配置

#进入从节点容器
docker exec -it slave2 bash 
#配置my.cnf, vim: command not found
vi /etc/mysql/my.cnf
#添加如下内容后保存
	[mysqld]
	#服务器唯一标识
	server_id=3
	#启动MySQL二进制日志，以备slave作为其它slave的master时使用
	log-bin=mysql-slave2-bin
	#SQL语句复制的方式
	binlog_format=mixed
	#relay_log配置中继日志
	relay_log=mysql-relay-bin
#重启mysql服务
service mysql restart
#启动从容器
docker start slave2
#再一次进入从节点容器
docker exec -it slave2 bash
# 连接mysql
mysql -u root -p
#从节点上连接主节点
change master to master_host='172.17.0.2',master_user='root',master_password='123456',master_port=3306,master_log_file='mysql-master-bin.000002',master_log_pos=785,master_connect_retry=30;
#开启主从复制
start slave;
#查询主从同步状态,SlaveIORunning 和 SlaveSQLRunning 都是Yes，说明主从复制已经开启。
show slave status \G;

slave3从节点配置

#进入从节点容器
docker exec -it slave3 bash 
#配置my.cnf, vim: command not found
vi /etc/mysql/my.cnf
#添加如下内容后保存
	[mysqld]
	#服务器唯一标识
	server_id=4
	#启动MySQL二进制日志，以备slave作为其它slave的master时使用
	log-bin=mysql-slave3-bin
	#SQL语句复制的方式
	binlog_format=mixed
	#relay_log配置中继日志
	relay_log=mysql-relay-bin
#重启mysql服务
service mysql restart
#启动从容器
docker start slave3
#再一次进入从节点容器
docker exec -it slave3 bash
# 连接mysql
mysql -u root -p
#从节点上连接主节点
change master to master_host='172.17.0.2',master_user='root',master_password='123456',master_port=3306,master_log_file='mysql-master-bin.000002',master_log_pos=785,master_connect_retry=30;
#开启主从复制
start slave;
#查询主从同步状态,SlaveIORunning 和 SlaveSQLRunning 都是Yes，说明主从复制已经开启。
show slave status \G;

验证同步过程
首先在一主一从的基础上又搭建了同步的两台从机，在两台从机上分别创建ddm数据库。其次这两台机器是没有之前测试同步的user表，如果此时修改user表数据，会导致新建的两台从机Slave_SQL_Running:No

Slave_SQL_Running:No解决方案

将主从上的user表复制到其他两台从机上

其次停止那两台从机的主从同步，设置后重新启动，此时就可以同步成功

stop slave;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;

#master容器上执行
mysqldump -uroot -p123456 --all-databases >/root/all_database.sql;
#将all_database.sql拷贝到从库中来，在slave从库容器上执行
mysql -uroot -p123456 <all_database.sql;
#如此两步骤，主库和从库数据会追加相平，保持同步！
#再此过程中，若主库存在业务，在同步的时候要先锁表，让其不要有修改！如需要，可以master容器中，执行以下命令锁定数据库以防止写入数据。
FLUSH TABLES WITH READ LOCK;
#等待主从数据追平，主从同步后在打开锁！解锁数据表。主从数据追平后，定位好从库到主库日志的文件名字，和相应的位置，再重新设置从库。
UNLOCK TABLES;

在这里插入图片描述

4.主从（双主）配置实践

主节点配置

#进入主容器
docker exec -it master bash
#修改配置文件
vi /etc/mysql/my.cnf
#添加如下内容
[mysqld]
	server_id = 1
	log-bin= mysql-master-bin
	
	replicate-ignore-db=mysql
	replicate-ignore-db=sys
	replicate-ignore-db=information_schema
	replicate-ignore-db=performance_schema
	
	read-only=0
	relay_log=mysql-master-relay-bin
	log-slave-updates=on
	auto-increment-offset=1
	auto-increment-increment=2
#重启mysql服务
service mysql restart
#启动从容器
docker start master
#进入主容器，登录mysql
docker exec -it master mysql -uroot -p
#从节点如果要同步主节点的数据,主节点需要开通权限
grant replication slave, replication client on *.* to 'root'@'*' identified by '123456';
#查看master状态,记录File 和 Position的数据
show master status;
#从节点上连接主节点
change master to master_host='172.17.0.3',master_user='root',master_password='123456',master_port=3306,master_log_file='mysql-slave-bin.000003',master_log_pos=462,master_connect_retry=30;
#开启主从复制
start slave;
#查看状态
show slave status\G;

从节点配置

#进入从容器
docker exec -it slave bash
#修改配置文件
vi /etc/mysql/my.cnf
#添加如下内容
[mysqld]
	server_id = 2
	log-bin= mysql-slave-bin
	
	replicate-ignore-db=mysql
	replicate-ignore-db=sys
	replicate-ignore-db=information_schema
	replicate-ignore-db=performance_schema
	
	read-only=0
	relay_log=mysql-slave-relay-bin
	log-slave-updates=on
	auto-increment-offset=2
	auto-increment-increment=2
#重启mysql服务
service mysql restart
#启动从容器
docker start slave
#进入从容器，登录mysql
docker exec -it slave mysql -uroot -p
#从节点如果要同步主节点的数据,主节点需要开通权限
grant replication slave, replication client on *.* to 'root'@'*' identified by '123456';
#查看master状态,记录File 和 Position的数据
show master status;
#从节点上连接主节点
change master to master_host='172.17.0.2',master_user='root',master_password='123456',master_port=3306,master_log_file='mysql-master-bin.000003',master_log_pos=462,master_connect_retry=30;
#开启主从复制，可能报Slave failed to initialize relay log info structure from the repository错误
#如果有这个错误先执行reset slave;再执行change master to...,最后执行start slave;
start slave;
#查看状态
show slave status\G;

容器无法启动

#容器无法启动，查看报错日志
docker logs master
#复制当前目录的my.cnf到从机器的/etc/mysql/my.cnf
docker cp  my.cnf 62bc4d014a74:/etc/mysql/
#容器无法启动，复制当前目录的my.cnf到主机器的/etc/mysql/my.cnf
docker cp  my.cnf 88bb8d47a27d:/etc/mysql/

验证主主复制同步
在这里插入图片描述