基于DOCKER的MySQL Group Replication + ProxySQL + keepalived 高可用集群搭建

秒速五厘米 2022-05-06 13:28 315阅读 0赞

基于DOCKER的MySQL Group Replication + ProxySQL + keepalived 高可用环境配置

整体规划

<table> 
 <thead> 
  <tr> 
   <th>host</th> 
   <th>ip</th> 
   <th>port</th> 
   <th>remark</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>服务器域名</td> 
   <td>服务器外网ip</td> 
   <td>10088</td> 
   <td>服务器访问端口</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>192.168.100.30</td> 
   <td></td> 
   <td>keepalived虚拟ip地址</td> 
  </tr> 
  <tr> 
   <td>mysql-proxy</td> 
   <td>192.168.100.21</td> 
   <td>6601-admin管理端口,6603-客户端连接端口</td> 
   <td>ProxySQL代理</td> 
  </tr> 
  <tr> 
   <td>mysql-proxy3</td> 
   <td>192.168.100.23</td> 
   <td>6601-admin管理端口,6603-客户端连接端口</td> 
   <td>ProxySQL代理</td> 
  </tr> 
  <tr> 
   <td>mysql-master</td> 
   <td>192.168.100.11</td> 
   <td>3306-mysql端口，33060-mgr端口</td> 
   <td>mysql初始主节点</td> 
  </tr> 
  <tr> 
   <td>mysql-slave1</td> 
   <td>192.168.100.12</td> 
   <td>3306-mysql端口，33060-mgr端口</td> 
   <td>mysql初始从节点</td> 
  </tr> 
  <tr> 
   <td>mysql-slave2</td> 
   <td>192.168.100.13</td> 
   <td>3306-mysql端口，33060-mgr端口</td> 
   <td>mysql初始从节点</td> 
  </tr> 
 </tbody> 
</table>

基本流程

### 文章目录 ###

*   *   *   *  配置MGR
             *   *  Step1 给3个mysql容器编写配置文件
                 *  Step2 创建3个mysql容器
                 *  Step3 配置master节点
                 *  Step4 配置slave节点
             *  配置ProxySQL 集群
             *   *  Step5 安装ProxySQL
                 *  Step6 配置ProxySQL
                 *  Step7 配置ProxySQL集群
                 *  Step8 配置第二台ProxySQL
             *  配置keepalived
             *   *  Step9 配置100.21上的keepalived
                 *  Step10 配置100.23上的keepalived
             *  配置宿主机端口转发
             *   *  Step11 在主机配置端口转发

#### 配置MGR ####

##### Step1 给3个mysql容器编写配置文件 #####

master的my.cnf

# For advice on how to change settings please see
    # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
    [client]    
    default-character-set=utf8  
      
    [mysql]    
    default-character-set=utf8
    
    [mysqld]
    skip-name-resolve
    character-set-server=utf8
    collation-server=utf8_general_ci
    
    innodb_buffer_pool_size=2048M
    innodb_log_file_size=128M
    query_cache_size=64M
    max_connections=128
    max_allowed_packet = 50M
    log_timestamps=SYSTEM
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    
    #服务器id
    server-id=1
    pid-file=/var/run/mysqld/mysqld.pid
    
    gtid_mode=on
    enforce_gtid_consistency=on
    master_info_repository=table
    relay_log_info_repository=table
    binlog_checksum=none
    log_slave_updates=on
    log_bin=binlog
    binlog_format=row
    relay-log=bogn-relay-bin
    #唯一确定事务影响行的主键，必须开启
    transaction_write_set_extraction=XXHASH64
    #唯一标识一个组
    loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
    loose-group_replication_allow_local_disjoint_gtids_join=on
    #重启自动自动组复制，第一次初始化的时候可以off，不然进去初始化之前需要先关闭stop group_replication
    loose-group_replication_start_on_boot=on
    #unreachable的超时时间设置
    loose-group_replication_unreachable_majority_timeout=10
    #用于组间通信的地址
    loose-group_replication_local_address="192.168.100.11:33060"
    #donor地址
    loose-group_replication_group_seeds="192.168.100.11:33060,192.168.100.12:33060,192.168.100.13:33060"
    #引导节点设置
    loose-group_replication_bootstrap_group=off
    
    [mysqld_safe]
    default-character-set=utf8

