MySQL读写分离-proxysql

环境准备

master 192.168.0.141
slave1 192.168.0.137
slave2 192.168.0.138
proxysql 192.168.0.186

从库设置只读

在实现主从复制时从节点必须要设置read_only，这是ProxySQL区分是用来作为读服务器还是写服务器的依据

mysql> set global read_only=1;

mysql创建服务账号和monitor账号

create user 'proxysql'@'192.168.0.%' identified by 'proxysql';
GRANT ALL ON *.* TO 'proxysql'@'192.168.0.%';
create user 'monitor'@'192.168.0.%' identified by 'monitor';
GRANT SELECT ON *.* TO 'monitor'@'192.168.0.%';

proxysql yum源

vim /etc/yum.repos.d/proxysql.repo

[proxysql_repo]
name= ProxySQL YUM repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/latest
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key

proxysql安装

yum install proxysql

proxysql配置文件说明

rpm -ql proxysql

/etc/init.d/proxysql              #管理脚本
/etc/logrotate.d/proxysql
/etc/proxysql.cnf                #配置文件
/usr/bin/proxysql
/usr/share/proxysql/tools/proxysql_galera_checker.sh
/usr/share/proxysql/tools/proxysql_galera_writer.pl

修改proxysql端口

vim /etc/proxysql.cnf

...
interfaces="0.0.0.0:3306"   #6033改为3306
...

启动

systemctl start proxysql.service

登录到proxysql管理端

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)

main：默认数据库，存放用户验证、路由规则等信息。我们要做的配置都是针对这个库的
disk：持久化到硬盘的配置
stats：proxysql运行抓取的统计信息，如各命令的执行次数、查询执行时间等
monitor：monitor模块收集的信息，db的健康情况、各种检查等

查看配置表

mysql> show tables;
+--------------------------------------------+
| tables                                     |
+--------------------------------------------+
| global_variables                           |
| mysql_aws_aurora_hostgroups                |
| mysql_collations                           |
| mysql_galera_hostgroups                    |
| mysql_group_replication_hostgroups         |
| mysql_query_rules                          |
| mysql_query_rules_fast_routing             |
| mysql_replication_hostgroups               |
| mysql_servers                              |
| mysql_users                                |
| proxysql_servers                           |
| runtime_checksums_values                   |
| runtime_global_variables                   |
| runtime_mysql_aws_aurora_hostgroups        |
| runtime_mysql_galera_hostgroups            |
| runtime_mysql_group_replication_hostgroups |
| runtime_mysql_query_rules                  |
| runtime_mysql_query_rules_fast_routing     |
| runtime_mysql_replication_hostgroups       |
| runtime_mysql_servers                      |
| runtime_mysql_users                        |
| runtime_proxysql_servers                   |
| runtime_scheduler                          |
| scheduler                                  |
+--------------------------------------------+
24 rows in set (0.01 sec)

global_variables：各种变量，包括监听的端口、管理账号、是否禁用monitor等，详情可以参考官方文档
mysql_：mysql开头的表就是我们配置要操作的表，具体都是干什么的还是看官方文档吧，介绍的很细，后面我会针对读写分离的配置做介绍
runtime_：runtime开头的表是运行时读的表，不能通过DML语句修改，我们针对mysql开头的表做完配置修改之后，要执行load mysql xxx to runtime以将对应的配置加载到运行时环境
注意：当执行完load语句将配置加载到运行时环境后，还要执行save mysql xxx to disk将配置存到硬盘上，以便下次重启时加载，如果忘记执行，当重启时本次修改的配置会丢失

添加DB实例

一共有三个节点（一个master两个slave），要进行读写分离，将master设为写节点，两个slave设为读节点

mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(1000,'192.168.0.141',3306,1,100,10,'write group');
Query OK, 1 row affected (0.00 sec)

mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(2000,'192.168.0.137',3306,1,100,10,'read group');
Query OK, 1 row affected (0.00 sec)

mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(2000,'192.168.0.138',3306,1,100,10,'read group');
Query OK, 1 row affected (0.00 sec)

