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.2docker inspect --format '{{ .NetworkSettings.IPAddress }}' MySQL_Master2
172.16.86.3docker inspect --format '{{ .NetworkSettings.IPAddress }}' MySQL_Slave1
172.16.86.4docker 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不同的端口来实现的。