服务部署 搭建 MySQL架构 MGR+Consul 高可用

10.88.128.163 consul server 目前只部署了一个server，可部署集群模式 10.88.6.251 mysql server mnode1、consul client 10.88.6.252 mysql server mnode2、consul client 10.88.6.253 mysql server mnode3、consul client

搭建consul 使其mysql-primary和mysql-slave 注册到服务发现上

consul-server：10.88.128.163 consul-client：10.88.6.251、10.88.6.252、10.88.6.253 consul安装so easy

plugin_load_add='group_replication.so'
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot=off #是否需要随着MySQL Server一并启动MGR插件，在有PRIMARY节点时，建议重新设置为ON。
group_replication_local_address= "s1:33061" #当前节点service地址
group_replication_group_seeds= "s1:33061,s2:33061,s3:33061" #种子节点的service地址
group_replication_bootstrap_group=off #是否使用这个Server来引导集群
loose-group_replication_enforce_update_everywhere_checks=off
loose-group_replication_ip_whitelist='0.0.0.0/0'
loose-group_replication_single_primary_mode=on
loose-group_replication_gtid_assignment_block_size=1
​
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE #8.0.20及其之前额版本需要开启此参数
log_bin=binlog #8.0.3及其之前的版本需要开启
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64 #8.0.2及其之前需要开启此参数
​
​
| binlog_checksum                  | CRC32         | NONE           | Update the server variable and the config file |
| transaction_write_set_extraction | OFF           | XXHASH64       | Update the config file and restart the server  |
​
​
 ansible-playbook  -i "139.196.196.50,139.196.138.6,106.14.220.165,101.132.24.35,"  playbook/initalization.yml   -e  "@./extra_varas/initalization.json"
 
 ansible-playbook  -i "139.196.196.50,139.196.138.6,106.14.220.165,"  playbook/mysqld.yml  -t "RunStart" --extra-vars  "@./extra_varas/mysqld.json"
 
ansible  all   -i "root@139.196.196.50,root@139.196.138.6,root@106.14.220.165,root@101.132.24.35," \
-m "shell" -a "mkdir -p   /data/consul/shell" 
​
​
​
​
CREATE USER 'rocky'@'%' IDENTIFIED WITH mysql_native_password BY 'rocky';
GRANT ALL ON *.* TO 'rocky'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES;
​
​
​
​
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE #8.0.20及其之前额版本需要开启此参数
log_bin=binlog #8.0.3及其之前的版本需要开启
log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64 #8.0.2及其之前需要开启此参数
​
​
​
​
plugin_load_add='group_replication.so'
loose-group_replication_group_name="8B715EA0-7D00-4337-AE14-7925ED4ACC29"
loose-group_replication_start_on_boot=off 
loose-group_replication_local_address= "172.19.200.80:3306" #当前节点service地址
loose-group_replication_group_seeds= "172.19.200.79:3306,172.19.200.80:3306,172.19.200.81:3306"
loose-group_replication_bootstrap_group=off #是否使用这个Server来引导集群
loose-group_replication_enforce_update_everywhere_checks=off
loose-group_replication_ip_whitelist='0.0.0.0/0'
loose-group_replication_single_primary_mode=on
loose-group_replication_gtid_assignment_block_size=1
​
​
​
SET SQL_LOG_BIN=0;
CREATE USER repl_user@'%' IDENTIFIED BY 'user_repl';
GRANT REPLICATION SLAVE ON . TO rpl_user@'%';
mysql> GRANT BACKUP_ADMIN ON . TO 'repl_user'@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
​
​
CHANGE MASTER TO MASTER_USER='rocky', MASTER_PASSWORD='rocky' FOR CHANNEL 'group_replication_recovery';
​
change master to master_user='repl',master_password='123456' for channel 'group_replication_recovery';
​
SET SQL_LOG_BIN=0;
CREATE USER repl_user@'%' IDENTIFIED BY 'user_repl';
GRANT REPLICATION SLAVE ON . TO repl_user@'%';
GRANT BACKUP_ADMIN ON . TO repl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1
​
CHANGE MASTER TO MASTER_USER='rocky', MASTER_PASSWORD='rocky' FOR CHANNEL 'group_replication_recovery';
​
​
mysql> START GROUP_REPLICATION;
mysql> START GROUP_REPLICATION USER='rocky', PASSWORD='rocky'; #用此方式启动集群也可以

在官网:https://www.consul.io/downloads.html下载对应的版本，解压后copy consul 到/usr/local/bin/下即可

touch /var/log/consul.log /etc/consul.d/proxysql.json
​
### Install pre-requisite packages:
sudo dnf -y install wget unzip bind-utils dnsmasq
 
### Install Consul:
useradd consul
mkdir -p /opt/consul /etc/consul.d
cd /opt/consul
wget https://releases.hashicorp.com/consul/1.14.3/consul_1.14.3_linux_amd64.zip
unzip consul_1.14.3_linux_amd64.zip
ln -s /opt/consul/consul /usr/bin/consul

分别在4台机器上安装然后运行

​
mkdir -pv etc/consul.d/  && mkdir -pv data/consul/
​
​
在consul server 10.88.128.163上 编写配置文件
vim /etc/consul.d/server.json
{
  "data_dir": "/data/consul",
  "datacenter": "dc1",
  "log_level": "INFO",
  "server": true,
  "advertise_addr":"10.88.128.163",
  "bootstrap_expect": 3,
  "bind_addr": "10.88.128.163",
  "client_addr": "10.88.128.163",
  "ui":true
}
​
在consul client 10.88.6.251、10.88.6.252、10.88.6.253上编写配置文件,三台服务器的上bind_addr 修改为响应IP即可
vim  /etc/consul.d/client.json
{
  "data_dir": "/data/consul",
  "enable_script_checks": true,
  "bind_addr": "10.88.6.252",
  "retry_join": ["10.88.128.163"],
  "retry_interval": "30s",
  "rejoin_after_leave": true,
  "start_join": ["10.88.128.163"]
}
 
