Docker系列4 - 在Docker中测试MySQL主从数据库

系列文章
Docker系列1 - CentOS7 安装Docker
Docker系列2 - Docker常用命令
Docker系列3 - Docker文件系统及搭建Docker私服

注意：在生产环境中，不建议使用docker部署运行mysql
CentOS 7搭建MySQL主从数据库

---

在docker中启动mysql单实例

mysql配置文件

mkdir -p /data/mysql/conf
vi /data/mysql/conf/my.cnf

[client]
default-character-set=utf8mb4
 
[mysql]
default-character-set=utf8mb4
 
[mysqld]
init_connect='SET collation_connection = utf8mb4_unicode_ci'
init_connect='SET NAMES utf8mb4'
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
skip-character-set-client-handshake
skip-name-resolve
lower_case_table_names=1

启动mysql容器

docker run --name mysql -p 3306:3306 \
-v /data/mysql/log:/var/log/mysql \
-v /data/mysql/data:/var/lib/mysql \
-v /data/mysql/conf:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:5.7


#连接mysql，查看字符集
docker exec -it mysql /bin/bash
mysql -uroot -p
show variables like 'character%';

---

启动mysql master主节点实例

#master mysql
docker run -p 3307:3306 --name mysql-master \
-v /data/mysql-master/log:/var/log/mysql \
-v /data/mysql-master/data:/var/lib/mysql \
-v /data/mysql-master/conf:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:5.7

设置主机配置文件
vi /data/mysql-master/conf/my.cnf

[mysqld]
# 开启日志
log-bin=mysql-bin
# 设置服务id，主从不能一致
server-id=1
# 设置需要同步的数据库
binlog_do_db=user_db
# 屏蔽系统库同步
binlog_ignore_db=mysql
binlog_ignore_db=information_schema
binlog_ignore_db=performance_schema

binlog_format=mixed
# binlog过期清理时间
expire_logs_days=7
# binlog每个日志文件大小
max_binlog_size=100m
# binlog缓存大小
binlog_cache_size=4m
# 最大binlog缓存大小
max_binlog_cache_size=512m
#跳过主从复制中遇到的所有错误或指定类型的错误
slave_skip_errors=1062

#重启mysql
docker restart mysql-master

#创建一个复制数据用的账户
docker exec -it mysql-master /bin/bash
mysql -uroot -p
CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
FLUSH PRIVILEGES;
exit;
exit;

---

启动mysql slave从节点实例

#slave mysql
docker run -p 3308:3306 --name mysql-slave \
-v /data/mysql-slave/log:/var/log/mysql \
-v /data/mysql-slave/data:/var/lib/mysql \
-v /data/mysql-slave/conf:/etc/mysql/conf.d \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:5.7

设置从机配置文件
vi /data/mysql-slave/conf/my.cnf

[mysqld]
# 开启日志
log-bin=mysql-bin
# 设置服务id，主从不能一致
server-id=2
# 设置需要同步的数据库
replicate_wild_do_table=user_db.%
# 屏蔽系统库同步
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
slave_skip_errors=1062
#relay_log配置中继日志
relay_log=mysql_relay_bin
log_slave_updates=1
read_only=1

#重启mysql
docker restart mysql-slave 

---

在master mysql中查看状态

docker exec -it mysql-master /bin/bash
mysql -uroot -p

show master status;
#\G 把查询结果按列显示
show master status \G;

#关注File、Position，在slave的命令中需要用到
+------------------+----------+--------------+---------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |
+------------------+----------+--------------+---------------------------------------------+-------------------+
| mysql-bin.000001 |      769 | user_db      | mysql,information_schema,performance_schema |                   |
+------------------+----------+--------------+---------------------------------------------+-------------------+

#退出mysql-master
exit
exit

---

在slave中操作

docker exec -it mysql-slave /bin/bash
mysql -uroot -p

#先停止同步
STOP SLAVE;

#修改从库指向到主库，使用上一步记录的文件名以及位点
CHANGE MASTER TO
master_host = '192.168.99.203',
master_port = 3307,
master_user = 'slave',
master_password = '123456',
master_log_file = 'mysql-bin.000001',
master_log_pos = 769,
master_connect_retry = 30;

#启动同步
START SLAVE;

#查看Slave_IO_Runing和Slave_SQL_Runing字段值都为Yes，表示同步配置成功。如果不为Yes，请排查相关异常。
show slave status \G;

#在主库中依次执行SQL
docker exec -it mysql-master /bin/bash
mysql -uroot -p

CREATE DATABASE `user_db` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `user_db`;
CREATE TABLE `t_user`
(
    `user_id`  bigint(20) NOT NULL,
    `username` varchar(50) NOT NULL,
    `ustatus`  varchar(10) NOT NULL,
    PRIMARY KEY (`user_id`)
) ENGINE = InnoDB;
insert into `t_user` values(1, 'zhang', 'Normal');

#在从库中查询
docker exec -it mysql-slave /bin/bash
mysql -uroot -p

show databases;
USE `user_db`;
show tables;
select * from `t_user`;