一主一从搭建
一主一从
概述
主从复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中,然后在从库上对这些日志重新执行(也叫重做),从而使得从库和主库的数据保持同步。
MySQL支持一台主库同时向多台从库进行复制, 从库同时也可以作为其他从服务器的主库,实现链状复制。
优点主要包含以下三个方面
-
主库出现问题,可以快速切换到从库提供服务。
-
实现读写分离,降低主库的访问压力。
-
可以在从库中执行备份,以避免备份期间影响主库服务
原理
从上图来看,复制分成三步:
-
Master 主库在事务提交时,会把数据变更记录在二进制日志文件 Binlog 中。
-
从库通过IOthread读取主库的二进制日志文件 binlog ,写入到从库的中继日志 Relay Log 中
-
slave通过SQLThread读取中继日志中的事件,将改变反映它自己的数据。
基于Docker一主一从搭建
以下教程使用docker启动两个不同端口号的MySQL作为主从复制。使用13306作为主机,使用13307作为从机。使用普通的Linux安装MySQL本质是一样的,就是通更改MySQL的配置文件,实现主从复制。
准备工作
创建主库配置文件地址
mkdir -p /lx/mysql/master/conf
创建主库数据存储地址
mkdir -p /lx/mysql/master/data
创建从配置文件地址
mkdir -p /lx/mysql/slave1/conf
创建从库数据存储地址
mkdir -p /lx/mysql/slave1/data
在/lx 目录下创建docker-compose-mysql.yml文件内容如下
version: '3'
services:
lx-mysql-master:
image: mysql:8.0.29
restart: always
environment:
MYSQL_ROOT_PASSWORD: 123456
MYSQL_PASSWORD: 123456
ports:
- 13306:3306
volumes:
- "/lx/mysql/master/conf:/etc/mysql/conf.d"
- "/lx/mysql/master/data:/var/lib/mysql"
container_name: "lx-mysql-master"
lx-mysql-slave1:
image: mysql:8.0.29
environment:
MYSQL_ROOT_PASSWORD: 123456
MYSQL_PASSWORD: 123456
ports:
- 13307:3306
volumes:
- "/lx/mysql/slave1/conf:/etc/mysql/conf.d"
- "/lx/mysql/slave1/data:/var/lib/mysql"
container_name: "lx-mysql-slave1"
主机搭建
创建主机配置文件
在 /lx/mysql/master/conf 目录下创建my.cnf文件
vim my.cnf
输入以下内容
[mysqld]
# 设置授权访问的加密策略
default_authentication_plugin=mysql_native_password
# 主从复制配置
# 服务器ID,保证整个集群环境中唯一,取值范围:1 – 2^32-1,和主库不一样即可
server-id=202404
# 启用二进制日志master-bin是自定义的,保证唯一即可
log-bin=master-bin
# 设置logbin格式:STATEMENT(同步SQL脚本) / ROW(同步数据行) / MIXED(混合同步)
binlog_format=MIXED
# 设置日志最长保存时间
expire_logs_days=30
# 0-读写,1-只读
read-only=0
# 设置忽略同步的数据库 可以配置多个
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
# 设置需要同步的数据库
#binlog-do-db=pmonitor
#binlog-do-db=ucoal
log-bin=master-bin:启用二进制日志,并指定二进制日志文件的名称前缀为 master-bin。二进制日志用于记录数据库的更改操作,用于主从复制、恢复数据等操作。
这些二进制日志文件通常存储在指定的数据目录中,该目录在 MySQL 的配置文件中指定。每个二进制日志文件都以指定的前缀命名,然后跟随一个数字序列,如 master-bin.000001、master-bin.000002 等。
binlog_format=ROW
STATEMENT:基于 SQL 语句的复制。在此模式下,MySQL 将每个执行的 SQL 语句记录到二进制日志中,从而使得从库可以通过执行相同的 SQL 语句来复制主库上的操作。这种模式在某些情况下可能会导致主从复制的不一致性,因为在某些情况下,同一条 SQL 语句在不同的服务器上执行可能会产生不同的结果。例如函数、@@hostname
ROW:基于行的复制。在此模式下,MySQL 将每次数据修改的行变化记录到二进制日志中,从而确保从库上的数据与主库完全一致。这种模式通常比基于 SQL 语句的复制更可靠,但同时也会占用更多的磁盘空间。
MIXED:混合模式。在此模式下,MySQL 会根据具体的情况自动选择使用 STATEMENT 或 ROW 模式来记录二进制日志。通常情况下,MySQL 会尽可能使用 STATEMENT 模式来提高性能,但在遇到无法安全地使用 STATEMENT 模式的情况下,会自动切换到 ROW 模式来确保数据的一致性。
默认情况下,MySQL 8 的 binlog_format 模式是 ROW,这是因为 ROW 模式能够提供最高的数据一致性和可靠性,适用于大多数主从复制的场景。
使用docker-compose启动主机
docker-compose -f docker-compose-mysql.yml up -d lx-mysql-master
使用连接工具连接主机
账号:root
密码:123456
创建从机连接用户
创建slave1用户,并设置密码,该用户可在任意主机连接该MySQL服务
CREATE USER 'slave1'@'%' IDENTIFIED WITH mysql_native_password BY '123456' ;
为 ‘slave1’@‘%’ 用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%';
查看二进制日志 (binary log) 的状态信息
SHOW MASTER STATUS;
返回信息说明:
- File:当前正在写入的二进制日志文件名。(和配置文件中启用二进制日志的配置有关系。配置的什么文件名就以什么开头)
- Position:当前二进制日志文件中正在写入的位置(以字节为单位)。
- Binlog_Do_DB:指定哪些数据库的操作会被记录到二进制日志中。(就是配置文件中设置需要同步的数据库的配置,不配置就是除了忽略同步的数据库的其他数据库)
- Binlog_Ignore_DB:指定哪些数据库的操作不会被记录到二进制日志中。(就是设置忽略同步的数据库配置)
通过语句查看二进制日志状态之后就不要在操作主库了,下面搭建从机数据库
从机搭建
在 /lx/mysql/slave1/conf/目录下创建my.cnf文件
vim my.cnf
输入以下内容
[mysqld]
# 设置授权验证的加密策略
default_authentication_plugin=mysql_native_password
# 服务器ID,保证整个集群环境中唯一,取值范围:1 – 2^32-1,和主库不一样即可
server-id=2024041
# 启用中继日志 这两个key的值也是自定义的,保证唯一即可
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
# 设置日志最长保存时间
expire_logs_days=30
# 0-读写,1-只读;slave设置为只读(具有super权限的用户除外)
read_only=1
# 开启二进制日志功能,以便本机可以作为其它Slave的Master时使用
log-bin=slave-bin
# 设置logbin格式:STATEMENT(同步SQL脚本) / ROW(同步数据行) / MIXED(混合同步)
binlog_format=MIXED
# 1表示slave将复制事件写进自己的二进制日志,以便从服务器也可以作为主服务器向其他从服务器复制数据
log_slave_updates=1
# 设置允许复制的库
# replicate-do-db=pmonitor-cloud
# replicate-do-db=ucoal
# 设置忽略复制的库
# replicate-ignore-db=mysql
# replicate-ignore-db=information_schema
# replicate-ignore-db=performance_schema
启动从机
docker-compose -f docker-compose-mysql.yml up -d lx-mysql-slave1
使用连接工具进行连接
账号:root
密码:123456
登录从机-设置主库配置
change master to master_host='192.168.0.106',
master_port=13306,
master_user='slave1',
master_password='123456',
master_log_file='master-bin.000004',
master_log_pos=157;
参数说明
- TO: 指示接下来将要指定新的复制源的参数。
- SOURCE_HOST=‘192.168.0.106’: 指定新的复制源的主机地址为 192.168.0.106。这表示复制将从该主机上的 MySQL 服务器进行。
- master_port=13306。如果是默认的主机端口号不指定也可以
- SOURCE_USER=‘slave1’: 指定连接到新的复制源所使用的用户名为 slave1。这是用于在新的复制源上建立连接所需的用户名。
- SOURCE_PASSWORD=‘123456’: 指定连接到新的复制源所使用的密码为 123456。这是与上述用户名相对应的密码。
- SOURCE_LOG_FILE=‘master-bin.000004’: 指定在新的复制源上复制操作的日志文件名为 master-bin.000004。这表示复制将从此日志文件开始。
- SOURCE_LOG_POS=157: 指定在新的复制源上复制操作的起始位置为日志文件中的偏移量 157。这表示复制将从此位置开始。
从机开启同步操作
start replica ; #8.0.22之后
start slave ; #8.0.22之前
查看主从同步状态
show replica status ; #8.0.22之后
show slave status ; #8.0.22之前
其实是两个SQL语句都可执行。
只不过是show replica status;查询结果是需要注意Replica_IO_Running和Replica_SQL_Running
show slave status 查询结果需要注意 Slave_IO_Running和Slave_SQL_Running
当Replica_IO_Running和Replica_SQL_Running都出现Yes的时候说明主从同步成功
或者
Slave_IO_Running和Slave_SQL_Running都出现Yes的时候说明主从同步成功
搭建失败问题排查
如果Replica_IO_Running和Replica_SQL_Running有一个出现非YES的情况说明同步失败。可以检查是否连接主机的IP、端口号、主机服务器的防火墙、二进制文件名称等写错了。下面我们模拟搭建失败应该处理。
如果出现Connecting
从查询的结果看是第四行主机的Source_Port端口号写错了,Source_Host地址也写错了。
- 先停止从机的主从复制
stop slave;
- 更改从机连接的配置, 添加 master_port=13306 指定端口号 因为是模拟的问题。因为博主第一次搭建是没有指定端口号才出现了开启同步数据失败。
change master to master_host='192.168.0.106', master_port=13306, master_user='slave1', master_password='123456', master_log_file='master-bin.000004', master_log_pos=157;
- 再次开启从机的主从复制
start replica ;
- 再次查询主同步状态
show replica status ;
验证主从复制的结果
主库上创建数据库、表和插入数据
主机创建库;
create database ms_db;
主机创建表
CREATE TABLE `dict` (
`dict_id` int NOT NULL COMMENT '主键',
`dict_group` varchar(20) DEFAULT NULL COMMENT '分组',
`dict_name` varchar(20) DEFAULT NULL COMMENT '名称',
`dict_code` varchar(20) DEFAULT NULL COMMENT '编码',
PRIMARY KEY (`dict_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
插入数据
INSERT INTO `dict`(`dict_id`, `dict_group`, `dict_name`, `dict_code`) VALUES (1, 'model', '小型', '10');
查看从机的数据
可关注公众号 佳哇程序员
或直接扫码关注