docker查询mysql 有哪些版本的镜像_运维有话说 | 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"' 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)

编辑：路程