ProxySQL读写分离从配置到使用

在美团点评DBProxy读写分离使用说明文章中已经说明了使用目的，本文介绍ProxySQL的使用方法以及和DBProxy的性能差异。具体的介绍可以看官网的相关说明，并且这个中间件也是percona推的一款中间件。其特性和其他读写分离的中间件差距不大，具体的会在文中介绍。本文大致简单的介绍在使用过程中的一些说明，也可以看官方的wiki获得使用帮助。

环境：

Distributor ID： Ubuntu
Description： Ubuntu 14.04.5 LTS
Release： 14.04Codename： trusty

下载

percona站点：

https：//www.percona.com/downloads/proxysql/

github/官网：

https：//github.com/sysown/proxysql/releases

我们首先看一下自己的环境：

[root@localhost bin]# cat /etc/masterha/app1.cnf [server default] manager_log=/var/log/mha/app1/manager.log manager_workdir=/var/log/mha/app1.log master_binlog_dir=/home/mysql/db3306/log/master_ip_failover_script=/usr/local/bin/master_ip_failover master_ip_online_change_script=/usr/local/bin/master_ip_online_change password=123456ping_interval=1remote_workdir=/tmp repl_password=123456repl_user=root report_script=/usr/local/bin/send_report shutdown_script=""ssh_user=root user=root [server1] hostname=172.16.16.35port=3306[server2] candidate_master=1check_repl_delay=0hostname=172.16.16.34port=3306[server3] hostname=172.16.16.35port=3307

Installed: proxysql.x86_64 0:1.3.7-1.1.el6 Complete!

[root@localhost bin]# cat /etc/proxysql-admin.cnf # proxysql admin interface credentials. export PROXYSQL_USERNAME="admin"export PROXYSQL_PASSWORD="admin"export PROXYSQL_HOSTNAME="localhost"export PROXYSQL_PORT="6032" # PXC admin credentials for connecting to pxc-cluster-node. export CLUSTER_USERNAME="admin"export CLUSTER_PASSWORD="admin"export CLUSTER_HOSTNAME="localhost"export CLUSTER_PORT="3306" # proxysql monitoring user. proxysql admin script will create this user in pxc to monitor pxc-nodes. export MONITOR_USERNAME="monitor"export MONITOR_PASSWORD="monit0r" # Application user to connect to pxc-node through proxysql export CLUSTER_APP_USERNAME="proxysql_user"export CLUSTER_APP_PASSWORD="passw0rd" # ProxySQL read/write hostgroup export WRITE_HOSTGROUP_ID="10"export READ_HOSTGROUP_ID="11" # ProxySQL read/write configuration mode. export MODE="singlewrite"

[root@localhost bin]# proxysql-admin --config-file=/etc/proxysql-admin.cnf --enable This script will assist with configuring ProxySQL (currently only Percona XtraDB cluster in combination with ProxySQL is supported) ProxySQL read/write configuration mode is singlewrite ProxySQL is not running; please start the proxysql service

[root@localhost bin]# service proxy proxy proxysql proxysql-admin proxysql_galera_checker proxysql_node_monitor [root@localhost bin]# service proxysql start Starting ProxySQL: DONE![root@localhost bin]# 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 1Server version: 5.7.14 (ProxySQL Admin Module) Copyright (c) 2000, 2016, 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>

[root@localhost bin]# 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 2Server version: 5.7.14 (ProxySQL Admin Module) Copyright (c) 2000, 2016, 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 | | +-----+---------+-------------------------------+4 rows in set (0.00 sec) mysql> use admin Database changed mysql> show tables;+--------------------------------------+ | tables | +--------------------------------------+ | global_variables | | mysql_collations | | mysql_query_rules | | mysql_replication_hostgroups | | mysql_servers | | mysql_users | | runtime_global_variables | | runtime_mysql_query_rules | | runtime_mysql_replication_hostgroups | | runtime_mysql_servers | | runtime_mysql_users | | runtime_scheduler | | scheduler | +--------------------------------------+13 rows in set (0.00 sec)

mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(100,'172.16.16.35',3306,1,1000,10,'test'); Query OK, 1 row affected (0.00 sec) mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(101,'172.16.16.34',3306,1,1000,10,'test'); Query OK, 1 row affected (0.00 sec) mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(101,'172.16.16.35',3307,1,1000,10,'test'); Query OK, 1 row affected (0.00 sec) mysql> select * from mysql_servers;+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 100 | 172.16.16.35 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | test | | 101 | 172.16.16.34 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | test | | 101 | 172.16.16.35 | 3307 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | test | +--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+3 rows in set (0.00 sec)

mysql> insert into mysql_replication_hostgroups values(100,101,'masterha') ; Query OK, 1 row affected (0.00 sec) mysql> select * from mysql_replication_hostgroups;+------------------+------------------+----------+ | writer_hostgroup | reader_hostgroup | comment | +------------------+------------------+----------+ | 100 | 101 | masterha | +------------------+------------------+----------+1 row in set (0.00 sec)

mysql> GRANT SUPER, REPLICATION CLIENT ON *.* TO 'proxysql'@'172.16.16.%' IDENTIFIED BY 'proxysql'; Query OK, 0 rows affected, 1 warning (0.09 sec) mysql> flush privileges; Query OK, 0 rows affected (0.10 sec)

mysql> set mysql-monitor_username='proxysql'; Query OK, 1 row affected (0.00 sec) mysql> set mysql-monitor_password='proxysql'; Query OK, 1 row affected (0.00 sec) mysql> load mysql variables to runtime; Query OK, 0 rows affected (0.00 sec) mysql> save mysql variables to disk; Query OK, 74 rows affected (0.02 sec)