day14-01-ProxySQL企业实战应用

安装配置

安装perl的软件包支持

yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev

下载proxySQL

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

安装

[root@oldboy data]# rpm -ivh proxysql-2.0.4-1-centos7.x86_64.rpm
[root@oldboy data]# proxysql --version
ProxySQL version 2.0.4-116-g7d371cf, codename Truls

启动

启动ProxySQL
[root@oldboy data]# chkconfig proxysql on
[root@oldboy data]# systemctl start proxysql      
[root@oldboy data]# systemctl status proxysql

[root@oldboy data]# netstat -lnp|grep proxysql
tcp        0      0 0.0.0.0:6032            0.0.0.0:*               LISTEN      3244/proxysql       
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      3244/proxysql 

ProxySQL配置文件概览

[root@oldboy data]# egrep -v "^#|^$" /etc/proxysql.cnf
datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"
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="0.0.0.0:6033"
    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 =
(
)
mysql_users:
(
)
mysql_query_rules:
(
)
scheduler=
(
)
mysql_replication_hostgroups=
(
)

ProxySQL软件结构

proxysql的数据目录
[root@oldboy data]# ll /var/lib/proxysql/
total 312
drwxrwxr-x. 2 proxysql proxysql      6 May 31 15:01 perl5
-rw-rw-r--. 1 proxysql proxysql   1050 May 31 15:01 proxysql-ca.pem
-rw-rw-r--. 1 proxysql proxysql   1058 May 31 15:01 proxysql-cert.pem
-rw-------. 1 proxysql proxysql 163840 May 31 15:01 proxysql.db
-rw-rw-r--. 1 proxysql proxysql   1675 May 31 15:01 proxysql-key.pem
-rw-------. 1 proxysql proxysql   3791 May 31 15:01 proxysql.log
-rw-r--r--. 1 proxysql proxysql      5 May 31 15:01 proxysql.pid
-rw-------. 1 proxysql proxysql 135168 May 31 15:05 proxysql_stats.db
[root@oldboy data]#

ProxySQL内置库介绍

[root@oldboy data]# mysql -uadmin -padmin -h127.0.0.1 -P6032

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.00 sec)
mysql> 

main库：
内存配置数据库，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容，不能通过dml语句修改，只能修改对应的不以 runtime_ 开头的（在内存）里的表，然后LOAD使其生效， SAVE使其存到硬盘以供下次重启加载。
disk库：
是持久化到硬盘的配置库，对应/var/lib/proxysql/proxysql.db文件，也就是sqlite的数据文件。
stats库：
是proxysql运行抓取的统计信息库，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等。
monitor库：
存储monitor模块收集的信息，主要是对后端db的健康、延迟检查。

proxySQL配置应用

添加后端节点

mysql -uadmin -padmin -h127.0.0.1 -P6032
注：配置后端DB 的读写分组(hostgroup_id=0为主，hostgroup_id=1为从)

insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values (0,'10.0.0.131',3307,1,300,10,'proxysql');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values (1,'10.0.0.131',3307,1,3000,10,'proxysql');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values (1,'10.0.0.131',3308,5,3000,10,'proxysql');
insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values (1,'10.0.0.131',3309,5,3000,10,'proxysql');

查询后端节点：
select * from mysql_servers;

ProxySQL 配置相关层次结构

配置用户后端用户

后端主库创建用户：
mysql> grant all on *.* to mon@'10.0.0.%' identified by '123';
mysql> grant all on *.* to app@'10.0.0.%' identified by '123';
proxySQL中添加用户：
应用用户：
insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent)values
('app','123',1,0,1);
监控用户：
set mysql-monitor_username='mon';
set mysql-monitor_password='123';

配置HG

常用示例：
mysql> insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active) values(0,2,1,3,1);

配置组策略

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',0,1);insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',1,1);

保存配置

load mysql servers to runtime;
load mysql users to runtime;
load mysql variables to runtime;

save mysql servers to disk;
save mysql users to disk;
save mysql variables to disk;load mysql servers to runtime;

测试读写分离