MGR+Consul+ProxySQL

--------------------------------------------------------------------------------------------------------------------------------------------
CentOS7 配置如下

5.7.22 Group Replication
MySQL5.7.22
安装略

在三台db服务器上面设置/etc/hosts映射，如下：
192.168.1.101 mydb1
192.168.1.102 mydb2
192.168.1.103 mydb3

安装的数据库服务器：
数据库服务器地址                  端口     数据目录                                  Server-id
192.168.1.101（mydb1） 3306    /app/mysqldata/3306/data/ 1013306
192.168.1.102（mydb2） 3306    /app/mysqldata/3306/data/ 1023306
192.168.1.103（mydb3） 3306    /app/mysqldata/3306/data/ 1033306

在3台mysql实例上mydb1、mydb2、mydb3分配账号：
mysql> set sql_log_bin=0;
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.%' IDENTIFIED BY 'repl';
mysql> set sql_log_bin=1;

在3台my.cnf上面配置gtid：
[mysqld]
gtid_mode=ON
log-slave-updates=ON
enforce-gtid-consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=none
#group replication config
plugin-load = group_replication.so
transaction_write_set_extraction = XXHASH64
group_replication_start_on_boot = OFF
group_replication_bootstrap_group = OFF
group_replication_group_name = '__GROUP_UUID__'
group_replication_local_address = 'mydb1:6606'
group_replication_group_seeds = 'mydb1:6606,mydb2:6606,mydb3:6606'
group_replication_single_primary_mode = true
group_replication_enforce_update_everywhere_checks = false

[mysqld]
gtid_mode=ON
log-slave-updates=ON
enforce-gtid-consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=none
#group replication config
plugin-load = group_replication.so
transaction_write_set_extraction = XXHASH64
group_replication_start_on_boot = OFF
group_replication_bootstrap_group = OFF
group_replication_group_name = '__GROUP_UUID__'
group_replication_local_address = 'mydb2:6606'
group_replication_group_seeds = 'mydb1:6606,mydb2:6606,mydb3:6606'
group_replication_single_primary_mode = true
group_replication_enforce_update_everywhere_checks = false

[mysqld]
gtid_mode=ON
log-slave-updates=ON
enforce-gtid-consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=none
#group replication config
plugin-load = group_replication.so
transaction_write_set_extraction = XXHASH64
group_replication_start_on_boot = OFF
group_replication_bootstrap_group = OFF
group_replication_group_name = '__GROUP_UUID__'
group_replication_local_address = 'mydb3:6606'
group_replication_group_seeds = 'mydb1:6606,mydb2:6606,mydb3:6606'
group_replication_single_primary_mode = true
group_replication_enforce_update_everywhere_checks = false
--------------
group_replication_allow_local_disjoint_gtids_join=off
group_replication_member_weight=80
group_replication_unreachable_majority_timeout=5
group_replication_compression_threshold=131072
group_replication_transaction_size_limit=20971520

重启3台mysql服务

开始构建集群，在mydb1（master）上执行：
# 构建集群
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
#开启group_replication
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
mysql> select * from performance_schema.replication_group_members;
mysql> select * from performance_schema.replication_group_member_stats\G
mysql> select * from performance_schema.replication_connection_status;
mysql> select * from performance_schema.replication_applier_status;

mydb2、mydb3上加入
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;

验证集群复制功能
create database test;
use test;
create table t(id int primary key auto_increment,name varchar(20));
insert into t(name) values ('allen');
insert into t(name) values ('bob');
select * from t;

默认为单主模式，如果要使用多主模式，则需要在加入组前将参数设置为set global group_replication_single_primary_mode=off;

基于主键的并行执行
set global slave_parallel_type='LOGICAL_CLOCK';
set global slave_parallel_workers=N;
set global slave_preserve_commit_order=ON;
Group Replication在开启并行复制时，要求必须要设置slave_preserve_commit_order的值为ON
打开这个参数可以保证Applier上执行事务的提交顺序和源MySQL服务器上的提交顺序相同

强制移除故障成员，只需要在列表中的任意一个成员上设置即可
set global group_replication_force_members = 'mydb1:6606,mydb2:6606';