slave1的my.cnf，只有下面的与master不同

server-id=2
    loose-group_replication_local_address= "192.168.100.12:33060"

slave2的my.cnf，同样也只有这两部分需要改动

server-id=3
    loose-group_replication_local_address= "192.168.100.13:33060"

##### Step2 创建3个mysql容器 #####

在docker创建容器之前，先创建一个网络

> docker network create --subnet=192.168.100.0/24 mysqlnet

再用docker创建3个mysql容器

/home/mt/mysql/10001/conf/my.cnf是配置文件的存放位置

/etc/mysql/conf.d/my.cnf 是容器内部配置文件的位置

> docker run -p 10001:3306 -p 10011:33060 --name master --hostname=mysql-master --net=mysqlnet --ip=192.168.100.11 --add-host mysql-master:192.168.100.11 --add-host mysql-slave1:192.168.100.12 --add-host mysql-slave2:192.168.100.13 -v /home/mt/mysql/10001/conf/my.cnf:/etc/mysql/conf.d/my.cnf -v /home/mt/mysql/10001/logs:/logs -v /home/mt/mysql/10001/data:/mysql\_data -e MYSQL\_ROOT\_PASSWORD=123456 -d mysql:5.7.23
> 
> docker run -p 10002:3306 -p 10012:33060 --name slave1 --hostname=mysql-slave1 --net=mysqlnet --ip=192.168.100.12 --add-host mysql-master:192.168.100.11 --add-host mysql-slave1:192.168.100.12 --add-host mysql-slave2:192.168.100.13 -v /home/mt/mysql/10002/conf/my.cnf:/etc/mysql/conf.d/my.cnf -v /home/mt/mysql/10002/logs:/logs -v /home/mt/mysql/10002/data:/mysql\_data -e MYSQL\_ROOT\_PASSWORD=123456 -d mysql:5.7.23
> 
> docker run -p 10003:3306 -p 10013:33060 --name slave2 --hostname=mysql-slave2 --net=mysqlnet --ip=192.168.100.13 --add-host mysql-master:192.168.100.11 --add-host mysql-slave1:192.168.100.12 --add-host mysql-slave2:192.168.100.13 -v /home/mt/mysql/10003/conf/my.cnf:/etc/mysql/conf.d/my.cnf -v /home/mt/mysql/10003/logs:/logs -v /home/mt/mysql/10003/data:/mysql\_data -e MYSQL\_ROOT\_PASSWORD=123456 -d mysql:5.7.23

##### Step3 配置master节点 #####

> 第一步：创建用于复制的用户  
> set sql\_log\_bin=0;  
> create user mgruser@’%’ identified by ‘123456’;  
> grant replication slave,replication client on *.* to mgruser@’%’;  
> create user mgruser@‘127.0.0.1’ identified by ‘123456’;  
> grant replication slave,replication client on *.* to mgruser@‘127.0.0.1’;  
> create user mgruser@‘localhost’ identified by ‘123456’;  
> grant replication slave,replication client on *.* to mgruser@‘localhost’;  
> set sql\_log\_bin=1;  
> flush privileges;
> 
> 第二步：配置复制所使用的用户  
> change master to master\_user=‘mgruser’,master\_password=‘123456’ for channel ‘group\_replication\_recovery’;  
> flush privileges;  
> 第三步：安装mysql group replication 这个插件
> 
> install plugin group\_replication soname ‘group\_replication.so’;  
> 　  
> 第四步：建个群(官方点的说法就是初始化一个复制组)
> 
> set global group\_replication\_bootstrap\_group=on;  
> start group\_replication;  
> set global group\_replication\_bootstrap\_group=off;
> 
> 最后，查看一下当前的组员
> 
> SELECT \* FROM performance\_schema.replication\_group\_members;

##### Step4 配置slave节点 #####

步骤的前三步同上面master一样，最后一步只需要start group\_replication;

成功之后再查看一下当前的组员

SELECT \* FROM performance\_schema.replication\_group\_members;

![Image 1][]

这里之前按照网上的配置使用的时候有个问题

> 2018-10-09T02:19:06.813142-00:00 0 \[ERROR\] Plugin group\_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: e1f0e952-cb68-11e8-acef-0242c0a8640c:1-6 > Group transactions: aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-2,