mysql> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment     |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
| 1000         | 192.168.0.141 | 3306 | 0         | ONLINE | 1      | 0           | 100             | 10                  | 0       | 0              | write group |
| 2000         | 192.168.0.137 | 3306 | 0         | ONLINE | 1      | 0           | 100             | 10                  | 0       | 0              | read group  |
| 2000         | 192.168.0.138 | 3306 | 0         | ONLINE | 1      | 0           | 100             | 10                  | 0       | 0              | read group  |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
3 rows in set (0.00 sec)

hostgroup_id：一个角色一个id，该表的主键是hostgroup_id+hostname+port
hostname：db实例IP
port：db实例端口
weight：权重，如果有多个相同角色的实例，会优先选择权重高的
status：状态
-ONLINE 正常
-SHUNNED 临时被剔除
-OFFLINE_SOFT 软离线状态，不再接受新的连接，已建立的连接会等待
-OFFLINE_HARD 离线，不接收新连接， 已建立的连接也会强制断开（宕机或者网络不可用）
max_connections：最大连接数
max_replication_lag：允许的最大延迟

添加服务账号

mysql> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('proxysql','proxysql',1000);
Query OK, 1 row affected (0.01 sec)


mysql> 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 | proxysql | 1      | 0       | 1000              | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+--------+
1 row in set (0.00 sec)

设置监控账号

mysql> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)

mysql> set mysql-monitor_password='monitor';
Query OK, 1 row affected (0.00 sec)

添加规则

读写分离规则

mysql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1000,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2000,1);
Query OK, 1 row affected (0.00 sec)

mysql> select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;
+---------+--------+----------------------+-----------------------+-------+
| rule_id | active | match_digest         | destination_hostgroup | apply |
+---------+--------+----------------------+-----------------------+-------+
| 1       | 1      | ^SELECT.*FOR UPDATE$ | 1000                  | 1     |
| 2       | 1      | ^SELECT              | 2000                  | 1     |
+---------+--------+----------------------+-----------------------+-------+
2 rows in set (0.00 sec)

完善配置

我们的mysql集群是基于MHA的，master挂掉之后，slave会提升为新的master，这个时候我们希望proxysql的规则自动变更，在mysql_servers中增加一条记录，将新的master的hostname和port添加到写的hostgroup中

proxysql是支撑这种配置的，根据mysql_replication_hostgroups中的数据，proxysql通过检测到各server的read_only值来自动为server设置hostgroup_id

mysql> insert into  mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) values(1000,2000,'Reading and Writing Separation');
Query OK, 1 row affected (0.00 sec)

mysql> select * from mysql_replication_hostgroups;
+------------------+------------------+------------+--------------------------------+
| writer_hostgroup | reader_hostgroup | check_type | comment                        |
+------------------+------------------+------------+--------------------------------+
| 1000             | 2000             | read_only  | Reading and Writing Separation |
+------------------+------------------+------------+--------------------------------+
1 row in set (0.00 sec)

将配置加载到运行时

mysql> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)

mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)

mysql> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)

mysql> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)

mysql> load admin variables to runtime;
Query OK, 0 rows affected (0.00 sec)

将配置保存到硬盘

mysql> save mysql users to disk;
Query OK, 0 rows affected (0.15 sec)

mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.33 sec)

mysql> save mysql query rules to disk;
Query OK, 0 rows affected (0.36 sec)

mysql> save mysql variables to disk;
Query OK, 96 rows affected (0.09 sec)

mysql> save admin variables to disk;
Query OK, 32 rows affected (0.09 sec)

到此读写分离的相关配置就大功告成了！之后我们的应用可以通过配置中配置proxysql用户连接proxysql服务操作我们的mysql集群

mysql -uproxysql -pproxysql -h 192.168.0.186

mysql -uproxysql -pproxysql -h192.168.0.186  -e "select @@server_id;"
mysql -uproxysql -pproxysql -h192.168.0.186  -e "begin;insert into test.t1 value(3);SELECT @@server_id;commit;"

proxysql应用(wordpress)相关操作

MySQL(主库)创建库,用户,并授权

create database wordpress charset utf8;
create user 'wordpress'@'192.168.0.%' identified by '123456';
GRANT ALL ON wordpress.* TO 'wordpress'@'192.168.0.%';

proxysql添加MySQL用户

mysql -uadmin -padmin -h127.0.0.1 -P6032
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('wordpress','123456',1000);
select * from mysql_users;

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

mysql -uwordpress -p123456 -h192.168.0.186