- 3.1. 概要
- 3.2. admin交互界面的进入
- 3.3. 主要的表
- 3.4. 增加后端mysql服务
- 3.5. 增加proxysql查看后端数据库状态账号密码
- 3.6. 设置监控配置
- 3.7. 保存配置
- 3.8. 健康检查/监控monitor
- 3.9. replication组配置
- 3.10. 配置保存
- 3.11. 数据库用户配置
- 3.12. 连接proxysql测试:
- 4.1. 10.17.1.33创建账号
- 4.2. sysbench压测
- 4.3. proxysql查看状态
*前言: 快速了解proxysql的其功能特性。
1. proxysql安装
下载网页
https://proxysql.com/documentation/installing-proxysql/
# 根据版本下载
wget https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/centos/7/proxysql-2.5.1-1-centos7.x86_64.rpm
yum -y install proxysql-2.5.1-1-centos7.x86_64.rpm
# 查看安装文件路径
rpm -ql proxysql
# 启动proxysql
systemctl start proxysql
当 ProxySQL 启动后，将默认监听两个端口：
- 默认端口为6032: admin管理接口，该端口用于查看、配置ProxySQL
- 默认端口为6033: 接收SQL语句的接口，这个接口类似于MySQL的3306端口，提供给业务访问
ProxySQL 的 admin 管理接口是一个使用 MySQL 协议的接口，所以可以直接使用 mysql 客户端、navicat 等工具去连接这个管理接口，其默认的用户名和密码均为 admin
2. proxysql服务启停
service proxysql stop
service proxysql start
service proxysql restart
-- 或者进入admin
mysql -u admin -padmin -h 127.0.0.1 -P6032
proxysql stop
proxysql restart
3. proxysql配置
3.1. 概要
proxysql大概配置步骤为:
- 配置mysql_servers。添加想要实现负载/高可用/读写分离的后端mysql数据库
- 配置monitor。对后端mysql数据库做监控。以实现后端mysql数据库的健康检查、实现故障转移等
- 配置mysql_users。添加对应业务的账号。提给给应用连接使用
- 配置mysql_query_rules。以实现一些复杂的读写分离策略
3.2. admin交互界面的进入
默认账号密码是admin/admin、端口为6032
mysql -uadmin -padmin -P6032 -h127.0.0.1
# MySQL client version is version 8.04 or higher add --default-auth=mysql_native_password
admin@127.0.0.1 16:00:26> [(none)]>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 |
3.3. 主要的表
ProxySQL Admin> SELECT * FROM mysql_servers;
Empty set (0.00 sec)
ProxySQL Admin> SELECT * from mysql_replication_hostgroups;
Empty set (0.00 sec)
ProxySQL Admin> SELECT * FROM mysql_users;
Empty set (0.00 sec)
ProxySQL Admin> SELECT * from mysql_query_rules;
Empty set (0.00 sec)
3.4. 增加后端mysql服务
hostgroup_id是一个组。组里面可以包括多个ip+端口。但是需要保证 hostgroup_id,hostname,port是唯一的。weight是优先级
admin@127.0.0.1 10:02:40> [(none)]> INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,comment) VALUES (1,'192.168.2.1',3306,1,'主');
Query OK, 1 row affected (0.01 sec)
admin@127.0.0.1 10:02:40> [(none)]> INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight,comment) VALUES (1,'192.168.2.2',3306,1,'备');
Query OK, 1 row affected (0.01 sec)
admin@127.0.0.1 10:02:40> [(none)]> SELECT hostgroup_id, hostname, port,weight,comment,status FROM mysql_servers;
+--------------+--------------+------+--------+
| hostgroup_id | hostname | port | status |
+--------------+--------------+------+--------+
| 1 | 192.168.2.1 | 3306 | ONLINE |
| 1 | 192.168.2.2 | 3306 | ONLINE |
+--------------+--------------+------+--------+
3.5. 增加proxysql查看后端数据库状态账号密码
主要是用于监控connect/ping/replication_lag/read_only 的状态。需要在数据库中创建一个账号。再修改下proxysql的配置
-- 查看相关配置
select * from global_variables where variable_name like "mysql-monitor%";
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='password' WHERE variable_name='mysql-monitor_password';
3.6. 设置监控配置
可以设置检查的频率。以及超时时间。可以根据业务做不同调整
UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
3.7. 保存配置
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
3.8. 健康检查/监控monitor
3.8.1. 相关表
admin@127.0.0.1 14:14:45> [(none)]>show tables from monitor;
+--------------------------------------+
| tables |
+--------------------------------------+
| mysql_server_aws_aurora_check_status |
| mysql_server_aws_aurora_failovers |
| mysql_server_aws_aurora_log |
| mysql_server_connect_log |
| mysql_server_galera_log |
| mysql_server_group_replication_log |
| mysql_server_ping_log |
| mysql_server_read_only_log |
| mysql_server_replication_lag_log |
+--------------------------------------+
9 rows in set (0.00 sec)
3.8.2. 查询sql
admin@127.0.0.1 14:20:32> [(none)]>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 |
+--------------+------+------------------+----------------------+------------+
| 192.168.2.2 | 3306 | 1681798887705141 | 170 | NULL |
| 192.168.2.1 | 3306 | 1681798887704832 | 499 | NULL |
| 192.168.2.2 | 3306 | 1681798885704946 | 234 | NULL |
+--------------+------+------------------+----------------------+------------+
3 rows in set (0.00 sec)
admin@127.0.0.1 14:21:28> [(none)]>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 |
+--------------+------+------------------+-------------------------+---------------+
| 192.168.2.2 | 3306 | 1681798897733598 | 531 | NULL |
| 192.168.2.1 | 3306 | 1681798897707133 | 2133 | NULL |
| 192.168.2.1 | 3306 | 1681798895728574 | 1986 | NULL |
+--------------+------+------------------+-------------------------+---------------+
3 rows in set (0.00 sec)
3.9. replication组配置
INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (1,2,'cluster');
admin@127.0.0.1 14:26:18> [(none)]>LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
admin@127.0.0.1 14:26:21> [(none)]>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 |
+--------------+------+------------------+-----------------+-----------+-------+
| 192.168.2.2 | 3306 | 1681799183790021 | 181 | 1 | NULL |
| 192.168.2.1 | 3306 | 1681799183789702 | 525 | 0 | NULL |
| 192.168.2.1 | 3306 | 1681799181794529 | 466 | 0 | NULL |
+--------------+------+------------------+-----------------+-----------+-------+
3 rows in set (0.00 sec)
admin@127.0.0.1 14:28:09> [(none)]>SELECT hostgroup_id, hostname, port, status, weight, comment FROM mysql_servers;
+--------------+--------------+------+--------+--------+---------+
| hostgroup_id | hostname | port | status | weight | comment |
+--------------+--------------+------+--------+--------+---------+
| 1 | 192.168.2.1 | 3306 | ONLINE | 1 | |
| 2 | 192.168.2.2 | 3306 | ONLINE | 1 | |
+--------------+--------------+------+--------+--------+---------+
2 rows in set (0.00 sec)
3.10. 配置保存
Admin> SAVE MYSQL SERVERS TO DISK; Query OK, 0 rows affected (0.01 sec) Admin> SAVE MYSQL VARIABLES TO DISK; Query OK, 54 rows affected (0.00 sec)
3.11. 数据库用户配置
admin@127.0.0.1 17:26:25> [(none)]>INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('test_user','password',1);
Query OK, 1 row affected (0.00 sec)
admin@127.0.0.1 17:26:25> [(none)]>INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('test_sysbench','password',1);
Query OK, 1 row affected (0.00 sec)
admin@127.0.0.1 17:26:27> [(none)]>INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('monitor','password',1);
Query OK, 1 row affected (0.00 sec)
admin@127.0.0.1 17:26:42> [(none)]>select username, password, active, default_hostgroup from mysql_users;
+--------------+-------------+--------+-------------------+
| username | password | active | default_hostgroup |
+--------------+-------------+--------+-------------------+
| test_user | password | 1 | 1 |
| test_sysbench| password | 1 | 1 |
+--------------+-------------+--------+-------------------+
2 rows in set (0.00 sec)
ProxySQL Admin> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
ProxySQL Admin> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.01 sec)
3.12. 连接proxysql测试:
[root@ ~]# mysql -u test_user-p -h 127.0.0.1 -P6033 -e "SELECT @@server_id"
Enter password:
+-------------+
| @@server_id |
+-------------+
| 19216821 |
+-------------+
4. sysbench测试
4.1. 192.168.2.1创建账号
create user test_sysbench@'192.168.2.3' identified by "password";
grant all on *.* to test_sysbench@'192.168.2.3';
create database sbtest;
4.2. sysbench压测
yum install sysbench
sysbench --report-interval=5 --num-threads=4 --num-requests=0 --max-time=20 --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-user='test_sysbench' --mysql-password='password' --oltp-table-size=10000 --mysql-host=127.0.0.1 --mysql-port=6033 prepare
sysbench --report-interval=5 --num-threads=4 --num-requests=0 --max-time=20 --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-user='test_sysbench' --mysql-password='password' --oltp-table-size=10000 --mysql-host=127.0.0.1 --mysql-port=6033 run
4.3. proxysql查看状态
admin@127.0.0.1 17:54:29> [(none)]>SHOW TABLES FROM stats;
+---------------------------------------+
| tables |
+---------------------------------------+
| global_variables |
| stats_memory_metrics |
| stats_mysql_client_host_cache |
| stats_mysql_client_host_cache_reset |
| stats_mysql_commands_counters |
| stats_mysql_connection_pool |
| stats_mysql_connection_pool_reset |
| stats_mysql_errors |
| stats_mysql_errors_reset |
| stats_mysql_free_connections |
| stats_mysql_global |
| stats_mysql_gtid_executed |
| 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_message_metrics |
| stats_proxysql_message_metrics_reset |
| stats_proxysql_servers_checksums |
| stats_proxysql_servers_clients_status |
| stats_proxysql_servers_metrics |
| stats_proxysql_servers_status |
+---------------------------------------+
admin@127.0.0.1 17:54:32> [(none)]>SELECT * FROM stats.stats_mysql_connection_pool;
+-----------+--------------+----------+--------------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
| hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+--------------+----------+--------------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
| 1 | 192.168.2.1 | 3306 | ONLINE | 0 | 4 | 5 | 176 | 4 | 13209 | 0 | 2638656 | 25377684 | 527 |
| 1 | 192.168.2.2 | 3306 | OFFLINE_HARD | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 206 |
| 2 | 192.168.2.2 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 206 |
+-----------+--------------+----------+--------------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
3 rows in set (0.00 sec)
admin@127.0.0.1 17:54:32> [(none)]> SELECT * FROM stats_mysql_commands_counters
admin@127.0.0.1 17:54:32> [(none)]> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
5. query_rules策略
ProxySQL Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (10,1,'stnduser','^SELECT c FROM sbtest1 WHERE id=?,2,1);
Query OK, 1 row affected (0.00 sec)
ProxySQL Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (20,1,'stnduser','DISTINCT c FROM sbtest1',2,1);
Query OK, 1 row affected (0.00 sec)
最后修改时间：2024-04-07 17:20:12
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
