vlambda
2020-09-11

运维有话说 | Mysql容器化主主从架构搭建

1 前言

1.1 介绍

MySQL可以适用于容器吗？在这一篇里，简单地为大家介绍一下MySQL在容器上的使用并且实现主主从的架构实施。

容器是软件的标准单元，它将代码及其所有依赖项打包，以便应用程序在不同的计算环境之间快速可靠地运行。Docker容器映像是一个轻量级的、独立的、可执行的软件包，它包括运行应用程序所需的一切:代码、运行时、系统工具、系统库和设置。Docker是实现容器技术的一个开源项目，它将程序以及程序所有的依赖都打包到Docker容器里面。

使用Docker时，需要用到Docker镜像。镜像可以从正式的镜像库下载，也可以人工制作和更新。本次Mysql镜像从https://hub.docker.com/下载，也可以从https://container-registry.oracle.com下载企业版和社区版本两个版本。

1.2 环境

系统	版本	IP
CentOS Linux	7.7.1908	192.168.100.75
Docker	19.03.12
Mysql	5.7.31

2 实施过程

2.1 docker环境

2.1.1 Docker服务安装

yum install -y docker-ce

2.1.2 下载Mysql镜像

$ docker pull mysql:5.7.31$ docker imagesREPOSITORY TAG IMAGE ID CREATED SIZEmynginx/nginx v1.1.2 eb4bb8b766f5 2 weeks ago 340MBmynginx/nginx v1.1.1 f9c14ba8b254 2 weeks ago 328MBcentos latest 0d120b6ccaa8 2 weeks ago 215MBnginx latest 08393e824c32 3 weeks ago 132MBmysql 5.7.31 718a6da099d8 3 weeks ago 448MB

2.1.3 修改镜像

修改dockerfile中的配置，包括镜像的时区和默认系统字符集

$ cd /root/dockert/dockerfile$ vi DockerfileFROM mysql:5.7.31MAINTAINER LiuRUN echo "alias ls='ls --color=auto'" >> /root/.bashrc \ && ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtimeENV LANG=C.UTF-8

编译镜像

docker build -t mysqlt:5.7.31 .

查看到新的创建镜像

docker images

REPOSITORY      TAG       IMAGE ID       CREATED          SIZEmysqlt 5.7.31 7a2a0aadd202 25 seconds ago 448MBmynginx/nginx v1.1.2 eb4bb8b766f5 2 weeks ago 340MBmynginx/nginx v1.1.1 f9c14ba8b254 2 weeks ago 328MBcentos latest 0d120b6ccaa8 2 weeks ago 215MBnginx latest 08393e824c32 3 weeks ago 132MBmysql 5.7.31 718a6da099d8 3 weeks ago 448MB

2.2 启动Mysql

MySQL容器化其实也需要映射端口，数据持久化，加载配置文件等操作。本地启动三个mysql实例容器，容器端口3306分别映射到宿主机的33061、33062、33063端口。

2.2.1 创建目录

Data目录存放各mysql实例的数据文件，etc存放mysql的配置启动文件。

mkdir -p /data/5.7.31/33061/datamkdir -p /data/5.7.31/33061/etcmkdir -p /data/5.7.31/33062/datamkdir -p /data/5.7.31/33062/etcmkdir -p /data/5.7.31/33063/datamkdir -p /data/5.7.31/33063/etc

2.2.2 添加配置文件

因需配置主主从复制架构在master1和master2上打开bin-log和log_slave_updates参数。

cd /data/5.7.31/33061/etcvim my.cnf

[mysqld]# basic settings #pid-file = /var/run/mysqld/mysqld.pidsocket = /var/run/mysqld/mysqld.sockdatadir = /var/lib/mysqlserver_id = 561101user = mysqlport = 3306log_error = /var/lib/mysql/error.logsql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"autocommit = 1character_set_server=utf8transaction_isolation = READ-COMMITTED explicit_defaults_for_timestamp = 1 max_allowed_packet = 16777216 event_scheduler = 1 log_timestamps = system lower_case_table_names=1 # connection #interactive_timeout = 1800 wait_timeout = 1800 lock_wait_timeout = 1800 skip_name_resolve = 1 max_connections = 512 max_connect_errors = 100000 # table cache performance settings #table_open_cache = 4096 table_definition_cache = 4096 table_open_cache_instances = 16                             # session memory settings #read_buffer_size = 16M read_rnd_buffer_size = 32M sort_buffer_size = 32M tmp_table_size = 64M join_buffer_size = 128M thread_cache_size = 64 # log settings #slow_query_log = 1 slow_query_log_file = /var/lib/mysql/slow.log log_queries_not_using_indexes = 1 log_slow_admin_statements = 1 log_slow_slave_statements = 1 log_throttle_queries_not_using_indexes = 10 expire_logs_days = 7 long_query_time = 2 min_examined_row_limit = 100 binlog-rows-query-log-events = 1 log-bin-trust-function-creators = 1 max_binlog_size = 512M # InnoDB settings #innodb_page_size = 16384 innodb_data_file_path = ibdata1:256M:autoextend innodb_buffer_pool_size = 512M innodb_buffer_pool_instances = 16 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_lru_scan_depth = 4096 innodb_lock_wait_timeout = 50 innodb_io_capacity = 10000 innodb_io_capacity_max = 20000innodb_flush_method = O_DIRECT innodb_file_format = Barracuda innodb_file_format_max = Barracudainnodb_undo_logs = 128 innodb_undo_tablespaces = 3 innodb_flush_neighbors = 0 innodb_log_buffer_size = 16777216 innodb_log_file_size = 131072000 innodb_log_files_in_group = 3 innodb_purge_threads = 4 innodb_large_prefix = 1 innodb_thread_concurrency = 64 innodb_print_all_deadlocks = 1 innodb_strict_mode = 1 innodb_sort_buffer_size = 67108864 innodb_write_io_threads = 16 innodb_read_io_threads = 16 innodb_file_per_table = 1 innodb_stats_persistent_sample_pages = 64 innodb_autoinc_lock_mode = 2 innodb_online_alter_log_max_size = 1G innodb_open_files = 4096 innodb_flush_log_at_trx_commit = 1 # replication settings ##slave_parallel_workers = 4 #slave_parallel_type = 'LOGICAL_CLOCK' #master_info_repository = table #relay_log_info_repository = table sync_binlog = 1 gtid_mode = on                                                   enforce_gtid_consistency = 1                                     log_slave_updates = 1                                            log_bin = /var/lib/mysql/mysql-bin                     log_bin_index = /var/lib/mysql/binlog.index            binlog_format = row                                               binlog_row_image = minimal                                        binlog_rows_query_log_events = 1

