docker部署mysql双机热备+haproxy代理
步骤
一、工作环境准备(centos 7.x)
服务器 | ip | 系统 | mysql复制账号 |
---|---|---|---|
master1 | 192.168.1.100 | Centos 7.x | replicate_m1 |
master2 | 192.168.1.101 | Centos 7.x | replicate_m2 |
1.两台服务器均安装docker
yum install update
yum install docker
2.两台服务均拉取MySQL镜像
docker pull mysql
3.配置my.cnf文件
vi /root/my.cnf
# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# The MySQL Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld]
server-id = 1 # 唯一id
log-bin = mysql-master # 其中这两行是本来就有的,可以不用动,添加下面两行即可.指定日志文件
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Custom config should go here
!includedir /etc/mysql/conf.d/
4.启动MySql容器
# 初始化root密码,并设置自启
docker run --name mysql -e MYSQL_ROOT_PASSWORD=root -p 3306:3306 -v /root/my.cnf:/etc/mysql/my.cnf --restart=always -d mysql
5.使用navicat连接工具进行测试连接,如果连接不上请检查防火墙是否开启,或容器是否正确启动
6.分别创建复制账号
master1、master2 分别创建 replicate_m1、replicate_m2账号 并授权 Select、Replication、File,注意要开启远程访问。
二、主-主配置(MMM-Master-Master replication manager for MySQL)
1.master1、master2分别进行以下配置
# 分别查看各自的master状态
show master status
master1
master2
# master1执行命令配置slave
1.stop slave
2.change master to master_host='192.168.1.101',master_user='replicate_m2',master_password='123456' ,master_log_file='mysql-master.000002',master_log_pos=6524;
3.reset slave
4.start slave
5.show slave status
# master2执行命令配置slave
1.stop slave
2.change master to master_host='192.168.1.100',master_user='replicate_m1',master_password='123456' ,master_log_file='mysql-master.000002',master_log_pos=4453;
3.reset slave
4.start slave
5.show slave status
# 当看见Slave_IO_Running: YES、Slave_SQL_Running: YES时,主从复制即完成。
2.测试
- 在master1数据库中新建表或进行数据的新增更改查看master2中是否有相应变化
- 在master2数据库中新建表或进行数据的新增更改查看master1中是否有相应变化
三、集成haproxy代理
1.master1主机拉取haproxy镜像
docker pull haproxy
2.配置haproxy.cfg
vi /root/haproxy.cfg
global # 全局配置
log 127.0.0.1 local0 # 日志文件
# user haproxy
# group haproxy
defaults
log global
retries 2
timeout connect 3000
timeout server 5000
timeout client 5000
listen mysql-cluster
bind *:3308 # 监听地址
mode tcp
#option mysql-check user haproxy_check (This is not needed as for Layer 4 balancing)
option tcp-check
balance roundrobin
# The below nodes would be hit on 1:1 ratio. If you want it to be 1:2 then add 'weight 2' just after the line.
server mysql-master1 192.168.1.100:3306 check # 代理地址
server mysql-master2 192.168.1.101:3306 check # 代理地址
# Enable cluster status
listen mysql-clusterstats # 管理页面配置
bind *:8080
mode http
stats enable
stats uri /
stats realm Strictly\ Private
stats auth admin:admin
3.启动容器
docker run -d -p 8080:8080 -p 3308:3308 --name haproxy -v /root/haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg:ro haproxy:latest
4.测试连接
- 浏览器输入http://192.168.1.100:8080/
- 使用navicat连接代理地址
- 如果无法连接请检查防火墙配置