[root@localhost /]# docker search mysql
NAME DESCRIPTION STARS OFFICIAL AUTOMATED
mysql MySQL is a widely used, open-source relation… 8605 [OK]
mariadb MariaDB is a community-developed fork of MyS… 2991 [OK]
mysql/mysql-server Optimized MySQL Server Docker images. Create… 635 [OK]
centos/mysql-57-centos7 MySQL 5.7 SQL database server 62
centurylink/mysql Image containing mysql. Optimized to be link… 61 [OK]
mysql/mysql-cluster Experimental MySQL Cluster Docker images. Cr… 51
deitch/mysql-backup REPLACED! Please use http://hub.docker.com/r… 41 [OK]
tutum/mysql Base docker image to run a MySQL database se… 34
bitnami/mysql Bitnami MySQL Docker Image 33 [OK]
schickling/mysql-backup-s3 Backup MySQL to S3 (supports periodic backup… 28 [OK]
prom/mysqld-exporter 22 [OK]
linuxserver/mysql A Mysql container, brought to you by LinuxSe… 21
centos/mysql-56-centos7 MySQL 5.6 SQL database server 16
circleci/mysql MySQL is a widely used, open-source relation… 14
mysql/mysql-router MySQL Router provides transparent routing be… 12
arey/mysql-client Run a MySQL client from a docker container 11 [OK]
imega/mysql-client Size: 36 MB, alpine:3.5, Mysql client: 10.1.… 8 [OK]
openshift/mysql-55-centos7 DEPRECATED: A Centos7 based MySQL v5.5 image… 6
yloeffler/mysql-backup This image runs mysqldump to backup data usi… 6 [OK]
fradelg/mysql-cron-backup MySQL/MariaDB database backup using cron tas… 4 [OK]
genschsa/mysql-employees MySQL Employee Sample Database 2 [OK]
jelastic/mysql An image of the MySQL database server mainta… 1
ansibleplaybookbundle/mysql-apb An APB which deploys RHSCL MySQL 1 [OK]
widdpim/mysql-client Dockerized MySQL Client (5.7) including Curl… 0 [OK]
monasca/mysql-init A minimal decoupled init container for mysql 0
[root@localhost /]# docker pull mysql
docker pull mysql 默认拉去最新版本的。
root@keda_mysql:/# mysql --version
mysql Ver 8.0.17 for Linux on x86_64 (MySQL Community Server - GPL)
docker images 查看镜像
接下来,需要 生成 3个 mysql镜像容器。 1主2从
######## 创建 docker 数据卷 用于 映射mysql容器内的 数据目录,方便在容器外操作 #####
### master 的 数据卷 ###
docker volum create mysql_conf;
docker volum create mysql_data;
docker volum create mysql_logs;
### 3307从数据库 的 数据卷 ###
docker volum create mysql_conf_3307;
docker volum create mysql_data_3307;
docker volum create mysql_logs_3307;
### 3308从数据库 的 数据卷 ###
docker volum create mysql_conf_3308;
docker volum create mysql_data_3308;
docker volum create mysql_logs_3308;
### master 数据库 映射端口 3306 到主机 端口为 3306 ########
docker run -id \
--privileged=true \
--name=keda_mysql_server \
--hostname keda_mysql \
-p 3306:3306 \
-v mysql_conf:/etc/mysql/conf.d \
-v mysql_data:/var/lib/mysql \
-v mysql_logs:/logs \
-e MYSQL_ROOT_PASSWORD=keda123456 \
-d mysql:latest
### master 数据库 映射端口 3306 到主机 端口为 3307 ########
docker run -id \
--privileged=true \
--name=keda_mysql_server_3307 \
--hostname keda_mysql_3307 \
-p 3307:3306 \
-v mysql_conf_3307:/etc/mysql/conf.d \
-v mysql_data_3307:/var/lib/mysql \
-v mysql_logs_3307:/logs \
-e MYSQL_ROOT_PASSWORD=keda123456 \
-d mysql:latest
### master 数据库 映射端口 3306 到主机 端口为 3308 ########
docker run -id \
--privileged=true \
--name=keda_mysql_server_3308 \
--hostname keda_mysql_3308 \
-p 3308:3306 \
-v mysql_conf_3308:/etc/mysql/conf.d \
-v mysql_data_3308:/var/lib/mysql \
-v mysql_logs_3308:/logs \
-e MYSQL_ROOT_PASSWORD=keda123456 \
-d mysql:latest
-e MYSQL_ROOT_PASSWORD=keda123456 : 设置默认 root 密码 “keda123456” 就是我设置的密码
docker ps 可以 查看所有运行中的 容器
docker ps --filter name=mysql 只查看 包含 mysql 的容器
这里 显示 我成功创建的 3个mysql 容器:
[root@localhost /]# docker ps --filter name=mysql
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
92563b89ee7f mysql:latest "docker-entrypoint.s…" 4 hours ago Up About an hour 33060/tcp, 0.0.0.0:3307->3306/tcp keda_mysql_server_3307
76e83aec0e12 mysql:latest "docker-entrypoint.s…" 4 hours ago Up About an hour 33060/tcp, 0.0.0.0:3308->3306/tcp keda_mysql_server_3308
eeaf37520887 mysql "docker-entrypoint.s…" 2 days ago Up About an hour 0.0.0.0:3306->3306/tcp, 33060/tcp keda_mysql_server
[root@localhost /]#
先进入 master 容器 修改mysql相应的配置
[root@localhost /]# docker exec -it keda_mysql_server bash
root@keda_mysql:/# mysql -u root -p
Enter password:
由于mysql8以前的加密规则与mysql8以后的存在差异。会造成,SQLyog的软件,无法登陆mysql 错误信息如下:
1251 client does not support authentication protocol requested by server;consider upgrading Mysql client
解决办法:
USE mysql
FLUSH PRIVILEGES;
##修改加密规则
ALTER USER 'root'@'%' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
##更新一下用户的密码 password 为自己想要重新设置的密码
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Keda@123456';
##刷新权限
FLUSH PRIVILEGES;
在三台机器上分别创建2个数据库,分别为test1和test2,字符编码为utf8:
CREATE DATABASE `test1` CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE DATABASE `test2` CHARACTER SET utf8 COLLATE utf8_general_ci;
用另一个 SSH 渠道 ,到 宿主机 跟目录下。 进入 docker 映射的 数据卷 目录下:
[root@localhost volumes]# pwd
/var/lib/docker/volumes
[root@localhost volumes]# ll
总用量 32
drwxr-xr-x. 3 root root 19 9月 17 10:28 5d4948c3ce3d1ce38ff92cb247d130461f7352c3cf221b8558a3b280aff6a7de
drwxr-xr-x. 3 root root 19 9月 16 16:39 68d99a345475b0baa063ac382a7ab1965bf2b44b271e6b49f7b69d48c26b50c3
-rw-------. 1 root root 65536 9月 20 11:05 metadata.db
drwxr-xr-x. 3 root root 19 9月 17 15:48 mysql_conf
drwxr-xr-x. 3 root root 19 9月 20 11:05 mysql_conf_3307
drwxr-xr-x. 3 root root 19 9月 20 11:05 mysql_conf_3308
drwxr-xr-x. 3 root root 19 9月 17 15:48 mysql_data
drwxr-xr-x. 3 root root 19 9月 20 11:05 mysql_data_3307
drwxr-xr-x. 3 root root 19 9月 20 11:05 mysql_data_3308
drwxr-xr-x. 3 root root 19 9月 17 15:48 mysql_logs
drwxr-xr-x. 3 root root 19 9月 20 11:05 mysql_logs_3307
drwxr-xr-x. 3 root root 19 9月 20 11:05 mysql_logs_3308
[root@localhost volumes]# cd mysql_conf/_data/
[root@localhost _data]# ll
总用量 8
-rw-rw-r--. 1 root root 43 9月 12 12:23 docker.cnf
-rw-r--r--. 1 root root 1684 9月 20 13:35 mysql.cnf
[root@localhost _data]# vim mysql.cnf
进入 master 映射的数据卷目录中, 编辑 mysql.cnf
内容如下:
#
# The MySQL Client configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysql]
[mysqld]
#server-id给数据库服务的唯一标识
server-id=1
#
##log-bin设置此参数表示启用binlog功能,并指定路径名称
log-bin=/var/lib/mysql/mysql-bin
sync_binlog=0
##设置日志的过期天数
expire_logs_days=7
binlog-do-db=test1
binlog-do-db=test2
binlog-ignore-db=information_schema
binlog-ignore-db=sys
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
- 这里的server-id用于标识唯一的数据库,在从库必须设置为不同的值。
- binlog-ignore-db:表示同步的时候忽略的数据库
- binlog-do-db:指定需要同步的数据库
重启mysql,配置生效,执行以下的命令:docker restart 容器名
如果,装了 Portainer 容器管理器,可以使用web页面,直接重启容器。[笔者会接着,更新 Portainer 的安装教程]
然后,需要创建从库权限账号,并赋予从库权限。
在这之前,需要 查看 3 个 mysql容器的 ip地址,如下
[root@localhost ~]# clear
[root@localhost ~]# docker exec -it keda_mysql_server bash
root@keda_mysql:/# ifconfig
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 172.17.0.7 netmask 255.255.0.0 broadcast 172.17.255.255
ether 02:42:ac:11:00:07 txqueuelen 0 (Ethernet)
RX packets 817 bytes 48721 (47.5 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 804 bytes 85814 (83.8 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536
inet 127.0.0.1 netmask 255.0.0.0
loop txqueuelen 1000 (Local Loopback)
RX packets 0 bytes 0 (0.0 B)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 0 bytes 0 (0.0 B)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
root@keda_mysql:/#
- docker exec -it keda_mysql_server bash 进入容器 注: keda_mysql_server 是容器名
- ifconfig 查看端口。 如果命令用不了,需要 安装,安装命令如下
- apt-get update
- apt-get install net-tools
我的 三个 容器的 IP地址分别为 :
主: 172.17.0.7
从1: 172.17.0.10
从2: 172.17.0.11
# 创建 主库 的权限用户,并授权所有权限#
mysql>CREATE USER 'root'@'172.17.0.7' IDENTIFIED WITH mysql_native_password BY 'keda123456';
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.17.0.7';
# 创建 主库 的 3307从库 权限用户,并授权File权限 和 replication slave 权限#
mysql> CREATE USER 'keda3307'@'172.17.0.11' IDENTIFIED WITH mysql_native_password BY 'keda123456';
mysql> grant FILE on *.* to 'keda3307'@'172.17.0.11';
mysql> grant replication slave on *.* to 'keda3307'@'172.17.0.11';
mysql> flush privileges;
# 创建 主库 的 3308从库 权限用户,并授权File权限 和 replication slave 权限#
mysql> CREATE USER 'keda3308'@'172.17.0.10' IDENTIFIED WITH mysql_native_password BY 'keda123456';
mysql> grant FILE on *.* to 'keda3308'@'172.17.0.10';
mysql> grant replication slave on *.* to 'keda3308'@'172.17.0.10';
mysql> flush privileges;
重启 mysql, 重启命令:docker restart 容器名
然后查看 :show master status; 如下 这个 master 数据。需要在 从库中配置的时候,使用。
mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000002 | 1336 | test1,test2 | information_schema,sys,mysql,performance_schema | |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)
从库设置:
用另一个 SSH 渠道 ,到 宿主机 跟目录下。 进入 docker 映射的 数据卷 目录下:
修改 mysql_conf_3307 和 mysql_conf_3308 目录下的 mysql.cnf
[root@localhost volumes]# pwd
/var/lib/docker/volumes
[root@localhost volumes]# ll
总用量 32
drwxr-xr-x. 3 root root 19 9月 17 10:28 5d4948c3ce3d1ce38ff92cb247d130461f7352c3cf221b8558a3b280aff6a7de
drwxr-xr-x. 3 root root 19 9月 16 16:39 68d99a345475b0baa063ac382a7ab1965bf2b44b271e6b49f7b69d48c26b50c3
-rw-------. 1 root root 65536 9月 20 11:05 metadata.db
drwxr-xr-x. 3 root root 19 9月 17 15:48 mysql_conf
drwxr-xr-x. 3 root root 19 9月 20 11:05 mysql_conf_3307
drwxr-xr-x. 3 root root 19 9月 20 11:05 mysql_conf_3308
drwxr-xr-x. 3 root root 19 9月 17 15:48 mysql_data
drwxr-xr-x. 3 root root 19 9月 20 11:05 mysql_data_3307
drwxr-xr-x. 3 root root 19 9月 20 11:05 mysql_data_3308
drwxr-xr-x. 3 root root 19 9月 17 15:48 mysql_logs
drwxr-xr-x. 3 root root 19 9月 20 11:05 mysql_logs_3307
drwxr-xr-x. 3 root root 19 9月 20 11:05 mysql_logs_3308
[root@localhost volumes]# cd mysql_conf_3307/_data/
[root@localhost _data]# ll
总用量 8
-rw-rw-r--. 1 root root 43 9月 12 12:23 docker.cnf
-rw-r--r--. 1 root root 1684 9月 20 13:35 mysql.cnf
[root@localhost _data]# vim mysql.cnf
进入 从库 映射的数据卷目录中, 编辑 mysql.cnf
#
# The MySQL Client configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysql]
[mysqld]
#server-id给数据库服务的唯一标识
server-id=2
log-bin=mysql-bin
binlog-ignore-db=information_schema
binlog-ignore-db=sys
binlog-ignore-db=mysql
replicate-do-db=test1
replicate-do-db=test2
replicate-ignore-db=mysql
log-slave-updates
-- 插入 --
注意:两个从库的server-id不一样,需要唯一。
重启 mysql, 重启命令:docker restart 容器名
进入 从库 命令界面
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> reset slave all;
Query OK, 0 rows affected (0.14 sec)
mysql> change master to master_host='172.17.0.7',master_user='root',master_password='keda123456',master_log_file='mysql-bin.000002', master_log_pos=1336;
Query OK, 0 rows affected, 2 warnings (0.26 sec)
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
- master_log_file是在Master中show master status显示的File,
- master_log_pos是在Master中show master status显示的Position。
配置第二个从库的时候,需要重新从matser获取File和position。
然后可以通过show slave status查看配置信息。
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.7
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1336
Relay_Log_File: keda_mysql_3307-relay-bin.000002
Relay_Log_Pos: 818
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test1,test2
Replicate_Ignore_DB: mysql
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: 1336
Relay_Log_Space: 1036
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: 1
Master_UUID: d67c8a8b-d91f-11e9-af33-0242ac110008
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:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
上面的信息有Slave_IO_Running: Yes和Slave_SQL_Running: Yes,证明主从同步成功。
出错清理掉之前的配置,防止同步已经同步了的数据,执行以下命令:
mysql> stop slave;
mysql> reset slave all;
上述的步骤需要在2个从库中操作,操作完成后。
可以在Master建表插入数据,然后再从2个库中查看,如果2个都有数据,则证明主从数据库同步成功。