Mysql学习之ProxySQL

环境介绍:

        在本实验中,我用的是两台虚拟机(192.169.10.241,192.169.10.242)搭建环境。
        其中:
                192.169.10.241上安装一主(3306)、一从(3307)、ProxySQL
                192.169.10.242上安装一从(3306)
        Mysql版本:5.7.19

ProxySQL安装与启动:

    ProxySQL下载地址:
        https://www.percona.com/downloads/proxysql/
        https://github.com/sysown/proxysql
    安装:
        rpm -ivh proxysql-1.4.9-1.1.el6.x86_64.rpm
    配置文件位置:
        /etc/proxysql.cnf
    启动ProxySQL,观察运行情况:

[root@rhel6 ~]# service proxysql start
Starting ProxySQL: DONE!
[root@rhel6 ~]# ps -ef|grep proxysql
proxysql  6450     1  0 11:56 ?        00:00:00 /usr/bin/proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
proxysql  6451  6450  0 11:56 ?        00:00:00 /usr/bin/proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
root      6474  6349  0 11:56 pts/11   00:00:00 grep proxysql
[root@rhel6 ~]# netstat -anlp|grep proxysql
tcp        0      0 0.0.0.0:6032                0.0.0.0:*                   LISTEN      6451/proxysql       
tcp        0      0 0.0.0.0:6033                0.0.0.0:*                   LISTEN      6451/proxysql 

    登录ProxySQL(登录信息在配置文件中配置)

[root@rhel6 ~]# mysql -uadmin -padmin -h127.0.0.1 -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 2
Server version: 5.5.30 (ProxySQL Admin Module)

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> 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 |
+-----+---------------+-------------------------------------+
5 rows in set (0.10 sec)

ProxySQL监控配置:

    首先在我的主从环境中创建监控用户和对外访问用户:

mysql> create user 'monitor'@'%' identified by 'monitor';
Query OK, 0 rows affected (0.26 sec)

mysql> grant all privileges on *.* to 'monitor'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> create user 'proxyuser'@'%' identified by 'proxyuser';
Query OK, 0 rows affected (0.26 sec)

mysql> grant all privileges on *.* to 'proxyuser'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)

    在ProxySQL中配置主从服务器信息:

mysql> use main;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from mysql_servers;
Empty set (0.00 sec)

mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(1,'192.169.10.241',3306);
Query OK, 1 row affected (0.00 sec)

mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(1,'192.169.10.241',3307),(1,'192.169.10.242',3306);
Query OK, 2 rows affected (0.00 sec)

mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.11 sec)

mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from mysql_servers;
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 192.169.10.241 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 192.169.10.241 | 3307 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 192.169.10.242 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

    配置监控账号:

mysql> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)

mysql> set mysql-monitor_password='monitor';
Query OK, 1 row affected (0.00 sec)

mysql> load mysql variables to runtime;
Query OK, 0 rows affected (0.09 sec)

mysql> save mysql variables to disk;
Query OK, 95 rows affected (0.01 sec)

    查看监控信息,验证配置:

mysql> select * from monitor.mysql_server_connect_log order by time_start_us desc limit 10;
+----------------+------+------------------+-------------------------+---------------+
| hostname       | port | time_start_us    | connect_success_time_us | connect_error |
+----------------+------+------------------+-------------------------+---------------+
| 192.169.10.242 | 3306 | 1564713971041574 | 1474                    | NULL          |
| 192.169.10.241 | 3307 | 1564713971030954 | 1813                    | NULL          |
| 192.169.10.241 | 3306 | 1564713971020374 | 605                     | NULL          |
| 192.169.10.242 | 3306 | 1564713911041250 | 2280                    | NULL          |
| 192.169.10.241 | 3307 | 1564713911030804 | 478                     | NULL          |
| 192.169.10.241 | 3306 | 1564713911020225 | 695                     | NULL          |
| 192.169.10.242 | 3306 | 1564713892495124 | 1840                    | NULL          |
| 192.169.10.241 | 3307 | 1564713892484660 | 2628                    | NULL          |
| 192.169.10.241 | 3306 | 1564713892474069 | 720                     | NULL          |
| 192.169.10.242 | 3306 | 1564713832494958 | 2164                    | NULL          |
+----------------+------+------------------+-------------------------+---------------+
10 rows in set (0.00 sec)

mysql> select * from monitor.mysql_server_ping_log order by time_start_us desc limit 10;
+----------------+------+------------------+----------------------+------------+
| hostname       | port | time_start_us    | ping_success_time_us | ping_error |
+----------------+------+------------------+----------------------+------------+
| 192.169.10.242 | 3306 | 1564714011078199 | 531                  | NULL       |
| 192.169.10.241 | 3307 | 1564714011076144 | 60                   | NULL       |
| 192.169.10.241 | 3306 | 1564714011074308 | 82                   | NULL       |
| 192.169.10.242 | 3306 | 1564714001077933 | 503                  | NULL       |
| 192.169.10.241 | 3307 | 1564714001075931 | 57                   | NULL       |
| 192.169.10.241 | 3306 | 1564714001073905 | 82                   | NULL       |
| 192.169.10.242 | 3306 | 1564713991078404 | 539                  | NULL       |
| 192.169.10.241 | 3307 | 1564713991075946 | 120                  | NULL       |
| 192.169.10.241 | 3306 | 1564713991073807 | 86                   | NULL       |
| 192.169.10.242 | 3306 | 1564713981077951 | 530                  | NULL       |
+----------------+------+------------------+----------------------+------------+
10 rows in set (0.00 sec)

