[[email protected] /]# 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
[[email protected] /]# docker pull mysql
docker pull mysql 預設拉去最新版本的。
[email protected]_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 容器:
[[email protected] /]# 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
[[email protected] /]#
先進入 master 容器 修改mysql相應的配置
[[email protected] /]# docker exec -it keda_mysql_server bash
[email protected]_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 '[email protected]';
##重新整理權限
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 映射的 資料卷 目錄下:
[[email protected] volumes]# pwd
/var/lib/docker/volumes
[[email protected] 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
[[email protected] volumes]# cd mysql_conf/_data/
[[email protected] _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
[[email protected] _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位址,如下
[[email protected] ~]# clear
[[email protected] ~]# docker exec -it keda_mysql_server bash
[email protected]_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
[email protected]_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
[[email protected] volumes]# pwd
/var/lib/docker/volumes
[[email protected] 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
[[email protected] volumes]# cd mysql_conf_3307/_data/
[[email protected] _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
[[email protected] _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個都有資料,則證明主從資料庫同步成功。
參考資料:
https://blog.csdn.net/forezp/article/details/94173427