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

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/2的从服务器
MySQL Slave2	MySQL 5.7.16	Docker环境是MySQL Master1/2的从服务器

宿主机服务部署

下载安装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负载均衡与读写分离Docker环境使用

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

秒客网

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

Docker环境描述

宿主机服务部署

下载安装MySQL Route软件包

下载MySQL镜像

创建MySQL服务容器，并映射宿主机端口

授权MySQL数据库连接，并测试是否可以从宿主机直连

配置当前MySQL服务的主主复制以及主从复制

配置启动MySQL Route

测试负载均衡与读写分离

相关文章