ProxySQL 簡介

ProxySQL是一個高性能的MySQL中間件，是靈活強(qiáng)大的MySQL代理層。像C羅一樣的強(qiáng)大，可以實(shí)現(xiàn)讀寫分離，支持Query路由功能，支持動態(tài)指定某個SQL進(jìn)行cache，支持動態(tài)加載配置、故障切換和一些SQL的過濾功能?，F(xiàn)在由percona支持。

特性

• 連接池
• 主機(jī)和用戶的最大連接數(shù)限制
• 自動下線后端DB
• 強(qiáng)大的規(guī)則路由引擎
• 支持prepared statement
• 支持Query Cache
• 支持負(fù)載均衡

ProxySQL 安裝

相關(guān)網(wǎng)站

http://www.proxysql.com/ （官網(wǎng)）
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

• 查看服務(wù)是否安裝成功

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相勾結(jié)

修改配置文件

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的監(jiān)聽用戶，注意是在MySQL中添加

use mysql;
CREATE USER 'monitor'@'host' IDENTIFIED BY 'monitor';
grant all privileges on mq.* to monitor@localhost identified by 'monitor';
flush privileges;

重啟proxysql服務(wù)

• 當(dāng)我們修改了配置文件需要重新加載配置文件（存放在sqlite）

service proxysql initial
#其他相關(guān)命令
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

代碼測試

編寫測試?yán)?/h4>

<?php
//這里采用Unix Domain Socket形式連接proxysql，對應(yīng)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的監(jiān)控日志（在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)