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

1. 在master1数据库中新建表或进行数据的新增更改查看master2中是否有相应变化
2. 在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.测试连接

1. 浏览器输入http://192.168.1.100:8080/
   
2. 使用navicat连接代理地址
3. 如果无法连接请检查防火墙配置