docker 部署高可用 HAProxy Mysql 双主方案

  1. 主从模式:
    mysql主从架构中其实就一个主在工作,而从就相当于一个备份机器,从通过日志监测的方式来备份主库上的数据而保证主库的数据安全。

  2. 主主模式:
    主主复制,就是在mysql主从架构上让mysql主实现监测从的日志变化,从而实现两台机器相互同步。

  3. 多主多从模式:
    MMM 即Master-Master Replication Manager for MySQL(mysql 主主复制管理器)
    Percona XtraDB Cluster

Docker 部署 HAProxy Mysql集群方案图

这里写图片描述

第一步:准备配置文件和存储文件夹

vito@caas:/opt2/mysql$ ls -al
总用量 24
drwxr-xr-x 2 root root 4096 625 10:08 haproxy
drwxr-xr-x 2 root root 4096 625 10:02 logs
drwxrwxrwx 6 root root 4096 625 11:02 mysql1
drwxrwxrwx 6 root root 4096 625 11:02 mysql2

vito@caas:/opt2/mysql$ cd mysql1
vito@caas:/opt2/mysql/mysql1$ ls -al
总用量 24
drwxr-xr-x 2 root         root   4096 625 10:52 backup
drwxr-xr-x 2 root         root   4096 625 10:52 conf.d
drwxr-xr-x 6 guest-gkd7rd docker 4096 625 15:52 data
drwxrwxrwx 2 root         root   4096 625 14:26 log

分别对文件夹说明:

  • backup:存放初始化脚本
  • conf.d : 存放mysql配置文件
  • data : 存放mysql数据文件
  • log : 存放日志文件
    以上文件设置权限 999:999,创建两个配置文件:
    /opt2/mysql/mysql1/conf.d/server1.cnf
[mysqld]
 server-id = 101
 log_bin = /var/log/mysql/mysql-bin.log
 binlog_do_db = mydata
 bind-address = 0.0.0.0 # make sure to bind it to all IPs, else mysql listens on 127.0.0.1
 character_set_server = utf8
 collation_server = utf8_general_ci

[mysql]
 default_character_set = utf8

/opt2/mysql/mysql2/conf.d/server2.cnf

[mysqld]
server-id = 102 # Remember this is only Integer per official documentation
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = mydata
bind-address = 0.0.0.0 # make sure to bind it to all IPs, else mysql listens on 127.0.0.1
character_set_server = utf8
collation_server = utf8_general_ci
[mysql]
default_character_set = utf8

创建用户replicator,密码repl1234or,授权该用户访问任何IP,grantt可以授权某个用户访问指定的ip地址,我们这里的设置是该用户访问任何ip。下面采用脚本的方式实现
/opt2/mysql/mysql1/backup/initdb.sql

use mysql;
create user 'replicator'@'%' identified by 'repl1234or';
grant replication slave on *.* to 'replicator'@'%';
# do note that the replicator permission cannot be granted on single database.
FLUSH PRIVILEGES;
SHOW MASTER STATUS;
SHOW VARIABLES LIKE 'server_id';

创建用户replicator,密码repl1234or,授权该用户访问任何IP,下面采用脚本的方式实现
/opt2/mysql/mysql2/backup/initdb.sql

use mysql;
create user 'replicator'@'%' identified by 'repl1234or';
grant replication slave on *.* to 'replicator'@'%';
# do note that the replicator permission cannot be granted on single database.
FLUSH PRIVILEGES;
SHOW MASTER STATUS;
SHOW VARIABLES LIKE 'server_id';

登录mysql,查看bin-log日志是否开启,确认是开启状态,如下:

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

第二步:使用配置文件启动mysql

启动mysql1
注意其中的挂载目录,挂载了我们自己定义的配置文件

docker run --name mysql1 -e MYSQL_ROOT_PASSWORD=mysqlpass -e MYSQL_DATABASE=mydata -dit -p 33061:3306 -v /opt2/mysql/mysql1/conf.d:/etc/mysql/mysql.conf.d/   -v /opt2/mysql/mysql1/data:/var/lib/mysql -v /opt2/mysql/mysql1/log:/var/log/mysql -v /opt2/mysql/mysql1/backup:/backup -h  mysql1 mysql