Group Replication的监控
Group Replication的状态信息被存储到了以下五个performance_schema表中，可以很方便地进行SQL语句查询：
replication_group_members
replication_group_member_stats
replication_connection_status
replication_applier_status
threads

查看主节点是哪个
show global status like "group_replication_primary_member";
select * from performance_schema.replication_group_members where member_id =(select variable_value from performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member');

本节点执行的GTID
select @@global.gtid_executed\G
获取的GTID
SELECT Received_transaction_set FROM performance_schema.replication_connection_status WHERE Channel_name = 'group_replication_applier';

Group Replication是MySQL复制今后的发展方向
Group Replication创建了一个独立的TCP端口进行通信，各个MySQL服务器上的Group Replication插件通过这个端口连接在一起，两两之间可以直接通信。Binlog Event的传输并不像异步复制那样是简单的点到点之间的传输。Group Replication在传输数据时，使用了Paxos协议。Paxos协议保证了数据传输的一致性和原子性。Group Replication基于Paxos协议构建了一个分布式的状态复制机制，这是实现多主复制的核心技术。Group Replication中不会出现脑裂的现象。Group Replication的冗余能力很好，能够保证Binlog Event至少被复制到超过一半的成员上
单主模式：组内的成员自动选举出主成员。初始化时，被初始化的成员自动选举为主成员，其他加入组的成员自动成为从成员。当主成员发生故障或离开组时，会从组内的其他成员中选出一个新的主成员。选取主成员的方法很简单，首先对所有在线成员的UUID进行排序，选取UUID最小的成员作为主成员
读写模式的自动切换
Set global super_read_only=1;
Set global super_read_only=0;
多主模式：在启动Group Replication插件时，它会检测用户是否配置了MySQL的自增变量。如果用户没有配置这两个变量(auto_increment_offset和auto_increment_increment都为1)，则会自动将group_replication_auto_increment_increment和server-id的值设置到MySQL的auto_increment_increment和auto_increment_offset全局变量中

注意使用mysqldump进行导入的时候gtid_purged

测试1：MGR主节点宕机
测试2：MGR一个从节点宕机
测试3：过半从节点同时宕机
测试4：主节点连续宕机
测试5：主节点网络抖动测试
测试6：从节点网络抖动测试
测试7：主从节点网络分区
测试8：主从节点网络延迟
测试9：主节点KILL大事务
测试10：对MyISAM表的处理
测试11：MGR对外键的支持-单主模式
测试12：mysqldump对MGR的支持
测试13：无主键表的支持
测试14：gh-ost对MGR的支持
测试15：pt-osc对MGR的支持
测试16：xtrabackup对MGR的支持
测试17：binlog_format=statement的影响

--------------------------------------------------------------------------------------------------------------------------------------------
Consul部分
consul_1.2.3_linux_amd64.zip
192.168.1.121 consul server
192.168.1.101 consul client   mgr
192.168.1.102 consul client   mgr
192.168.1.103 consul client   mgr

1、新建目录(server和client都要建立)
[root@kettle1 ~]# mkdir /etc/consul.d/   //存放consul配置文件
[root@kettle1 ~]# mkdir -p /data/consul  //数据目录
[root@kettle1 ~]# mkdir -p /data/consul/shell  //存放检查脚本
[root@mydb1 ~]# mkdir /etc/consul.d/   //存放consul配置文件
[root@mydb1 ~]# mkdir -p /data/consul/  //数据目录
[root@mydb1 ~]# mkdir -p /data/consul/shell/  //存放检查脚本
[root@mydb2 ~]# mkdir /etc/consul.d/   //存放consul配置文件
[root@mydb2 ~]# mkdir -p /data/consul/  //数据目录
[root@mydb2 ~]# mkdir -p /data/consul/shell/  //存放检查脚本
[root@mydb3 ~]# mkdir /etc/consul.d/   //存放consul配置文件
[root@mydb3 ~]# mkdir -p /data/consul/  //数据目录
[root@mydb3 ~]# mkdir -p /data/consul/shell/  //存放检查脚本

2、新建server.json，将相关配置写入(两种方法：一种写入配置文件，启动时指定；另一种启动时候写成命令行的形式)
[root@kettle1 ~]# cat /etc/consul.d/server.json
{
"data_dir": "/data/consul",
"datacenter": "dc1",
"log_level": "INFO",          //打印日志级别
"server": true,              //是否是consul server
"node_name": "wsh",          //集群node，集群中必须唯一，默认为主机名
"bootstrap_expect": 1,          //通知consul我们准备加入的server节点个数，该参数是为了延迟日志复制的启动直到我们指定数量的server节点成功加入后启动
"bind_addr": "192.168.1.121",         //指明节点的ip地址，用于集群之间的交流
"client_addr": "192.168.1.121",       //client绑定的接口，默认是127.0.0.1
"ui":true
}
配置的时候将注释去掉

3、启动server
[root@kettle1 ~]# consul agent -config-dir=/etc/consul.d -enable-script-checks > /data/consul/consul.log &
[root@kettle1 soft]# consul members -http-addr='192.168.1.121:8500'

4、配置client：除了自身的client.json，还加入了检查MGR的脚本--192.168.1.101/102/103
[root@mydb1 ~]# cat /etc/consul.d/client.json
{
"data_dir": "/data/consul",
"enable_script_checks": true,
"bind_addr": "192.168.1.101",
"retry_join": ["192.168.1.121"],
"retry_interval": "30s",
"rejoin_after_leave": true,
"start_join": ["192.168.1.121"] ,
"node_name": "mgr_client1"
}
[root@mydb2 ~]# cat /etc/consul.d/client.json
{
"data_dir": "/data/consul",
"enable_script_checks": true,
"bind_addr": "192.168.1.102",
"retry_join": ["192.168.1.121"],
"retry_interval": "30s",
"rejoin_after_leave": true,
"start_join": ["192.168.1.121"] ,
"node_name": "mgr_client2"
}
[root@mydb3 ~]# cat /etc/consul.d/client.json
{
"data_dir": "/data/consul",
"enable_script_checks": true,
"bind_addr": "192.168.1.103",
"retry_join": ["192.168.1.121"],
"retry_interval": "30s",
"rejoin_after_leave": true,
"start_join": ["192.168.1.121"] ,
"node_name": "mgr_client3"
}
[root@mydb1 ~]# cat /etc/consul.d/r-test-mgr-ser.json
{
"service": {
    "name": "r-test-3306-mydb-ser",
    "tags": ["测试-3306"],
    "address": "192.168.1.101",
    "meta": {
      "meta": "for my service"
    },
    "port": 3306,
    "enable_tag_override": false,
    "checks": [
      {
        "args": ["/data/consul/shell/check_mysql_mgr_slave.sh"],
        "interval": "1s"
      }
    ]
}
}
[root@mydb1 ~]# cat /etc/consul.d/w-test-mgr-ser.json
{
"service": {
    "name": "w-test-3306-mydb-ser",
    "tags": ["测试-3306"],
    "address": "192.168.1.101",
    "meta": {
      "meta": "for my service"
    },
    "port": 3306,
    "enable_tag_override": false,
    "checks": [
      {
        "args": ["/data/consul/shell/check_mysql_mgr_master.sh"],
        "interval": "10s"
      }
    ]
}
}
注意在mydb2,mydb3上调整ip
检测脚本如下
[root@mydb1 ~]# cat /data/consul/shell/check_mysql_mgr_master.sh
#!/bin/bash
host="192.168.1.101"
port=3306
user="dba_user"
passwod="msds007"
comm="/usr/local/mysql/bin/mysql -u$user -h$host -P $port -p$passwod"
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
[root@mydb1 ~]# cat /data/consul/shell/check_mysql_mgr_slave.sh
#!/bin/bash
host="192.168.1.101"
port=3306
user="dba_user"
passwod="msds007"
comm="/usr/local/mysql/bin/mysql -u$user -h$host -P $port -p$passwod"
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 slave ........"
   exit 0
else
   node_num=`$comm -Nse "select count(*) from performance_schema.replication_group_members"`
# 判断如果没有任何从节点，主节点也注册从角色服务。
   if [ $node_num -eq 1 ]
   then
       echo "MySQL $port Instance is slave ........"
       exit 0
   else
       echo "MySQL $port Instance is master ........"
       exit 2
   fi
fi

5、启动3台client
[root@mydb1 ~]# consul agent -config-dir=/etc/consul.d -enable-script-checks > /data/consul/consul.log &
[root@mydb2 ~]# consul agent -config-dir=/etc/consul.d -enable-script-checks > /data/consul/consul.log &
[root@mydb3 ~]# consul agent -config-dir=/etc/consul.d -enable-script-checks > /data/consul/consul.log &

6、查看集群状态
[root@kettle1 ~]# consul members -http-addr='192.168.1.121:8500'
Node         Address             Status Type    Build Protocol DC   Segment
wsh          192.168.1.121:8301 alive   server 1.2.3 2         dc1 <all>
mgr_client1 192.168.1.101:8301 alive   client 1.2.3 2         dc1 <default>
mgr_client2 192.168.1.102:8301 alive   client 1.2.3 2         dc1 <default>
mgr_client3 192.168.1.103:8301 alive   client 1.2.3 2         dc1 <default>
[root@mydb1 ~]# consul members
Node         Address             Status Type    Build Protocol DC   Segment
wsh          192.168.1.121:8301 alive   server 1.2.3 2         dc1 <all>
mgr_client1 192.168.1.101:8301 alive   client 1.2.3 2         dc1 <default>
mgr_client2 192.168.1.102:8301 alive   client 1.2.3 2         dc1 <default>
mgr_client3 192.168.1.103:8301 alive   client 1.2.3 2         dc1 <default>

[root@mydb1 ~]# dig @192.168.1.121 -p 8600 w-test-3306-mydb-ser.service.consul
; <<>> DiG 9.9.4-RedHat-9.9.4-50.el7 <<>> @192.168.1.121 -p 8600 w-test-3306-mydb-ser.service.consul
; (1 server found)
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 17258
;; flags: qr aa rd; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 2
;; WARNING: recursion requested but not available

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 4096
;; QUESTION SECTION:
;w-test-3306-mydb-ser.service.consul. IN A

;; ANSWER SECTION:
w-test-3306-mydb-ser.service.consul. 0 IN A 192.168.1.101

;; ADDITIONAL SECTION:
w-test-3306-mydb-ser.service.consul. 0 IN TXT "consul-network-segment="

;; Query time: 0 msec
;; SERVER: 192.168.1.121#8600(192.168.1.121)
;; WHEN: 三 10月 17 01:57:18 CST 2018
;; MSG SIZE rcvd: 116

[root@mydb1 ~]# dig @192.168.1.121 -p 8600 r-test-3306-mydb-ser.service.consul
; <<>> DiG 9.9.4-RedHat-9.9.4-50.el7 <<>> @192.168.1.121 -p 8600 r-test-3306-mydb-ser.service.consul
; (1 server found)
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 54349
;; flags: qr aa rd; QUERY: 1, ANSWER: 2, AUTHORITY: 0, ADDITIONAL: 3
;; WARNING: recursion requested but not available

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 4096
;; QUESTION SECTION:
;r-test-3306-mydb-ser.service.consul. IN A

;; ANSWER SECTION:
r-test-3306-mydb-ser.service.consul. 0 IN A 192.168.1.102
r-test-3306-mydb-ser.service.consul. 0 IN A 192.168.1.103

;; ADDITIONAL SECTION:
r-test-3306-mydb-ser.service.consul. 0 IN TXT "consul-network-segment="
r-test-3306-mydb-ser.service.consul. 0 IN TXT "consul-network-segment="

;; Query time: 1 msec
;; SERVER: 192.168.1.121#8600(192.168.1.121)
;; WHEN: 三 10月 17 01:58:45 CST 2018
;; MSG SIZE rcvd: 168

7、模拟故障
停掉写节点192.168.1.101的MySQL服务，查看状态：
[root@mydb1 ~]# dig @192.168.1.121 -p 8600 w-test-3306-mydb-ser.service.consul
; <<>> DiG 9.9.4-RedHat-9.9.4-50.el7 <<>> @192.168.1.121 -p 8600 w-test-3306-mydb-ser.service.consul
; (1 server found)
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 49700
;; flags: qr aa rd; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 2
;; WARNING: recursion requested but not available

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 4096
;; QUESTION SECTION:
;w-test-3306-mydb-ser.service.consul. IN A

;; ANSWER SECTION:
w-test-3306-mydb-ser.service.consul. 0 IN A 192.168.1.102

;; ADDITIONAL SECTION:
w-test-3306-mydb-ser.service.consul. 0 IN TXT "consul-network-segment="

;; Query time: 0 msec
;; SERVER: 192.168.1.121#8600(192.168.1.121)
;; WHEN: 三 10月 17 02:04:02 CST 2018
;; MSG SIZE rcvd: 116

再停掉192.168.1.102的MySQL服务，再次查看状态：读写节点全都压在了同一台机器
[root@mydb1 ~]# dig @192.168.1.121 -p 8600 w-test-3306-mydb-ser.service.consul
; <<>> DiG 9.9.4-RedHat-9.9.4-50.el7 <<>> @192.168.1.121 -p 8600 w-test-3306-mydb-ser.service.consul
; (1 server found)
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 48398
;; flags: qr aa rd; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 2
;; WARNING: recursion requested but not available

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 4096
;; QUESTION SECTION:
;w-test-3306-mydb-ser.service.consul. IN A

;; ANSWER SECTION:
w-test-3306-mydb-ser.service.consul. 0 IN A 192.168.1.103

;; ADDITIONAL SECTION:
w-test-3306-mydb-ser.service.consul. 0 IN TXT "consul-network-segment="

;; Query time: 0 msec
;; SERVER: 192.168.1.121#8600(192.168.1.121)
;; WHEN: 三 10月 17 02:06:22 CST 2018
;; MSG SIZE rcvd: 116

ping w-test-3306-mydb-ser.service.consul
ping r-test-3306-mydb-ser.service.consul

--------------------------------------------------------------------------------------------------------------------------------------------
ProxySQL部分
安装依赖包
# yum install -y perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL
# vim /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
# yum install -y proxysql

查看proxysql涉及到哪些文件
# rpm -ql proxysql
/etc/init.d/proxysql
/etc/proxysql.cnf
/usr/bin/proxysql
/usr/share/proxysql/tools/proxysql_galera_checker.sh
/usr/share/proxysql/tools/proxysql_galera_writer.pl

# systemctl status proxysql.service
● proxysql.service - LSB: High Performance Advanced Proxy for MySQL
   Loaded: loaded (/etc/rc.d/init.d/proxysql; bad; vendor preset: disabled)
   Active: inactive (dead)
     Docs: man:systemd-sysv-generator(8)

/etc/init.d/proxysql脚本涉及到如下目录、文件
OLDDATADIR="/var/run/proxysql"
DATADIR="/var/lib/proxysql"
OPTS="-c /etc/proxysql.cnf -D $DATADIR"
PIDFILE="$DATADIR/proxysql.pid"

# more /run/systemd/generator.late/proxysql.service
# Automatically generated by systemd-sysv-generator

[Unit]
Documentation=man:systemd-sysv-generator(8)
SourcePath=/etc/rc.d/init.d/proxysql
Description=LSB: High Performance Advanced Proxy for MySQL
Before=runlevel2.target
Before=runlevel3.target
Before=runlevel4.target
Before=runlevel5.target
Before=shutdown.target
After=network-online.target
After=vmware-tools.service
After=vmware-tools-thinprint.service
Conflicts=shutdown.target

[Service]
Type=forking
Restart=no
TimeoutSec=5min
IgnoreSIGPIPE=no
KillMode=process
GuessMainPID=no
RemainAfterExit=yes
ExecStart=/etc/rc.d/init.d/proxysql start
ExecStop=/etc/rc.d/init.d/proxysql stop
ExecReload=/etc/rc.d/init.d/proxysql reload

检查版本
# which proxysql
/usr/bin/proxysql

# proxysql --version
ProxySQL version v1.4.10-1-g5eb0f3e, codename Truls

# proxysql --help
High Performance Advanced Proxy for MySQL

USAGE: proxysql [OPTIONS]

OPTIONS:

-c, --config ARG             Configuraton file
-D, --datadir ARG            Datadir
-e, --exit-on-error          Do not restart ProxySQL if crashes
-f, --foreground             Run in foreground
-h, -help, --help, --usage   Display usage instructions.
-M, --no-monitor             Do not start Monitor Module
-n, --no-start               Starts only the admin service
-r, --reuseport              Use SO_REUSEPORT
-S, --admin-socket ARG       Administration Unix Socket
-V, --version                Print version
--idle-threads               Create auxiliary threads to handle idle connections
--initial                    Rename/empty database file
--reload                     Merge config file into database file
--sqlite3-server             Enable SQLite3 Server

ProxySQL rev. v1.4.10-1-g5eb0f3e -- Tue Aug 7 12:31:55 2018
Copyright (C) 2013-2018 ProxySQL LLC
This program is free and without warranty

配置文件/etc/proxysql.cnf
配置文件只在第一次启动的时候读取进行初始化，后面只读取db文件
先启动，然后再修改参数

启动proxysql
# systemctl start proxysql
# ps -ef | grep proxysql
root       3157      1 0 01:21 ?        00:00:00 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
root       3158   3157 1 01:21 ?        00:00:00 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
root       3183   2821 0 01:21 pts/0    00:00:00 grep --color=auto proxysql
# netstat -antp | grep proxysql
tcp        0      0 0.0.0.0:6032            0.0.0.0:*               LISTEN      3158/proxysql
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      3158/proxysql

6032是管理端口
6033是对外服务端口
默认管理用户及密码：admin,admin

连接proxysql 6032管理端口
# mysql -uadmin -padmin -h 127.0.0.1 -P6032
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)

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.

