正文
Linux学习-基于CentOS7的ProxySQL实现读写分离
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
一、实验环境
主机：3台，一台ProxySQL(192.168.214.37)，两台主从复制，master(192.168.214.17)，slave(192.168.214.27)
系统：CentOS7.6
数据库：mariadb-server-5.5.60（光盘yum源）
ProxySQL：proxysql-1.4.16
ProxySQL组成
服务脚本：/etc/init.d/proxysql
配置文件：/etc/proxysql.cnf
主程序：/usr/bin/proxysql
基于SQLITE的数据库文件：/var/lib/proxysql/
启动ProxySQL：service proxysql start启动后会监听两个默认端口
6032：ProxySQL的管理端口
6033：ProxySQL对外提供服务的端口
二、相关步骤
1、实现读写分离前，先实现主从复制，即master(192.168.214.17)，slave(192.168.214.27)实现主从复制，实现方法可参照：https://www.cnblogs.com/hovin/p/11990677.html
2、安装ProxySQL，在192.168.214.37这台主机上操作，安装方法有
基于RPM下载安装：https://github.com/sysown/proxysql/releases
基于YUM仓库安装：
cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasevergpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF
[root@centos7- ~]# yum install -y proxysql
3、使用mysql客户端连接到ProxySQL的管理接口6032，默认管理员用户和密码都是admin
[root@centos7- ~]# mysql -uadmin -padmin -P6032 -h127.0.0.
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.5. (ProxySQL Admin Module) Copyright (c) , , Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| | main | |
| | disk | /var/lib/proxysql/proxysql.db |
| | stats | |
| | monitor | |
| | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
rows in set (0.00 sec)
数据库说明:
main 是默认的”数据库”名，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容，不能通过dml语句修改，只能修改对应的不以 runtime_ 开头的(在内存)里的表，然后 LOAD 使其生效，SAVE 使其存到硬盘以供下次重启加载；
disk 是持久化到硬盘的配置，sqlite数据文件
stats 是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等
monitor 库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查
说明:
在main和monitor数据库中的表， runtime_开头的是运行时的配置，不能修改，只能修改非 runtime_ 表
修改后必须执行LOAD ... TO RUNTIME才能加载到RUNTIME生效
执行save ... to disk 才将配置持久化保存到磁盘，即保存在proxysql.db文件中global_variables 有许多变量可以设置，其中就包括监听的端口、管理账号等
4、向ProxySQL中添加MySQL节点，以下操作不需要 use main 也可成功
MySQL [(none)]> show tables; #显示表
+--------------------------------------------+
| tables |
+--------------------------------------------+
| global_variables |
| mysql_collations |
| 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_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 |
+--------------------------------------------+
rows in set (0.00 sec) MySQL [(none)]> select * from sqlite_master where name='mysql_servers'\G;
*************************** . row ***************************
type: table
name: mysql_servers
tbl_name: mysql_servers
rootpage:
sql: CREATE TABLE mysql_servers (hostgroup_id INT CHECK (hostgroup_id>=) NOT NULL DEFAULT , hostname VARCHAR NOT NULL , port INT NOT NULL DEFAULT , status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE' , weight INT CHECK (weight >= ) NOT NULL DEFAULT , compression INT CHECK (compression >= AND compression <= ) NOT NULL DEFAULT , max_connections INT CHECK (max_connections >=) NOT NULL DEFAULT , max_replication_lag INT CHECK (max_replication_lag >= AND max_replication_lag <= ) NOT NULL DEFAULT , use_ssl INT CHECK (use_ssl IN(,)) NOT NULL DEFAULT , max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=) NOT NULL DEFAULT , comment VARCHAR NOT NULL DEFAULT '' , PRIMARY KEY (hostgroup_id, hostname, port) )
row in set (0.00 sec) ERROR: No query specified MySQL [(none)]> select * from mysql_servers; #查询节点主机，目前没有
Empty set (0.00 sec) MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(,'192.168.214.17',); #添加主节点
Query OK, row affected (0.00 sec) MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(,'192.168.214.27',); #添加从节点
Query OK, row affected (0.00 sec) MySQL [(none)]> select * from mysql_servers; #再查询有了
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| | 192.168.214.17 | | ONLINE | | | | | | | |
| | 192.168.214.27 | | ONLINE | | | | | | | |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
rows in set (0.00 sec) MySQL [(none)]> load mysql servers to runtime; #添加后记得先加载
Query OK, rows affected (0.00 sec) MySQL [(none)]> save mysql servers to disk; #再写入磁盘持久化保存
Query OK, rows affected (0.01 sec)
5、添加监控后端节点的用户；ProxySQL通过每个节点的read_only值来自动调整它们是属于读组还是写组(因此主从配置时从节点配置文件中记得加read_only选项)
1). 在主节点master(192.168.214.17)上执行
MariaDB [(none)]> grant replication client on *.* to monitor@'192.168.214.%' identified by 'monitor';
2). 在ProxySQL(192.168.214.37)上配置监控，同时记得加载到RUNTIME，并保存到disk
MySQL [(none)]> set mysql-monitor_username='monitor';
MySQL [(none)]> set mysql-monitor_password='monitor';
MySQL [(none)]> load mysql variables to runtime;
MySQL [(none)]> save mysql variables to disk;
监控模块的指标保存在monitor库的log表中，查看监控连接是否正常的 (对connect指标的监控):(如果connect_error的结果为NULL则表示正常)
MySQL [(none)]> select * from mysql_server_connect_log;
查看监控心跳信息 (对ping指标的监控)： MySQL [(none)]> select * from mysql_server_ping_log;
查看read_only和replication_lag的监控日志：
MySQL [(none)]> select * from mysql_server_read_only_log;
MySQL [(none)]> select * from mysql_server_replication_lag_log;
6、设置分组信息，需要修改的是main库中的mysql_replication_hostgroups表，该表有3个字段：writer_hostgroup，reader_hostgroup，comment, 指定写组的id为10，读组的id为20，记得将mysql_replication_hostgroups表的修改加载到RUNTIME生效，同时保存到磁盘
MySQL [(none)]> insert into mysql_replication_hostgroups values(,,"test");
Query OK, row affected (0.00 sec) MySQL [(none)]> load mysql servers to runtime;
Query OK, rows affected (0.00 sec) MySQL [(none)]> save mysql servers to disk;
Query OK, rows affected (0.01 sec)
Monitor模块监控后端的read_only值，按照read_only的值将节点自动移动到读/写组
MySQL [(none)]> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+----------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+----------------+------+--------+--------+
| | 192.168.214.17 | | ONLINE | |
| | 192.168.214.27 | | ONLINE | |
+--------------+----------------+------+--------+--------+
rows in set (0.00 sec)
7、配置发送SQL语句的用户，在主节点master(192.168.214.17)上创建访问用户
MariaDB [(none)]> grant all on *.* to sqluser@'192.168.214.%' identified by 'centos';
Query OK, rows affected (0.00 sec)
8、在ProxySQL(192.168.214.37)上配置，将用户sqluser添加到mysql_users表中，default_hostgroup默认组设置为写组10，当读写分离的路由规则不符合时，会访问默认组的数据库，同时加载到RUNTIME并保存磁盘
MySQL [(none)]> insert into mysql_users(username,password,default_hostgroup) values('sqluser','centos',);
Query OK, row affected (0.00 sec)
MySQL [(none)]> load mysql user to runtime;
Query OK, rows affected (0.00 sec)
MySQL [(none)]> save mysql user to disk;
Query OK, rows affected (0.01 sec)
9、使用sqluser用户测试是否能路由到默认的10写组，实现读、写数据
[root@centos7- ~]# mysql -usqluser -pcentos -P6033 -h127.0.0. -e 'select @@server_id'
+-------------+
| @@server_id |
+-------------+
| |
+-------------+
[root@centos7- ~]# mysql -usqluser -pcentos -P6033 -h127.0.0. -e 'create database testdb'
[root@centos7- ~]# mysql -usqluser -pcentos testdb -P6033 -h127.0.0. -e 'create table t(id int)'
10、在proxysql(192.168.214.37)上配置路由规则，实现读写分离，与规则有关的表:mysql_query_rules和mysql_query_rules_fast_routing，后者是前者的扩展表，1.4.7之后支持；插入路由规则：将select语句分离到20的读组，select语句中有一个特殊语句SELECT...FOR UPDATE它会申请写锁，应路由到10的写组，插入后记得加载RUNTIME并保存到磁盘
MySQL [(none)]> insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)VALUES (,,'^SELECT.*FOR UPDATE$',,),(,,'^SELECT',,);
Query OK, rows affected (0.00 sec) MySQL [(none)]> load mysql query rules to runtime;
Query OK, rows affected (0.00 sec) MySQL [(none)]> save mysql query rules to disk;
Query OK, rows affected (0.01 sec) MySQL [(none)]> select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;
+---------+--------+----------------------+-----------------------+-------+
| rule_id | active | match_digest | destination_hostgroup | apply |
+---------+--------+----------------------+-----------------------+-------+
| | | ^SELECT.*FOR UPDATE$ | | |
| | | ^SELECT | | |
+---------+--------+----------------------+-----------------------+-------+
rows in set (0.00 sec)
注意:因ProxySQL根据rule_id顺序进行规则匹配，select ... for update规则的rule_id必须要小于普通的select规则的rule_id
11、配置已完成，开始测试
1). 测试读操作是否路由给20的读组
[root@centos7- ~]# mysql -usqluser -pcentos -P6033 -h127.0.0. -e 'select @@server_id'
+-------------+
| @@server_id |
+-------------+
| |
+-------------+
2). 测试写操作，以事务方式进行测试
[root@centos7- ~]# mysql -usqluser -pcentos -P6033 -h127.0.0. -e 'start transaction;select @@server_id;commit;select @@server_id'
+-------------+
| @@server_id |
+-------------+
| |
+-------------+
+-------------+
| @@server_id |
+-------------+
| |
+-------------+
[root@centos7- ~]# mysql -usqluser -pcentos -P6033 -h127.0.0. -e 'insert testdb.t values (1)'
[root@centos7- ~]# mysql -usqluser -pcentos -P6033 -h127.0.0. -e 'select id from testdb.t'
+------+
| id |
+------+
| |
+------+
3). 路由的信息：查询stats库中的stats_mysql_query_digest表
MySQL [(none)]> SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------+
| hg | sum_time | count_star | digest_text |
+----+----------+------------+----------------------------------+
| | | | select @@server_id |
| | | | create table t(id int) |
| | | | insert testdb.t values (?) |
| | | | select @@server_id |
| | | | start transaction |
| | | | select id from testdb.t |
| | | | create database testdb |
| | | | commit |
| | | | select @@version_comment limit ? |
| | | | select @@version_comment limit ? |
+----+----------+------------+----------------------------------+
rows in set (0.00 sec)
