proxymysql的安装与应用
2024-08-29 12:55:58
具体的资料我们可以查看官方的文档:https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration
推荐下载最新的Proxysql。
下面跟大家一起来安装和使用我们的proxy的一个简单使用。
首先我给大家说说Proxy的一些理论知识,说到中间件,首先我们关注的可定是自带连接池,能读写分离,能自动的分库分表等等,其它的就不说了,反正Proxy在每个公司玩的花样都蛮多的。我就跟大家说说我印象中的ProxySQL:server可以进行分组、 读写分离 动态指定某一个SQL进行Cache、 故障切换(依赖于他的配置动态加载)、配置的动态更新,不具备故障选主(可以结合keepalived )Proxy的一些其他优点:
1.SQL 的归一化统计, 2 .可以在6033 端口上进行数据的一些统计 3 prepare
connection pool的概念:1 连接池保护 2允许延迟最大值(max_replication_lag)3对响应延迟 (max_latency_ms)
环境说明:我在我的虚拟机上面安装了2个MySQL的实例. 端口一个是3306 ,另外一个是3307
1:启动我们的proxysql
service proxysql start
小结:和MySQL的很相似,我们启动一个进程,然后fork出一个子进程,父进程负责监控子进程运行状况如果挂了则拉起来,子进程负责执行真正的任务。
ProxySQL也是有管理接口和客户端接口,通过配置文件/etc/proxysql.cnf可以看到管理和客户端接口的信息
2 查看端口号
观察到我们可以看到2个端口号:一个是 6032(管理端口) 一个是6033(对外链接端口)
Active Internet connections (servers and established)Proto Recv-Q Send-Q Local Address Foreign Address Statetcp 0 0 *:sunrpc *:* LISTENtcp 0 0 *:6032 *:* LISTENtcp 0 0 *:6033 *:* LISTENtcp 0 0 *:ssh *:* LISTENtcp 0 0 localhost:ipp *:* LISTENtcp 0 0 *:4505 *:* LISTENtcp 0 0 localhost:smtp *:* LISTENtcp 0 0 *:4506 *:* LISTENtcp 0 0 *:43709 *:* LISTENtcp 0 0 pxc1:ssh 192.168.5.209:51899 ESTABLISHEDtcp 0 0 10.0.2.15:54410 64.145.88.40:http ESTABLISHEDtcp 0 0 *:46667 *:* LISTEN
二:登录到我们管理的界面查看表信息的一些操作。
/usr/local/mysql/bin/mysql -h127.0.0.1 -uadmin -padmin -P6032
[root@pxc1 etc]# /usr/local/mysql/bin/mysql -h127.0.0.1 -uadmin -padmin -P6032mysql: [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 1Server version: 5.7.20 (ProxySQL Admin Module)Copyright (c) 2009-2017 Percona LLC and/or its affiliatesCopyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
操作我们的管理界面:
查看我们的库
"admin@127.0.0.1:6032 [(none)]>show databases;+-----+---------+-------------------------------+| seq | name | file |+-----+---------+-------------------------------+| 0 | main | || 2 | disk | /var/lib/proxysql/proxysql.db || 3 | stats | || 4 | monitor | |+-----+---------+-------------------------------+
查看mian下面的表:
use mainshow create table global_variables;
"admin@127.0.0.1:6032 [main]>show create table global_variables;+------------------+----------------------------------------------------------------------------------------------------------------------+| table | Create Table |+------------------+----------------------------------------------------------------------------------------------------------------------+| global_variables | CREATE TABLE global_variables (variable_name VARCHAR NOT NULL PRIMARY KEY,variable_value VARCHAR NOT NULL) |+------------------+----------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
三配置:
1 创建mysql_servers
insert into mysql_servers(hostgroup_id,hostname,port) values(1,'127.0.0.1',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(2,'127.0.0.1',3307);
LOAD MYSQL SERVERS TO RUNTIME;
select * from mysql_servers;
2:配置用户
INSERT INTO mysql_users(username,password,default_hostgroup,default_schema) VALUES ('root','123456',1,'book');
INSERT INTO mysql_users(username,password,default_hostgroup,default_schema) VALUES ('czg','123456',1,'book');
LOAD MYSQL USERS TO RUNTIME;
3规则的配置:
INSERT INTO mysql_query_rules(active,username,match_pattern,destination_hostgroup,apply) VALUES(1,'root','^SELECT.*FOR UPDATE$',1,1);
INSERT INTO mysql_query_rules(active,username,match_pattern,destination_hostgroup,apply) VALUES(1,'root','^SELECT',2,1);
规则的生效:LOAD MYSQL QUERY RULES TO RUNTIME
LOAD MYSQL QUERY RULES TO RUN;(最后运行我们的规则)
四:前端连接proxy
[root@pxc1 ~]# /usr/local/mysql/bin/mysql -h127.0.0.1 -uczg -P6033 -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.7.20 (ProxySQL)Copyright (c) 2009-2017 Percona LLC and/or its affiliatesCopyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
这个时候我们就可以在里面进行我们的增删改查的命令。基本上和我们连接mysql客户端操作是一样的。
五:管理平台查看
"admin@127.0.0.1:6032 [monitor]>select * from stats_mysql_query_digest;+-----------+------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+| hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |+-----------+------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+| 100 | czg | czg | 0x3765930C7143F468 | select * from t1 | 12 | 1510032943 | 1510035665 | 20094 | 576 | 7004 || 100 | czg | czg | 0xF62E7C5ACFD919B2 | insert into t1 values (?,?) | 2 | 1510033783 | 1510034106 | 2583 | 1157 | 1426 || 100 | czg | czg | 0x54C6E5B16410BDE5 | delete from t1 where id=? | 1 | 1510039448 | 1510039448 | 744 | 744 | 744 || 100 | czg | czg | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 1 | 1510032930 | 1510032930 | 0 | 0 | 0 || 101 | czg | czg | 0x4DE01DE1A91A7A02 | select * from t1 where id>? | 1 | 1510036110 | 1510036110 | 1347 | 1347 | 1347 || 1 | czg | root | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 1 | 1510026604 | 1510026604 | 0 | 0 | 0 || 1 | czg | root | 0x99531AEFF718C501 | show tables | 2 | 1510026649 | 1510026878 | 20001881 | 10000756 | 10001125 || 1 | czg | root | 0xBA1ADF966D0B70F4 | show databses | 1 | 1510026623 | 1510026623 | 10001112 | 10001112 | 10001112 || 1 | czg | root | 0x02033E45904D3DF0 | show databases | 2 | 1510026897 | 1510026946 | 20000713 | 10000051 | 10000662 || 100 | czg | czg | 0x6F60EEB7FFFC144D | insert into values (?,?) | 1 | 1510034097 | 1510034097 | 547 | 547 | 547 || 1 | czg | root | 0x594F2C744B698066 | select USER() | 1 | 1510026604 | 1510026604 | 0 | 0 | 0 || 100 | czg | czg | 0x620B328FE9D6D71A | SELECT DATABASE() | 1 | 1510032938 | 1510032938 | 523 | 523 | 523 || 100 | czg | czg | 0x3DCE919B79C9576C | select * from t1 where id=? | 1 | 1510033820 | 1510033820 | 1570 | 1570 | 1570 || 100 | czg | czg | 0x28652853E5DCDAB9 | select * from t1 where id =? | 1 | 1510033203 | 1510033203 | 540 | 540 | 540 || 100 | czg | czg | 0x4DE01DE1A91A7A02 | select * from t1 where id>? | 1 | 1510034958 | 1510034958 | 1281 | 1281 | 1281 || 100 | czg | czg | 0x594F2C744B698066 | select USER() | 1 | 1510032930 | 1510032930 | 0 | 0 | 0 || 100 | czg | czg | 0x9ED2186F2E9C392C | update t1 set name=? where id=? | 2 | 1510034987 | 1510036037 | 3477 | 1410 | 2067 || 101 | czg | czg | 0x9FF6DCAA8E5CFDBB | select * from t3 | 1 | 1510035978 | 1510035978 | 3964 | 3964 | 3964 || 100 | czg | czg | 0x02033E45904D3DF0 | show databases | 1 | 1510032934 | 1510032934 | 4791 | 4791 | 4791 || 101 | czg | czg | 0x3DCE919B79C9576C | select * from t1 where id=? | 1 | 1510036074 | 1510036074 | 947 | 947 | 947 || 100 | czg | czg | 0x340305CD48735BD6 | insert into t1 names (?,?) | 1 | 1510039342 | 1510039342 | 521 | 521 | 521 || 101 | czg | czg | 0x3765930C7143F468 | select * from t1 | 3 | 1510035957 | 1510039371 | 5215 | 452 | 3027 || 100 | czg | czg | 0x3CE4C46484576DFD | insert into t1 values(?,?) | 2 | 1510039362 | 1510039411 | 2422 | 865 | 1557 |+-----------+------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
23 rows in set (0.00 sec)
我们要清空 stats_mysql_query_digest 的数据直接执行以下的语句即可:
select * from stats_mysql_query_digest_reset;
最新文章
- python 实现树结构的打印
- C#参考书的链接推荐
- JS 面向对象 编程设计
- UCOS-消息邮箱(学习笔记)
- 树莓派 不稳定 ssh经常断 解决
- Note of IOS 7 - Views
- phpcmsV9中表单向导在js调用里日期控件在IE下报Calendar未定义的解决办法
- 第一章 Windows NT System Components
- UFLDL教程(五)之self-taught learning
- SQL用row_number进行高速循环
- icon数目
- current imporant Posts
- Javascript类型检测
- 云端TensorFlow读取数据IO的高效方式
- MOOC网视频下载并视频倍速播放,超简单哒,亲测可用,( •̀ ω •́ )y
- 第一次作业:来自一个奋斗的IT学子
- python调用函数超时设置
- 关于Java中IO流的练习
- Struts中的匹配规则
- 产品开发- DFX