记录部署ProxySql的过程

安装与启动

先去https://github.com/sysown/proxysql/releases 下载稳定版本，我是下载的proxysql-2.0.4-1-centos7.x86_64.rpm，然后rpm -ivh proxysql-2.0.4-1-centos7.x86_64.rpm，如果出现了如下错误：

[root@dvlshop-proxysql-001 ~]# rpm -ivh proxysql-2.0.4-1-centos7.x86_64.rpm 
error: Failed dependencies:
	perl(DBD::mysql) is needed by proxysql-2.0.4-1.x86_64
	perl(DBI) is needed by proxysql-2.0.4-1.x86_64

就执行一下yum install -y perl-IO-Socket-SSL perl-DBD-MySQL就可以了。

安装成功之后，启动的命令是service proxysql start：

[root@dvlshop-proxysql-001 ~]# service proxysql start
Starting ProxySQL: 2019-05-31 11:23:10 [INFO] Using config file /etc/proxysql.cnf
2019-05-31 11:23:10 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generating new keys/certificates.
DONE!
[root@dvlshop-proxysql-001 ~]#

启动完毕就可以使用初始命令mysql -uadmin -padmin -h 127.0.0.1 -P 6032来登录proxysql了，如图：

补充一下：proxysql的默认管理端口是6032，客户端服务端口是6033。默认的用户名密码都是admin，可以在配置文件里看到。

再说一下各个重要文件的位置：proxysql的静态配置文件是/etc/proxysql.cnf(只在第一次启动的时候有用，后续所有的配置修改都是对SQLite数据库操作，并且不会更新到proxysql.cnf文件中。)，日志文件是/var/lib/proxysql/proxysql.log，SQLITE的数据文件是/var/lib/proxysql/proxysql.db。

基本概念

与之前的中间件atlas不同，配置ProxySQL是基于sql命令的方式完成的，而且配置完成之后直接应用无需重启。怎么做到这个的呢？是因为ProxySQL的三层管理配置设计：

runtime：运行中使用的配置文件，这些表的数据库无法直接修改，只能从其他层级load加载。这一份配置会直接影响到生产环境的，所以要将配置加载进RUNTIME层时需要三思而行；
memory：提供用户动态修改配置文件，它是我们修改proxysql的唯一正常入口。一般来说在修改一个配置时，首先修改Memory层，确认无误后再接入RUNTIME层，最后持久化到DISK和CONFIG FILE层。也就是说memeory层里面的配置随便改，不影响生产，也不影响磁盘中保存的数据。
disk：将修改的配置保存到磁盘SQLit表中（即：proxysql.db），DISK/CONFIG FILE层表示持久存储的那份配置，持久层对应的磁盘文件是$(DATADIR)/proxysql.db，在重启ProxySQL的时候，会从proxysql.db文件中加载信息。即如果不持久化下来，重启后，配置都将丢失。
config：一般不使用它（即：proxysql.cnf）

再重复一遍：proxysql分为三个级别，RUNTIME是即时生效的，MEMORY是保存在内存中但并不立即生效的，DISK|CONFIG FILE是持久化或写在配置文件中的。修改的配置都是在memory层。可以load到runtime，使配置在不用重启proxysql的情况下也可以生效，也可以save到disk，将对配置的修改持久化！如图：

这三个级别的配置文件互不干扰，在某个层级修改了配置文件，想要加载或保存到另一个层级，需要额外的LOAD或SAVE操作：”LOAD xx_config FROM xx_level | LOAD xx_config TO xx_level | SAVE xx_config TO xx_level | SAVE xx_config FROM xx_level”等等。

由此可见，如果想要更改proxysql的初始账号密码以及端口，改了/etc/proxysql.cnf是没用的，应该通过sql命令行进入到proxysql里，先查看global_variables这个表：

要修改表里的内容才算是真正的修改了配置文件！于是想要在admin:admin的基础上添加一个lcshop:lcshop2019这个用户同时把登录端口改成127.0.0.1:6969，那么语句如下：

update global_variables set variable_value = 'admin:admin;lcshop:lcshop2019' where variable_name = 'admin-admin_credentials';
update global_variables set variable_value = '127.0.0.1:6969' where variable_name = 'admin-mysql_ifaces';	#登录proxysql的管理端口是6969了
LOAD MYSQL SERVERS TO RUNTIME;  #常用，让修改的配置生效
SAVE MYSQL SERVERS TO DISK;     #常用，将修改的配置持久化

此时就是使用新账号和新端口访问proxysql管理界面了！如图：

跟mysql一样，如果你想查看历史命令记录，cat ~/mysql_history即可。

具体配置

假设我有一组阿里云RDS数据库，主库的内网地址是rm-bp1el471x0ltbg402.mysql.rds.aliyuncs.com，从库的内网地址是rr-bp10ki29n7n8z0ex0.mysql.rds.aliyuncs.com，端口皆3306，并且将这台proxysql的IP地址添加到双方的白名单里。

首先先把两个mysql信息插入到mysql_servers，如下：

MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'rm-bp1el471x0ltbg402.mysql.rds.aliyuncs.com',3306);	#10表示写组，为20表示读组。
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(20,'rr-bp10ki29n7n8z0ex0.mysql.rds.aliyuncs.com',3306);
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname                                    | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | rm-bp1el471x0ltbg402.mysql.rds.aliyuncs.com | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 20           | rr-bp10ki29n7n8z0ex0.mysql.rds.aliyuncs.com | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)

然后还要在阿里云的RDS控制台上创建两个用户，一个是监控用户sqlmonitor，一个是普通的进程用户proxysql。先将进程用户proxysql添加到mysql_users这个表里

