Mysql 配置主从复制
推荐编译安装，但是太麻烦了，所以直接 docker 安装。
参考
https://blog.csdn.net/abcde123_123/article/details/106244181
https://www.cnblogs.com/songwenjie/p/9371422.html
拉取镜像
推荐使用 mysql 5.7
docker pull mysql:5.7.39
启动两个服务
https://zhuanlan.zhihu.com/p/342836513
创建数据目录
# 主库
mkdir -p /opt/mysql-master/log
mkdir -p /opt/mysql-master/conf
mkdir -p /opt/mysql-master/data
# 从库
mkdir -p /opt/mysql-slave/log
mkdir -p /opt/mysql-slave/conf
mkdir -p /opt/mysql-slave/data
先启动主库
先拷贝一份mysql文件
# 启动临时的mysql服务
docker run -d \
> -p 3306:3306 \
> --name mysql \
> -e MYSQL_ROOT_PASSWORD=123 \
> mysql:5.7
# 复制配置文件和目录
docker cp mysql:/etc/my.cnf /opt/mysql-master
docker cp mysql:/etc/mysql/conf.d /opt/mysql-master/conf
docker cp mysql:/etc/mysql/mysql.conf.d /opt/mysql-master/conf
# 删除服务
docker stop mysql && docker rm mysql
启动
docker run -p 3307:3306 --name mysql-master \
-v /opt/mysql-master/log:/var/log/mysql \
-v /opt/mysql-master/data:/var/lib/mysql \
-v /opt/mysql-master/my.cnf:/etc/my.cnf \
-v /opt/mysql-master/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=ding123456789 \
-e TZ=Asia/Shanghai \
-d mysql:5.7.39
开放外网访问
docker exec -it mysql-master /bin/bash
# 登录mysql
mysql -uroot -pding123456789
# 授权
grant all privileges on *.* to 'root'@'%' WITH GRANT OPTION;
# 刷新权限
flush privileges;
# 退出mysql会话
quit
# 退出容器
exit
安装从库
# 复制一份mysql配置文件，参考master
懒的话，直接copy master
cp -r /opt/mysql-master /opt/mysql-slave
# 启动容器
docker run -p 3308:3306 --name mysql-slave \
-v /opt/mysql-slave/log:/var/log/mysql \
-v /opt/mysql-slave/data:/var/lib/mysql \
-v /opt/mysql-slave/my.cnf:/etc/my.cnf \
-v /opt/mysql-slave/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=ding123456789 \
-d mysql:5.7.39
开启访问
docker exec -it mysql-slave /bin/bash
# 登录mysql
mysql -uroot -pding123456789
# 授权
grant all privileges on *.* to 'root'@'%' WITH GRANT OPTION;
# 刷新权限
flush privileges;
# 退出mysql会话
quit
# 退出容器
exit
修改uuid
https://zhuanlan.zhihu.com/p/375776925
https://blog.csdn.net/t1anyuan/article/details/78117672
https://cloud.tencent.com/developer/article/2027898
每个mysql 实例的uuid相同导致复制报错。
因为用的都是同一个镜像，所以 mysql 实例的uuid 相同需要手动修改
生成uuid
select uuid();
37421595-3594-11ed-bb5b-0242ac110003
修改主库的uuid
vi /opt/mysql-master/data/auto.cnf
[auto]
server-uuid=37421595-3594-11ed-bb5b-0242ac110003
重启主库
docker restart mysql-master
配置
编辑主库的配置文件 my.cnf
添加下面配置
[mysqld]
server-id=1
# 开启日志
log-bin=master-bin
binlog-format=ROW
# 指定数据库
binlog-do-db=sense
# 时区
default-time-zone='+08:00'
重启主库
创建用户，用于数据同步
CREATE USER 'slave'@'%' IDENTIFIED BY 'ding123456789';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
重启容器
docker restart mysql-master
配置从库
设置配置问 my.cnf
[mysqld]
server-id=2
## 备库日志
log-bin=mysql-slave-bin
## 中继日志
relay_log=edu-mysql-relay-bin
# 只读权限
read_only=1
# 时区
default-time-zone='+08:00'
重启从库
开启主从复制
# 进入主库
# 登录 mysql
# 查看主库状态
show master status;
file master-bin.000001, position 769
记住File和Position，后面需要用到。此时一定不要操作Master库，否则将会引起Master状态的变化，File和Position字段也将会进行变化。
进入从库，打开mysql 会话
修改主库同步信息
change master to master_host='172.17.0.1', master_user='slave', master_password='ding123456789', master_port=3307, master_log_file='master-bin.000003', master_log_pos=154, master_connect_retry=30;
开启同步
start slave;
查看同步状态
show slave status \G;
看到如下两项都为yes 同步成功
Slave_IO_Running:YES
Slave_SQL_Running:YES
扩展
设置时区
https://blog.csdn.net/jia_gushuai/article/details/105601423
https://cloud.tencent.com/developer/article/1913544
my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
如果修改主库后，需要刷新日志才能看到当前的日志位置
flush logs;
Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file n
从库配置参数有误，日志位置写错了
https://blog.csdn.net/u011488009/article/details/104608760
https://blog.csdn.net/lihuarongaini/article/details/101299375
https://developer.aliyun.com/article/27685
