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

最新推荐文章于 2024-05-16 13:12:22 发布

vito0319

最新推荐文章于 2024-05-16 13:12:22 发布

阅读量6.1k

点赞数

分类专栏： docker 文章标签： mysql ha

本文链接：https://blog.csdn.net/warrior_0319/article/details/80805030

版权

docker 专栏收录该内容

24 篇文章 1 订阅

订阅专栏

主从模式：
mysql主从架构中其实就一个主在工作，而从就相当于一个备份机器，从通过日志监测的方式来备份主库上的数据而保证主库的数据安全。
主主模式：
主主复制，就是在mysql主从架构上让mysql主实现监测从的日志变化，从而实现两台机器相互同步。
多主多从模式：
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 6月  25 10:08 haproxy
drwxr-xr-x 2 root root 4096 6月  25 10:02 logs
drwxrwxrwx 6 root root 4096 6月  25 11:02 mysql1
drwxrwxrwx 6 root root 4096 6月  25 11:02 mysql2

vito@caas:/opt2/mysql$ cd mysql1
vito@caas:/opt2/mysql/mysql1$ ls -al
总用量 24
drwxr-xr-x 2 root         root   4096 6月  25 10:52 backup
drwxr-xr-x 2 root         root   4096 6月  25 10:52 conf.d
drwxr-xr-x 6 guest-gkd7rd docker 4096 6月  25 15:52 data
drwxrwxrwx 2 root         root   4096 6月  25 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

vito0319

关注

0
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
docker 部署高可用 HAProxy Mysql 双主方案

Docker 部署 HAProxy Mysql集群方案图第一步：准备配置文件和存储文件夹vito@caas:/opt2/mysql$ ls -al总用量 24drwxr-xr-x 2 root root 4096 6月 25 10:08 haproxydrwxr-xr-x 2 root root 4096 6月 25 10:02 logsdrwxrwxrwx 6 r...
复制链接

扫一扫

专栏目录