基于Docker的MYSQL8主从搭建

本次安装基于ubuntu22.4

创建文件夹

1、创建工作文件夹，主从服务器均需操作（因为使用了挂载磁盘，所以在/mnt/data目录下）

# 创建主从数据库目录结构
sudo mkdir -p /mnt/data/mysql/{master,slave}/{conf,data,logs,backup}

# 设置目录权限
sudo chown -R 999:999 /mnt/data/mysql  # MySQL容器默认使用999用户
sudo chmod -R 755 /mnt/data/mysql

加载mysql镜像

两台服务器均需操作

sudo docker load -i /mnt/data/packages/mysql8.tar

注：如果迁移docker文件目录后需要删除原有images，重新加载image，不然的话会出现镜像不完整的情况

主服务器mysql

1、创建my.cnf文件

/mnt/data/mysql/master/conf目录下创建my.cnf

[mysqld]
server_id = 78
log_bin = mysql-bin
binlog_format = ROW
# binlog_do_db = *
log-bin-trust-function-creators = 1
expire_logs_days = 7
max_binlog_size = 100M
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 512M

# 开启GTID
# gtid_mode = ON
# enforce_gtid_consistency = ON

# 复制设置
# replicate_wild_do_table = %
# replicate_wild_ignore_table = mysql.%,sys.%,information_schema.%,performance_schema.%

# 数据目录
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock

# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

2、修改权限

 sudo chmod 644 my.cnf

3、启动主服务器mysql

sudo docker run -d 
  --name mysql-master 
  --restart=always 
  --network=host 
  -v /mnt/data/mysql/master/data:/var/lib/mysql 
  -v /mnt/data/mysql/master/conf:/etc/mysql/conf.d 
  -v /mnt/data/mysql/master/logs:/var/log/mysql 
  -e MYSQL_ROOT_PASSWORD=123456 
  -e TZ=Asia/Shanghai 
  mysql:8.0 
  --character-set-server=utf8mb4 
  --collation-server=utf8mb4_unicode_ci

4、进入主mysql创建复制用户

CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';

ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
FLUSH PRIVILEGES;

5、查看主mysql状态

SHOW MASTER STATUS;

注：尤其注意my.cnf配置文件

从服务器mysql

1、创建my.cnf文件

/mnt/data/mysql/slave/conf目录下创建my.cnf

[mysqld]
server_id = 77
log_bin = mysql-bin
relay_log = mysql-relay-bin
# gtid_mode = ON
# enforce_gtid_consistency = ON
read_only = 0
log-slave-updates = 1
skip_slave_start = 0
binlog_expire_logs_seconds = 604800  # 用这个替代 expire_logs_days

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

2、修改权限

sudo chmod 644 my.cnf

3、启动从服务器mysql

sudo docker run -d 
  --name mysql-slave 
  --restart=always 
  --network=host 
  -v /mnt/data/mysql/slave/data:/var/lib/mysql 
  -v /mnt/data/mysql/slave/conf:/etc/mysql/conf.d 
  -v /mnt/data/mysql/slave/logs:/var/log/mysql 
  -e MYSQL_ROOT_PASSWORD=123456 
  -e TZ=Asia/Shanghai 
  mysql:8.0 
  --character-set-server=utf8mb4 
  --collation-server=utf8mb4_unicode_ci

4、关闭从数据库同步（数据库内操作）

stop slave;

5、配置同步信息

change master to master_host= '主mysql内网ip', 
MASTER_PORT=3306,
master_user='repl', 
master_password='123456', 
master_log_file='mysql-bin.000003', 
master_log_pos=1515;

注：master_log_file和master_log_pos均需查看主mysql对应的状态

6、开启同步

start slave;

7、查看同步信息

SHOW SLAVE STATUSG