mysql grant proxy on_利用 ProxySQL 实现 MySQL 的读写分离

注释：

一般，修改的配置都是在memory层。可以load到runtime，使配置在不用重启proxysql的情况下也可以生效，也可以save到disk，将对配置的修改持久化。

[1] LOAD MYSQL USERS FROM MEMORY 或 LOAD MYSQL USERS TO RUNTIME [2] SAVE MYSQL USERS TO MEMORY 或 SAVE MYSQL USERS FROM RUNTIME [3] LOAD MYSQL USERS TO MEMORY 或 LOAD MYSQL USERS FROM DISK [4] SAVE MYSQL USERS FROM MEMORY 或 SAVE MYSQL USERS TO DISK [5] LOAD MYSQL USERS FROM CONFIG

安装步骤：

$ rpm –ivh proxysql-1.3.9-1-centos67.x86_64.rpm // RPM安装

$ service proxysql start // 启动ProxySQL

$ rpm -ql proxysql // 查看配置文件的位置

/etc/init.d/proxysql

/etc/proxysql.cnf

/usr/bin/proxysql

由此得知：proxysql的配置文件在/etc/proxysql.cnf

$ mysql -uadmin -padmin -P6032 -h127.0.0.1 // 登录，从配置文件中找到默认的用户名和密码

$ rpm –ivh proxysql-1.3.9-1-centos67.x86_64.rpm // RPM安装

$ service proxysql start // 启动ProxySQL

$ rpm -ql proxysql // 查看配置文件的位置

/etc/init.d/proxysql

/etc/proxysql.cnf

/usr/bin/proxysql

由此得知：proxysql的配置文件在/etc/proxysql.cnf

$ mysql -uadmin -padmin -P6032 -h127.0.0.1 // 登录，从配置文件中找到默认的用户名和密码

ProxySQL的启动原理:

ProxySQL在启动时，会启动两个进程，监听两个端口号(6032是管理的端口；6033是对外提供服务的端口)，如下图：

启动解释：

在proxysql初次启动时，会从配置文件中获取信息启动；

在proxysql再次启动时，首先从proxysql.cnf中找到datadir，获取proxysql.db的位置，从proxysql.db获取数据，将其加载到内存memory，然后从内存加载到runtime中，因此这种正常的启动，不会从配置文件中获取参数。

由此得知：在线修改时，一定要保存到磁盘，否则再次启动时丢失配置的数据。

三、配置读写分离

机器的信息：

主库：IP：192.168.152.128；port：3306

从库：IP：192.168.152.128；port：3307

Proxysql安装在主库。

以下操作是在main库下的表操作的！

1、设置读写的分组编号(即 hostgroup_id)

insert into mysql_replication_hostgroups values(1,2,db-128);

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

注：设置1为写分组；2为读分组；备注：db-128；将其加载到运行中，保存到磁盘。

2、将MySQL实例分配到各个组中：主库分到写组；从库分到读组。

insert into mysql_servers(hostgroup_id,hostname,port,transaction_persistent,max_connections) values(1,'192.168.152.128',3306,1,100),(2,'192.168.152.128','3307',1,100);

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

注：

若这里有多个从库，可以将其他从库的信息添加到读分组(即：hostgroup_id=2)中。

每个server需要属于一个组；一个组里可以有多个server；一个server可以属于多个组。

3、创建账号，用于对外服务连接进来proxysql的账号

① 在proxysql中创建账号，在外部使用它连接进来时，会在表main.mysql_users中检查该账号的相关信息。

insert into mysql_users(username,password,active,default_hostgroup,default_schema)

values('proxysql','proxysql',1,1,'guolmdb');

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

注：proxysql/proxysql账号用于外部连接进来使用，默认进入写分组的guolmdb库，active=1代表活跃的账号。

② 修改提供外部连接的IP和端口号

update global_variables set variable_value='192.168.152.128:6033' where variable_name='mysql-interfaces';

或 set mysql-interfaces='192.168.152.128:6033';

save mysql variables to disk;

> proxysql restart;

注：

该变量的值可以有多个，即可以设置通过多个IP连接进来，格式：'IP1:port1;IP2:port2'。

修改该变量需要重启才能生效！

有两种设置变量的方式：set 设置 或 update 设置。

③ 将以上创建的账号，在MySQL主库中创建一遍，要不然，proxysql没有权限连接到MySQL。

