目录
1.题目部分
- 参考文档: https://hub.docker.com/_/mysql
- 使用docker-compose, 创建两个MySQL容器, 满足如下条件:
- 使用自定义的my.cnf
- 形成主从复制关系
- 容器销毁后, 主实例的数据仍保留, 从实例的数据清零销毁
- 重建两个容器, 主实例沿用之前的数据, 从实例重建数据, 并建立复制
2.解答部分
2.1 docker安装
docker-compose依赖docker环境，需要先安装好docker，本实验环境为在CentOS7虚拟机中安装docker，参考链接：https://docs.docker.com/install/linux/docker-ce/centos/
## 卸载旧版本的docker及相关组件
[root@10-186-61-162 ~]# yum remove docker
docker-client
docker-client-latest
docker-common
docker-latest
docker-latest-logrotate
docker-logrotate
docker-engine
## 安装yum工具包及docker相关依赖包
[root@10-186-61-162 ~]# yum install -y yum-utils device-mapper-persistent-data lvm2
## 添加docker社区版的软件源(这里将软件源替换为了阿里云提供的软件源，加快镜像下载速度)
[root@10-186-61-162 ~]# yum-config-manager --add-repo https://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo
## 安装并启动docker-ce
[root@10-186-61-162 ~]# yum install docker-ce docker-ce-cli containerd.io
[root@10-186-61-162 ~]# systemctl start docker
[root@10-186-61-162 ~]# systemctl status docker
## 验证docker运行正常
[root@10-186-61-162 ~]# docker --version
Docker version 19.03.6, build 369ce74a3c
[root@10-186-61-162 ~]# docker run hello-world
Unable to find image 'hello-world:latest' locally
latest: Pulling from library/hello-world
1b930d010525: Pull complete
Digest: sha256:9572f7cdcee8591948c2963463447a53466950b3fc15a247fcad1917ca215a2f
Status: Downloaded newer image for hello-world:latest
## 有以下输出表示docker安装且运行正常
Hello from Docker!
This message shows that your installation appears to be working correctly.
To generate this message, Docker took the following steps:
1. The Docker client contacted the Docker daemon.
2. The Docker daemon pulled the "hello-world" image from the Docker Hub.
(amd64)
3. The Docker daemon created a new container from that image which runs the
executable that produces the output you are currently reading.
4. The Docker daemon streamed that output to the Docker client, which sent it
to your terminal.
To try something more ambitious, you can run an Ubuntu container with:
$ docker run -it ubuntu bash
Share images, automate workflows, and more with a free Docker ID:
https://hub.docker.com/
For more examples and ideas, visit:
https://docs.docker.com/get-started/
2.2 docker-compose 安装
docker-compose是一个用来编排多个容器的工具，我们可以编辑一个YMAL格式的配置文件，将多个容器的配置写入，使用compose工具来统一启停维护。安装参考链接：
## 直接使用官方链接提供的下载命令下载1.23.3版本docker-compose并保存到/usr/local/bin/docker-compose
curl -L "https://github.com/docker/compose/releases/download/1.25.3/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose
## 对docker-compose增加可执行权限
chmod +x /usr/local/bin/docker-compose
## 验证docker版本
[root@10-186-61-162 ~]# docker-compose --version
docker-compose version 1.25.3, build d4d1b42b
2.3 docker-compose 配置
在编排前需规划好相关运行目录及my.cnf配置文件
## 创建docker-compose编排MySQL的系统目录便于维护管理
[root@10-186-61-162 mysql]# mkdir -p /data/docker-compose/mysql
## 在该目录下创建主从需要的文件及目录
[root@10-186-61-162 mysql]# pwd
/data/docker-compose/mysql
[root@10-186-61-162 mysql]# tree -L 2
.
├── docker-compose.yml ## MySQL主从编排的配置文件
├── master
│ ├── data ## master节点保留数据，数据用volumes的方式保留在宿主机本地，需要额外创建data目录
│ ├── init_sql ## 在MySQL初始化完成后需要执行的SQL文件目录，目录下包含创建的初始化用户、数据库、表及测试数据等。
│ └── my.cnf ## 自定义的MySQL配置文件
└── slave
├── init_sql
└── my.cnf
2.3.1 init.sql内容
- master节点
- 创建一个repl用户做复制同步使用
- 创建一个测试库zhenxing并在其中创建一张t1表插入少量数据
- slave节点
- reset master清除执行的基础操作产生的GTID信息
- change master to指向master节点建立主从复制同步连接
## master节点的初始化sql文件
[root@10-186-61-162 init_sql]# pwd
/data/docker-compose/mysql/master/init_sql
[root@10-186-61-162 init_sql]# cat init.sql
create user 'repl'@'%' identified by 'repl';
grant replication client,replication slave on *.* to 'repl'@'%';
create database zhenxing;
use zhenxing;
create table t1(id int primary key auto_increment,username varchar(20));
insert into t1(username) values('yuzhenxing'),('zhenxingyu');
## slave节点的初始化sql文件
[root@10-186-61-162 init_sql]# pwd
/data/docker-compose/mysql/slave/init_sql
[root@10-186-61-162 init_sql]# cat init.sql
reset master;
CHANGE MASTER TO MASTER_HOST='172.20.0.10',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
start slave;
2.3.2 my.cnf配置文件
其中除server_id与从库不一样以外，其他参数均一致
[mysqld]
# Base Config
server_id = 1623306
user = mysql
port = 3306
default_storage_engine = InnoDB
character_set_server = utf8mb4
# skip_slave_start = 1 ## 该参数需要注释，不然docker调用初始化脚本时不会触发start slave开启主从复制同步
skip-name-resolve = 1
skip-external-locking = 1
lower_case_table_names = 1
query_cache_type = 0
query_cache_size = 0
max_connections = 1000
default-time-zone = '+8:00'
log_timestamps = SYSTEM
# InnoDB config
innodb_strict_mode = 1
innodb_file_per_table = 1
innodb_stats_on_metadata = 0
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 3
innodb_data_file_path = ibdata1:128M:autoextend
innodb_buffer_pool_size = 128M
innodb_log_file_size = 32M
innodb_log_buffer_size = 8M
innodb_max_dirty_pages_pct = 60
innodb_io_capacity = 200
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_flush_neighbors = 1
# Cache config
key_buffer_size = 8M
tmp_table_size = 8M
max_heap_table_size = 8M
thread_cache_size = 1000
table_open_cache = 2048
open_files_limit = 65535
max_allowed_packet = 64M
# Log config
log_error = mysql-error.log
slow_query_log_file = mysql-slow.log
relay-log = mysql-relay
log-bin = mysql-bin
slow_query_log = 1
long_query_time = 0.2
#log_slow_admin_statements = 1
#log_slow_slave_statements = 1
# Replication config
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 4
expire_logs_days = 14
binlog_format = row
log_slave_updates = ON
binlog_checksum = NONE
max_binlog_size = 250M
binlog_cache_size = 2M
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
relay-log-info-repository = TABLE
master_info_repository = TABLE
relay_log_recovery = 1
binlog_rows_query_log_events = 1
log_bin_trust_function_creators = 1
# GTID
gtid-mode = ON
enforce-gtid-consistency = 1
# Performance Schema
performance-schema-instrument = 'wait/lock/metadata/sql/mdl=ON'
2.3.3 docker-compose.yml
- 采用MySQL5.7.29版本数据库
- 主从配置文件均采用自定义的配置文件
- 主库的数据目录采用宿主机上创建的data目录
- 将init_sql下的文件映射到/docker-entrypoint-initdb.d下(注：/docker-entrypoint-initdb.d下以sql或sh结尾的文件会在数据库初始化完成后自动执行)
[root@10-186-61-162 mysql]# cat docker-compose.yml
version: '3.7'
networks:
mysql_net:
name: mysql_net
driver: bridge
ipam:
driver: default
config:
- subnet: 172.20.0.1/24
services:
master:
networks:
mysql_net:
ipv4_address: 172.20.0.10
image: mysql:5.7.29
restart: always
environment:
MYSQL_ROOT_PASSWORD: oracle
hostname: 172-20-0-10
container_name: 172-20-0-10
volumes:
- "./master/my.cnf:/etc/my.cnf"
- "./master/data:/var/lib/mysql"
- "./master/init_sql:/docker-entrypoint-initdb.d/"
slave:
networks:
mysql_net:
ipv4_address: 172.20.0.11
image: mysql:5.7.29
restart: always
environment:
MYSQL_ROOT_PASSWORD: oracle
hostname: 172-20-0-11
container_name: 172-20-0-11
volumes:
- "./slave/my.cnf:/etc/my.cnf"
- "./slave/init_sql:/docker-entrypoint-initdb.d/"
2.4 docker-compose运行
当相关配置文件编辑完成后，即可启动docker-compose
## 使用docker-compose后台方式创建容器
[root@10-186-61-162 mysql]# cd /data/docker-compose/mysql
[root@10-186-61-162 mysql]# docker-compose up -d
Creating network "mysql_net" with driver "bridge"
Creating 172-20-0-11 ... done
Creating 172-20-0-10 ... done
## 查看mysql_net自定义网络
[root@10-186-61-162 mysql]# docker network ls
NETWORK ID NAME DRIVER SCOPE
d5072726a5c8 bridge bridge local
3443080f11fc host host local
deda6b29ddd0 mysql_net bridge local
25391fba169c none null local
c4aeb18be8c4 root_default bridge local
## 查看当前网络组下各节点对应具体IP
[root@10-186-61-162 mysql]# docker network inspect mysql_net |egrep "IPv4Address|Name"
"Name": "mysql_net",
"Name": "172-20-0-10",
"IPv4Address": "172.20.0.10/16",
"Name": "172-20-0-11",
"IPv4Address": "172.20.0.11/16",
## 使用docker ps命令可以看到当前运行的容器状态
[root@10-186-61-162 mysql]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
e1f7ff7f1e2e mysql:5.7.29 "docker-entrypoint.s…" 4 minutes ago Up 4 minutes 3306/tcp, 33060/tcp 172-20-0-11
af1fe13e199d mysql:5.7.29 "docker-entrypoint.s…" 4 minutes ago Up 4 minutes 3306/tcp, 33060/tcp 172-20-0-10
## 也可使用docker-compose的ps命令查看状态
[root@10-186-61-162 mysql]# docker-compose ps
Name Command State Ports
-----------------------------------------------------------------------
172-20-0-10 docker-entrypoint.sh mysqld Up 3306/tcp, 33060/tcp
172-20-0-11 docker-entrypoint.sh mysqld Up 3306/tcp, 33060/tcp
## 访问数据库主节点,数据均初始化成功
[root@10-186-61-162 mysql]# mysql -h172.20.0.10 -uroot -P3306 -poracle
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zhenxing |
+--------------------+
5 rows in set (0.01 sec)
mysql> select * from zhenxing.t1;
+----+------------+
| id | username |
+----+------------+
| 1 | yuzhenxing |
| 2 | zhenxingyu |
+----+------------+
2 rows in set (0.01 sec)
## 访问MySQL从库,从库为空实例
[root@10-186-61-162 mysql]# mysql -h172.20.0.11 -uroot -P3306 -poracle
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
## 主从同步正常
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.20.0.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 190
Relay_Log_File: mysql-relay.000006
Relay_Log_Pos: 395
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 190
Relay_Log_Space: 835
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1623306
Master_UUID: 72aa5e86-51ff-11ea-8a5a-0242ac14000a
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 72aa5e86-51ff-11ea-8a5a-0242ac14000a:2-10
Executed_Gtid_Set: 72aa5e86-51ff-11ea-8a5a-0242ac14000a:1-10
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
2.5 重置容器并保存master节点数据
- 因为主节点数据保存在容器外宿主机上，可直接将容器删除，再重新构建时指定容器使用已存在的数据即可完成构建。不会对已存在的数据库做改变,
- If you start your mysql container instance with a data directory that already contains a database (specifically, a mysql subdirectory), the $MYSQL_ROOT_PASSWORD variable should be omitted from the run command line; it will in any case be ignored, and the pre-existing database will not be changed in any way
## 在删除容器前在主节点新增一条数据验证是否会在重建后保留
[root@10-186-61-162 mysql]# mysql -h172.20.0.10 -uroot -P3306 -poracle
mysql> insert into zhenxing.t1 select null,'xingzhenyu';
mysql> select * from t1;
+----+------------+
| id | username |
+----+------------+
| 1 | yuzhenxing |
| 2 | zhenxingyu |
| 3 | xingzhenyu |
+----+------------+
3 rows in set (0.00 sec)
## 停止并删除容器
## 使用docker-compose stop && docker-compose rm不会删除创建的网络，如需要一并删除网络可使用docker-compose down
[root@10-186-61-162 mysql]# docker-compose stop && docker-compose rm
Stopping 172-20-0-11 ... done
Stopping 172-20-0-10 ... done
Going to remove 172-20-0-11, 172-20-0-10
Are you sure? [yN] y
Removing 172-20-0-11 ... done
Removing 172-20-0-10 ... done
## 重新构建容器
[root@10-186-61-162 mysql]# docker-compose up -d
Creating 172-20-0-11 ... done
Creating 172-20-0-10 ... done
## 登录主库查看数据
[root@10-186-61-162 mysql]# mysql -h172.20.0.10 -uroot -P3306 -poracle
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zhenxing |
+--------------------+
5 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000004 | 190 | | | 036a86a3-516e-11ea-9394-0242ac130003:1-10 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
## 验证主库数据仍保留完整
mysql> select * from zhenxing.t1;
+----+------------+
| id | username |
+----+------------+
| 1 | yuzhenxing |
| 2 | zhenxingyu |
| 3 | xingzhenyu |
+----+------------+
3 rows in set (0.01 sec)
## 登录slave查看与主库的同步
[root@10-186-61-162 mysql]# mysql -h172.20.0.11 -uroot -P3306 -poracle
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.20.0.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 190
Relay_Log_File: mysql-relay.000007
Relay_Log_Pos: 395
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 190
Relay_Log_Space: 1158
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1623306
Master_UUID: 72aa5e86-51ff-11ea-8a5a-0242ac14000a
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 72aa5e86-51ff-11ea-8a5a-0242ac14000a:2-11
Executed_Gtid_Set: 72aa5e86-51ff-11ea-8a5a-0242ac14000a:1-11
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
## 数据同步正常
mysql> select * from zhenxing.t1;
+----+------------+
| id | username |
+----+------------+
| 1 | yuzhenxing |
| 2 | zhenxingyu |
| 3 | xingzhenyu |
+----+------------+
3 rows in set (0.00 sec)
## 也可使用dump方式导出导入到slave后再建立主从同步
docker exec -i master sh -c 'exec mysqldump --all-databases -uroot -p"$MYSQL_ROOT_PASSWORD"' > all.sql
docker exec -i slave sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD"' <all.sql
## 或将master的宿主机上的数据拷贝一份供从库使用
2.6 参考链接
- docker 安装
- docker-compose 安装
- docker-compose 配置参数说明
- docker 自定义网络创建