Mysql使用ProxySQL实现读写分离-转载类文章

转载

细哥细妹 2018-05-30 14:37:28

转载文献： http://blog.51cto.com/bigboss/2103290

ProxySQL简介： • ProxySQL是一个高性能的MySQL中间件，拥有强大的规则引擎。 • 详细的中文介绍：别人写的，基本跟官方无差别 • 官方文档：https://github.com/sysown/proxysql/wiki/ • 下载地址：https://github.com/sysown/proxysql/releases 准备工作 • 本文所用环境： o 系统均为CentOS7.4，并且关闭防火墙和selinux o ProxySQL版本：proxysql-1.4.8-1-centos7.x86_64.rpm o Mysql版本：mariadb 5.5.56-2.el7 o ProxySQL主机IP：192.168.100.2 o Mysql主库IP：192.168.100.3 o Mysql从库IP：192.168.100.4 • 前提条件： Mysql主从已经配置好了同步开始安装ProxySQL • 安装ProxySQL： wget https://github.com/sysown/proxysql/releases/download/v1.4.8/proxysql-1.4.8-1-centos7.x86_64.rpm yum install -y proxysql-1.4.8-1-centos7.x86_64.rpm

#proxysql需要依赖一些perl库，所以使用yum安装

#安装生成的文件： [root@ProxySQL ~]#rpm -ql proxysql /etc/init.d/proxysql #启动脚本

/etc/proxysql.cnf #配置文件，仅在第一次(/var/lib/proxysql/proxysql.db文件不存在)启动时有效 #启动后可以在proxysql管理端中通过修改数据库的方式修改配置并生效(官方推荐方式。) /usr/bin/proxysql #主程序文件 /usr/share/proxysql/tools/proxysql_galera_checker.sh /usr/share/proxysql/tools/proxysql_galera_writer.pl

• 启动proxysql： /etc/init.d/proxysql start

#proxysql客户端监听在6033端口上，管理端监听6032端口

[root@ProxySQL ~]#/etc/init.d/proxysql start Starting ProxySQL: DONE!

[root@ProxySQL ~]#ss -tanl State Recv-Q Send-Q Local Address:Port Peer Address:Port LISTEN 0 128 *:6032 : LISTEN 0 128 *:6033 : LISTEN 0 128 *:6033 : LISTEN 0 128 *:6033 : LISTEN 0 128 *:6033 :

• 连接proxysql管理端进行配置： mysql -uadmin -padmin -h127.0.0.1 -P6032

#默认的管理端账号密码都是admin，登录进去之后可以修改变量进行修改账号密码

[root@ProxySQL ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.30 (ProxySQL Admin Module)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> • 添加后端的mysql主机： insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'192.168.100.3',3306,1,'Write Group'); insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'192.168.100.4',3306,1,'Read Group');

#使用insert语句添加主机到mysql_servers表中，其中：hostgroup_id 1 表示写组，2表示读组。

MySQL [(none)]> select * from mysql_servers; +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+-- | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | u +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+-- | 1 | 192.168.100.3 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 2 | 192.168.100.4 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 +--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+-- 2 rows in set (0.00 sec) • 添加可以访问后端主机的账号： GRANT ALL ON . TO 'proxysql'@'192.168.100.%' IDENTIFIED BY '123456';

#在后端mysql中添加可以增删改查的账号

insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('proxysql','123456',1,1);

#在proxysql主机的mysql_users表中添加刚才创建的账号，proxysql客户端需要使用这个账号来访问数据库。 #default_hostgroup默认组设置为写组，也就是1 #当读写分离的路由规则不符合时，会访问默认组的数据库

MySQL [(none)]> insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('proxysql','123456',1,1); Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> select * from mysql_users\G *************************** 1. row *************************** username: proxysql password: 123456 active: 1 use_ssl: 0 default_hostgroup: 1 default_schema: NULL schema_locked: 0 transaction_persistent: 1 fast_forward: 0 backend: 1 frontend: 1 max_connections: 10000 1 row in set (0.00 sec) • 添加健康监测的账号： GRANT SELECT ON . TO 'monitor'@'192.168.100.%' IDENTIFIED BY 'monitor';

#在后端主机中添加可以访问数据库的账号,SELECT权限即可

set mysql-monitor_username='monitor' set mysql-monitor_password='monitor'

#在proxysql管理端中修改变量设置健康检测的账号 • 添加读写分离的路由规则：

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

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

MySQL [(none)]> 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$ | 1 | 1 | | 2 | 1 | ^SELECT | 2 | 1 | +---------+--------+----------------------+-----------------------+-------+ 2 rows in set (0.00 sec) • 将刚才我们修改的数据加载至RUNTIME中(参考ProxySQL的多层配置结构)：

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)中，永久保存配置

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

... ... ... ...

MySQL [(none)]> save admin variables to disk; Query OK, 31 rows affected (0.01 sec) 测试读写分离 • 连接proxysql客户端: mysql -uproxysql -p123456 -h127.0.0.1 -P6033

#登录用户是刚才我们在mysql_user表中创建的用户，端口为6033

[root@centos7 ~]#mysql -uproxysql -p123456 -h127.0.0.1 -P6033 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.5.30 (ProxySQL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> • 尝试修改数据库和查询： create database bigboss; create database weijinyun; select user,host from mysql.user;

#创建两个数据库和查个表。

MySQL [(none)]> create database bigboss; Query OK, 1 row affected (0.01 sec)

MySQL [(none)]> create database weijinyun; Query OK, 1 row affected (0.00 sec)

#从下面的hostgroup和digest_text值来看，所有的写操作都被路由至1组，读操作都被路由至2组， #其中1组为写组，2组为读组！ #读写分离成功！！！

MySQL [(none)]> select * from stats_mysql_query_digest; +-----------+--------------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+ | hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time | +-----------+--------------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+ | 1 | information_schema | proxysql | 0xA6212D89D814BAC5 | create database weijinyun | 1 | 1523658457 | 1523658457 | 1244 | 1244 | 1244 | | 2 | information_schema | proxysql | 0x0F02B330C823D739 | select user,host from mysql.user | 1 | 1523658520 | 1523658520 | 12538 | 12538 | 12538 | | 1 | information_schema | proxysql | 0x02033E45904D3DF0 | show databases | 5 | 1523658103 | 1523658486 | 24852 | 1263 | 17592 | | 1 | information_schema | proxysql | 0xA175FD2982EC6396 | create database bigboss | 1 | 1523658437 | 1523658437 | 1833 | 1833 | 1833 | | 1 | information_schema | proxysql | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 3 | 1523658098 | 1523658473 | 0 | 0 | 0 | +-----------+--------------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+ 6 rows in set (0.00 sec)

转载文献： http://blog.51cto.com/bigboss/2103290