Docker搭建MySQL主从同步（全网最全保姆教程）

今天药忘吃喽~ 2023-09-23 16:35 137阅读 0赞

# 第03章 MySQL主从同步 #

## 1、MySQL主从同步原理 ##

![image-1669398007375][]

**基本原理：**

slave会从master读取binlog来进行数据同步

**具体步骤：**

*  `step1：`master将数据改变记录到`二进制日志（binary log）`中。
 *  `step2：` 当slave上执行 `start slave` 命令之后，slave会创建一个 `IO 线程`用来连接master，请求master中的binlog。
 *  `step3：`当slave连接master时，master会创建一个 `log dump 线程`，用于发送 binlog 的内容。在读取 binlog 的内容的操作中，会对主节点上的 binlog 加锁，当读取完成并发送给从服务器后解锁。
 *  `step4：`IO 线程接收主节点 binlog dump 进程发来的更新之后，保存到 `中继日志（relay log）` 中。
 *  `step5：`slave的`SQL线程`，读取relay log日志，并解析成具体操作，从而实现主从操作一致，最终数据一致。

## 2、一主多从配置 ##

服务器规划：使用`docker`方式创建，`主从服务器IP一致，端口号不一致`

![image-1669398025764][]

*  主服务器：容器名`hitd-mysql-master`，端口`3306`
 *  从服务器：容器名`hitd-mysql-slave1`，端口`3307`
 *  从服务器：容器名`hitd-mysql-slave2`，端口`3308`

\*\*注意：\*\*如果此时防火墙是开启的，`则先关闭防火墙，并重启docker`，否则后续安装的MySQL无法启动

#关闭docker
    systemctl stop docker
    #关闭防火墙
    systemctl stop firewalld
    #启动docker
    systemctl start docker

### 2.1、准备主服务器 ###

*  **step1：在docker中创建并启动MySQL主服务器：**`端口3306`
 *  **: 前为宿主机**

docker run -d \
    -p 3306:3306 \
    -v /hitd/mysql/master/conf:/etc/mysql/conf.d \
    -v /hitd/mysql/master/data:/var/lib/mysql \
    -e MYSQL_ROOT_PASSWORD=123456 \
    --name hitd-mysql-master \
    mysql:8.0.29

*  **step2：创建MySQL主服务器配置文件：**

默认情况下MySQL的binlog日志是自动开启的，可以通过如下配置定义一些可选配置

vim /hitd/mysql/master/conf/my.cnf

配置如下内容

[mysqld]
    # 服务器唯一id，默认值1
    server-id=1
    # 设置日志格式，默认值ROW
    binlog_format=STATEMENT
    # 二进制日志名，默认binlog
    # log-bin=binlog
    # 设置需要复制的数据库，默认复制全部数据库
    #binlog-do-db=mytestdb
    # 设置不需要复制的数据库
    #binlog-ignore-db=mysql
    #binlog-ignore-db=infomation_schema

重启MySQL容器

docker restart hitd-mysql-master

`binlog格式说明：`

*  binlog\_format=STATEMENT：日志记录的是主机数据库的`写指令`，性能高，但是now()之类的函数以及获取系统参数的操作会出现主从数据不同步的问题。
 *  binlog\_format=ROW（默认）：日志记录的是主机数据库的`写后的数据`，批量操作时性能较差，解决now()或者 user()或者 @@hostname 等操作在主从机器上不一致的问题。
 *  binlog\_format=MIXED：是以上两种level的混合使用，有函数用ROW，没函数用STATEMENT，但是无法识别系统变量

`binlog-ignore-db和binlog-do-db的优先级问题：`

![image-1669398042516][]

*  **step3：使用命令行登录MySQL主服务器：**

#进入容器：env LANG=C.UTF-8 避免容器中显示中文乱码
    docker exec -it hitd-mysql-master env LANG=C.UTF-8 /bin/bash
    #进入容器内的mysql命令行
    mysql -uroot -p
    #修改默认密码校验方式 
    ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