解决方法就是在前面的配置文件里面的

loose-group\_replication\_allow\_local\_disjoint\_gtids\_join=on

如果已经进入了发生了这个问题，也可以通过设置

> mysql> show variables like ‘group\_replication\_allow\_local\_disjoint\_gtids\_join’;  
> ±--------------------------------------------------±------+  
> | Variable\_name | Value |  
> ±--------------------------------------------------±------+  
> | group\_replication\_allow\_local\_disjoint\_gtids\_join | OFF |  
> ±--------------------------------------------------±------+  
> 1 row in set (0.00 sec)
> 
> mysql> set global group\_replication\_allow\_local\_disjoint\_gtids\_join=1;  
> Query OK, 0 rows affected (0.00 sec)
> 
> mysql> START GROUP\_REPLICATION;  
> Query OK, 0 rows affected (2.12 sec)

**至此，GMR的配置就配置完成了，可以关掉master之后看到写节点就变成了slave1**

#### 配置ProxySQL 集群 ####

##### Step5 安装ProxySQL #####

这里没有找到ProxySQL的官方镜像，于是使用了一个centos的镜像，在centos的镜像里面再安装ProxySQL

> 第一步，安装centos的镜像
> 
> docker run --privileged --name proxysql --hostname=mysql-proxy --net=mysqlnet --ip=192.168.100.21 -p 10021:6601 -p 10022:6602 -p 10023:6603 -v /home/mt/centos:/home -d -i -t centos:7.2.1511 /usr/sbin/init
> 
> 第二步，安装ProxySQL
> 
> 根据https://github.com/sysown/proxysql/wiki的官方文档进行安装
> 
> Adding repository:
> 
>     cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
>     [proxysql_repo]
>     name= ProxySQL YUM repository
>     baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
>     gpgcheck=1
>     gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
>     EOF
> 
> Installing:
> 
>     yum install proxysql OR yum install proxysql-version
> 
> 安装好之后使用systemctl start proxysql即可

##### Step6 配置ProxySQL #####

> 第一步，在mgr的写节点导入视图，这个具体的内容可以在这里下https://github.com/lefred/mysql\_gr\_routing\_check/blob/master/addition\_to\_sys.sql
> 
> mysql -p < addition\_to\_sys.sql
> 
> 第二步，proxysql增加帐号
> 
> create user ‘monitor’@’%’ identified by ‘monitor’;  
> grant all privileges on *.* to ‘monitor’@’%’ with grant option;  
> create user ‘proxysql’@’%’ identified by ‘proxysql’;  
> grant all privileges on *.* to ‘proxysql’@’%’ with grant option;  
> flush privileges;
> 
> 第三步，登陆proxysql，配置mysql节点信息
> 
> mysql -uadmin -padmin -h127.0.0.1 -P6601
> 
> 注意admin只能在本地登陆
> 
> 添加节点
> 
> insert into mysql\_servers (hostgroup\_id, hostname, port) values(1,‘192.168.100.11’, 3306);  
> insert into mysql\_servers (hostgroup\_id, hostname, port) values(3,‘192.168.100.12’, 3306);  
> insert into mysql\_servers (hostgroup\_id, hostname, port) values(3,‘192.168.100.13’, 3306);
> 
> 配置用户（主要是添加程序端的这个用户，也就是run，将其设置到写组1里面）  
> insert into mysql\_users(username,password,default\_hostgroup) values(‘proxysql’,‘proxysql’,1);
> 
> 添加mysql group replication 信息  
> insert into mysql\_group\_replication\_hostgroups (writer\_hostgroup,backup\_writer\_hostgroup,reader\_hostgroup, offline\_hostgroup,active,max\_writers,writer\_is\_also\_reader,max\_transactions\_behind) values (1,2,3,4,1,1,0,100);
> 
> proxysql 读写分离  
> insert into mysql\_query\_rules(rule\_id,active,match\_digest,destination\_hostgroup,apply)values(1,1,’^SELECT.\*FOR UPDATE$’,1,1);
> 
> insert into mysql\_query\_rules(rule\_id,active,match\_digest,destination\_hostgroup,apply)values(2,1,’^SELECT’,3,1);
> 
> 最后加载新的配置
> 
> load mysql servers to runtime;  
> load mysql users to runtime;  
> load mysql variables to runtime;  
> load mysql query to runtime;  
> load mysql rules to runtime;
> 
> 保存新的配置
> 
> save mysql servers to disk;  
> save mysql users to disk;  
> save mysql variables to disk;  
> save mysql query to disk;  
> save mysql rules to disk;

