ProxySQL简介
ProxySQL为MySQL的中间件，其有两个版本官方版和percona版，percona版是基于官方版基础上修改而来。ProxySQL是由C++语言开发，轻量级但性能优异（支持处理千亿级数据），其具有中间件所需要的绝大多数功能，如：
- 多种方式的读写分离
- 定制基于用户、基于schema、基于语言的规则对SQL语句进行路由
- 缓存查询结果
- 后端节点的控制
安装
下载安装
# 获取centos版本信息下载对应的
cat /etc/centos-release
curl -O https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/centos/7/proxysql-2.5.0-1-centos7.x86_64.rpm
yum install proxysql-2.5.0-1-centos7.x86_64.rpmdocker 安装(推荐)
vim proxysql.cnf
datadir="/var/lib/proxysql"
admin_variables=
{
admin_credentials="admin:admin;radmin:radmin"
mysql_ifaces="0.0.0.0:6032"
}
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"
default_schema="information_schema"
stacksize=1048576
server_version="5.5.30"
connect_timeout_server=3000
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
}启动
docker run --privileged=true -p 16032:6032 -p 16033:6033 -p 16070:6070 -d -v /home/lys/proxysql.cnf:/etc/proxysql.cnf proxysql/proxysql推荐使用这个
docker run --privileged=true network=host -d -v /home/lys/proxysql.cnf:/etc/proxysql.cnf proxysql/proxysql配置
登录
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin>'插入主备节点
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.6.8.174',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,'10.6.8.147',3306);设置用户
UPDATE global_variables SET variable_value='monitor12345678' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor12345678' WHERE variable_name='mysql-monitor_password';Admin>SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
+----------------------------------------------------------------------+-----------------+
| variable_name | variable_value |
+----------------------------------------------------------------------+-----------------+
| mysql-monitor_enabled | true |
| mysql-monitor_connect_timeout | 600 |
| mysql-monitor_ping_max_failures | 3 |
| mysql-monitor_ping_timeout | 1000 |
| mysql-monitor_read_only_max_timeout_count | 3 |
| mysql-monitor_replication_lag_interval | 10000 |
| mysql-monitor_replication_lag_timeout | 1000 |
| mysql-monitor_replication_lag_count | 1 |
| mysql-monitor_groupreplication_healthcheck_interval | 5000 |
| mysql-monitor_groupreplication_healthcheck_timeout | 800 |
| mysql-monitor_groupreplication_healthcheck_max_timeout_count | 3 |
| mysql-monitor_groupreplication_max_transactions_behind_count | 3 |
| mysql-monitor_groupreplication_max_transactions_behind_for_read_only | 1 |
| mysql-monitor_galera_healthcheck_interval | 5000 |
| mysql-monitor_galera_healthcheck_timeout | 800 |
| mysql-monitor_galera_healthcheck_max_timeout_count | 3 |
| mysql-monitor_replication_lag_use_percona_heartbeat | |
| mysql-monitor_query_interval | 60000 |
| mysql-monitor_query_timeout | 100 |
| mysql-monitor_slave_lag_when_null | 60 |
| mysql-monitor_threads_min | 8 |
| mysql-monitor_threads_max | 128 |
| mysql-monitor_threads_queue_maxsize | 128 |
| mysql-monitor_wait_timeout | true |
| mysql-monitor_writer_is_also_reader | true |
| mysql-monitor_username | monitor12345678 |
| mysql-monitor_password | monitor12345678 |
| mysql-monitor_history | 600000 |
| mysql-monitor_connect_interval | 60000 |
| mysql-monitor_ping_interval | 10000 |
| mysql-monitor_read_only_interval | 1500 |
| mysql-monitor_read_only_timeout | 500 |
+----------------------------------------------------------------------+-----------------+检查登录情况
Admin>SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;
+------------+------+------------------+-------------------------+-----------------------------------------------------------------------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+------------+------+------------------+-------------------------+-----------------------------------------------------------------------------+
| 10.6.8.147 | 3306 | 1681299772585477 | 0 | Access denied for user 'monitor12345678'@'10.6.8.174' (using password: YES) |
| 10.6.8.174 | 3306 | 1681299771791832 | 0 | Access denied for user 'monitor12345678'@'10.6.8.174' (using password: YES) |
| 10.6.8.174 | 3306 | 1681299767550776 | 0 | Access denied for user 'monitor'@'10.6.8.174' (using password: YES) |
+------------+------+------------------+-------------------------+-----------------------------------------------------------------------------+
3 rows in set (0.00 sec)放开白名单即可
然后健康检查结果
Admin>SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;
+------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+------------+------+------------------+-------------------------+---------------+
| 10.6.8.174 | 3306 | 1681300972640975 | 604 | NULL |
| 10.6.8.147 | 3306 | 1681300971793675 | 1768 | NULL |
| 10.6.8.147 | 3306 | 1681300912640330 | 1582 | NULL |
+------------+------+------------------+-------------------------+---------------+
3 rows in set (0.00 sec)
Admin>SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 3;
+------------+------+------------------+----------------------+------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+------------+------+------------------+----------------------+------------+
| 10.6.8.174 | 3306 | 1681301032123280 | 178 | NULL |
| 10.6.8.147 | 3306 | 1681301031931784 | 458 | NULL |
| 10.6.8.174 | 3306 | 1681301022061284 | 225 | NULL |
+------------+------+------------------+----------------------+------------+
3 rows in set (0.00 sec)配置主备切换的自动检测
all the MySQL backend servers that are either configured in hostgroup 1 or 2 will be placed into their respective hostgroup based on their read_only value:
If they have , they will be moved to hostgroup 1read_only=0
If they have , they will be moved to hostgroup 2read_only=1
INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (1,2,'cluster1');
# 生效
LOAD MYSQL SERVERS TO RUNTIME;检查生效
Admin>SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 3;
+------------+------+------------------+-----------------+-----------+-------+
| hostname | port | time_start_us | success_time_us | read_only | error |
+------------+------+------------------+-----------------+-----------+-------+
| 10.6.8.174 | 3306 | 1681809841749869 | 278 | 1 | NULL |
| 10.6.8.147 | 3306 | 1681809841734498 | 650 | 0 | NULL |
| 10.6.8.174 | 3306 | 1681809840250699 | 258 | 1 | NULL |
+------------+------+------------------+-----------------+-----------+-------+
3 rows in set (0.00 sec)配置读写分离规则
Key points about these query rules (and query rules in general):
- Query rules are processed as ordered by rule_id
- Only rules that have active=1 are processed
- The first rule example uses caret (^) and dollar ($) : these are special regex characters that mark the beginning and the end of a pattern i.e. in this case match_digestormatch_pattern should completely match the query
- The second rule in the example doesn’t use caret or dollar : the match could be anywhere in the query
- The question mark is escaped as it has a special meaning in regex
apply=1 means that no further rules should be evaluated if the current rule was matched
INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (10,1,'monitor12345678','^SELECT',2,1);
# 生效
LOAD MYSQL QUERY RULES TO RUNTIME;验证读写分离
Admin>SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest where hg=2 ORDER BY sum_time asc;
+----+----------+------------+---------------------------------------------------+
| hg | sum_time | count_star | digest_text |
+----+----------+------------+---------------------------------------------------+
| 2 | 515 | 1 | SELECT * FROM `test01`.`aa` WHERE `a` = ? LIMIT ? |
| 2 | 9565 | 13 | SELECT * FROM `test01`.`aa` LIMIT ?,? |
+----+----------+------------+---------------------------------------------------+
2 rows in set (0.01 sec)
Admin>SELECT hostgroup hg, SUM(sum_time), SUM(count_star) FROM stats_mysql_query_digest GROUP BY hostgroup;
+----+---------------+-----------------+
| hg | SUM(sum_time) | SUM(count_star) |
+----+---------------+-----------------+
| 1 | 505998 | 300 |
| 2 | 10080 | 14 |
+----+---------------+-----------------+
2 rows in set (0.00 sec)参考文献
https://proxysql.com/documentation/installing-proxysql/
https://proxysql.com/documentation/ProxySQL-Configuration/
标签：monitor,10.6,proxysql,读写,MySQL,ProxySQL,time,timeout,mysql From： https://blog.51cto.com/liuyunshengsir/6215476