基本概念
MySQL的主从复制是一种数据复制技术,允许在一个或多个数据库(称为“从数据库”或“slave”)上复制另一个数据库(称为“主数据库”或“master”)的数据更改。这种机制是一种常见的数据库架构,用于实现数据的备份、负载均衡、读写分离和故障恢复等场景。
主数据库(Master):负责处理写操作和更新数据,是业务操作的主要入口;
从数据库(Slave):复制主数据库的数据,通常用于读操作和数据备份,以减轻主数据库的压力;
工作流程
MySQL 主从复制的工作流程图如下:
流程如下:
1)记录主库更新的操作记录到二进制日志 binlog 中,通过 binlog dump线程发送给从库;
1.1)当主库发生更新事件(如UPDATE、INSERT、DELETE、CREATE)时,这些操作会被顺序地写入二进制日志(binlog)中;
1.2)主库会开启一个 binlog dump线程,负责将 binlog 日志中的事件发送给从库;
2)从库 IO 线程通过设置的主库的 slave 账号,连接到主库请求 binlog,并接收主库的 binlog,写入到中断日志 relay log 中;
2.1)从库通过 IO 线程连接到主库,并请求 binlog 日志的内容;
2.2)主库的 binlog dump线程将 binlog 日志中的事件发送给从库的 IO 线程;
2.3)从库的 IO 线程接收这些事件,并将其写入到从库的中继日志(relay log)中;
3)从库 SQL 线程实时监控 relay log,读取、解析并执行 relay log 中的SQL语句,实现数据的同步;
3.1)从库开启一个SQL线程,实时监控relay log的内容是否有更新;
3.2)SQL线程解析relay log中的SQL语句,并在从库上执行这些语句,从而实现数据的同步;
复制格式与同步方式
复制格式
MySQL支持三种binlog格式:statement、row和mixed。不同的格式对数据的复制精度和性能有不同的影响。
1)Statement:基于语句的复制,记录SQL原文,可能因索引选择不同而导致主从不一致;
2)Row:基于行的复制,记录操作的具体行信息,避免主从不一致,但可能占用更多空间;
3)Mixed:混合使用statement和row格式,根据具体情况选择;
不同的MySQL版本默认采用的复制方式不同,在MySQL 8中,默认的复制格式为基于行的复制。可通过主库中的binlog_format进行配置。
同步方式
1)异步复制:主库写入binlog后即可返回成功,无需等待从库确认。这种方式效率高但可能存在数据不一致的风险;
2)半同步复制:主库在提交事务时需要等待至少一个从库确认已接收事务。这种方式增强了数据一致性但牺牲了一部分性能;
3)同步复制:主库在所有从库都确认已接收并应用事务后才能提交。这种方式最安全但效率最低;
不同的MySQL版本默认采用的同步方式不同,在MySQL 8中,默认的同步方式为异步复制。
以下以 Docker 中部署 MySQL 主从复制为例,分享 MySQL 主从复制的实现。
Docker MySQL 镜像下载
1)拉取 MySQL 的镜像。目前官网不能用,通过 hub.atomgit.com 中下载;
docker pull hub.atomgit.com/amd64/mysql:8.0.34
2)创建 MySQL 的卷,用于 MySQL 容器与服务器的文件映射;
# 在 /data/docker 目录中创建
mkdir test_db
# 进入test_db,创建data、conf、init、log、mysql-files文件夹
cd test_db
mkdir data
mkdir conf
mkdir init
mkdir log
mkdir mysql-files
主库安装及配置
5.1 创建 my.cnf 配置
本例中,在 /data/docker/test_db/conf 目录下创建 my.cnf 文件,配置信息如下:
[mysqld]
## 设置server_id,同一局域网中需要唯一
server_id=100
## 开启二进制日志
log_bin=mysql-bin
#设置需要同步的数据库
binlog_do_db=test1
binlog_do_db=test2
#屏蔽系统库同步
binlog_ignore_db=mysql,information_schema,performance_schema
## 设置二进制日志使用内存大小(事务)
binlog_cache_size=1M
## 设置使用的二进制日志格式(mixed,statement,row)
binlog_format=mixed
## 二进制日志过期清理时间。默认值为0,表示不自动清理。
expire_logs_days=7
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
default-character-set=utf8mb4
注:不同的版本对于biglog_do_db的配置不同,对于多个数据库的同步,有些版本是通过逗号隔开,有些版本需要分开写,具体以使用的版本为准。如果配置错误,会导致无法同步,且可能不会报错。
对于在用的数据库进行主库配置时,只需在原来配置的基础上,添加server_id、log_bin、binlog_do_db的配置。
5.2 启动主库的MySQL镜像
docker run -it
-p 3307:3306 # 端口映射,宿主机的3307端口映射到 docker 容器的3306端口
--privileged=true
-v /data/docker/test_db/log:/var/log/mysql
-v /data/docker/test_db/data:/var/lib/mysql
-v /data/docker/test_db/conf:/etc/mysql/conf.d
-v /data/docker/test_db/mysql-files:/var/lib/mysql-files
-v /data/docker/test_db/init:/docker-entrypoint-initdb.d
-e MYSQL_ROOT_PASSWORD=123456 # 设置环境变量 MYSQL_ROOT_PASSWORD 的值
--name test_db # 设置启动的容器的名称
hub.atomgit.com/amd64/mysql:8.0.34 # 镜像名称
--privileged=true:赋予容器内的进程与宿主机相同的权限,容器内的root用户将拥有与宿主机上的root用户相同的能力,包括访问宿主机的所有设备、修改或加载内核模块、挂载文件系统等;
-v /data/docker/test_db/data:/var/lib/mysql:-v为文件挂载,将宿主机的/data/docker/test_db/data挂载到/var/lib/mysql中。即两个目录为同一个目录;
可以通过 docker logs -f test_db 实时查看test_db的过程日志。
5.3 进入MySQL
1)方式一:
docker exec -it test_db bash # 进入 test_db 的虚拟机
mysql -uroot -p123456 # 进入mysql数据库
2)方式二:
docker exec -it test_db mysql -uroot -p123456 # 直接进入mysql数据库
5.4 创建同步用户
# 创建test用户,密码为123456。ip 为从库的 ip
create user 'test'@'127.0.0.1' identified by '123456'
# 给test用户授权,用于从库操作主库
grant replication slave on *.* to 'test'@'127.0.0.1'
# 刷新权限
flush PRIVILEGES
# 查看主库的状态
show master status \G;
# 显示信息如下:
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 135
Binlog_Do_DB: test1,test2
Binlog_Ignore_DB: mysql,information_schema,performance_schema
Executed_Gtid_Set:
对于在用的数据库,需要备份数据库,在从库中执行备份的数据库数据。
5.5 说明
5.5.1 log_bin文件
开启log_bin之后,在对应的数据库的data目录中(Linux系统中,默认在 /var/lib/mysql 目录),会自动生成对应的binlog文件。如本例的my.cnf中配置的log_bin=mysql-bin,则自动生成mysql-bin开头的二进制文件。如下:
-rw-r----- 1 mysql mysql 971 Sep 26 15:51 mysql-bin.000001
-rw-r----- 1 mysql mysql 19 Sep 26 15:12 mysql-bin.index
5.5.2 查看工作线程
通过 show processlist 查看线程列表。如下:
从库安装及配置
6.1 创建 my.cnf 配置
本例中,在从库的服务器的 /data/docker/test_db/conf 目录(如果是在同一个服务器上,可以在 /data/docker 目录中,创建别的文件夹,在文件夹下创建data、conf等)下创建 my.cnf 文件,配置信息如下:
[mysqld]
## 设置server_id,同一局域网中需要唯一
server_id=103
#设置需要同步的数据库
binlog_do_db=test1
binlog_do_db=test2
#屏蔽系统库同步
binlog_ignore_db=mysql,information_schema,performance_schema
## 开启二进制日志功能,以备Slave作为其它数据库实例的Master时使用
log-bin=mysql-slave-bin
## 设置二进制日志使用内存大小(事务)
binlog_cache_size=1M
## 设置使用的二进制日志格式(mixed,statement,row)
binlog_format=mixed
## 二进制日志过期清理时间。默认值为0,表示不自动清理。
expire_logs_days=7
## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
#slave_skip_errors=1062
## relay_log配置中继日志
relay_log=mysql-relay-bin
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
## slave设置为只读(具有super权限的用户除外)
read_only=1
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
default-character-set=utf8mb4
注:不同的版本对于biglog_do_db的配置不同,对于多个数据库的同步,有些版本是通过逗号隔开,有些版本需要分开写,具体以使用的版本为准。如果配置错误,会导致无法同步,且可能不会报错。
启动从库的docker同5.2,如果是同一台服务器,需要修改对应映射的端口以及挂载的目录。
6.2 开启同步
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='', # 主库ip
SOURCE_PORT=3306, # 主库mysql的端口
SOURCE_USER='test', # 主库设置的用户
SOURCE_PASSWORD='123456', # 主库用户的密码
SOURCE_LOG_FILE='mysql-bin.000001', # 主库中通过show master status 查看的文件名
SOURCE_LOG_POS=135; # 主库中通过show master status 查看的文件位置
# 启动备份
start replica;
# 查看状态
show replica status \G;
6.3 说明
6.3.1 relay_log 文件
从库开启同步之后,当 IO 线程从主库中成功下载 relay_log 后,保存到对应的数据库的data目录中(Linux系统中,默认在 /var/lib/mysql 目录)。如本例的my.cnf中配置的relay_log=mysql-relay-bin,则下载后的relay_log文件名为mysql-relay-bin。如下:
-rw-r----- 1 systemd-coredump input 214 Sep 27 14:33 mysql-relay-bin.000001
-rw-r----- 1 systemd-coredump input 14755718 Sep 30 09:55 mysql-relay-bin.000002
-rw-r----- 1 systemd-coredump input 58 Sep 27 14:33 mysql-relay-bin.index
6.3.2 查看工作线程
通过 show processlist 查看线程列表。如下:
6.3.3 从库的状态
通过 show replica status \G,查看从库的状态。如下:
mysql> show replica status \G;
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event # I/O 线程的状态
Source_Host: 192.168.131.6 # master的ip
Source_User: slave # 同步的账号
Source_Port: 3306 # master数据库的端口
Connect_Retry: 60 # 如果与主服务器的连接失败,I/O 线程在重试连接之前将等待的秒数(默认为 60 秒)
Source_Log_File: mysql-bin.000001 # 表示 I/O 线程当前正在读取的主服务器的二进制日志文件名
Read_Source_Log_Pos: 135 # 表示 I/O 线程当前正在读取的主服务器的二进制日志文件的位置
Relay_Log_File: mysql-relay-bin.000001 # 表示 SQL 线程当前正在处理的中继日志文件名
Relay_Log_Pos: 122 # 表示 SQL 线程当前正在处理的中继日志文件的位置
Relay_Source_Log_File: mysql-bin.000001 # 表示 SQL 线程最近执行事件的主服务器的二进制日志文件名
Replica_IO_Running: Yes # 从库 I/O 线程的状态,Yes表示正在运行;如果为No,表示 I/O 线程已停止
Replica_SQL_Running: Yes # 从库 SQL 线程的状态,Yes表示正在运行;如果为No,表示 SQL 线程已停止
Exec_Source_Log_Pos: 14763218
Relay_Log_Space: 14763172
Until_Condition: None
Source_SSL_Allowed: No
Seconds_Behind_Source: 0 # 表示从库复制延迟的时间(以秒为单位),即从库 SQL 线程处理的事件落后于主库二进制日志中事件的时间
Source_SSL_Verify_Server_Cert: No
Source_Server_Id: 100
Source_UUID: d661d387-172b-11eb-8a28-fa163e9f832c
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
Source_Retry_Count: 86400
Last_IO_Errno: 0 # 如果 I/O 线程遇到错误,此处显示错误信息
Last_IO_Error: # 如果 I/O 线程遇到错误,此处显示错误信息
Last_SQL_Errno: 0 # 如果 SQL 线程遇到错误,此处显示错误信息
Last_SQL_Error: # 如果 SQL 线程遇到错误,此处显示错误信息
# 省略其他
小结
以上为本篇分享的全部内容,以下做一个小结:
1)MySQL主从复制的流程包含:两个日志()和三个线程(msaster);
1.1)两个日志:二进制日志binlog和relay log日志;
1.2)三个线程:
Master的 dump 线程:发生binlog日志到slave;
Slave的 IO 线程:请求Master的binlog日志,写入到Slave的relay log中;
Slave的 SQL 线程:将Slave中的relay log,重放写入到Slave库;
2)MySQL主从复制的配置;
2.1)Master库:
a)配置文件中设置server_id和log_bin,开启二进制日志写入。当数据库有更新操作时,自动写入到二进制日志;
b)创建同步账号;
c)通过 show master status \G,查看同步文件名及位置;
2.2)Slave库:
a)配置文件中设置server_id和relay_log。server_id 唯一,relay_log 可以不设置;
b)通过Master提供的同步账号、IP、同步文件名及位置,配置Master信息;
c)通过 start replica 开启复制同步;
3)不同的 MySQL 版本,配置会有差异。如果有报错,根据具体错误进行修改调整;如果没有报错,需要确认配置项是否正确;
如设置同步的数据库,在MySQL8,binlog_do_db一条命令只能设置一个库。对于多个库同步,如果中间用逗号隔开,会导致匹配不了。
关于本篇内容你有什么自己的想法或独到见解,欢迎在评论区一起交流探讨下吧。