php mysql 代理_让PHP像C罗一样操作MySQL之ProxySQL

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

代码测试

编写测试例子

//这里采用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 .'
';

$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)