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.7

docker 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\G

Slave_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:3003

systemctl start mysqlroute

测试负载均衡与读写分离

以上测试我们可以实现读操作的负载均衡，读写分离是通过我们连接MySQL Route不同的端口来实现的。