2.2.3 运行Mysql实例

Mysql容器端口3306映射主机33061，

/var/lib/mysql绑定本地/data/5.7.31/33061/data，

/etc/mysql绑定本地/data/5.7.31/33061/etc，

mysql实例root密码指定123456

$ docker run -itd -p 33061:3306 --name mysqlmaster1 --hostname=master1 -v /data/5.7.31/33061/etc:/etc/mysql -v /data/5.7.31/33061/data:/var/lib/mysql --privileged=true -e MYSQL_ROOT_PASSWORD=123456 mysqlt:5.7.31df94f5a97a57084ec1cdaab49e64f1e4bb4c44f0e8e01de5a736977abdb316c4

# 解释下各个参数的含义

-d: 后台运行容器，并返回容器ID

-i: 以交互模式运行容器，通常与 -t 同时使用

-t: 为容器重新分配一个伪输入终端，通常与 -i 同时使用

-p: 指定端口映射，格式为：主机(宿主)端口:容器端口

--name="mysql57": 为容器指定一个名称

--hostname=mysql57: 指定容器的hostname

-v: 绑定一个卷

--privileged=true: 以特权方式启动容器

查看容器运行情况

$ docker psCONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMESdf94f5a97a57 mysqlt:5.7.31 "docker-entrypoint.s?? 5 seconds ago Up 2 seconds 33060/tcp, 0.0.0.0:33061->3306/tcp mysqlmaster1

启动过程中可查看日志

docker logs df94f5a97a57084ec1cdaab49e64f1e4bb4c44f0e8e01de5a736977abdb316c4

查看mysql日志

tail -100f error.log

删除容器

docker rm df94f5a97a57

相同方法启动另两条mysql实例

docker run -itd -p 33062:3306 --name mysqlmaster2 --hostname=master2 -v /data/5.7.31/33062/etc:/etc/mysql -v /data/5.7.31/33062/data:/var/lib/mysql --privileged=true -e MYSQL_ROOT_PASSWORD=123456 mysqlt:5.7.31

$ docker psCONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMESb16630629a70 mysqlt:5.7.31 "docker-entrypoint.s?? 8 seconds ago Up 5 seconds 33060/tcp, 0.0.0.0:33063->3306/tcp mysqlslave13020b33142a8 mysqlt:5.7.31 "docker-entrypoint.s?? 30 seconds ago Up 28 seconds 33060/tcp, 0.0.0.0:33062->3306/tcp mysqlmaster2b179db93d92f mysqlt:5.7.31 "docker-entrypoint.s?? 4 minutes ago Up 4 minutes 33060/tcp, 0.0.0.0:33061->3306/tcp mysqlmaster1

补充：# 更改mysql实例配置只需要修改宿主机 /data/5.7.31/33061/etc/my.cnf 文件，然后重启容器即可

docker restart mysqlmaster1

2.3 建立主主从关系

2.3.1 创建测试数据

在主库master1上创建测试数据

登录数据库两种方式

1）本地登录数据库

mysql -uroot -p -P33061 -h192.168.100.75

2）进去容器里后登录数据库

docker exec -it mysqlmaster1 /bin/bash

或

docker exec -it [CONTAINER ID] /bin/bash

进入容器后，登录数据库进行操作

root@master1:/# mysql -uroot -p123456mysql> create database test1;mysql> use test1;mysql> create table tbl_test ( id bigint(20) NOT NULL AUTO_INCREMENT, name varchar(20) NOT NULL, PRIMARY KEY (id), KEY idx_name (name)) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