启动consul server 在10.88.128.163上
pm2 start consul -- agent -config-dir=/etc/consul.d 
nohup consul agent -config-dir=/etc/consul.d > /data/consul/consul.log &
​
启动consul client 在10.88.6.251、10.88.6.252、10.88.6.253
​
nohup consul agent -config-dir=/etc/consul.d > data/consul/consul.log &
观察consul server的log日志3个client自动注册到了consul上了
​
​

consul client端接服务发现的json脚本

检测master
[root@mnode1 consul.d]# cat etc/consul.d/master.json
{
  "services": [
    {
      "name": "write-mysql-primary",
      "tags": [
        "master-write"
      ],
      "address": "10.88.6.251",
      "port": 3309,
      "checks": [
        {
           "Args":["/data/consul/shell/check_mysql_mgr_master.sh"],
          "Shell": "/bin/bash",
          "interval": "15s"
        }
      ]
    }
  ]
}
检测slave
 cat etc/consul.d/slave.json
{
  "services": [
    {
      "name": "read-mysql-slave",
      "tags": [
        "slave-read"
      ],
      "address": "10.88.6.251",
      "port": 3309,
      "checks": [
        {
           "Args":["/data/consul/shell/check_mysql_mgr_slave.sh"],
           "Shell": "/bin/bash",
           "interval": "15s"
        }
      ]
    }
  ]
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  检测primay 脚本
vim data/consul/shell/check_mysql_mgr_master.sh

检测slave脚本
vim data/consul/shell/check_mysql_mgr_slave.sh



dig @127.0.0.1 -p 8600 write-mysql-primary.service.consul






{
  "services": [
    {
      "name": "read-mysql-slave",
      "tags": [
        "slave-read"
      ],
      "port": 3306,
      "checks": [
        {
           "Args":["/data/consul/shell/check_mysql_mgr_slave.sh"],
           "Shell": "/bin/bash",
           "interval": "15s"
        }
      ]
    }
  ]
}



{
  "services": [
    {
      "name": "write-mysql-primary",
      "tags": [
        "master-write"
      ],
      "port": 3306,
      "checks": [
        {
           "Args":["/data/consul/shell/check_mysql_mgr_master.sh"],
          "Shell": "/bin/bash",
          "interval": "15s"
        }
      ]
    }
  ]
}


error="fork/exec /data/consul/shell/check_mysql_mgr_master.sh: exec format error"

编辑配置 vim /data/consul/shell/check_mysql_mgr_slave.sh

#!/bin/bash
port=3309
user="root"
passwod="123123"

comm="mysql -u$user -P $port -p$passwod -S  /data/mysql_3306_core/run/mysql_3306_core.sock"
value=`$comm -Nse "select 1"`
primary_member=`$comm -Nse "select variable_value from performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member'"`
server_uuid=`$comm -Nse "select variable_value from performance_schema.global_variables where VARIABLE_NAME='server_uuid';"`


# 判断mysql是否存活
if [ -z $value ]
then
   echo "mysql $port is down....."
   exit 2
fi

# 判断节点状态
node_state=`$comm -Nse "select MEMBER_STATE from performance_schema.replication_group_members where MEMBER_ID='$server_uuid'"`
if [ $node_state != "ONLINE" ]
then
   echo "MySQL $port state is not online...."
   exit 2
fi


# 判断是不是主节点
if [[ $server_uuid == $primary_member ]]
then
   echo "MySQL $port  Instance is master ........"
   exit 0
else
   echo "MySQL $port  Instance is slave ........"
   exit 2
fi

编辑配置 vim /data/consul/shell/check_mysql_mgr_master.sh

#!/bin/bash
port=3306
user="root"
passwod="123123"

comm="mysql -u$user -P $port -p$passwod -S  /data/mysql_3306_core/run/mysql_3306_core.sock"
value=`$comm -Nse "select 1"`
primary_member=`$comm -Nse "select variable_value from performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member'"`
server_uuid=`$comm -Nse "select variable_value from performance_schema.global_variables where VARIABLE_NAME='server_uuid';"`


# 判断mysql是否存活
if [ -z $value ]
then
   echo "mysql $port is down....."
   exit 2
fi


# 判断节点状态
node_state=`$comm -Nse "select MEMBER_STATE from performance_schema.replication_group_members where MEMBER_ID='$server_uuid'"`
if [ $node_state != "ONLINE" ]
then
   echo "MySQL $port state is not online...."
   exit 2
fi


# 判断是不是主节点
if [[ $server_uuid == $primary_member ]]
then
   echo "MySQL $port  Instance is master ........"
   exit 0
else
   echo "MySQL $port  Instance is slave ........"
   exit 2
fi

{
  "services": [
    {
      "name": "write-mysql-primary",
      "tags": [
        "master-write"
      ],
      "port": 3306,
      "address": "172.19.200.79",
      "checks": [
        {
          "Args":["/data/consul/shell/check_mysql_mgr_master.sh"],
          "Shell": "/bin/bash",
          "interval": "15s"
        }
      ]
    }
  ]
}

{
  "services": [
    {
      "name": "write-mysql-primary",
      "tags": [
        "master-write"
      ],
      "port": 3306,
      "address": "172.19.200.79",
      "checks": [
        {
          "Args":["/data/consul/shell/check_mysql_mgr_master.sh"],
          "Shell": "/bin/bash",
          "interval": "4s"
        }
      ]
    }
  ]
}