docker mysql8.0 主从库

准备目录

mkdir mysql/master/conf
mkdir mysql/master/data

mkdir mysql/slave/conf
mkdir mysql/slave/conf

下载mysql镜像

docker pull mysql:8.0

准备配置文件

1. 主库 mysql/master/conf/mysql.conf

[mysqld]
## 设置server_id,注意要唯一
server-id=100
## 开启binlog
log-bin=mysql-bin
## binlog缓存
binlog_cache_size=1M
## binlog格式(mixed、statement、row,默认格式是statement)
binlog_format=mixed

## 指定同步的数据库, hello数据库需要自己创建
binlog-do-db=hello

## 屏蔽系统数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema

2. 从库 mysql/slave/conf/mysql.conf

[mysqld]
## 设置server_id,注意要唯一
server-id=200
log-bin=mysql-slave-bin
relay_log=z-mysql-relay-bin
log_bin_trust_function_creators=true
binlog_cache_size=2M
binlog_format=mixed
slave_skip_errors=1062

启动主库

docker run -itd -p 3307:3306 --name mysql-master -v D:\docker\mysql\master\conf:/etc/mysql/conf.d -v D:\docker\mysql\master\data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:8.0

配置同步用户以及权限

# 进入主库
docker exec -it mysql-master mysql -uroot -p
# 添加用户
mysql> CREATE USER `repl`@`%` IDENTIFIED WITH mysql_native_password BY 'repl';
mysql> GRANT Replication Client, Replication Slave ON *.* TO `repl`@`%`;
mysql> FLUSH PRIVILEGES;

# 查看server-id
mysql> show VARIABLES like '%server_id%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | 1     |
| server_id_bits | 32    |
+----------------+-------+
2 rows in set (0.00 sec)

解决server-id 不起作用问题

使用docker启动mysql时，出于安全考虑，对文件权限不正确的，mysqladmin自动忽略了自定义的配置文件

2023-09-06 15:45:57 mysql: [Warning] World-writable config file '/etc/mysql/conf.d/mysql.cnf' is ignored.
2023-09-06 15:45:57 
2023-09-06 15:45:57 2023-09-06 07:45:57+00:00 [Note] [Entrypoint]: Stopping temporary server
2023-09-06 15:45:57 mysqladmin: [Warning] World-writable config file '/etc/mysql/conf.d/mysql.cnf' is ignored.
2023-09-06 15:46:00 2023-09-06 07:46:00+00:00 [Note] [Entrypoint]: Temporary server stopped
2023-09-06 15:46:00 
2023-09-06 15:46:00 2023-09-06 07:46:00+00:00 [Note] [Entrypoint]: MySQL init process done. Ready for start up.
2023-09-06 15:46:00 
2023-09-06 15:46:00 mysqld: [Warning] World-writable config file '/etc/mysql/conf.d/mysql.cnf' is ignored.

fix:

docker exec -it mysql-master chmod 644 /etc/mysql/conf.d/mysql.cnf

修改好，记得重启mysql实例

启动从库

docker run -itd -p 3308:3306 --name mysql-slave -v D:\docker\mysql\slave\conf:/etc/mysql/conf.d -v D:\docker\mysql\slave\data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:8.0

同样要进行文件权限修改，否则server-id不生效

docker exec -it mysql-slave chmod 644 /etc/mysql/conf.d/mysql.cnf

配置从库

1. 查看主库master 信息, ip为: 172.17.0.4

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      156 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2. 登录从库

#配置主库信息
change master to master_host='172.17.0.4',master_port=3306,master_user='repl',master_password='repl',master_log_file='mysql-bin.000001',master_log_pos=156;

# master_host 主库ip或域名
# master_port 主库端口号
# master_user 可进行同步的用户名
# master_password 可进行同步的用户密码
# master_log_file binlog文件名称
# master_log_pos 同步开始位置

3. 开启同步

start slave;

验证 -- 主库

## 创建数据库
CREATE DATABASE `hello` CHARACTER SET 'utf8' COLLATE 'utf8mb4_general_ci';
## 新增表
CREATE TABLE `hello`.`user`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NULL,
  PRIMARY KEY (`id`)
);
## 新增数据
INSERT INTO `hello`.`user` (`id`, `username`) VALUES (1, 'test');

验证 -- 从库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hello              |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

mysql> select * from user;
+----+----------+
| id | username |
+----+----------+
|  1 | test     |
+----+----------+
1 row in set (0.00 sec)

通过上述可以看到主从可以正常同步了。 反向在从库对数据进行操作，主库不会变化。

问题：

1. [ERROR] [MY-013117] [Repl] Slave I/O for channel '': Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). Error_code: MY-013117

检查一下 mysql.cnf配置的server-id，主从库的server-id保持不相同，且唯一。

2. 当出现密码检测caching_sha2_password 时，将密码插件改为mysql_native_password, 且密码重新设置。记得FLUSH PRIVILEGES;