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

# 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

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

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

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;

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"
        }
)

! 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
    }
}

#!/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

! 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
    }
}

#!/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

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