启动mysql2
注意其中的挂载目录,挂载了我们自己定义的配置文件

docker run --name mysql2 --link mysql1 -e MYSQL_ROOT_PASSWORD=mysqlpass -e MYSQL_DATABASE=mydata -dit -p 33062:3306 -v /opt2/mysql/mysql2/conf.d:/etc/mysql/mysql.conf.d/   -v /opt2/mysql/mysql2/data:/var/lib/mysql -v /opt2/mysql/mysql2/log:/var/log/mysql -v /opt2/mysql/mysql2/backup:/backup -h  mysql2 mysql

第三步:连接两个docker容器

因为容器重启后ip地址会改变,我们采用修改hosts文件的方式来链接两个容器:

# 提取容器mysql2的ip地址

mysql2ip=$(docker inspect --format '{{ .NetworkSettings.IPAddress }}' mysql2)

# 添加mysql2的ip到mysql1中的hosts文件中

docker exec -i mysql1 sh -c "echo '$mysql2ip  mysql2  mysql2' >> /etc/hosts"

# 确认上面的命令是否成功执行
docker exec -i mysql1 sh -c "cat /etc/hosts"

# 确认容器是否链接成功
docker exec -ti mysql2 sh -c "ping mysql1"
docker exec -ti mysql1 sh -c "ping mysql2"

能ping通,说明两个容器链接成功

第四步:初始化mysql脚本

执行mysql1的初始化脚本,在bakup文件夹下:

vito@caas:/opt2/mysql/mysql1$ docker exec -ti mysql1 sh -c "mysql -uroot -p"
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> source /backup/initdb.sql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 | mydata       |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 101   |
+---------------+-------+
1 row in set (0.00 sec)

上面的 File | Position两个字段后面有用,在下一步中MASTER_LOG_FILE,MASTER_LOG_POS是这里查询出来的结果.。
同样在容器mysql2中执行初始化脚本,步骤同上:

vito@caas:~$ docker exec -ti mysql2 sh -c "mysql -uroot -p"
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> source /backup/initdb.sql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      154 | mydata       |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 102   |
+---------------+-------+
1 row in set (0.00 sec)

第五步:执行同步命令

在容器mysql1中,登录mysql,执行如下语句

stop slave;
CHANGE MASTER TO MASTER_HOST = 'mysql2', MASTER_USER = 'replicator',MASTER_PASSWORD = 'repl1234or', MASTER_LOG_FILE = 'mysql-bin.000003',MASTER_LOG_POS = 154;
start slave;

在容器mysql2中,登录mysql,执行如下语句:

stop slave;
CHANGE MASTER TO MASTER_HOST = 'mysql1', MASTER_USER = 'replicator',MASTER_PASSWORD = 'repl1234or', MASTER_LOG_FILE = 'mysql-bin.000003',MASTER_LOG_POS = 154;
start slave;

查看slave状态:

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql1
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql2-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 

Slave_IO_Running: Yes, Slave_SQL_Running: Yes 两个yes表示设置成功。

第六步:准备日志收集容器

docker pull voxxit/rsyslog
docker run --name haproxy-logger -dit -h haproxy-logger -v /opt2/mysql/logs:/var/log/ voxxit/rsyslog

第七步:准备HAProxy 容器

准备配置文件,配置文件目录:
/opt2/mysql/haproxy/haproxy.cfg

global
    log haproxy-logger local0 notice
    # user haproxy
    # group haproxy
defaults
    log global
    retries 2
    timeout connect 3000
    timeout server 5000
    timeout client 5000
listen mysql-cluster
    bind 0.0.0.0:3306
    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 mysql1 mysql1:3306 check
    server mysql2 mysql2:3306 check
# Enable cluster status
listen mysql-clusterstats
    bind 0.0.0.0:8080
    mode http
    stats enable
    stats uri /
    stats realm Strictly\ Private
    stats auth status:keypas5

启动HAProxy容器:

