MySQL读写分离ProxySQL安装部署
ProxySQL简介
ProxySQL为MySQL的中间件，其有两个版本官方版和percona版，percona版是基于官方版基础上修改而来。ProxySQL是由C++语言开发，轻量级但性能优异（支持处理千亿级数据），其具有中间件所需要的绝大多数功能，如：
安装
下载安装
# 获取centos版本信息下载对应的
cat /etc/centos-releasecurl -O https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/centos/7/proxysql-2.5.0-1-centos7.x86_64.rpmyum install proxysql-2.5.0-1-centos7.x86_64.rpm
docker 安装(推荐)
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=4max_connections=2048default_query_delay=0default_query_timeout=36000000have_compress=truepoll_timeout=2000interfaces="0.0.0.0:6033"default_schema="information_schema"stacksize=1048576server_version="5.5.30"connect_timeout_server=3000monitor_username="monitor"monitor_password="monitor"monitor_history=600000monitor_connect_interval=60000monitor_ping_interval=10000monitor_read_only_interval=1500monitor_read_only_timeout=500ping_interval_server_msec=120000ping_timeout_server=500commands_stats=truesessions_sort=trueconnect_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/
https://www.cnblogs.com/keme/p/12290977.html#4%E9%85%8D%E7%BD%AE-proxysql-%E6%89%80%E9%9C%80%E8%B4%A6%E6%88%B7
https://proxysql.com/documentation/getting-started/