ProxySQL是什么
介绍
- MySQL一款开源的中间件的产品
- 支持读写分离
- 支持 Query 路由功能
- 支持动态指定某个SQL进行缓存
- 支持动态加载（无需重启ProxySQL服务）
- 故障切换和SQL过滤功能。
ProxySQL初始化
安装
wget https://github.com/sysown/proxysql/releases/download/v2.4.4/proxysql-2.4.4-1-centos7.x86_64.rpm
rpm -ivh proxysql-2.4.4-1-centos7.x86_64.rpm
产生报错：
warning: proxysql-2.4.4-1-centos7.x86_64.rpm: Header V4 RSA/SHA512 Signature, key ID 8217c97e: NOKEY
error: Failed dependencies:
gnutls is needed by proxysql-2.4.4-1.x86_64
libgnutls.so.28()(64bit) is needed by proxysql-2.4.4-1.x86_64
libgnutls.so.28(GNUTLS_1_4)(64bit) is needed by proxysql-2.4.4-1.x86_64
libgnutls.so.28(GNUTLS_3_0_0)(64bit) is needed by proxysql-2.4.4-1.x86_64
libgnutls.so.28(GNUTLS_3_1_0)(64bit) is needed by proxysql-2.4.4-1.x86_64
执行：
yum install -y gnutls
rpm -ivh proxysql-2.4.4-1-centos7.x86_64.rpm
检查安装情况
rpm -ql proxysql
/etc/logrotate.d/proxysql
/etc/proxysql.cnf
/etc/systemd/system/proxysql-initial.service
/etc/systemd/system/proxysql.service
/usr/bin/proxysql
/usr/share/proxysql/tools/proxysql_galera_checker.sh
/usr/share/proxysql/tools/proxysql_galera_writer.pl
配置proxysql.cnf
admin_credentials="admin:Proxy2022"
mysql_ifaces="0.0.0.0:8032"
interfaces="0.0.0.0:3347"
monitor_password="Monitor2022"
vim /etc/systemd/system/proxysql.service
#修改以下项
PIDFile=/data/proxysql/proxysql.pid
启动
systemctl start proxysql.service
ps -ef | grep proxysql
netstat -anlp | grep proxysql
# systemctl restart proxysql.service
设置proxy
准备MySQL节点
- mysql58(主)
- mysql59(从)
- mysql60(从)
#mysql58上执行
create user 'proxysql'@'%' identified with mysql_native_password by 'pxoxy123456';
grant all on *.* to 'proxysql'@'%' ;
create user 'monitor'@'%' identified with mysql_native_password by 'Monitor2022';
grant select on *.* to 'monitor'@'%' ;
维护proxy中的节点信息
mysql -uadmin -pProxy2022 -h127.0.0.1 -P8032
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+-----+---------------+----------------------------------+
| seq | name | file |
+-----+---------------+----------------------------------+
| 0 | main | |
| 2 | disk | /data/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /data/proxysql/proxysql_stats.db |
+-----+---------------+----------------------------------+
5 rows in set (0.00 sec)
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values
(33601,'192.168.0.58',3360,1,'Master')
,(33602,'192.168.0.59',3360,1,'Slave1')
,(33602,'192.168.0.60',3360,1,'Slave2');
insert into mysql_users(username,password,default_hostgroup,transaction_persistent) values('proxysql','proxysql.123456',10,1);
insert into mysql_query_rules(rule_id,proxy_port,active,match_digest,destination_hostgroup,apply) values
(33601,3360,1,'',33601,1),
(33602,3360,1,'^select.*',33602,1);
load mysql servers to run;
save mysql servers to disk;
load mysql users to run;
save mysql users to disk;
load mysql users to run;
save mysql users to disk;
load mysql query rules to run;
save mysql query rules to disk;
验证proxy
mysql -h 127.0.0.1 -P 3360 -uproxysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
select @@server_id ;
高可用架构
- 一般proxySQL在高可用架构中的位置
>> Home