docker run --name mysql-cluster -dit \
    -h mysql-cluster \
    --link mysql1:mysql1cl  --link mysql2:mysql2cl \
    --link haproxy-logger:haproxy-loggercl \
    -v /opt2/mysql/haproxy/haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg:ro \
    -p 33060:3306 -p 38080:8080 \
    haproxy:latest

第八步:确认容器是否工作正常

caas:~$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                                              NAMES
c56801d6401a        haproxy:latest      "/docker-entrypoin..."   2 hours ago         Up 2 hours          0.0.0.0:33060->3306/tcp, 0.0.0.0:38080->8080/tcp   mysql-cluster
4831e91d8eb3        voxxit/rsyslog      "rsyslogd -n"            2 hours ago         Up 2 hours          514/tcp, 514/udp                                   haproxy-logger
00d99d4acba1        mysql               "docker-entrypoint..."   3 hours ago         Up 2 hours          0.0.0.0:33062->3306/tcp                            mysql2
10c54e299b01        mysql               "docker-entrypoint..."   3 hours ago         Up 3 hours          0.0.0.0:33061->3306/tcp                            mysql1

如果四个容器工作正常,说明服务启动正常。
登录mysql1,创建表格,命令如下:

vito@caas:~$ docker exec -ti mysql2 sh -c "mysql -uroot -p"
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.21-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE IF NOT EXISTS `mydata`.`user` (
    ->  `user_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '',
    ->  `user_name` VARCHAR(45) NOT NULL COMMENT '',
    ->  `user_age` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '',
    ->  `user_sex` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '',
    ->  PRIMARY KEY (`user_id`))
    ->  ENGINE = InnoDB
    ->  AUTO_INCREMENT = 1
    ->  DEFAULT CHARACTER SET = utf8
    ->  COLLATE = utf8_general_ci
    ->  COMMENT = '';
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+------------------+
| Tables_in_mydata |
+------------------+
| hello            |
| mytest           |
| user             |
+------------------+
3 rows in set (0.00 sec)

创建表格的mysql命令如下:

CREATE TABLE IF NOT EXISTS `mydata`.`user` (
 `user_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户编号',
 `user_name` VARCHAR(45) NOT NULL COMMENT '用户名称',
 `user_age` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户年龄',
 `user_sex` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户性别',
 PRIMARY KEY (`user_id`))
 ENGINE = InnoDB
 AUTO_INCREMENT = 1
 DEFAULT CHARACTER SET = utf8
 COLLATE = utf8_general_ci
 COMMENT = '用户表';

用golang写的一个程序,链接一下数据库,测试一下是否正常,golang源代码如下:

package main

import (
    "database/sql"
    "fmt"

    _ "github.com/go-sql-driver/mysql"
)

func main() {
    insert()
}

//插入demo
func insert() {
    //db, err := sql.Open("mysql", "root:@/test?charset=utf8")
    db, err := sql.Open("mysql", "root:mysql1pass@tcp(127.0.0.1:33060)/mydata?charset=utf8")
    checkErr(err)

    stmt, err := db.Prepare(`INSERT user (user_name,user_age,user_sex) values (?,?,?)`)
    checkErr(err)
    res, err := stmt.Exec("liu", 30, 2)
    checkErr(err)
    id, err := res.LastInsertId()
    checkErr(err)
    fmt.Println(id)
}

执行该程序,分别登录mysql1和mysql2数据库查看结果:
发现两个数据库可以同步

mysql> select * from user;
+---------+-----------+----------+----------+
| user_id | user_name | user_age | user_sex |
+---------+-----------+----------+----------+
|       1 | tony      |       20 |        1 |
|       2 | liu       |       30 |        2 |
|       3 | liu       |       30 |        2 |
+---------+-----------+----------+----------+

经过测试,停掉一个mysql2,仍然执行插入操作,能够正常插入,不会报错,再重新启动mysql2以后,继续插入,两个数据库数据完美同步。

参考:https://vnextcoder.wordpress.com/2016/09/22/haproxy-mysql-cluster-on-docker/
参考:https://vnextcoder.wordpress.com/2016/09/19/mysql-master-master-replication-setup-on-docker/
参考:https://blog.csdn.net/chengxuyuanyonghu/article/details/71136172

  • 0
    点赞
  • 10
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值