MySQL主从同步配置-编程学习网

Mysql 主从同步原理

在这里插入图片描述
就是基于数据库里面的那个 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.主从配置

先规划一下：
在这里插入图片描述

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

我们都使用 Docker 创建，在同一台虚拟机上面，保证它们的ip相同，端口号不同就行了

2.1 准备主数据库

step1:在docker中创建并启动MySQL主服务器

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

第三行，把容器内部的配置文件映射到宿主机上
第四行，把数据记录也映射到宿主机上

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

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

配置如下内容:

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

重启MySQL容器:

docker restart mysql-master

binlog格式说明：

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

这个时候我们用数据库连接工具，比如：SQLyog、低版本的一些 Navicat 去连接数据库的话，会发现连接不上。

在这里插入图片描述
其实这个原因是因为 Mysql 8.0.* 密码验证的问题。我们进入 Mysql 容器内部改一下它验证密码的方式就可以了

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

#进入容器：env LANG=C.UTF-8 避免容器中显示中文乱码docker exec -it 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 'atguigu_slave'@'%';-- 设置密码ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';-- 授予复制权限GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';-- 刷新权限FLUSH PRIVILEGES;

step5:主机中查询master状态

SHOW MASTER STATUS;

（上面执行命令，千万别把分号复制掉了，不然执行不了）

记下File和Position的值。执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化。
在这里插入图片描述

这个值最好先复制粘贴出来，一会儿我们配置从数据库的时候用得到。

执行完过后，我们连接数据库，在 mysql库下面的 user 表里面找到我们刚才增加的用户 slave .可以看到它什么权限都没有，只有一条远程复制的权限
在这里插入图片描述

到这儿，我们的主 Master数据库 算是准备完毕

2.2、准备从数据库

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

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

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

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

配置如下内容：

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

重启MySQL容器：

docker restart mysql-slave1

我第一次执行出现了一点儿小问题，mysql-slave1莫名退出了，看了一下 mysql-slave1 启动失败的原因：
在这里插入图片描述
好像是出现了一个权限的问题。

我删除了这个容器的 id过后，在执行命令上加了一行： --privileged=true。这样：

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

就启动成功了…

然后我停止并且又删掉了这个加了一行命令才成功的容器，用之前的命令。之前的命令又成功了，很奇怪。

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

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

（我是 2023年5月份左右实验的。现在mysql 8.* 版本应该不需要重新修改这个 mysql 密码校验方式了）

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

执行以下SQL操作：

CHANGE MASTER TO MASTER_HOST='192.168.31.206', MASTER_USER='slave1',MASTER_PASSWORD='123456', MASTER_PORT=3306,MASTER_LOG_FILE='binlog.000004',MASTER_LOG_POS=1126;

注意这里的 binlog.000004 和 1126 两个值，就是上面主机 master show status;
过后显示的值

2.3 启动主从同步

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

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

在这里插入图片描述
两个关键进程：下面两个参数是这样，则说明主从配置成功！

2.4、实现主从同步

接下来你自己去主数据库创建一张表，插入一条数据就行了

CREATE DATABASE user;
USE user;
CREATE TABLE t_user (
id BIGINT AUTO_INCREMENT,
uname VARCHAR(30),
PRIMARY KEY (id)
);
INSERT INTO t_user(uname) VALUES(‘Jarvis’);
INSERT INTO t_user(uname) VALUES(@@hostname);

2.5、停止和重置

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

典型的错误例如：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’

解决方案：

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

来源地址：https://blog.csdn.net/weixin_43582499/article/details/130474280

文章详情

MySQL主从同步配置