这样整个环境搭建就完成了，登陆验证一下

mysql -uproxysql -pproxysql -h192.168.100.21 -P6603

可以正常增删查改

挂掉master节点，也可以正常增删查改

##### Step7 配置ProxySQL集群 #####

update global_variables set variable_value='admin:admin;cluster:cluster' where variable_name='admin-admin_credentials';
     update global_variables set variable_value='cluster' where variable_name='admin-cluster_username';
     update global_variables set variable_value='cluster' where variable_name='admin-cluster_password';
     insert into proxysql_servers(hostname,port,weight,comment) values('192.168.100.21',6601,1,'primary'),('192.168.100.23',6601,1,'secondary');
     load admin variables to runtime;
     load proxysql servers to runtime;
    save admin variables to disk;
    save proxysql servers to disk;

##### Step8 配置第二台ProxySQL #####

第二台ProxySQL采用配置文件来配置

贴几个主要的配置部分

admin_variables=
    {
    	admin_credentials="admin:admin;cluster:cluster"
    #	mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
    	mysql_ifaces="0.0.0.0:6601"
    #	refresh_interval=2000
    #	debug=true
    	cluster_username="cluster"
    	cluster_password="cluster"
    	cluster_check_interval_ms=200                       
    	cluster_check_status_frequency=100
    	cluster_mysql_query_rules_save_to_disk=true
    	cluster_mysql_servers_save_to_disk=true
    	cluster_mysql_users_save_to_disk=true
    	cluster_proxysql_servers_save_to_disk=true
    	cluster_mysql_query_rules_diffs_before_sync=3
    	cluster_mysql_servers_diffs_before_sync=3
    	cluster_mysql_users_diffs_before_sync=3
    	cluster_proxysql_servers_diffs_before_sync=3
    }
    mysql_variables=
    {
    	threads=4
    	max_connections=2048
    	default_query_delay=0
    	default_query_timeout=36000000
    	have_compress=true
    	poll_timeout=2000
    #	interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
    	interfaces="0.0.0.0:6602"
    	default_schema="information_schema"
    	stacksize=1048576
    	server_version="5.5.30"
    	connect_timeout_server=3000
    # make sure to configure monitor username and password
    # https://github.com/sysown/proxysql/wiki/Global-variables#mysql-monitor_username-mysql-monitor_password
    	monitor_username="monitor"
    	monitor_password="monitor"
    	monitor_history=600000
    	monitor_connect_interval=60000
    	monitor_ping_interval=10000
    	monitor_read_only_interval=1500
    	monitor_read_only_timeout=500
    	ping_interval_server_msec=120000
    	ping_timeout_server=500
    	commands_stats=true
    	sessions_sort=true
    	connect_retries_on_failure=10
    }
    proxysql_servers =                                        
    (
            {
                    hostname="192.168.100.21"
                    port=6601
                    comment="primary"                        
            },
            {
                    hostname="192.168.100.23"
                    port=6601
                    comment="secondary"
            }
    )

#### 配置keepalived ####

##### Step9 配置100.21上的keepalived #####

keepalived.conf

! Configuration File for keepalived
    
    global_defs {
       router_id LVS_DEVEL_1
    }
    
    vrrp_script chk_proxysql {
        script "/home/check_proxysql.sh"
        interval 1
    }
    
    vrrp_instance proxysql {
        state BACKUP
        interface eth0
        virtual_router_id 51
        priority 90
        advert_int 1
    
        authentication {
            auth_type PASS
            auth_pass xxxx
        }
    
        track_script {
            chk_proxysql
        }
    
        virtual_ipaddress {
            192.168.100.30/24
        }
    }

check\_proxysql.sh