global_variables参数设置，通过set来设置

mysql_query_rules：
指定Query路由到后端不同服务器的规则列表

mysql_replication_hostgroups：
监视指定主机组中所有服务器的read_only值，并且根据read_only的值将服务器分配给写入器或读取器主机组

mysql_servers：
后端可以连接MySQL服务器的列表

mysql_users：
配置后端数据库的账号和监控的账号

disk数据库
持久化到硬盘的配置，sqlite数据文件

monitor数据库
存储monitor模块收集的信息，主要是对后端db的健康、延迟检查
(admin@127.0.0.1) [main]> show tables from monitor;
+------------------------------------+
| tables                             |
+------------------------------------+
| mysql_server_connect_log           |
| mysql_server_group_replication_log |
| mysql_server_ping_log              |
| mysql_server_read_only_log         |
| mysql_server_replication_lag_log   |
+------------------------------------+
5 rows in set (0.00 sec)

基本配置
配置有三个层次：runtime<=>memory<=>disk/config file
RUNTIME：代表proxysql当前生效的正在使用的配置，无法直接修改这里的配置，必须要从下一层load进来
MEMORY：MEMORY这一层上面连接RUNTIME层，下面连接持久层。在这层可以正常操作proxysql配置，随便修改，不会影响生产环境
修改一个配置一般都是先在MEMORY层完成，然后确认正常之后再加载到RUNTIME和持久层到磁盘上
DISK和CONFIG FILE：持久化配置信息，重启后内存中的配置信息会丢失，所以需要将配置信息保留在磁盘中。重启时，可以从磁盘快速加载回来

