etcd服务实现mysql集群，docker部署

准备三台服务器server-200, server-202, server-203

1.每一台服务器上安装etcd服务

yum install etcd

2. 每一台服务器配置etcd，编辑/etc/etcd/etcd.conf , 主要配置如下内容
#[Member]
#ETCD_CORS=""
ETCD_DATA_DIR="/var/lib/etcd/default.etcd"
ETCD_LISTEN_PEER_URLS="http://172.19.102.200:2380" #改成相应的ip
ETCD_LISTEN_CLIENT_URLS="http://172.19.102.200:2379"
ETCD_NAME="infra0"
#
#[Clustering]
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://172.19.102.200:2380" #改成相应的ip
ETCD_ADVERTISE_CLIENT_URLS="http://172.19.102.200:2379"  #改成相应的ip
ETCD_INITIAL_CLUSTER="infra0=http://172.19.102.200:2380,infra1=http://172.19.102.202:2380,infra2=http://172.19.102.203:2380"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_ENABLE_V2="true"

3. 每一台服务器配置完成后，启动etcd作为发现服务
systemctl enable etcd
systemctl start etcd

4. 每一台服务器，启动数据库实例（必须等待至少一个实例完成启动后，才能启动第二个数据库实例）
 docker  run -d  \
   -p 3306:3306 \
   --name db_200 \ #改成相应的实例名称
   -v /root/mysql:/var/lib/mysql \
   --network host \
   --env MYSQL_ROOT_PASSWORD=123456 \
   --env DISCOVERY_SERVICE=172.19.102.200:2379 \
   --env XTRABACKUP_PASSWORD=123456 \
   --env CLUSTER_NAME=galera \
   percona/percona-xtradb-cluster:5.7
 

5. 启动proxysql服务
docker run -d -p 3307:3306 -p 6032:6032 \
  --net=host --name=galera_proxysql \
  -e CLUSTER_NAME=galera \
  -e DISCOVERY_SERVICE=172.19.102.200:2379 \
  -e MYSQL_ROOT_PASSWORD=123456 \
  -e MYSQL_PROXY_USER=tricolor \
  -e MYSQL_PROXY_PASSWORD=123456 \
  perconalab/proxysql

6. 将mysql节点加入到proxysql下， 以及相关mysql的权限授予
修改galera_proxysql容器中的add_cluster_node.sh, 内容如下
#!/bin/bash
set -ex
ipaddr=$(hostname -i | awk ' { print $1 } ')
for i in $(curl http://$DISCOVERY_SERVICE/v2/keys/pxc-cluster/$CLUSTER_NAME/ | jq -r '.node.nodes[]?.key' | awk -F'/' '{print $(NF)}')
do
  last_db=$i
    echo $i
        mysql -h $i -uroot -p$MYSQL_ROOT_PASSWORD -e "GRANT ALL ON *.* TO '$MYSQL_PROXY_USER'@'$ipaddr' IDENTIFIED BY '$MYSQL_PROXY_PASSWORD'"
        mysql -h 127.0.0.1 -P6032 -uadmin -padmin -e "INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_replication_lag) VALUES (0, '$i', 3306, 20);"
 done
mysql -h $last_db -uroot -p$MYSQL_ROOT_PASSWORD -e "CREATE USER p_monitor@$ipaddr IDENTIFIED BY 'p_monitor312';"
mysql -h 127.0.0.1 -P6032 -uadmin -padmin -e "UPDATE global_variables SET variable_value='p_monitor' WHERE variable_name='mysql-monitor_username';"
mysql -h 127.0.0.1 -P6032 -uadmin -padmin -e "UPDATE global_variables SET variable_value='p_monitor312' WHERE variable_name='mysql-monitor_password';"
mysql -h 127.0.0.1 -P6032 -uadmin -padmin -e "INSERT INTO mysql_users (username, password, active, default_hostgroup, max_connections) VALUES ('$MYSQL_PROXY_USER', '$MYSQL_PROXY_PASSWORD', 1, 0, 200);"
mysql -h 127.0.0.1 -P6032 -uadmin -padmin -e "LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;"

改完后， 执行加入操作
docker exec -it galera_proxysql add_cluster_nodes.sh

7.验证
while true; do mysql -utricolor -p123456 -h172.19.102.81 -NBe 'select @@wsrep_node_address'; sleep 1; done

8.恢复操作， 当挂掉的节点超过半数时，仍使集群可用， 在mysql任一正常节点设置：
SET GLOBAL wsrep_provider_options='pc.bootstrap=YES';