*  **step4：主机中创建slave用户：**

-- 创建slave用户
    CREATE USER 'hitd_slave'@'%';
    -- 设置密码
    ALTER USER 'hitd_slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
    -- 授予复制权限
    GRANT REPLICATION SLAVE ON *.* TO 'hitd_slave'@'%';
    -- 刷新权限
    FLUSH PRIVILEGES;

*  **step5：主机中查询master状态：**

执行完此步骤后`不要再操作主服务器MYSQL`，防止主服务器状态值变化

SHOW MASTER STATUS;

记下`File`和`Position`的值（binlog.000003：1348）。执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化。

![image-1669398059341][]

### 2.2、准备从服务器 ###

可以配置多台从机slave1、slave2…，这里以配置slave1为例

*  **step1：在docker中创建并启动MySQL从服务器：**`端口3307`

docker run -d \
    -p 3307:3306 \
    -v /hitd/mysql/slave1/conf:/etc/mysql/conf.d \
    -v /hitd/mysql/slave1/data:/var/lib/mysql \
    -e MYSQL_ROOT_PASSWORD=123456 \
    --name hitd-mysql-slave1 \
    mysql:8.0.29

*  **step2：创建MySQL从服务器配置文件：**

vim /hitd/mysql/slave1/conf/my.cnf

配置如下内容：

[mysqld]
    # 服务器唯一id，每台服务器的id必须不同，如果配置其他从机，注意修改id
    server-id=2
    # 中继日志名，默认xxxxxxxxxxxx-relay-bin
    #relay-log=relay-bin

重启MySQL容器

docker restart hitd-mysql-slave1

*  **step3（可选）：使用命令行登录MySQL从服务器：**

#进入容器：
    docker exec -it hitd-mysql-slave1 env LANG=C.UTF-8 /bin/bash
    #进入容器内的mysql命令行
    mysql -uroot -p
    #修改默认密码校验方式 
    ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

*  **step4：在从机上配置主从关系：**

在**从机**上执行以下SQL操作,此处所需的值对应第一步master数据库中所操作记录的值

CHANGE MASTER TO MASTER_HOST='192.168.188.100', 
    MASTER_USER='hitd_slave',MASTER_PASSWORD='123456', MASTER_PORT=3306,
    MASTER_LOG_FILE='binlog.000003',MASTER_LOG_POS=1348;

MASTER\_HOST对应master的IP地址(不知道IP的 `ifconfig` 查看自己IP)

MASTER\_USER对应master数据库所创建给从库使用的用户

MASTER\_PASSWORD对应master数据库所创建给从库使用的用户密码

MASTER\_LOG\_FILE对应指令 master数据库执行`SHOW MASTER STATUS;` 命令的第一项值

MASTER\_LOG\_POS对应指令 master数据库执行`SHOW MASTER STATUS;` 命令的第二项值

### 补充 ###

可对照上述操作将映射到宿主机的端口改为3308再创建一个hitd-mysql-slave2

docker run -d \
    -p 3308:3306 \
    -v /hitd/mysql/slave2/conf:/etc/mysql/conf.d \
    -v /hitd/mysql/slave2/data:/var/lib/mysql \
    -e MYSQL_ROOT_PASSWORD=123456 \
    --name hitd-mysql-slave2 \
    mysql:8.0.29

vim /hitd/mysql/slave2/conf/my.cnf

[mysqld]
    # 服务器唯一id，每台服务器的id必须不同，如果配置其他从机，注意修改id
    server-id=3

docker restart hitd-mysql-slave2

==>可选<==
    #进入容器：
    docker exec -it hitd-mysql-slave2 env LANG=C.UTF-8 /bin/bash
    #进入容器内的mysql命令行
    mysql -uroot -p
    #修改默认密码校验方式 ShardingSphere使用的是mysql_native_password
    ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

CHANGE MASTER TO MASTER_HOST='192.168.188.100', 
    MASTER_USER='hitd_slave',MASTER_PASSWORD='123456', MASTER_PORT=3306,
    MASTER_LOG_FILE='binlog.000003',MASTER_LOG_POS=1348;