GRANT ALL PRIVILEGES ON *.* TO 'monitor'@'%' IDENTIFIED BY 'monitor';

GRANT ALL PRIVILEGES ON *.* TO 'proxysql'@'%' IDENTIFIED BY 'proxysql';

解释：

monitor/ monitor 对应着proxysql的表main. global_variables 的mysql-monitor_username

proxysql/proxysql 对应着在proxysql中的表main.mysql_users创建的账户

注意：

若不在MySQL中创建用户并赋权限，proxysql连接不到MySQL中，在proxysql.log中会报错：

# tail -100f proxysql.log

2017-11-13 17:02:35 MySQL_Monitor.cpp:412:monitor_connect_thread(): [ERROR] Server 192.168.152.128:3306 is returning "Access denied" for monitoring user

2017-11-13 17:02:35 MySQL_Monitor.cpp:412:monitor_connect_thread(): [ERROR] Server 192.168.152.128:3307 is returning "Access denied" for monitoring user

# tail -100f proxysql.log

2017-11-13 17:02:35 MySQL_Monitor.cpp:412:monitor_connect_thread(): [ERROR] Server 192.168.152.128:3306 is returning "Access denied" for monitoring user

2017-11-13 17:02:35 MySQL_Monitor.cpp:412:monitor_connect_thread(): [ERROR] Server 192.168.152.128:3307 is returning "Access denied" for monitoring user

4、设置读写的路由规则

insert into mysql_query_rules (active,username,match_pattern,schemaname,destination_hostgroup,apply) values(1,'proxysql','^select','guolmdb',2,1);

load mysql query rules to runtime;save mysql query rules to disk;

解释：

通过账户proxysql连接进来的，一个以select开头的SQL并操作guolmdb库的语句将会被分配到读分组执行。

注意：

这种设置若一个事务为：begin；select；update；commit；可能会将该事务分配到从库操作，这样是错误的！

为避免这种错误，可以针对并发高的SQL设置读的负载均衡。如下操作：

查看表 stats_mysql_query_digest，找到并发高的SQL对应的字段digest。

在main.mysql_query_rules中设置路由规则时，将查询到的digest，添加到路由表的字段digest中。

官网相关博客：http://proxysql.com/blog/configure-read-write-split

查看表 stats_mysql_query_digest，找到并发高的SQL对应的字段digest。

在main.mysql_query_rules中设置路由规则时，将查询到的digest，添加到路由表的字段digest中。

官网相关博客：http://proxysql.com/blog/configure-read-write-split

5、测试 配置的读写分离是否生效

执行一些操作：

# mysql -uproxysql -pproxysql -P6033 -h192.168.152.128

[guolmdb]>insert into t1 values(1);

[guolmdb]>select * from t1 where id=1;

# mysql -uproxysql -pproxysql -P6033 -h192.168.152.128

[guolmdb]>insert into t1 values(1);

[guolmdb]>select * from t1 where id=1;

注：通过对外提供服务的端口号和账号连接进proxysql，进而转接到主库上(因为该账户默认进入的是主库)

查看以上的SQL在哪里执行的：

# mysql -uadmin -padmin -P6032 -h127.0.0.1

[none] >select hostgroup,digest_text from stats_mysql_query_digest;

+-----------+----------------------------------+

| hostgroup | digest_text |

+-----------+----------------------------------+

| 1 | insert into t1 values(?) |

| 2 | select * from t1 where id=? |

+-----------+----------------------------------+

# mysql -uadmin -padmin -P6032 -h127.0.0.1

[none] >select hostgroup,digest_text from stats_mysql_query_digest;

+-----------+----------------------------------+

| hostgroup | digest_text |

+-----------+----------------------------------+

| 1 | insert into t1 values(?) |

| 2 | select * from t1 where id=? |

+-----------+----------------------------------+

注：查看到刚才select的语句使用的是读分组；insert语句使用的是写分组。

四、总结

ProxySQL主要的作用是：在线修改配置使之生效 、Query路由、Query cache。其中，Query路由可以指定一个SQL放在哪个数据库上执行；Query cache可以针对实时请求很高的SQL，在Query cache中缓存一些时间。

一般地，只需要将请求频繁的查询语句，做读的负载均衡，不频繁的可以直接在主库查询。

对ProxySQL做HA：使用两个ProxySQL，一个对外提供服务，一个做故障切换使用。