添加mysql服务器列表
就是插入数据到mysql_servers
(admin@127.0.0.1) [main]> insert into mysql_servers(hostgroup_id,hostname,port) values (1,'192.168.1.101',3306);
Query OK, 1 row affected (0.00 sec)
(admin@127.0.0.1) [main]> insert into mysql_servers(hostgroup_id,hostname,port) values (1,'192.168.1.102',3306);
Query OK, 1 row affected (0.00 sec)
(admin@127.0.0.1) [main]> insert into mysql_servers(hostgroup_id,hostname,port) values (1,'192.168.1.103',3306);
Query OK, 1 row affected (0.00 sec)
(admin@127.0.0.1) [main]> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)
(admin@127.0.0.1) [main]> save mysql servers to disk;
Query OK, 0 rows affected (0.03 sec)
(admin@127.0.0.1) [main]> select * from main.mysql_servers;
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 192.168.1.101 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 192.168.1.102 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 192.168.1.103 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
所有操作都要记得load to runtime和save to disk

在mydb1库中执行
mysql> create user 'usr_proxysql_mon'@'192.168.1.%' identified by '2wsx3edc';
mysql> grant all privileges on *.* to 'usr_proxysql_mon'@'192.168.1.%';
mysql> flush privileges;

添加监控账号
(admin@127.0.0.1) [main]> set mysql-monitor_username='usr_proxysql_mon';
Query OK, 1 row affected (0.00 sec)
(admin@127.0.0.1) [main]> set mysql-monitor_password='2wsx3edc';
Query OK, 1 row affected (0.00 sec)
(admin@127.0.0.1) [main]> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
(admin@127.0.0.1) [main]> save mysql variables to disk;
Query OK, 96 rows affected (0.30 sec)
(admin@127.0.0.1) [main]> select * from main.global_variables where variable_name in ('mysql-monitor_username','mysql-monitor_password');
+------------------------+------------------+
| variable_name          | variable_value   |
+------------------------+------------------+
| mysql-monitor_password | 2wsx3edc         |
| mysql-monitor_username | usr_proxysql_mon |
+------------------------+------------------+
2 rows in set (0.01 sec)
(admin@127.0.0.1) [main]> select * from main.runtime_global_variables where variable_name in ('mysql-monitor_username','mysql-monitor_password');
+------------------------+------------------+
| variable_name          | variable_value   |
+------------------------+------------------+
| mysql-monitor_password | 2wsx3edc         |
| mysql-monitor_username | usr_proxysql_mon |
+------------------------+------------------+
2 rows in set (0.00 sec)
(admin@127.0.0.1) [(none)]> select * from monitor.mysql_server_connect_log;