### 2.3、启动主从同步 ###

启动从机的复制功能，执行SQL：

START SLAVE;
    -- 查看状态（不需要分号）
    SHOW SLAVE STATUS\G

\*\*两个关键进程：\*\*下面两个参数都是Yes，则说明主从配置成功！

![image-1669398087071][]

### 2.4、实现主从同步 ###

在主机中执行以下SQL，在从机中查看数据库、表和数据是否已经被同步

CREATE DATABASE db_user;
    USE db_user;
    CREATE TABLE t_user (
     id BIGINT AUTO_INCREMENT,
     uname VARCHAR(30),
     PRIMARY KEY (id)
    );
    INSERT INTO t_user(uname) VALUES('zhang3');
    INSERT INTO t_user(uname) VALUES(@@hostname);

#### 效果图 ####

![image-1669398097145][]

![image-1669398103151][]

### 2.5、停止和重置 ###

需要的时候，可以使用如下SQL语句

-- 在从机上执行。功能说明：停止I/O 线程和SQL线程的操作。
    stop slave; 
    
    -- 在从机上执行。功能说明：用于删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件。
    reset slave;
    
    -- 在主机上执行。功能说明：删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件。
    -- 用于第一次进行搭建主从库时，进行主库binlog初始化工作；
    reset master;

### **2.6、常见问题** ###

#### 问题1 ####

启动主从同步后，常见错误是`Slave_IO_Running： No 或者 Connecting` 的情况，此时查看下方的 `Last_IO_ERROR`错误日志，根据日志中显示的错误信息在网上搜索解决方案即可

![image-1669398150523][]

**典型的错误例如：**`Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size'`

**解决方案：**

-- 在从机停止slave
    SLAVE STOP;
    
    -- 在主机查看mater状态
    SHOW MASTER STATUS;
    -- 在主机刷新日志
    FLUSH LOGS;
    -- 再次在主机查看mater状态（会发现File和Position发生了变化）
    SHOW MASTER STATUS;
    -- 修改从机连接主机的SQL，并重新连接即可

#### 问题2 ####

启动docker容器后提示 `WARNING: IPv4 forwarding is disabled. Networking will not work.`

![image-1669398159664][]

此错误，虽然不影响主从同步的搭建，但是如果想从远程客户端通过以下方式连接docker中的MySQL则没法连接

C:\Users\administrator>mysql -h 192.168.100.201 -P 3306 -u root -p

**解决方案：**

#修改配置文件：
    vim /usr/lib/sysctl.d/00-system.conf
    #追加
    net.ipv4.ip_forward=1
    #接着重启网络
    systemctl restart network

[image-1669398007375]: https://img-blog.csdnimg.cn/img_convert/74b59d5ea58315435f2b1cad3853ca6f.png
[image-1669398025764]: https://img-blog.csdnimg.cn/img_convert/41027c7b8531e5794881c29153b37556.png
[image-1669398042516]: https://img-blog.csdnimg.cn/img_convert/6b3fa202ccfca92865a8777ac4bbcdc3.png
[image-1669398059341]: https://img-blog.csdnimg.cn/img_convert/da98cebf9ba1cb60110958a330553be9.png
[image-1669398087071]: https://img-blog.csdnimg.cn/img_convert/3ee242cbfdb38bc1a0665d2dc7134710.png
[image-1669398097145]: https://img-blog.csdnimg.cn/img_convert/1f07a4d556c35aea1db23cd192db4d96.png
[image-1669398103151]: https://img-blog.csdnimg.cn/img_convert/6d302dab53bbce777a6f142b1ba49d44.png
[image-1669398150523]: https://img-blog.csdnimg.cn/img_convert/e58ff7fbb94c1f4306f72a0316858f55.png
[image-1669398159664]: https://img-blog.csdnimg.cn/img_convert/f13de3793468c5915f5200f75d5a3e92.png