mysql> insert into tbl_test(name) values('aaa');mysql> insert into tbl_test(name) values('bbb');mysql> insert into tbl_test(name) values('ccc');

mysql> select * from tbl_test;+----+------+| id | name |+----+------+| 1 | aaa || 2 | bbb || 3 | ccc |+----+------+3 rows in set (0.00 sec)

2.3.2 配置同步

1）在master1上创建同步账号

mysql> grant replication slave on *.* to 'rep'@'%' identified by 'rep';mysql> flush privileges;

2）将master1上的数据同步至 master2和slave1上

#导出docker exec mysqlmaster1 sh -c 'exec mysqldump --all-databases --set-gtid-purged=off--master-data=2 --flush-logs -uroot -p"$MYSQL_ROOT_PASSWORD" ' > /tmp/all-databases.sql#导入docker exec -i mysqlmaster2 sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD"' < /tmp/all-databases.sqldocker exec -i mysqlslave1 sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD"' < /tmp/all-databases.sql

3）确认三个容器内部网络IP

$ docker inspect --format='{{.NetworkSettings.IPAddress}}' mysqlmaster1172.17.0.2$ docker inspect --format='{{.NetworkSettings.IPAddress}}' mysqlmaster2172.17.0.3$ docker inspect --format='{{.NetworkSettings.IPAddress}}' mysqlslave1;172.17.0.4

4）确认日志位置，进行同步

查询主库master1的日志位置

mysql> Show master status ;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000007 | 194 | | |+------------------+----------+--------------+------------------+

在master2和slave1上执行

CHANGE MASTER TO MASTER_HOST='172.17.0.2', MASTER_PORT=3306, MASTER_USER='rep', MASTER_PASSWORD='rep', MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=194;Start slave ;show slave status\G; Slave_IO_Running: Yes Slave_SQL_Running: Yes

这两个状态为Yes，证明主从关系创建成功。

5）现在是mster2和slave1分别同步与master1同步，本次需要设置为双主一从模式，现配置master1与master2进行同步。

Master2Mysql实例中操作

mysql> set sql_log_bin=off; ---关闭binlog写入mysql> grant replication slave on *.* to 'rep2'@'%' identified by 'rep2';mysql> show master status;mysql> set sql_log_bin=on;mysql> show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000005 | 234 | | |+------------------+----------+--------------+------------------+

mater1Mysql实例中操作

mysql> show slave status;mysql> set sql_log_bin=off;mysql> stop slave;mysql> CHANGE MASTER TO MASTER_HOST='172.17.0.3', MASTER_PORT=3306, MASTER_USER='rep2', MASTER_PASSWORD='rep2', MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=234;mysql> Start slave;mysql> set sql_log_bin=on;

2.4 数据验证

通过主机ip和映射端口访问数据库

master1--

$ mysql -uroot -p123456  -h192.168.100.75 -P33061mysql> insert into test1.tbl_test(name) values('ddd');mysql> insert into test1.tbl_test(name) values('eee');

master2--

$ mysql -uroot -p123456 -h192.168.100.75 -P33062mysql> insert into test1.tbl_test(name) values('fff');mysql> insert into test1.tbl_test(name) values('ggg');

slave1--

$ mysql -uroot -p123456 -h192.168.100.75 -P33063mysql> select * from test1.tbl_test;+----+------+| id | name |+----+------+| 1 | aaa || 2 | bbb || 3 | ccc || 4 | ddd || 5 | eee || 6 | fff || 7 | ggg |+----+------+7 rows in set (0.00 sec)

编辑：路程

vlambda博客
学习文章列表

运维有话说 | Mysql容器化主主从架构搭建

1.2 环境

2 实施过程

2.1 docker环境

2.1.1 Docker服务安装

2.1.2 下载Mysql镜像

2.1.3 修改镜像

2.2 启动Mysql

2.2.1 创建目录

2.2.3 运行Mysql实例

2.3 建立主主从关系

2.3.1 创建测试数据

2.3.2 配置同步

2.4 数据验证

标签:

推荐阅读

相关文章

SQL语言--mysql基础查询语句

mysql查询用户留存语法（用户留存和用户留存率问题）大厂面试必问

mysql聚合函数---总体聚合、总体累加、分组聚合、分组累加

mysql QPS 过高问题处理

SQL语句--mysql高级查询语句

vlambda博客 学习文章列表

运维有话说 | Mysql容器化主主从架构搭建

1.2 环境

2 实施过程

2.1 docker环境

2.1.1 Docker服务安装

2.1.2 下载Mysql镜像

2.1.3 修改镜像

2.2 启动Mysql

2.2.1 创建目录

2.2.3 运行Mysql实例

2.3 建立主主从关系

2.3.1 创建测试数据

2.3.2 配置同步

2.4 数据验证

标签:

推荐阅读

相关文章

SQL语言--mysql基础查询语句

mysql查询用户留存语法（用户留存和用户留存率问题）大厂面试必问

mysql聚合函数---总体聚合、总体累加、分组聚合、分组累加

mysql QPS 过高问题处理

SQL语句--mysql高级查询语句

vlambda博客
学习文章列表