ProxySQL主从分组配置:

mysql> show create table mysql_replication_hostgroups\G
*************************** 1. row ***************************
       table: mysql_replication_hostgroups
Create Table: CREATE TABLE mysql_replication_hostgroups (
    writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
    reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>0),
    comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))
1 row in set (0.00 sec)

mysql> insert into mysql_replication_hostgroups values(1,2,'proxy');
Query OK, 1 row affected (0.00 sec)

mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)

mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from mysql_servers;
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 192.169.10.241 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 192.169.10.241 | 3307 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 192.169.10.242 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

    ProxySQL会根据server的read_only值来进行分组,read_only=0的放到writer_hostgroup,read_only=1的放到reader_hostgroup。(所以我们在一主二从配置中,从库一定要设置read_only=1)。

    配置对外访问账号:

mysql> show create table mysql_users\G
*************************** 1. row ***************************
       table: mysql_users
Create Table: CREATE TABLE mysql_users (
    username VARCHAR NOT NULL,
    password VARCHAR,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
    default_hostgroup INT NOT NULL DEFAULT 0,
    default_schema VARCHAR,
    schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
    transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1,
    fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
    backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
    frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
    max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
    PRIMARY KEY (username, backend),
    UNIQUE (username, frontend))
1 row in set (0.00 sec)

mysql> insert into mysql_users(username,password,default_hostgroup) values ('proxyuser','proxyuser',1);
Query OK, 1 row affected (0.00 sec)

mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)

mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from mysql_users;
+-----------+-----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username  | password  | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+-----------+-----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| proxyuser | proxyuser | 1      | 0       | 1                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |
+-----------+-----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
1 row in set (0.00 sec)

mysql> save mysql users to mem;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from mysql_users;
+-----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username  | password                                  | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+-----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| proxyuser | *1E6567D49FB42C02423CCE5DC28510EE1F6A166B | 1      | 0       | 1                 |                | 0             | 1                      | 0            | 1       | 1        | 10000           |
+-----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
1 row in set (0.00 sec)

    使用对外登录账号,登录验证一下:

[root@rhel6 ~]# mysql -uproxyuser -pproxyuser -h192.169.10.241 -P6033
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 7
Server version: 5.5.30 (ProxySQL)

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> show slave hosts;
+-----------+----------------+------+-----------+--------------------------------------+
| Server_id | Host           | Port | Master_id | Slave_UUID                           |
+-----------+----------------+------+-----------+--------------------------------------+
|     10243 |                | 3306 |     10241 | d21170f1-aa92-11e9-89bd-000c29d8ba4d |
|     10242 | 192.169.10.241 | 3307 |     10241 | d812b5b4-b13c-11e9-a0a8-000c296ee978 |
+-----------+----------------+------+-----------+--------------------------------------+
2 rows in set (0.12 sec)

mysql> select @@port;
+--------+
| @@port |
+--------+
|   3306 |
+--------+
1 row in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|       10241 |
+-------------+
1 row in set (0.00 sec)

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| rhel6      |
+------------+
1 row in set (0.00 sec)

ProxySQL读写分离策略配置:

mysql> insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT.*FOR UPDATE$',1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^SELECT',2,1);
Query OK, 1 row affected (0.00 sec)

mysql> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)

mysql> save mysql query rules to disk;
Query OK, 0 rows affected (0.01 sec)

    在ProxySQL进行查询操作,然后查询stats_mysql_query_digest观察读写分配策略: 

mysql> select * from stats_mysql_query_digest;
+-----------+--------------------+-----------+--------------------+-------------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname         | username  | digest             | digest_text                         | count_star | first_seen | last_seen  | sum_time | min_time | max_time |
+-----------+--------------------+-----------+--------------------+-------------------------------------+------------+------------+------------+----------+----------+----------+
| 2         | information_schema | proxyuser | 0x848F114A754B3578 | select * from test.test where id=?  | 4          | 1564717404 | 1564717406 | 133772   | 535      | 131406   |
| 2         | information_schema | proxyuser | 0x44DE3BEBB4726FE0 | select c from test.test where id=?  | 1          | 1564717455 | 1564717455 | 879      | 879      | 879      |
| 1         | information_schema | proxyuser | 0xDB3A841EF5443C35 | commit                              | 1          | 1564717305 | 1564717305 | 147414   | 147414   | 147414   |
| 1         | information_schema | proxyuser | 0x905802020202A472 | update test.test set d=? where id=? | 1          | 1564717298 | 1564717298 | 96988    | 96988    | 96988    |
| 1         | information_schema | proxyuser | 0x848F114A754B3578 | select * from test.test where id=?  | 2          | 1564717280 | 1564717301 | 204771   | 307      | 204464   |
| 1         | information_schema | proxyuser | 0xA592C94A099E89DC | begin                               | 1          | 1564717269 | 1564717269 | 181      | 181      | 181      |
| 2         | information_schema | proxyuser | 0x2E767D4A1E8566DC | select count(?) from test.test      | 1          | 1564717056 | 1564717056 | 976      | 976      | 976      |
+-----------+--------------------+-----------+--------------------+-------------------------------------+------------+------------+------------+----------+----------+----------+
7 rows in set (0.00 sec)

 

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值