MySQL [(none)]>	INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('proxysql','这里是账号对应的密码',10);
MySQL [(none)]> select * from mysql_users;
+----------+--------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| username | password           | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+--------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| proxysql | 这里是账号对应的密码| 1      | 0       | 10                | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |         |
+----------+--------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
1 row in set (0.00 sec)

   LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

这里说一下default_hostgroup，它的意思是“这个用户的请求没有匹配到规则时，默认发到这个hostgroup，默认0”，由于我们上面设定10是写组，所以这里写成10。

这里默认保存的密码是明文的，如果想保存加密后的密码，可以先去mysql的界面里进行加密，比如：

MySQL [(none)]> select PASSWORD('114514');
+-------------------------------------------+
| PASSWORD('114514')                        |
+-------------------------------------------+
| *D9050F2D99C3DDD8138912B7BDF8F4BACBE3A8E7 |
+-------------------------------------------+

然后用这个密码输入，比如：insert into mysql_users(username,password,active,default_hostgroup) values ('proxysql2','*D9050F2D99C3DDD8138912B7BDF8F4BACBE3A8E7',1,20);。注意！mysql_users的username是不能重复的。

确认一下账号已经正确连接：

MySQL [(none)]> select * from mysql_server_connect_log;
+---------------------------------------------+------+------------------+-------------------------+---------------+
| hostname                                    | port | time_start_us    | connect_success_time_us | connect_error |
+---------------------------------------------+------+------------------+-------------------------+---------------+
| rm-bp1el471x0ltbg402.mysql.rds.aliyuncs.com | 3306 | 1559649241943118 | 8607                    | NULL          |
| rr-bp10ki29n7n8z0ex0.mysql.rds.aliyuncs.com | 3306 | 1559649242748088 | 1291                    | NULL          |
| rr-bp10ki29n7n8z0ex0.mysql.rds.aliyuncs.com | 3306 | 1559649301943192 | 1396                    | NULL          |
| rm-bp1el471x0ltbg402.mysql.rds.aliyuncs.com | 3306 | 1559649302760022 | 7218                    | NULL          |
+---------------------------------------------+------+------------------+-------------------------+---------------+
4 rows in set (0.00 sec)

发现connect_error为空，而且connect_success_time_us有值可见已经成功连接了。如果connect_error有具体的错误，那么就可以根据错误来修改。

然后添加sqlmonitor用户，它主要是用来健康监测：

set mysql-monitor_username='sqlmonitor';
set mysql-monitor_password='对应的密码';
#或者是UPDATE global_variables SET variable_value='monitor' WHERE variable_name='sqlmonitor';
#UPDATE global_variables SET variable_value='unixfbi' WHERE variable_name='对应的密码'; 
LOAD MYSQL VARIABLES TO RUNTIME;  #别忘了让修改的配置生效
SAVE MYSQL VARIABLES TO DISK;     #将修改的配置持久化

此时在另外一个xshell对话框窗口尝试一下使用proxysql连接数据库，使用mysql -h127.0.0.1 -P6033 -uproxysql -p效果如图：

已经成功的通过proxysql来访问到数据库了！

验证读写分离

首先在proxysql账号下创建一个tables：

然后往里面随机插入三条数据：

1
2
3

INSERT INTO players ( name, team, num ) VALUES ( "Jordan", "Bulls" , 23);
INSERT INTO players ( name, team, num ) VALUES ( "Kobe", "Lakers" , 24);
INSERT INTO players ( name, team, num ) VALUES ( "Duncan", "Spurs" , 21);

查看数据如下：

此时新开一个xshell窗口登录proxysql的管理端，select * from stats_mysql_query_digest;查看语句细节如下：

发现所有的语句的hostgroup都是10，也就是我们上面设定的写库，即读库并没有承担起读的作用。这是为什么呢？因为proxysql还需要设定路由。于是我们添加两个路由：

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',10,1);
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',20,1);
load mysql query rules to runtime;	#生效
save mysql query rules to disk;		#持久化

如图：

加上这几句话的含义是：1.将select语句全部路由至hostgroup_id=20的组(也就是读组)； 2.但是select * from tb for update这样的语句是修改数据的，所以需要单独定义，将它路由至hostgroup_id=10的组(也就是写组)； 3.其他没有被规则匹配到的组将会被路由至用户默认的组(mysql_users表中的default_hostgroup)

然后我们返回到mysql，再插入几句数据，再重新打开select * from stats_mysql_query_digest;，发现符合标准的select都落到了hostgroup是20的mysql上了，测试读写分离成功，如图：

最后补充几个常用的语句：

select hostgroup_id, hostname, status from runtime_mysql_servers;	#查看在用的mysql状态
select * from mysql_server_ping_log;	#查看mysql的连接情况
select * from stats_mysql_query_rules;	#查看路由规则命中情况
load mysql users to runtime;
load mysql servers to runtime;
load mysql query rules to runtime;
load mysql variables to runtime;
load admin variables to runtime;
#load进runtime，是配置生效

save mysql users to disk;
save mysql servers to disk;
save mysql query rules to disk;
save mysql variables to disk;
save admin variables to disk;
#save到磁盘(/var/lib/proxysql/proxysql.db)中，永久保存配置

参考资料

https://github.com/sysown/ProxySQL
https://arstercz.com/proxysql-%E4%BB%8B%E7%BB%8D%E5%8F%8A%E6%B5%8B%E8%AF%95%E4%BD%BF%E7%94%A8/
http://seanlook.com/2017/04/10/mysql-proxysql-install-config/ （具体各表的信息可以看这个）
https://www.cnblogs.com/kevingrace/p/10329714.html （墙裂推荐！mysql 5.7的主从同步也有）