Mysql proxysql

rpm -ivh proxysql-2.4.1-1-centos7.x86_64.rpm

版本：proxysql --version
启动：service proxysql start
暂停：service proxysql stop
重启：service proxysql restart
状态：service proxysql status

控制台
mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt='Admin> ' --default-auth=mysql_native_password

--Main: 内存配置数据库，即 memory ，表里存放后端 db 实例，用户验证，路由规则等信息。 Main 库中有如下信息：
--
--mysql_servers-- 后端可以连接 mysql 服务器的列表
--
--mysql_users-- 配置后端数据库的账号和监控的账号
--
--mysql_query_rules-- 指定 query 路由到后端不同服务器的规则列表
--
--disk 库：持续化磁盘的配置。
--
--Stats 库：统计信息的汇总。
--
--Monitor 库：一些监控的收集信息，包括数据库的健康状态。

添加主从【ProxySQL】

insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'10.100.21.15',16330,1,'主库');
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'10.100.21.18',16330,1,'从库1');
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'10.100.21.19',16330,1,'从库2');

Admin> select * from mysql_servers;
+--------------+--------------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 | 10.100.21.15 | 16330 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | 主库 |
| 2 | 10.100.21.18 | 16330 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | 从库1 |
| 2 | 10.100.21.19 | 16330 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | 从库2 |
+--------------+--------------+-------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

创建主从账号【MySQL】
CREATE USER 'proxysql'@'%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON . TO 'proxysql'@'%' WITH GRANT OPTION;

添加主从账号【ProxySQL】
insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('proxysql','123456',1,1);

查看主从账号【ProxySQL】

Admin> select * from mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | attributes | comment |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| proxysql | 123456 | 1 | 0 | 1 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | | |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+

创建监控账号【MySQL】

CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
GRANT SELECT ON . TO 'monitor'@'%' WITH GRANT OPTION;

添加监控账号【ProxySQL】

set mysql-monitor_username='monitor';
set mysql-monitor_password='monitor';

查看监控账号【ProxySQL】

SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';

//也可以这样快速定位
Admin> select @@mysql-monitor_username;
+--------------------------+
| @@mysql-monitor_username |
+--------------------------+
| monitor |
+--------------------------+
1 row in set (0.01 sec)

Admin> select @@mysql-monitor_password;
+--------------------------+
| @@mysql-monitor_password |
+--------------------------+
| monitor |
+--------------------------+
1 row in set (0.00 sec)

检测监控【ProxySQL】

读写映射【ProxySQL】
这里配置主从自动切换：互为主从，自动切换，保证高可用。

如果你没有做到互为主从，请跳过此项。

insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup) values(1,2);

1 row in set (0.00 sec)

select * from mysql_server_read_only_log limit 3;

+--------------+-------+------------------+-----------------+-----------+-------+
| hostname | port | time_start_us | success_time_us | read_only | error |
+--------------+-------+------------------+-----------------+-----------+-------+
| 10.100.21.18 | 16330 | 1655950773100231 | 632 | 1 | NULL |
| 10.100.21.15 | 16330 | 1655950773114834 | 260 | 0 | NULL |
| 10.100.21.19 | 16330 | 1655950773130543 | 521 | 1 | NULL |
+--------------+-------+------------------+-----------------+-----------+-------+

读写路由【ProxySQL】

mysql_query_rules

常用的配置字段：
rule_id：规则编号，自动增长的整数，可以不指定；
active：规则是否有效，默认值为0，表示无效，需要在定义规则时，将其设置为1；
match_digest：定义规则的具体匹配内容；由正则表达式元字符组成，用来匹配SQL语句；
destination_hostgroup：对于符合规则的请求，设置目标主机组，从而实现路由转发；
apply：是否有效提交；默认值为0，表示无效，需要在定义规则时，将其值设置为1；

insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,log,apply)values(1,1,'^UPDATE',1,1,1);
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,log,apply)values(3,1,'^SELECT',2,1,1);

--insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values (2,1,'^SELECT.*FOR UPDATE$',1,1);
--insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values (4,1,'^INSERT',1,1);

查看路由【ProxySQL】
Admin> select * from mysql_query_rules \G;
*************************** 1. row ***************************
rule_id: 1
active: 1
username: NULL
schemaname: NULL
flagIN: 0
client_addr: NULL
proxy_addr: NULL
proxy_port: NULL
digest: NULL
match_digest: NULL
match_pattern: ^UPDATE
negate_match_pattern: 0
re_modifiers: CASELESS
flagOUT: NULL
replace_pattern: NULL
destination_hostgroup: 1
cache_ttl: NULL
cache_empty_result: NULL
cache_timeout: NULL
reconnect: NULL
timeout: NULL
retries: NULL
delay: NULL
next_query_flagIN: NULL
mirror_flagOUT: NULL
mirror_hostgroup: NULL
error_msg: NULL
OK_msg: NULL
sticky_conn: NULL
multiplex: NULL
gtid_from_hostgroup: NULL
log: 1
apply: 1
attributes:
comment: NULL
*************************** 2. row ***************************
rule_id: 2
active: 1
username: NULL
schemaname: NULL
flagIN: 0
client_addr: NULL
proxy_addr: NULL
proxy_port: NULL
digest: NULL
match_digest: NULL
match_pattern: ^SELECT
negate_match_pattern: 0
re_modifiers: CASELESS
flagOUT: NULL
replace_pattern: NULL
destination_hostgroup: 2
cache_ttl: NULL
cache_empty_result: NULL
cache_timeout: NULL
reconnect: NULL
timeout: NULL
retries: NULL
delay: NULL
next_query_flagIN: NULL
mirror_flagOUT: NULL
mirror_hostgroup: NULL
error_msg: NULL
OK_msg: NULL
sticky_conn: NULL
multiplex: NULL
gtid_from_hostgroup: NULL
log: 1
apply: 1
attributes:
comment: NULL
2 rows in set (0.00 sec)

配置生效【ProxySQL】

//加载到内存
load mysql users to runtime;
load mysql servers to runtime;
load mysql query rules to runtime;
load mysql variables to runtime;
load admin variables to runtime;

//永久生效
save mysql users to disk;
save mysql servers to disk;
save mysql query rules to disk;
save mysql variables to disk;
save admin variables to disk;

验证【ProxySQL】

Admin> select * from stats_mysql_query_rules;
+---------+------+
| rule_id | hits |
+---------+------+
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
+---------+------+

2 rows in set (0.00 sec)

mysql -uproxysql -h10.100.21.15 -P6033 -p12356 -e 'select @@server_id for update;'
mysql -uproxysql -h10.100.21.15 -P6033 -p12356 -e 'select @@server_id;'

相关文章