添加组复制信息
(admin@127.0.0.1) [main]> 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,100,0,100);
Query OK, 1 row affected (0.00 sec)
(admin@127.0.0.1) [main]> select * from mysql_group_replication_hostgroups;
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| 1                | 2                       | 3                | 4                 | 1      | 100         | 0                     | 100                     | NULL    |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
1 row in set (0.00 sec)
(admin@127.0.0.1) [main]> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)
(admin@127.0.0.1) [main]> save mysql servers to disk;
Query OK, 0 rows affected (0.04 sec)
(admin@127.0.0.1) [main]> select * from runtime_mysql_servers;
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 192.168.1.101 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 3            | 192.168.1.102 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 3            | 192.168.1.103 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.01 sec)

创建检查MGR节点状态的函数和视图，该视图将为ProxySQL提供组复制相关的监控状态指标
在mydb1库中添加如下的试图，及其依赖的存储过程
下载https://github.com/lefred/mysql_gr_routing_check/blob/master/addition_to_sys.sql
[root@mydb1 ~]# cat addition_to_sys.sql
USE sys;

DELIMITER $$
CREATE FUNCTION IFZERO(a INT, b INT)
RETURNS INT
DETERMINISTIC
RETURN IF(a = 0, b, a)$$

CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)
RETURNS INT
DETERMINISTIC
RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$

CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))
RETURNS TEXT(10000)
DETERMINISTIC
RETURN GTID_SUBTRACT(g, '')$$

CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE result BIGINT DEFAULT 0;
DECLARE colon_pos INT;
DECLARE next_dash_pos INT;
DECLARE next_colon_pos INT;
DECLARE next_comma_pos INT;
SET gtid_set = GTID_NORMALIZE(gtid_set);
SET colon_pos = LOCATE2(':', gtid_set, 1);
WHILE colon_pos != LENGTH(gtid_set) + 1 DO
     SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);
     SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);
     SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);
     IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN
       SET result = result +
         SUBSTR(gtid_set, next_dash_pos + 1,
                LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -
         SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;
     ELSE
       SET result = result + 1;
     END IF;
     SET colon_pos = next_colon_pos;
END WHILE;
RETURN result;
END$$

CREATE FUNCTION gr_applier_queue_length()
RETURNS INT
DETERMINISTIC
BEGIN
RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT Received_transaction_set FROM performance_schema.replication_connection_status WHERE Channel_name = 'group_replication_applier' ), (SELECT @@global.GTID_EXECUTED) )));
END$$

CREATE FUNCTION gr_member_in_primary_partition()
RETURNS VARCHAR(3)
DETERMINISTIC
BEGIN
RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN performance_schema.replication_group_member_stats USING(member_id));
END$$