#!/bin/sh
    echo "aaaaa"
    peer_ip='192.168.100.23'
    peer_port=22
    proxysql='proxysql-1'
    vip='192.168.100.30'
    log=/home/keepalived.log
    alias date='date +"%y-%m-%d_%H:%M:%S"'
    echo "`date`  enter script." >> $log
    #check if this keepalived is MASTER
    ip a|grep $vip
    if [ $? -ne 0 ];then
        exit 0
    fi
    echo "`date`  after check keepalived master script." >> $log
    #check if data port(6033) is alive
    data_port_stats=$(timeout 2  bash -c 'cat < /dev/null > /dev/tcp/0.0.0.0/6603' &> /dev/null;echo $?)
    if [ $data_port_stats -eq 0 ];then
        exit 0
    else
        #check if the other keepalived is running
        peer_keepalived=$(ssh -p$peer_port $peer_ip 'systemctl is-active keepalived.service')
        if [ "$peer_keepalived" != "active" ];then
            echo "`date`  data port of $proxysql is not available, but the BACKUP keepalived is not running, so can't do the failover" >> $log
        else
            echo "`date`  data port of proxysql is not available, now SHUTDOWN keepalived." >> $log
            systemctl stop keepalived.service
        fi
    fi

##### Step10 配置100.23上的keepalived #####

keepalived.conf

! Configuration File for keepalived
    
    global_defs {
       router_id LVS_DEVEL_3
    }
    
    vrrp_script chk_proxysql {
        script "/home/check_proxysql.sh"
        interval 1
    }
    
    vrrp_instance proxysql {
        state BACKUP
        interface eth0
        virtual_router_id 53
        priority 80
        advert_int 1
    
        authentication {
            auth_type PASS
            auth_pass xxxx
        }
    
        track_script {
            chk_proxysql
        }
    
        virtual_ipaddress {
            192.168.100.30/24
        }
    }

check\_proxysql.sh

#!/bin/sh
    echo "aaaaa"
    peer_ip='192.168.100.21'
    peer_port=22
    proxysql='proxysql-2'
    vip='192.168.100.30'
    log=/home/keepalived.log
    alias date='date +"%y-%m-%d_%H:%M:%S"'
    echo "`date`  enter script." >> $log
    #check if this keepalived is MASTER
    ip a|grep $vip
    if [ $? -ne 0 ];then
        exit 0
    fi
    echo "`date`  after check keepalived master script." >> $log
    #check if data port(6033) is alive
    data_port_stats=$(timeout 2  bash -c 'cat < /dev/null > /dev/tcp/0.0.0.0/6603' &> /dev/null;echo $?)
    if [ $data_port_stats -eq 0 ];then
        exit 0
    else
        #check if the other keepalived is running
        peer_keepalived=$(ssh -p$peer_port $peer_ip 'systemctl is-active keepalived.service')
        if [ "$peer_keepalived" != "active" ];then
            echo "`date`  data port of $proxysql is not available, but the BACKUP keepalived is not running, so can't do the failover" >> $log
        else
            echo "`date`  data port of proxysql is not available, now SHUTDOWN keepalived." >> $log
            systemctl stop keepalived.service
        fi
    fi

#### 配置宿主机端口转发 ####

##### Step11 在主机配置端口转发 #####

firewall-cmd --permanent --add-forward-port=port=10088:proto=tcp:toaddr=192.168.100.30:toport=6603
    firewall-cmd --reload

最后在本地电脑远程通过ip:10088连接到了192.168.100.30的vip代理下的ProxySQL

参考

\[1\] [http://www.enmotech.com/web/detail/1/171/1.html][http_www.enmotech.com_web_detail_1_171_1.html]

\[2\] [https://blog.csdn.net/jolly10/article/details/80418758][https_blog.csdn.net_jolly10_article_details_80418758]

\[3\] [https://blog.csdn.net/kai404/article/details/77524733][https_blog.csdn.net_kai404_article_details_77524733]

欢迎关注微信交流  
![在这里插入图片描述][70]

[Image 1]: 
[http_www.enmotech.com_web_detail_1_171_1.html]: http://www.enmotech.com/web/detail/1/171/1.html
[https_blog.csdn.net_jolly10_article_details_80418758]: https://blog.csdn.net/jolly10/article/details/80418758
[https_blog.csdn.net_kai404_article_details_77524733]: https://blog.csdn.net/kai404/article/details/77524733
[70]: /images/20220506/c9b1704b906346d9948bcbf3896aa59b.png