mysql主从同步

原理

• 概述

将主库的数据变更同步到从库，从而保证主库和从库数据一致
数据备份，失败迁移，读写分离，降低单库读写压力

• 原理
  

主数据库设置

docker run --restart=always --name mysql-master -p 3306:3306 -v /home/apps/mysql-master/conf:/etc/mysql/conf.d -v /home/apps/mysql-master/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0.16 
1

• –restart=always
  开启容器随docker自启动
• -v /home/apps/mysql-master/conf:/etc/mysql/conf.d
  容器内的/etc/mysql/conf.d目录映射到主机目录/home/apps/mysql-master/conf，因为这个目录是配置mysql的，所以需要映射出来

给主mysql数据库设置my.cnf

进入主mysql配置目录，并创建my.cnf

cd /home/apps/mysql-master/conf

vim my.cnf

[mysqld]
# 服务器唯一id，每台服务器的id必须不同，如果配置其他从机，注意修改id
server-id=1
# 日志格式调整为STATEMENT
binlog_format=STATEMENT
# 只同步这个名称的数据库
# binlog-do-db=test
# 跳过域名缓存，否则连接会很慢
skip-host-cache
skip-name-resolve
1
2
3
4
5
6
7
8
9
10

重启主数据库容器：docker restart mysql-master

进入主数据库容器的mysql

docker exec -it xxx /bin/bash

mysql -u root -p

并执行以下操作

-- 创建slave用户
CREATE USER 'slave'@'%';
-- 设置密码
ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
-- 授予复制权限
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
-- 刷新权限
FLUSH PRIVILEGES;
 
-- 主机中查询master状态
SHOW MASTER STATUS;
 
-- 在主机上执行。功能说明：删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件。（同步有问题时使用）
reset master;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

从数据库中需要用到

从数据库设置

docker run --restart=always --name mysql-slave1 -p 3306:3306 -v /home/apps/mysql-slave1/conf:/etc/mysql/conf.d -v /home/apps/mysql-slave1/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0.16 
1

这里mysql的3306端口映射为主机的3307端口

进入主mysql配置目录，并创建my.cnf

[mysqld]
# 服务器唯一id，每台服务器的id必须不同，如果配置其他从机，注意修改id
server-id=10
# 中继日志名，默认xxxxxxxxxxxx-relay-bin
# relay-log=relay-bin
# 跳过域名缓存，否则连接会很慢
skip-host-cache
skip-name-resolve
1
2
3
4
5
6
7
8

如果主数据库存在有database，需要同步，那么先要把主数据库数据导出，再执行导入到从数据库，要求保证主从数据库均存在相同的数据库和数据，这样才能进行同步
进入从数据库容器的mysql，执行以下内容：

-- 配置从数据库同步
CHANGE MASTER TO MASTER_HOST='主数据库IP', MASTER_USER='slave',MASTER_PASSWORD='123456', MASTER_PORT=3306,MASTER_LOG_FILE='binlog.000003',MASTER_LOG_POS= 1234;
 
-- 开启数据库同步
START SLAVE;
 
-- 检查同步状态
SHOW SLAVE STATUS
-- 或者
SHOW SLAVE STATUS\G
 
-- 停止同步
stop slave; 
 
-- 在从机上执行。功能说明：用于删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件。
reset slave all;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

检查同步状态：SHOW SLAVE STATUS\G

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

这2项均为Yes，创建成功。

注意

Slave_IO_Running Slave_SQL_Running保证这2个都是Yes，其中一个为No都不能进行同步

在开启同步时，需要保证主从数据库的数据库版本、数据库和数据是一致的，数据库是否忽略大小写问题等。