Lou Chen

MySQL主从复制

Lou.Chen2022年11月2日
大约 6 分钟

1.为什么要主从复制

  • 在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。

  • 做数据的热备

  • 架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

2.什么是MySQL主从复制

主从复制是指将主数据库的DDL和DML操作通过二进制日志binlog传到从数据库上，然后在从数据库上对这些日志进行重新执行，从而使从数据库和主数据库的数据保持一致。

  • DDL(data definition language):数据定义语言，create、alter、drop等
  • DML(data manipulation language):数据操作语言，select、update、insert、delete等
  • DCL(data control language):数据控制语言，grant、deny、revoke、commit、savepoint、rollback等
  • binlog:用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中

3.MySQL主从复制原理

  • MySql主库在事务提交时会把数据变更作为事件记录在二进制日志Binlog中；
  • 主库推送二进制日志文件Binlog中的事件到从库的中继日志Relay Log中，之后从库根据中继日志重做数据变更操作，通过逻辑复制来达到主库和从库的数据一致性；
  • MySql通过三个线程来完成主从库间的数据复制，其中Binlog Dump线程跑在主库上，I/O线程和SQL线程跑着从库上；
  • 当在从库上启动复制时，首先创建I/O线程连接主库，主库随后创建Binlog Dump线程读取数据库事件并发送给I/O线程，I/O线程获取到事件数据后更新到从库的中继日志Relay Log中去，之后从库上的SQL线程读取中继日志Relay Log中更新的数据库事件并应用

​ image-20221013104859415

4.主从实例搭建

4.1 主实例搭建

  • 运行mysql主实例：
docker run -p 3307:3306 --name mysql-master \
-v /mydata/mysql-master/log:/var/log/mysql \
-v /mydata/mysql-master/data:/var/lib/mysql \
-v /mydata/mysql-master/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=root  \
-d mysql:5.7
  • 在mysql的配置文件夹/mydata/mysql-master/conf中创建一个配置文件my.cnf并添加如下内容：
[mysqld]
## 设置server_id，同一局域网中需要唯一
server_id=101 
## 指定不需要同步的数据库名称
binlog-ignore-db=mysql  
## 开启二进制日志功能
log-bin=mall-mysql-bin  
## 设置二进制日志使用内存大小（事务）
binlog_cache_size=1M  
## 设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed  
## 二进制日志过期清理时间。默认值为0，表示不自动清理。
expire_logs_days=7  
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062

一些配置说明：

  • log-bin：同步的日志路径及文件名，一定注意这个目录要是 MySQL 有权限写入的。

  • binlog-do-db：要同步的数据库名，当从机连上主机后，只有这里配置的数据库才会被同步，其他的不会被同步。

    • 如不配置，则会同步所有。

  • server-id: MySQL 在主从环境下的唯一标志符，给个任意数字，注意不能和从机重复。

  • 修改完配置后重启实例：
docker restart mysql-master
  • 进入容器
docker exec -it mysql-master /bin/bash
  • 登录MySQL客户端
mysql -uroot -proot
  • 创建用户 slave，访问ip无限制，密码为123456，拥有全部库和表的权限
CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

4.2 从实例搭建

  • 运行mysql主实例：
docker run -p 3308:3306 --name mysql-slave \
-v /mydata/mysql-slave/log:/var/log/mysql \
-v /mydata/mysql-slave/data:/var/lib/mysql \
-v /mydata/mysql-slave/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=root  \
-d mysql:5.7
  • 在mysql的配置文件夹/mydata/mysql-master/conf中创建一个配置文件my.cnf并添加如下内容：
[mysqld]
## 设置server_id，同一局域网中需要唯一
server_id=102
## 指定不需要同步的数据库名称
binlog-ignore-db=mysql  
## 开启二进制日志功能，以备Slave作为其它数据库实例的Master时使用
log-bin=mall-mysql-slave1-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=mall-mysql-relay-bin  
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1  
## slave设置为只读（具有super权限的用户除外）
read_only=1
  • 修改完配置后重启实例：
docker restart mysql-slave

4.3 主从数据库连接

4.3.1 连接**主数据库客户端**
  • 查看主数据库状态：
show master status;
20221013-142210

记住以下内容：

File：mall-mysql-bin.000001

Position ：617

4.3.2 连接从数据库客户端
  • 连接从数据库客户端后执行以下主从配置命令：
change master to master_host='10.0.4.14', master_user='slave', master_password='123456', master_port=3307, master_log_file='mall-mysql-bin.000001', master_log_pos=617, master_connect_retry=30;

主从复制命令参数说明：

  • master_host：主数据库的IP地址；
  • master_port：主数据库的运行端口；
  • master_user：在主数据库创建的用于同步数据的用户账号；
  • master_password：在主数据库创建的用于同步数据的用户密码；
  • master_log_file：指定从数据库要复制数据的日志文件，通过查看主数据的状态，获取File参数；
  • master_log_pos：指定从数据库从哪个位置开始复制数据，通过查看主数据的状态，获取Position参数；
  • master_connect_retry：连接失败重试的时间间隔，单位为秒。
image-20221013142912143
  • 查看主从同步状态
show slave status \G;
image-20221013143050555

从上图可以发现还没有开启同步

  • 开启主从同步：
start slave;
image-20221013143238243

从上图可以发现主从同步已开始

4.3.3 主从同步测试
  • 连接主数据库客户端后创建数据库test_db
create database test_db;
  • 连接从数据库客户度后查看数据库
show database;
image-20221013143510151

从上图可以发现test_db数据库已同步

更多可以参考：