一、安装
1.1 依赖包
# yum install automake bzip2 cmake make gcc gcc-c++ git openssl openssl-devel patch
CentOS 6yum安装的软件版本过旧,需如下操作:
step1、升级gcc,以支持C++11标准(CentOS 6 默认4.4.7)
# wget https://mirrors.ustc.edu.cn/gnu/gcc/gcc-4.9.4/gcc-4.9.4.tar.gz
# tar zxvf gcc-4.9.4.tar.gz
# cd gcc-4.9.4
# ./contrib/download_prerequisites //自动安装mpfr-2.4.2.tar.bz2、gmp-4.3.2.tar.bz2、mpc-0.8.1.tar.gz
# mkdir debug
# cd debug
# ../configure --enable-checking=release --enable-languages=c,c++ --disable-multilib
# make -j 8
# make install
# source /etc/profile
# gcc -v
Using built-in specs.
COLLECT_GCC=gcc
COLLECT_LTO_WRAPPER=/usr/local/libexec/gcc/x86_64-unknown-linux-gnu/4.9.4/lto-wrapper
Target: x86_64-unknown-linux-gnu
Configured with: ../configure --enable-checking=release --enable-languages=c,c++ --disable-multilib
Thread model: posix
gcc version 4.9.4 (GCC)
step2、升级GLIBCXX和CXXABI(通过更新/usr/lib64/libstdc++.so.6指向的文件解决)
(升级gcc只会在/usr/local/lib64目录下包含最新的libstdc++.so.6动态库,
而不会更新/usr/lib64目录下的libstdc++.so.6。而执行程序时,程序会优先
从/usr/lib64目录下去寻找libstdc++.so.6,由于老版本的gcc在/usr/lib64中
有一个旧版本的动态库libstdc++.so.6,导致程序以为自己找到了正确的动态库,
实际上找到的却不是最新的。)感谢 @na_beginning
# cp /usr/local/lib64/libstdc++.so.6.0.20 /usr/lib64/
# cd /usr/lib64/
# unlink libstdc++.so.6
# ln -s libstdc++.so.6.0.20 libstdc++.so.6
# strings /usr/lib64/libstdc++.so.6 | grep GLIBCXX
1.2 安装ProxySQL
# git clone -b v1.4.14 https://github.com/sysown/proxysql.git
Initialized empty Git repository in /tmp/proxysql/.git/
# cd /tmp/proxysql
# make
# make install
启动
# cp /tmp/proxysql/etc/init.d/proxysql /etc/init.d/proxysql
# /etc/init.d/proxysql start
开放6033(对外服务端口)、6032(管理端口)
二、基础配置
使用mysql登录管理端口6032(使用MySQL 8.04以上版本客户端登录时,默认密码认证方式由mysql_native_password变成caching_sha2_password,而ProxySQL不支持后者,故需要添加 --default-auth=mysql_native_password)
mysql -uadmin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
databases如下,默认 show tables 是显示main库下的表
若需查看其他库下的表,需执行 show tables from disk;(use disk;show tables;无效)
Admin> show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)
在线修改的配置在 SAVE ... TO DISK 之后会落盘到该文件:/var/lib/proxysql/proxysql.db
/var/lib/proxysql/proxysql.db 和 /etc/proxysql.cnf 的关系:
1、ProxySQL启动时,首先会在 /etc/proxysql.cnf 中找到datadir,如果datadir下有proxysql.db,就加载proxysql.db中的配置信息
2、如果带--initial标示启动,则只采用 /etc/proxysql.cnf 的配置,相当于初始化proxysql.db
3、如果带--reload标示启动,则会把 /etc/proxysql.cnf 和 proxysql.db 进行合并,如果冲突则 proxysql.db 覆盖 /etc/proxysql.cnf
main库下的表
Admin> show tables;
+--------------------------------------------+
| tables |
+--------------------------------------------+
| global_variables |
| mysql_collations |
| mysql_group_replication_hostgroups |
| mysql_query_rules | 定义读写分离规则
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers | 定义后端的DB
| mysql_users | 定义连接后端DB的账户信息
| proxysql_servers |
| scheduler |
虚线以上为MEMORY态
====================================================================================
虚线以下为RUNTIME态
| runtime_checksums_values |
| runtime_global_variables |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules |
| runtime_mysql_query_rules_fast_routing |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_users |
| runtime_proxysql_servers |
| runtime_scheduler |
+--------------------------------------------+
20 rows in set (0.00 sec)
disk库下的表
Admin> show tables from disk;
+------------------------------------+
| tables |
+------------------------------------+
| global_variables |
| mysql_collations |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing | 处于DISK态的配置信息
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| proxysql_servers |
| scheduler |
+------------------------------------+
10 rows in set (0.00 sec)
ProxySQL的配置参数有 RUNTIME、MEMORY、DISK 三种状态
+-------------------------+
| RUNTIME |
+-------------------------+
/|\ |
| |
[1] | [2] |
| \|/
+-------------------------+
| MEMORY |
+-------------------------+ _
/|\ | |\
| | \
[3] | [4] | \ [5]
| \|/ \
+-------------------------+ +-------------------------+
| DISK | | CONFIG FILE |
+-------------------------+ +-------------------------+
针对不同类型的参数,运行如下SQL使修改立即生效
1、show variables like "mysql-%";
LOAD MYSQL VARIABLES TO RUNTIME;
2、show variables like "admin-%";
LOAD ADMIN VARIABLES TO RUNTIME;
3、修改完main.mysql_users
LOAD MYSQL USERS TO RUNTIME;
4、修改完main.mysql_servers
LOAD MYSQL SERVERS TO RUNTIME;
5、修改完main.mysql_servers
LOAD MYSQL QUERY RULES TO RUNTIME;
针对不同类型的参数,运行如下SQL使修改存盘
1、show variables like "mysql-%";
SAVE MYSQL VARIABLES TO DISK;
2、show variables like "admin-%";
SAVE ADMIN VARIABLES TO DISK;
3、修改完main.mysql_users
SAVE MYSQL USERS TO DISK;
4、修改完main.mysql_servers
SAVE MYSQL SERVERS TO DISK;
5、修改完main.mysql_servers
SAVE MYSQL QUERY RULES TO DISK;
例、ProxySQL中添加监控用的monitor账号(后端DB权限:show databases、replication client)
set mysql-monitor_username = "monitor";
set mysql-monitor_password = "monitor";
此时该variable尚未生效,需要将状态由 MEMORY => RUNTIME
LOAD MYSQL VARIABLES TO RUNTIME;
此时该variable已生效,但还需要如下存盘,避免重启后配置丢失
SAVE MYSQL VARIABLES TO DISK;
三、配合主从
注:ProxySQL后面挂的DB数,需要考量到ProxySQL所在机器的CPU和网卡性能
3.1、环境概述
后端DB:
M:192.168.47.30:3307
S1:192.168.47.31:3307(read_only=1、super_read_only=1)
S2:192.168.47.32:3307(read_only=1、super_read_only=1)
为了演示效果更明显,设置 mysql-monitor_writer_is_also_reader = false(默认True,表示writer_hostgroup中的DB同时充当reader_hostgroup的成员。实测该参数虽然可以动态修改参数值,但实际需要重启生效)
3.2、主从信息录入
在ProxySQL中录入主从库信息
Admin> insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup) values(1,2);
Query OK, 1 row affected (0.00 sec)
Admin> select * from mysql_replication_hostgroups;
+------------------+------------------+---------+
| writer_hostgroup | reader_hostgroup | comment |
+------------------+------------------+---------+
| 1 | 2 | |
+------------------+------------------+---------+
1 row in set (0.00 sec)
Admin> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)
Admin> save mysql servers to disk;
Query OK, 0 rows affected (0.03 sec)
Admin> insert into mysql_servers(hostgroup_id,hostname,port,max_connections) values(1,"192.168.47.30",3307,100),(2,"192.168.47.31",3307,100),(2,"192.168.47.32",3307,100);
Query OK, 3 rows affected (0.00 sec)
Admin> 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.47.30 | 3307 | ONLINE | 1 | 0 | 100 | 0 | 0 | 0 | |
| 2 | 192.168.47.31 | 3307 | ONLINE | 1 | 0 | 100 | 0 | 0 | 0 | |
| 2 | 192.168.47.32 | 3307 | ONLINE | 1 | 0 | 100 | 0 | 0 | 0 | |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
Admin> select * from runtime_mysql_servers;
Empty set (0.00 sec)
Admin> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)
Admin> 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.47.30 | 3307 | ONLINE | 1 | 0 | 100 | 0 | 0 | 0 | |
| 2 | 192.168.47.32 | 3307 | ONLINE | 1 | 0 | 100 | 0 | 0 | 0 | |
| 2 | 192.168.47.31 | 3307 | ONLINE | 1 | 0 | 100 | 0 | 0 | 0 | |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.02 sec)
Admin> select * from disk.mysql_servers;
Empty set (0.00 sec)
Admin> save mysql servers to disk;
Query OK, 0 rows affected (0.04 sec)
Admin> select * from disk.mysql_servers;
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 | 192.168.47.30 | 3307 | ONLINE | 1 | 0 | 100 | 0 | 0 | 0 | |
| 2 | 192.168.47.31 | 3307 | ONLINE | 1 | 0 | 100 | 0 | 0 | 0 | |
| 2 | 192.168.47.32 | 3307 | ONLINE | 1 | 0 | 100 | 0 | 0 | 0 | |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
Tips:
1、在main.mysql_servers中录入主从信息前,务必确认从库:read_only=1、super_read_only=1(MHA0.58版本支持super_read_only)
否则会导致如下结果,从库也会产生写入!(原因:main.mysql_servers中录入的hostgroup_id只是初始值,若具体配置的read_only与之不符,会另外新增一行记录,从而使从库同时在读、写组中)
Admin> select * from mysql_servers;
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 | 192.168.47.30 | 3307 | ONLINE | 1 | 0 | 100 | 0 | 0 | 0 | |
| 2 | 192.168.47.32 | 3307 | ONLINE | 1 | 0 | 100 | 0 | 0 | 0 | |
| 1 | 192.168.47.31 | 3307 | ONLINE | 1 | 0 | 100 | 0 | 0 | 0 | |
| 1 | 192.168.47.32 | 3307 | ONLINE | 1 | 0 | 100 | 0 | 0 | 0 | |
| 2 | 192.168.47.31 | 3307 | ONLINE | 1 | 0 | 100 | 0 | 0 | 0 | |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
5 rows in set (0.00 sec)
2、weight:权重值,默认1
3、max_replication_lag:复制延迟超过该值的DB将会暂时不参与数据吞吐,直到复制赶上。默认0,表示不检测复制延迟。(该值主要参考Seconds_Behind_Master,需谨慎设置)
3.3、账号级读写分离
账号信息:
后端DB中添加读写用的账号:writer、reader
ProxySQL在mysql_users表中添加读写用的账号:writer、reader
Admin> insert into mysql_users(username,password,default_hostgroup,max_connections) values(XXX,XXX,XXX,XXX);
Admin> LOAD MYSQL USERS TO RUNTIME;
Admin> SAVE MYSQL USERS TO DISK;
Admin> select * from runtime_mysql_users;
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| writer | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 | 0 | 1 | | 0 | 1 | 0 | 0 | 1 | 10000 |
| reader | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 | 0 | 2 | | 0 | 1 | 0 | 0 | 1 | 10000 |
| writer | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 | 0 | 1 | | 0 | 1 | 0 | 1 | 0 | 10000 |
| reader | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1 | 0 | 2 | | 0 | 1 | 0 | 1 | 0 | 10000 |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
4 rows in set (0.01 sec)
分别使用读、写账号,并查看实际进到的后端DB(ProxySQL将根据mysql_user中定义的default_hostgroup来路由具体的后端DB)
# /usr/local/mysql3307/bin/mysql -ureader -p -h192.168.47.33 -P6033
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.5.30 (ProxySQL)
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| 47_32 |
+------------+
1 row in set (0.01 sec)
# /usr/local/mysql3307/bin/mysql -ureader -p -h192.168.47.33 -P6033
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.5.30 (ProxySQL)
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| 47_31 |
+------------+
1 row in set (0.00 sec)
[root@47_32 ~]# /usr/local/mysql3307/bin/mysql -uwriter -p -h192.168.47.33 -P6033
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.5.30 (ProxySQL)
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| 47_30 |
+------------+
1 row in set (0.00 sec)
Tips:
在mysql_query_rules表中,也可以通过仅定义username和destination_hostgroup的方式,按账号分类,进行读写分离。同时,将会覆盖mysql_users表的参数值
3.4、IP/Port 级读写分离
在mysql_query_rules中仅设置 client_addr 和对应的 destination_hostgroup 来设置规则,mysql_query_rules表结构如下(该表十分重要,大部分ProxySQL功能实现都依赖该表):
Admin> show create table mysql_query_rules \G
*************************** 1. row ***************************
table: mysql_query_rules
Create Table: CREATE TABLE mysql_query_rules (
rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,
username VARCHAR,
schemaname VARCHAR,
flagIN INT NOT NULL DEFAULT 0,
client_addr VARCHAR,
proxy_addr VARCHAR,
proxy_port INT,
digest VARCHAR,
match_digest VARCHAR,
match_pattern VARCHAR,
negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
re_modifiers VARCHAR DEFAULT 'CASELESS',
flagOUT INT,
replace_pattern VARCHAR,
destination_hostgroup INT DEFAULT NULL,
cache_ttl INT CHECK(cache_ttl > 0),
reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
timeout INT UNSIGNED,
retries INT CHECK (retries>=0 AND retries <=1000),
delay INT UNSIGNED,
next_query_flagIN INT UNSIGNED,
mirror_flagOUT INT UNSIGNED,
mirror_hostgroup INT UNSIGNED,
error_msg VARCHAR,
OK_msg VARCHAR,
sticky_conn INT CHECK (sticky_conn IN (0,1)),
multiplex INT CHECK (multiplex IN (0,1,2)),
log INT CHECK (log IN (0,1)),
apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,
comment VARCHAR)
1 row in set (0.00 sec)
3.5、SQL级读写分离
方法一、match_pattern
1、新建测试用户
后端DB创建用户:
mysql> grant all privileges on *.* to "mix"@"%" identified by "123456";
mysql> flush privileges;
ProxySQL:
Admin> insert into mysql_users(username,password,default_hostgroup) values ('mix','123456',1);
Admin> load mysql users to runtime;
2、测试库数据如下
mysql> show create table tb1 \G
*************************** 1. row ***************************
Table: tb1
Create Table: CREATE TABLE `tb1` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` int(11) DEFAULT NULL,
`c3` varchar(100) DEFAULT NULL,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
mysql> select * from tb1;
+----+------+---------+
| c1 | c2 | c3 |
+----+------+---------+
| 1 | 23 | asdfsdf |
| 2 | 26 | asdddf |
| 3 | 16 | awwwdf |
+----+------+---------+
3 rows in set (0.00 sec)
3、建立路由规则
Admin> insert into mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',1,1),(2,1,'^SELECT',2,1);
Query OK, 2 rows affected (0.00 sec)
Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.01 sec)
此时路由规则如下(按照rule_id从小到大逐个匹配):
形如 SELECT FOR UPDATE 的SQL会被传送到 destination_hostgroup=1 的DB组执行
形如 SELECT 的SQL会被传送到 destination_hostgroup=2 的DB组执行
其他未在 mysql_query_rules 中定义的SQL将由 mysql_user 表中每个用户的 default_hostgroup 决定。
注:上述规则仅做测试用,生产环境中,只会将指定SQL(如频繁执行的、以及对数据库冲击大的读SQL)投放到从库。
4、测试结果如下
mysql> select c1,@@hostname from tb1 where c1=1 for update;
+----+------------+
| c1 | @@hostname |
+----+------------+
| 1 | 47_30 |
+----+------------+
1 row in set (0.01 sec)
mysql> select c1,@@hostname from tb1 where c1=1;
+----+------------+
| c1 | @@hostname |
+----+------------+
| 1 | 47_32 |
+----+------------+
1 row in set (0.00 sec)
方法二、digest
不同于match_pattern严重依赖于路由规则的先后次序(次序不同会有不同的SQL路由效果),digest采用的是精确匹配的方式。
前期准备,删除之前在mysql_query_rules中写入的rule_id=2的规则,由于mix用户的default_hostgroup=1,那么无论是否匹配到rule_id=1的规则,所有SQL都会走hostgroup=1的DB组,即主库192.168.47.30:3307
step1、利用sysbench准备一套测试数据
# sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql-host='192.168.47.33' --mysql-port=6033 --mysql-user='mix' --mysql-password='123456' --mysql-db=sysbench --table_size=10000 --time=1800 --threads=10 --report-interval=10 --tables=10 prepare
# sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql-host='192.168.47.33' --mysql-port=6033 --mysql-user='mix' --mysql-password='123456' --mysql-db=sysbench --table_size=10000 --time=1800 --threads=10 --report-interval=10 --tables=10 --mysql-ignore-errors=1062 run
为何在实际压测时需要添加:--mysql-ignore-errors=1062,原因参考以下文档
step2、利用stats_mysql_query_digest查看请求最频繁的top10 SQL
Admin> use stats;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
Admin> show tables from stats;
+--------------------------------------+
| tables |
+--------------------------------------+
| global_variables |
| stats_memory_metrics |
| stats_mysql_commands_counters |
| stats_mysql_connection_pool |
| stats_mysql_connection_pool_reset |
| stats_mysql_global |
| stats_mysql_prepared_statements_info |
| stats_mysql_processlist |
| stats_mysql_query_digest |
| stats_mysql_query_digest_reset |
| stats_mysql_query_rules |
| stats_mysql_users |
| stats_proxysql_servers_checksums |
| stats_proxysql_servers_metrics |
| stats_proxysql_servers_status |
+--------------------------------------+
15 rows in set (0.00 sec)
Admin> select * from stats_mysql_query_digest order by count_star desc limit 10 \G
*************************** 1. row ***************************
hostgroup: 1
schemaname: sysbench
username: mix
digest: 0x03744DC190BC72C7
digest_text: SELECT c FROM sbtest5 WHERE id=?
count_star: 26350
first_seen: 1558661817
last_seen: 1558662205
sum_time: 83273069
min_time: 307
max_time: 165864
*************************** 2. row ***************************
hostgroup: 1
schemaname: sysbench
username: mix
digest: 0x9D058B6F3BC2F754
digest_text: SELECT c FROM sbtest4 WHERE id=?
count_star: 26140
first_seen: 1558661817
last_seen: 1558662205
sum_time: 83955590
min_time: 300
max_time: 201088
*************************** 3. row ***************************
hostgroup: 1
schemaname: sysbench
username: mix
digest: 0x6DD78C71FF7350AE
digest_text: SELECT c FROM sbtest7 WHERE id=?
count_star: 26078
first_seen: 1558661817
last_seen: 1558662205
sum_time: 81638821
min_time: 310
max_time: 148443
*************************** 4. row ***************************
hostgroup: 1
schemaname: sysbench
username: mix
digest: 0x9B090963F41AD781
digest_text: SELECT c FROM sbtest10 WHERE id=?
count_star: 25960
first_seen: 1558661817
last_seen: 1558662204
sum_time: 81856394
min_time: 297
max_time: 110916
*************************** 5. row ***************************
hostgroup: 1
schemaname: sysbench
username: mix
digest: 0x1E7B7AC5611F30C2
digest_text: SELECT c FROM sbtest6 WHERE id=?
count_star: 25900
first_seen: 1558661817
last_seen: 1558662205
sum_time: 82848435
min_time: 312
max_time: 150716
略。。
所有SQL都走主库
主库:
mysql> show processlist;
+-----+---------+---------------------+----------+------------------+------+---------------------------------------------------------------+-----------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+---------+---------------------+----------+------------------+------+---------------------------------------------------------------+-----------------------------------------------------------+
| 2 | rpl | 192.168.47.31:57244 | NULL | Binlog Dump GTID | 2663 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 3 | rpl | 192.168.47.32:50834 | NULL | Binlog Dump GTID | 2661 | Sending to client | NULL |
| 142 | mix | 192.168.47.33:49302 | sysbench | Sleep | 0 | | NULL |
| 143 | mix | 192.168.47.33:49304 | sysbench | Execute | 0 | starting | COMMIT |
| 144 | mix | 192.168.47.33:49306 | sysbench | Execute | 0 | starting | COMMIT |
| 145 | mix | 192.168.47.33:49314 | sysbench | Sleep | 0 | | NULL |
| 146 | mix | 192.168.47.33:49318 | sysbench | Sleep | 0 | | NULL |
| 147 | mix | 192.168.47.33:49320 | sysbench | Execute | 0 | init | SELECT SUM(k) FROM sbtest4 WHERE id BETWEEN 5038 AND 5137 |
| 148 | mix | 192.168.47.33:49324 | sysbench | Sleep | 0 | | NULL |
| 149 | mix | 192.168.47.33:49328 | sysbench | Sleep | 0 | | NULL |
| 150 | mix | 192.168.47.33:49332 | sysbench | Execute | 0 | starting | COMMIT |
| 151 | mix | 192.168.47.33:49334 | sysbench | Sleep | 0 | | NULL |
| 152 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 164 | monitor | 192.168.47.33:49418 | NULL | Sleep | 1 | | NULL |
+-----+---------+---------------------+----------+------------------+------+---------------------------------------------------------------+-----------------------------------------------------------+
14 rows in set (0.01 sec)
从库:
mysql> show processlist;
+----+-------------+---------------------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+---------------------+------+---------+------+--------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 2916 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 0 | Slave has read all relay log; waiting for more updates | NULL |
| 3 | system user | | NULL | Connect | 0 | System lock | NULL |
| 4 | system user | | NULL | Connect | 0 | System lock | NULL |
| 5 | system user | | NULL | Connect | 0 | Waiting for an event from Coordinator | NULL |
| 6 | system user | | NULL | Connect | 0 | Waiting for an event from Coordinator | NULL |
| 8 | monitor | 192.168.47.33:47014 | NULL | Sleep | 1 | | NULL |
| 59 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-------------+---------------------+------+---------+------+--------------------------------------------------------+------------------+
8 rows in set (0.00 sec)
主库压力暴增(由于主从库所在server均是由同一台物理机虚拟出来,所以性能数据仅供参考)
[root@47_30 ~]# top
top - 09:56:44 up 1:16, 2 users, load average: 4.65, 4.23, 3.08
Tasks: 136 total, 1 running, 135 sleeping, 0 stopped, 0 zombie
Cpu(s): 13.6%us, 27.5%sy, 0.0%ni, 47.9%id, 8.0%wa, 0.5%hi, 2.4%si, 0.0%st
Mem: 1002996k total, 938468k used, 64528k free, 122316k buffers
Swap: 2031612k total, 84408k used, 1947204k free, 170324k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1497 mysql 20 0 5855m 527m 5216 S 206.0 53.9 39:20.06 mysqld
[root@47_31 ~]# top
top - 09:57:28 up 1:16, 1 user, load average: 1.67, 1.79, 1.35
Tasks: 134 total, 1 running, 133 sleeping, 0 stopped, 0 zombie
Cpu(s): 7.2%us, 8.4%sy, 0.0%ni, 74.7%id, 8.6%wa, 0.1%hi, 0.9%si, 0.0%st
Mem: 1003024k total, 932760k used, 70264k free, 128016k buffers
Swap: 2031612k total, 103536k used, 1928076k free, 274856k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1512 mysql 20 0 5386m 426m 4528 S 109.7 43.6 22:38.01 mysqld
[root@47_32 ~]# top
top - 09:57:49 up 1:16, 1 user, load average: 2.03, 2.07, 1.55
Tasks: 134 total, 1 running, 133 sleeping, 0 stopped, 0 zombie
Cpu(s): 6.6%us, 8.2%sy, 0.0%ni, 73.2%id, 11.5%wa, 0.0%hi, 0.4%si, 0.0%st
Mem: 1003024k total, 937676k used, 65348k free, 127912k buffers
Swap: 2031612k total, 100736k used, 1930876k free, 271216k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1494 mysql 20 0 5386m 436m 4520 S 96.5 44.6 21:15.73 mysqld
在myql_query_rules定义新规则,使最繁忙的top6 SQL走group id=2 的DB组
Admin> INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply) VALUES(2,1,'0x03744DC190BC72C7',2,1);
Query OK, 1 row affected (0.00 sec)
Admin> INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply) VALUES(3,1,'0x99396EC34E1F41D4',2,1);
Query OK, 1 row affected (0.00 sec)
Admin> INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply) VALUES(4,1,'0x9D058B6F3BC2F754',2,1);
Query OK, 1 row affected (0.01 sec)
Admin> INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply) VALUES(5,1,'0x9AF59B998A3688ED',2,1);
Query OK, 1 row affected (0.00 sec)
Admin> INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply) VALUES(6,1,'0xFAD1519E4760CBDE',2,1);
Query OK, 1 row affected (0.00 sec)
Admin> INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply) VALUES(7,1,'0x695FBF255DBEB0DD',2,1);
Query OK, 1 row affected (0.00 sec)
Admin> load mysql query rules to runtime;
Query OK, 0 rows affected (0.01 sec)
通过如下方法,可以看到部分请求已经到groupid=2的DB组上了。
Admin> use stats;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
Admin>
Admin> select * from stats_mysql_query_digest where hostgroup=2\G
*************************** 1. row ***************************
hostgroup: 2
schemaname: sysbench
username: mix
digest: 0x03744DC190BC72C7
digest_text: SELECT c FROM sbtest5 WHERE id=?
count_star: 8141
first_seen: 1559007744
last_seen: 1559008377
sum_time: 23992207
min_time: 307
max_time: 421965
*************************** 2. row ***************************
hostgroup: 2
schemaname: sysbench
username: mix
digest: 0x695FBF255DBEB0DD
digest_text: COMMIT
count_star: 8042
first_seen: 1559007744
last_seen: 1559008090
sum_time: 59528321
min_time: 355
max_time: 271146
*************************** 3. row ***************************
hostgroup: 2
schemaname: sysbench
username: mix
digest: 0x99396EC34E1F41D4
digest_text: SELECT c FROM sbtest8 WHERE id=?
count_star: 7561
first_seen: 1559007744
last_seen: 1559008090
sum_time: 20913059
min_time: 321
max_time: 183663
*************************** 4. row ***************************
hostgroup: 2
schemaname: sysbench
username: mix
digest: 0x9AF59B998A3688ED
digest_text: SELECT c FROM sbtest2 WHERE id=?
count_star: 8010
first_seen: 1559007744
last_seen: 1559008089
sum_time: 23271965
min_time: 299
max_time: 268409
*************************** 5. row ***************************
hostgroup: 2
schemaname: sysbench
username: mix
digest: 0x9D058B6F3BC2F754
digest_text: SELECT c FROM sbtest4 WHERE id=?
count_star: 8050
first_seen: 1559007744
last_seen: 1559008090
sum_time: 24637345
min_time: 282
max_time: 1830963
*************************** 6. row ***************************
hostgroup: 2
schemaname: sysbench
username: mix
digest: 0xFAD1519E4760CBDE
digest_text: BEGIN
count_star: 8871
first_seen: 1559007564
last_seen: 1559008090
sum_time: 36434653
min_time: 270
max_time: 504270
6 rows in set (0.00 sec)
3.6、从库故障转移
通过ProxySQL,查询SQL走groupid=2(47_31 or 47_32):
mysql> use db1;
Database changed
mysql> select c1,@@hostname from tb1;
+----+------------+
| c1 | @@hostname |
+----+------------+
| 1 | 47_31 |
| 2 | 47_31 |
| 3 | 47_31 |
+----+------------+
3 rows in set (0.00 sec)
关闭47_31上的DB:
[root@47_31 ~]# /usr/local/mysql3307/bin/mysqladmin shutdown -uroot -p -S /tmp/mysql3307.sock
重新执行一次查询SQL,期间会断连一次,最后查询成功转移到组内剩余DB上:
mysql> select c1,@@hostname from tb1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> select c1,@@hostname from tb1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 3
Current database: db1
+----+------------+
| c1 | @@hostname |
+----+------------+
| 1 | 47_32 |
| 2 | 47_32 |
| 3 | 47_32 |
+----+------------+
3 rows in set (0.05 sec)
四、连接池
在录入主从信息时,可设定每台DB的max_connections
Admin> select * from mysql_servers;
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 2 | 192.168.47.31 | 3307 | ONLINE | 1 | 0 | 100 | 0 | 0 | 0 | |
| 2 | 192.168.47.32 | 3307 | ONLINE | 1 | 0 | 100 | 0 | 0 | 0 | |
| 1 | 192.168.47.30 | 3307 | ONLINE | 1 | 0 | 100 | 0 | 0 | 0 | |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
Admin> select * from stats_mysql_connection_pool;
+-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| 2 | 192.168.47.31 | 3307 | ONLINE | 0 | 1 | 1 | 0 | 8 | 130 | 228 | 802 |
| 2 | 192.168.47.32 | 3307 | ONLINE | 1 | 0 | 2 | 0 | 23 | 370 | 298 | 811 |
| 1 | 192.168.47.30 | 3307 | ONLINE | 1 | 0 | 1 | 0 | 33 | 550 | 426 | 764 |
+-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
3 rows in set (0.00 sec)
五、审计(Query Logging)
5.1、配置参数详解
mysql-eventslog_filename:默认为空,set之后会把mysql_query_rules中配置的相关sql记录到log文件中(二进制,需专用工具打开)
mysql-eventslog_filesize:默认100M,超过该上限值之后,会轮转到下一文件中。(ProxySQL重启,也会轮转到下一个文件中)
例:events_log.00000001~events_log.00000002
5.2、打开审计功能
Admin> show variables like "mysql-eventslog_%";
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| mysql-eventslog_filename | |
| mysql-eventslog_filesize | 104857600 |
+--------------------------+-----------+
2 rows in set (0.00 sec)
Admin> set mysql-eventslog_filename = "/data/proxysql/events_log";
Query OK, 1 row affected (0.00 sec)
Admin> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
Admin> save mysql variables to disk;
Query OK, 97 rows affected (0.02 sec)
5.3、配置审计规则
Admin> insert into mysql_query_rules(rule_id,active,match_digest,log,apply) values(10,1,'^delete',1,1);
Query OK, 1 row affected (0.00 sec)
Admin> insert into mysql_query_rules(rule_id,active,match_digest,log,apply) values(11,1,'^update',1,1);
Query OK, 1 row affected (0.00 sec)
Admin> insert into mysql_query_rules(rule_id,active,match_digest,log,apply) values(12,1,'^insert',1,1);
Query OK, 1 row affected (0.00 sec)
Admin> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)
Admin> save mysql query rules to disk;
Query OK, 0 rows affected (0.02 sec)
5.4、测试增删改
mysql> show create table tb1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb1 | CREATE TABLE `tb1` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` int(11) DEFAULT NULL,
`c3` varchar(100) DEFAULT NULL,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> select * from tb1;
+----+------+---------+
| c1 | c2 | c3 |
+----+------+---------+
| 1 | 23 | asdfsdf |
| 2 | 26 | asdddf |
| 3 | 16 | awwwdf |
+----+------+---------+
3 rows in set (0.00 sec)
mysql> insert into tb1(c2,c3) values(23,"sfwe2");
Query OK, 1 row affected (0.01 sec)
mysql> update tb1 set c2=13 where c1=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tb1;
+----+------+---------+
| c1 | c2 | c3 |
+----+------+---------+
| 1 | 23 | asdfsdf |
| 2 | 13 | asdddf |
| 3 | 16 | awwwdf |
| 4 | 23 | sfwe2 |
+----+------+---------+
4 rows in set (0.00 sec)
mysql> delete from tb1 where c1=4;
Query OK, 1 row affected (0.03 sec)
对应日志增长情况,仅有增删改的操作会有日志记录增长,与配置相同
[root@237_33 ~]# cd /data/proxysql/
[root@237_33 proxysql]# ll
total 18864
-rw-------. 1 root root 19316183 May 30 14:53 events_log.00000001
-rw-------. 1 root root 0 May 30 15:24 events_log.00000002
[root@237_33 proxysql]# ll
total 18868
-rw-------. 1 root root 19316183 May 30 14:53 events_log.00000001
-rw-------. 1 root root 127 May 30 15:28 events_log.00000002
[root@237_33 proxysql]# ll
total 18868
-rw-------. 1 root root 19316183 May 30 14:53 events_log.00000001
-rw-------. 1 root root 244 May 30 15:29 events_log.00000002
[root@237_33 proxysql]# ll
total 18868
-rw-------. 1 root root 19316183 May 30 14:53 events_log.00000001
-rw-------. 1 root root 244 May 30 15:29 events_log.00000002
[root@237_33 proxysql]# ll
total 18868
-rw-------. 1 root root 19316183 May 30 14:53 events_log.00000001
-rw-------. 1 root root 356 May 30 15:29 events_log.00000002
5.5、日志查看工具
源码包里的tools目录中存在日志查看工具,make一下就能用
# cd /tmp/proxysql/tools/
# make
# mv eventslog_reader_sample /usr/local/bin/
# eventslog_reader_sample events_log.00000002
ProxySQL LOG QUERY: thread_id="2" username="mix" schemaname=db1" client="192.168.47.33:38876" HID=1 server="192.168.47.30:3307" starttime="2019-05-30 15:28:16.531982" endtime="2019-05-30 15:28:16.543924" duration=11942us digest="0xCAAE433A658B6415"
insert into tb1(c2,c3) values(23,"sfwe2")
ProxySQL LOG QUERY: thread_id="2" username="mix" schemaname=db1" client="192.168.47.33:38876" HID=1 server="192.168.47.30:3307" starttime="2019-05-30 15:29:03.948187" endtime="2019-05-30 15:29:03.954594" duration=6407us digest="0x976A2AD1378673E"
update tb1 set c2=13 where c1=2
ProxySQL LOG QUERY: thread_id="2" username="mix" schemaname=db1" client="192.168.47.33:38876" HID=1 server="192.168.47.30:3307" starttime="2019-05-30 15:29:59.375230" endtime="2019-05-30 15:29:59.402628" duration=27398us digest="0xB1C56080D5581A32"
delete from tb1 where c1=4
六、QC
Query Cache的实现主要依赖于mysql_query_rules表的cache_ttl字段,单位:毫秒。在命中规则之后,如果规则中同时设置了cache_ttl字段,则会将结果集缓存一段时间。
不同于MySQL的QC是基于表的,ProxySQL实现的是SQL级的QC。
测试过程概述:
1、在mysql_query_rules中添加规则:特定SQL走QC
2、运行10次该SQL
3、在规则统计表中,查看命中次数:
Admin> select * from stats_mysql_query_rules;
4、进一步区分命中过程中,有几次是需要走后端DB?有几次直接从QC取数据?
Admin> select hostgroup,count_star,sum_time,digest_text from stats_mysql_query_digest;
注:hostgroup = -1 表示从缓存中取数据
七、Query Rewrite
mysql_query_rules表中提供了字段:match_pattern,以正则表达式的方式对特定字符进行修改操作,从而实现了将前端发来的SQL按特定规则修改后再路由到后端DB上。
1、SQL紧急变更
如下SQL的order by c显然是不必要的,因为distinct已经做过一次排序了
SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c;
需要修改如下:
SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?
那么在不修改应用的情况下,如何快速实现SQL优化呢?
可以在ProxySQL层,使用query rewrite功能:
Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,replace_pattern,apply) VALUES (30,1,'msandbox','DISTINCT(.*)ORDER BY c','DISTINCT\1',1);
2、脱敏
假设要对如下表的查询操作进行敏感词规避(例如用户密码等),在ProxySQL层如何实现呢?
mysql> create table tb2(c1 int not null auto_increment,id int,password varchar(100),primary key(c1));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into tb2(id,password) values(123,'!@#$%');
Query OK, 1 row affected (0.01 sec)
mysql> insert into tb2(id,password) values(456,'^&*()');
Query OK, 1 row affected (0.02 sec)
mysql> insert into tb2(id,password) values(789,'*()_+^^');
Query OK, 1 row affected (0.02 sec)
mysql> select * from tb2;
+----+------+----------+
| c1 | id | password |
+----+------+----------+
| 1 | 123 | !@#$% |
| 2 | 456 | ^&*() |
| 3 | 789 | *()_+^^ |
+----+------+----------+
3 rows in set (0.00 sec)
mysql>
query rules如下
INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,replace_pattern,apply)
VALUES (30,1,'mix','password','"****" as password',1);
八、Web展示
打开Web功能
Admin> set admin-web_enabled = "true";
Query OK, 1 row affected (0.00 sec)
Admin> LOAD ADMIN VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.01 sec)
Admin> SAVE ADMIN VARIABLES TO DISK;
Query OK, 31 rows affected (0.02 sec)
Web页面登录密码
Admin> select * from global_variables where variable_name = "admin-stats_credentials";
+-------------------------+----------------+
| variable_name | variable_value |
+-------------------------+----------------+
| admin-stats_credentials | stats:stats |
+-------------------------+----------------+
1 row in set (0.01 sec)
Web端口
Admin> select * from global_variables where variable_name = "admin-web_port";
+----------------+----------------+
| variable_name | variable_value |
+----------------+----------------+
| admin-web_port | 6080 |
+----------------+----------------+
1 row in set (0.00 sec)
开放6080端口后,就可以访问到Web页面了。
九、配合MHA
ProxySQL+MHA可以实现不依靠VIP或DNS,进行故障切换,而应用无感知。
实现的基础:
(1)MHA在切换过程:Phase 3.3: New Master Recovery Phase,会Set read_only=0 on the new master
(2)ProxySQL是根据DB的read_only配置情况,来最终决定DB所属的读写组。
例、架构概述:
192.168.47.30:3306(master)
192.168.47.31:3306(slave1---candidate_master)
192.168.47.32:3306(slave2)
可读组id=1,可写组id=2
切换前,ProxySQL显示的DB集群情况:
Admin> select * from runtime_mysql_servers;
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 2 | 192.168.47.31 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 192.168.47.32 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 192.168.47.30 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
47.30故障下线后,由MHA进行主库的切换,切换到47.31。
切换后,ProxySQL根据read-only,调整读写组成员,具体显示的DB集群情况如下:
47.30已下线(SHUNNED),47.31转到可写组(host group id=1)
Admin> select * from runtime_mysql_servers;
+--------------+---------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 2 | 192.168.47.30 | 3306 | SHUNNED | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 192.168.47.31 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 192.168.47.32 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+---------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.01 sec)
十、配合MGR(单主)
需在每个节点运行以下存储过程
https://gist.github.com/lefred/77ddbde301c72535381ae7af9f968322
待更新
十一、ProxySQL高可用
可选:官方的ProxySQL Cluster、第三方的服务发现框架:zookeeper、consul等。
consul的实现可参考我的博文:
参考文档:
Percona Blog:ProxySQL and MHA Integration
MySQL High Availability with ProxySQL and MySQL Group Replication