CREATE VIEW gr_member_routing_candidate_status
AS
SELECT sys.gr_member_in_primary_partition() as viable_candidate,
       IF((SELECT (SELECT GROUP_CONCAT(variable_value) FROM performance_schema.global_variables WHERE variable_name IN ('read_only','super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,
       sys.gr_applier_queue_length() as transactions_behind,
       Count_Transactions_in_queue as 'transactions_to_cert'
from performance_schema.replication_group_member_stats;$$
DELIMITER ;

mysql> source /root/addition_to_sys.sql

在mydb1库中执行
mysql> create user 'root'@'192.168.1.%' identified by '1qaz2wsx';
mysql> grant all privileges on *.* to 'root'@'192.168.1.%';
mysql> create user 'msandbox'@'192.168.1.%' identified by '1qaz2wsx';
mysql> grant all privileges on *.* to 'msandbox'@'192.168.1.%';
mysql> flush privileges;

配置对外访问账号
(admin@127.0.0.1) [main]> select * from mysql_users;
Empty set (0.00 sec)
(admin@127.0.0.1) [main]> insert into main.mysql_users(username,password,default_hostgroup,transaction_persistent) values ('root','1qaz2wsx',1,1);
Query OK, 1 row affected (0.01 sec)
(admin@127.0.0.1) [main]> insert into main.mysql_users(username,password,default_hostgroup,transaction_persistent) values ('msandbox','1qaz2wsx',1,1);
Query OK, 1 row affected (0.00 sec)
(admin@127.0.0.1) [main]> select * from mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| root | 1qaz2wsx | 1 | 0 | 1 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
| msandbox | 1qaz2wsx | 1 | 0 | 1 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
2 rows in set (0.00 sec)
(admin@127.0.0.1) [main]> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
(admin@127.0.0.1) [main]> save mysql users to disk;
Query OK, 0 rows affected (0.00 sec)

# mysql -u root -p1qaz2wsx -h 127.0.0.1 -P6033
# mysql -u msandbox -p1qaz2wsx -h 127.0.0.1 -P6033

读写分离
配置读写分离策略需要使用mysql_query_rules表。表中的match_pattern字段就是代表设置的规则，destination_hostgroup字段代表默认指定的分组，apply代表真正执行应用规则
所有以select开头的语句全部分配到编号为3的读组中
select for update会产生一个写锁，对数据查询的时效性要求高，把它分配到编号为1的写组里，其他所有操作都会默认路由到写组中
(admin@127.0.0.1) [main]> select * from mysql_query_rules;
Empty set (0.00 sec)
(admin@127.0.0.1) [main]> insert into main.mysql_query_rules(active,match_pattern,destination_hostgroup, apply) VALUES(1,'^SELECT.*FOR UPDATE$',1,1);
Query OK, 1 row affected (0.00 sec)
(admin@127.0.0.1) [main]> insert into main.mysql_query_rules(active,match_pattern,destination_hostgroup, apply) VALUES(1,'^SELECT',3,1);
Query OK, 1 row affected (0.00 sec)
(admin@127.0.0.1) [main]> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)
(admin@127.0.0.1) [main]> save mysql query rules to disk;
Query OK, 0 rows affected (0.05 sec)

(admin@127.0.0.1) [(none)]> select * from mysql_server_group_replication_log order by time_start_us desc limit 5;
+---------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
| hostname      | port | time_start_us    | success_time_us | viable_candidate | read_only | transactions_behind | error |
+---------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
| 192.168.1.103 | 3306 | 1539870708023091 | 4295            | YES              | YES       | 0                   | NULL |
| 192.168.1.102 | 3306 | 1539870708022779 | 3293            | YES              | YES       | 0                   | NULL |
| 192.168.1.101 | 3306 | 1539870708019315 | 3643            | YES              | NO        | 0                   | NULL |
| 192.168.1.103 | 3306 | 1539870703023065 | 6330            | YES              | YES       | 0                   | NULL |
| 192.168.1.102 | 3306 | 1539870703022702 | 4926            | YES              | YES       | 0                   | NULL |
+---------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+
5 rows in set (0.00 sec)

添加Keepalive功能

posted @ 2019-08-12 11:59 AllenHU320 阅读(777) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

MGR+Consul+ProxySQL

公告