MySQL Route负载均衡与读写分离Docker环境使用

Docker环境描述

MySQL Route负载均衡与读写分离Docker环境使用-LMLPHP

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

测试宿主机是否可以连接至MySQL容器

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地址信息

配置主主复制与主从复制

配置启动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负载均衡与读写分离Docker环境使用-LMLPHP

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