ProxySQL 简介
ProxySQL是一个高性能的MySQL中间件，是灵活强大的MySQL代理层。像C罗一样的强大，可以实现读写分离，支持Query路由功能，支持动态指定某个SQL进行cache，支持动态加载配置、故障切换和一些SQL的过滤功能。现在由percona支持。
特性
- 连接池
- 主机和用户的最大连接数限制
- 自动下线后端DB
- 强大的规则路由引擎
- 支持prepared statement
- 支持Query Cache
- 支持负载均衡
ProxySQL 安装
相关网站
http://www.proxysql.com/ （官网）
https://www.percona.com/live/e17/sessions/utilizing-proxysql-for-connection-pooling-in-php
https://github.com/sysown/proxysql（git地址）
https://github.com/sysown/proxysql/wiki （帮助手册）
Ubuntu包安装
wget https://github.com/sysown/proxysql/releases/download/v1.4.9/proxysql_1.4.9-ubuntu16_amd64.deb
dpkg -i proxysql_1.4.9-ubuntu16_amd64.deb
- 查看服务是否安装成功
root@bd-All-Series:/var/run# proxysql -h
High Performance Advanced Proxy for MySQL
USAGE: proxysql [OPTIONS]
OPTIONS:
-c, --config ARG Configuraton file
-D, --datadir ARG Datadir
-e, --exit-on-error Do not restart ProxySQL if crashes
-f, --foreground Run in foreground
-h, -help, --help, --usage Display usage instructions.
-M, --no-monitor Do not start Monitor Module
-n, --no-start Starts only the admin service
-r, --reuseport Use SO_REUSEPORT
-S, --admin-socket ARG Administration Unix Socket
-V, --version Print version
--idle-threads Create auxiliary threads to handle idle connections
--initial Rename/empty database file
--reload Merge config file into database file
--sqlite3-server Enable SQLite3 Server
ProxySQL rev. 1.4.9-3-gd9fd599 -- Wed May 30 11:59:03 2018
Copyright (C) 2013-2017 René Cannaò
This program is free and without warranty
- 配置文件路径
/etc/proxysql.cnf - 启动和停止命令
service proxysql start #systemctl start proxysql
service proxysql stop #systemctl stop proxysql
- 连接后台
root@bd-All-Series:/var/run# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
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 165106
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2018, 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.
Admin>
与mysql相勾结
修改配置文件
datadir="/var/lib/proxysql"
admin_variables=
{
admin_credentials="admin:admin"
mysql_ifaces="0.0.0.0:6032"
}
mysql_variables=
{
threads=4
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces="/var/run/proxysql.sock"
default_schema="information_schema"
stacksize=1048576
server_version="5.5.30"
connect_timeout_server=3000
monitor_username="monitor"
monitor_password="monitor"
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
mysql_servers =
(
{ address="127.0.0.1" , port=3306 , hostgroup=0 }
)
mysql_users:
(
{ username = "root" , password = "root" , default_hostgroup = 0 , active = 1 }
)
mysql_query_rules:
(
)
scheduler=
(
)
mysql_replication_hostgroups=
(
)
在MySQL中增加proxysql的监听用户，注意是在MySQL中添加
use mysql;
CREATE USER 'monitor'@'host' IDENTIFIED BY 'monitor';
grant all privileges on mq.* to monitor@localhost identified by 'monitor';
flush privileges;
重启proxysql服务
- 当我们修改了配置文件需要重新加载配置文件（存放在sqlite）
service proxysql initial
#其他相关命令
proxysql --initial #Rename/empty database file
proxysql --reload #Merge config file into database file
查看是否成功启动
root@bd-All-Series:/home/bd/soft# ps -ef |grep -v 'grep' | grep proxy
root 29165 1 0 16:20 ? 00:00:00 proxysql --initial -c /etc/proxysql.cnf -D /var/lib/proxysql
root 29166 29165 0 16:20 ? 00:00:24 proxysql --initial -c /etc/proxysql.cnf -D /var/lib/proxysql
代码测试
编写测试例子
<?php
//这里采用Unix Domain Socket形式连接proxysql，对应proxysql的配置文件
$host = '127.0.0.1';
$user = 'root';
$password = 'root';
$database = 'yiiadmin';
$charset = 'utf8';
$socket = '/var/run/proxysql.sock';
$dsn = "mysql:dbname={$database};charset={$charset}";
if (empty($_GET['proxysql'])) {
$dsn .= ";host={$host}";
} else {
$dsn .= ';unix_socket=/var/run/proxysql.sock';
}
echo $dsn .'<br/>';
$dbh = new PDO($dsn, $user, $password);
$sql = 'SELECT * FROM admin LIMIT 10';
$value = $dbh->query($sql);
foreach ($value as $v) {
var_dump($v);
}
?>
ab测试
查看proxysql对MySQL的监控日志（在proxysql中查看）
Admin> SELECT * FROM monitor.mysql_server_ping_log limit 10;
+-----------+------+------------------+----------------------+------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+-----------+------+------------------+----------------------+------------+
| 127.0.0.1 | 3306 | 1531477104768035 | 233 | NULL |
| 127.0.0.1 | 3306 | 1531477114768168 | 231 | NULL |
| 127.0.0.1 | 3306 | 1531477124768243 | 235 | NULL |
| 127.0.0.1 | 3306 | 1531477134768373 | 217 | NULL |
| 127.0.0.1 | 3306 | 1531477144768454 | 241 | NULL |
| 127.0.0.1 | 3306 | 1531477154768586 | 234 | NULL |
| 127.0.0.1 | 3306 | 1531477164768663 | 235 | NULL |
| 127.0.0.1 | 3306 | 1531477174768792 | 235 | NULL |
| 127.0.0.1 | 3306 | 1531477184768866 | 240 | NULL |
| 127.0.0.1 | 3306 | 1531477194768994 | 214 | NULL |
+-----------+------+------------------+----------------------+------------+
10 rows in set (0.00 sec)
