mysql配置

ProxySQL实现MySQL读写分离

官方站点：https://proxysql.com/
官方手册：https://github.com/sysown/proxysql/wiki
proxysql安装

1. 基于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/\$releasever
	gpgcheck=1
	gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
	EOF

2. 基于RPM下载安装
   https://github.com/sysown/proxysql/releases

部署

1. 前提：配置mysql的主从
   主：192.168.8.17
   从：192.168.8.27 （slave节点需要设置read_only=1）
2. 启动后会监听两个默认端口
   6032：ProxySQL的管理端口
   6033：ProxySQL对外提供服务的端口
3. 使用mysql客户端连接到ProxySQL的管理接口6032，默认管理员用户和密码都是admin
   mysql -uadmin -padmin -P6032 -h127.0.0.1

读写分离

1. 向ProxySQL中添加MySQL节点，以下操作不需要use main也可成功

MySQL> show tables;
MySQL > select * from sqlite_master where name='mysql_servers'\G
MySQL > select * from mysql_servers;
MySQL > insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.8.17',3306);
MySQL > insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.8.27',3306);
MySQL > load mysql servers to runtime;
MySQL > save mysql servers to disk;

注意：添加监控后端节点的用户;ProxySQL通过每个节点的read_only值来自动调整它们是属于读组还是写组

2. 在master(主节点)上执行

MySQL> grant replication client on *.* to monitor@'192.168.8.%' identified by '12345678';

注释：创建用户

3. ProxySQL上配置监控

MySQL [(none)]> set mysql-monitor_username='monitor';
MySQL [(none)]> set mysql-monitor_password='12345678';

加载到RUNTIME，并保存到disk

MySQL [(none)]> load mysql variables to runtime;
MySQL [(none)]> save mysql variables to disk;

4. 监控模块的指标保存在monitor库的log表中

查看监控连接是否正常的(对connect指标的监控) – 如果connect_error的结果为NULL则表示正常

MySQL> select * from mysql_server_connect_log;

查看监控心跳信息 (对ping指标的监控)：

	MySQL> select * from mysql_server_ping_log;

查看read_only和replication_lag的监控日志

	MySQL> select * from mysql_server_read_only_log;
	MySQL> select * from mysql_server_replication_lag_log;

5. 设置分组信息

需要修改的是main库中的mysql_replication_hostgroups表;该表有3个字段:
  writer_hostgroup,reader_hostgroup,comment,
示例：指定写组的id为10，读组的id为20

MySQL> insert into mysql_replication_hostgroups values(10,20,"test");

将mysql_replication_hostgroups表的修改加载到RUNTIME生效

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

Monitor模块监控后端的read_only值,按照read_only的值将节点自动移动到读/写组

MySQL> select hostgroup_id,hostname,port,status,weight from mysql_servers;

6. 配置发送SQL语句的用户
   在master节点上创建访问用户

MySQL> grant all on *.* to sqluser@'192.168.8.%' identified by '12345678';

在ProxySQL配置,将用户sqluser添加到mysql_users表中,default_hostgroup默认组设置为写组10,当读写分离的路由规则不符合时，会访问默认组的数据库

MySQL> insert into mysql_users(username,password,default_hostgroup)values('sqluser','12345678',10);
MySQL> load mysql users to runtime;
MySQL> save mysql users to disk;

使用sqluser用户测试是否能路由到默认的10写组实现读/写数据`

mysql -usqluser -p12345678 -P6033 -h127.0.0.1 -e 'select @@server_id'
mysql -usqluser -p12345678 -P6033 -h127.0.0.1 -e 'create database testdb'
mysql -usqluser -p12345678 testdb -P6033 -h127.0.0.1 -e 'create table t(id int)'

7. 在proxysql上配置路由规则，实现读写分离
   插入路由规则：将select语句分离到20的读组,select语句中有一个特殊语句SELECT…FOR UPDATE它会申请写锁,应路由到10的写组

MySQL> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)
	   VALUES(1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1);
MySQL> load mysql query rules to runtime;
MySQL> save mysql query rules to disk;

注意：因ProxySQL根据rule_id顺序进行规则匹配,select … for update规则的rule_id必须要小于普通的select规则的rule_id
测试读操作是否路由给20的读组

mysql -usqluser -p12345678 -P6033 -h127.0.0.1 -e 'select @@server_id'

路由的信息：查询stats库中的stats_mysql_query_digest表 – (调度策略记录)

MySQL > SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;