proxymysql的安装与应用

具体的资料我们可以查看官方的文档：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             State      

tcp        0      0 *:sunrpc                    *:*                         LISTEN      

tcp        0      0 *:6032                      *:*                         LISTEN      

tcp        0      0 *:6033                      *:*                         LISTEN      

tcp        0      0 *:ssh                       *:*                         LISTEN      

tcp        0      0 localhost:ipp               *:*                         LISTEN      

tcp        0      0 *:4505                      *:*                         LISTEN      

tcp        0      0 localhost:smtp              *:*                         LISTEN      

tcp        0      0 *:4506                      *:*                         LISTEN      

tcp        0      0 *:43709                     *:*                         LISTEN      

tcp        0      0 pxc1:ssh                    192.168.5.209:51899         ESTABLISHED

tcp        0      0 10.0.2.15:54410             64.145.88.40:http           ESTABLISHED

tcp        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 -P6032

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.7.20 (ProxySQL Admin Module)

Copyright (c) 2009-2017 Percona LLC and/or its affiliates

Copyright (c) 2000, 2017, 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.

       操作我们的管理界面：

       查看我们的库

"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 main

show 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 -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.7.20 (ProxySQL)

Copyright (c) 2009-2017 Percona LLC and/or its affiliates

Copyright (c) 2000, 2017, 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客户端操作是一样的。

 

 五：管理平台查看

"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;