Docker環境描述
| 主機名 | 部署服務 | 備註 |
|---|---|---|
| MySQL Route | MySQL Route | 部署在宿主機上的MySQL Route服務 |
| MySQL Master1 | MySQL 5.7.16 | Docker環境與MySQL Master2互為主從 |
| MySQL Master2 | MySQL 5.7.16 | Docker環境與MySQL Master1互為主從 |
| MySQL Slave1 | MySQL 5.7.16 | Docker環境是MySQL Master1的從伺服器 |
| MySQL Slave2 | MySQL 5.7.16 | Docker環境是MySQL Master2的從伺服器 |
宿主機服務部署
下載安裝MySQL Route軟體包
wget https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-8.0.11-1.el7.x86_64.rpm
rpm -ivh mysql-router-8.0.11-1.el7.x86_64.rpm
下載MySQL映象
docker search mysql5.7
INDEX NAME DESCRIPTION STARS OFFICIAL AUTOMATED
docker.io docker.io/acdaic4v/mysql5.7-k2 Mysql 5.7 for use with joomla extension k2... 1 [OK]
docker.io docker.io/bingozhou/mysql5.7 mysql5.7 1
docker.io docker.io/eruma/java8-mysql5.7 1
docker.io docker.io/nidorpi/rpi-mysql5.7 MySQL for Raspberry Pi 1
docker.io docker.io/ymnoor21/mysql5.7 Dockerize MySQL 5.7 on a Ubuntu 14.04 setup. 1
docker.io docker.io/alanpeng/mysql5.7-replication-docker https://github.com/alanpeng/mysql5.7-repli... 0 [OK]
docker.io docker.io/balewski/mysql5.7 0
docker.io docker.io/bob69xxx/mysql5.7 0
docker.io docker.io/bunchjesse/mysql5.7 MySQL 5.7 0 [OK]
docker.io docker.io/codecloud/mysql5.7 0
docker.io docker.io/gbyoung/mysql5.7 0
docker.io docker.io/georgel/mysql5.7 0
docker.io docker.io/glwang88/mysql5.7 0
docker.io docker.io/guojicheng114/mysql5.7-replication-docker for minsheng test 0 [OK]
docker.io docker.io/hexwit/mysql5.7mb4 Based on official mysql image, but added f... 0
docker.io docker.io/javiersolis/mysql5.7 0
docker.io docker.io/naturadocker/mysql5.7 0
docker.io docker.io/pengfeifan/mysql5.7.16 0
docker.io docker.io/phungquocphu/mysql5.7 0
docker.io docker.io/shenrrow/mysql5.7 0
docker.io docker.io/shepard/mysql5.7 0
docker.io docker.io/showrisego/mysql5.7 0
docker.io docker.io/sixgod/mysql5.7 0
docker.io docker.io/vinodapplift/centos-mysql5.7 Cent OS 6.7 with Mysql 5.7 latest version 0
docker.io docker.io/yangguohai/mysql5.7docker pull docker.io/acdaic4v/mysql5.7-k2
建立MySQL服務容器，並對映宿主機埠
docker run -it --name MySQL_Master1 -e MYSQL_ROOT_PASSWORD='123456' -p 3000:3306 docker.io/acdaic4v/mysql5.7-k2
docker run -it --name MySQL_Master2 -e MYSQL_ROOT_PASSWORD='123456' -p 3001:3306 docker.io/acdaic4v/mysql5.7-k2
docker run -it --name MySQL_Slave1 -e MYSQL_ROOT_PASSWORD='123456' -p 3002:3306 docker.io/acdaic4v/mysql5.7-k2
docker run -it --name MySQL_Slave2 -e MYSQL_ROOT_PASSWORD='123456' -p 3003:3306 docker.io/acdaic4v/mysql5.7-k2
授權MySQL資料庫連線，並測試是否可以從宿主機直連
授權使用者可以登入MySQL
docker exec -it MySQL_Master1 /bin/bash
mysql -uroot -p123456
grant all privileges on *.* to 'federico'@'%' identified by '123456';測試宿主機是否可以連線至MySQL容器
mysql -h 192.168.1.205 -ufederico -P3000 -p123456
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.16 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> select version();
+-----------+
| version() |
+-----------+
| 5.7.16 |
+-----------+
1 row in set (0.00 sec)
MySQL [(none)]>配置當前MySQL服務的主主複製以及主從複製
檢視容器IP地址資訊
docker inspect --format '{{ .NetworkSettings.IPAddress }}' MySQL_Master1
172.16.86.2
docker inspect --format '{{ .NetworkSettings.IPAddress }}' MySQL_Master2
172.16.86.3
docker inspect --format '{{ .NetworkSettings.IPAddress }}' MySQL_Slave1
172.16.86.4
docker inspect --format '{{ .NetworkSettings.IPAddress }}' MySQL_Slave2
172.16.86.5配置主主複製與主從複製
1.主主複製
docker exec -it MySQL_Master1 /bin/bash
安裝vim用於後續修改MySQL配置檔案
apt-get update && apt-get install vim
修改MySQL配置檔案
vim /etc/mysql/mysql.conf.d/mysqld.cnf[mysqld] server-id=1 log-bin=mysql-bin重啟MySQL容器，讓修改的配置檔案生效，注意此配置需要在另外幾臺伺服器做相同操作
docker stop MySQL_Master1 && docker start MySQL_Master1
mysql -u root -p123456
Master上的SQL操作（以此為例其餘都需要正確配置）
grant replication slave on *.* to 'slave'@'%' identified by '123456';
flush privileges;
show master status\G*************************** 1. row *************************** File: mysql-bin.000001 Position: 582 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)Slave上的操作
stop slave;
change master to master_host='172.16.86.2',master_user='slave',master_port=3306,master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=582;
start slave;
show slave status\GSlave_IO_Running: Yes Slave_SQL_Running: Yes
配置啟動MySQL Route
vim /etc/mysqlroute
# 預設的日誌外掛等相關配置路徑
[DEFAULT]
logging_folder = /var/log/mysqlrouter/
plugin_folder = /usr/lib64/mysqlrouter
runtime_folder = /var/run/mysqlrouter
config_folder = /etc/mysqlrouter
# 日誌顯示級別
[logger]
level = INFO
[keepalive]
interval = 60
# 配置主伺服器的高可用，當主節點down機後自動由第二節點接手服務
[routing:failover]
bind_address = 192.168.1.205
bind_port = 10000
connect_timeout = 3
max_connections = 1024
destinations = 192.168.1.205:3000,192.168.1.205:3001
mode = read-write
# 設定從伺服器的負載均衡
[routing:balancing]
bind_address = 192.168.1.205
bind_port = 10001
connect_timeout = 3
max_connections = 1024
mode = read-only
destinations = 192.168.1.205:3002,192.168.1.205:3003systemctl start mysqlroute
測試負載均衡與讀寫分離
以上測試我們可以實現讀操作的負載均衡，讀寫分離是通過我們連線MySQL Route不同的埠來實現的。