MYSQL读写分离中间件PROXYSQL的安装使用
公司需要读写分离,使用的PHP,THINKPHP5框架。
什么是PROXYSQL?
ProxySQL is a high-performance proxy, currently for MySQL and its forks (like Percona Server for MySQL and MariaDB).
他能做什么,有什么功能?
MySQL firewall
Connection pooling
Shard lookup and automated routing
Ability to read/write split
Automatically switch to another master in case of active master failure
Query cache
Performance metrics
Other neat features
我为什么使用proxysql
GITHUB一直在更新,作者很活跃啊,而且又是PERCONA推荐的中间件
我的环境
10.0.0.100:3307 主
10.0.0.101:3306 从
10.0.0.102:3306 从
安装PROXYSQL
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm -y
yum install proxysql -y
有2个配置文件
/etc/proxysql-admin.cnf /etc/proxysql.cnf
只在第一次启动的时候有用,后续所有的配置修改都是对SQLite数据库操作,并且不会更新配置文件。ProxySQL绝大部分配置都可以在线修改,配置存储在 /var/lib/proxysql/proxysql.db 中。
/etc/init.d/proxysql start
一共有2个端口 6032是管理端口,6033是数据端口
proxysql 启动后会像 mysqld 一样,马上fork一个子进程,真正处理请求,而父进程负责监控子进程运行状况,如果crash了就拉起来。
[root@vpc-nosql ~]# ps -ef|grep proxysql
root 1637 1 0 Jul17 ? 00:00:00 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
root 1639 1637 0 Jul17 ? 00:10:28 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
root 18009 17216 0 09:36 pts/0 00:00:00 grep proxysql
开机启动
chkconfig --add proxysql
进入管理接口
mysql -uadmin -padmin -h127.0.0.1 -P6032 -prompt='proxysql>'
配置后端MySQL,添加server时就为其划分好hostgroup_id(例如10表示写组,11表示读组)
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(10,'10.0.0.100',3307,1,1000,10,'online');
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(11,'10.0.0.101',3306,1,1000,10,'online');
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(11,'10.0.0.102',3306,1,1000,10,'online');
Query OK, 1 row affected (0.00 sec)
查看后端MySQL
mysql> select * from mysql_servers;
配置后端MySQL业务账号密码
mysql> insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('yewuid','yewumima',1,10,1);
Query OK, 1 row affected (0.00 sec)
transaction_persistent :如果为1,则一个完整的SQL只可能路由到一个节点;这点非常重要,主要解决这种情况:一个事务有混合的读操作和写操作组成,事务未提交前,如果事务中的读操作和写操作路由到不同节点,那么读取到的结果必然是脏数据。所以一般情况下,该值应该设置为1,尤其是业务中使用到事务机制的情况(默认为0)
创建健康检测monit0r 账号,后端MYSQL也需要创建这个账号和密码
set mysql-monitor_username='monit0r';
set mysql-monitor_password='monit0r';
查看监控是否生效
SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
其他变量的修改
set mysql-default_charset = 'utf8mb4';
以上都是variables,加载并且保存到DISK里面
LOAD MYSQL VARIABLES TO RUNTIME; /*将配置应用于proxysql运行环境*/
SAVE MYSQL VARIABLES TO DISK; /*将配置存储到sqlite数据库中*/
刚才创建的主机和用户也需要加载保存到DISK里面,不然重启PROXYSQL配置信息会没掉的
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
连接数据库,通过proxysql的客户端接口访问(6033) 10.0.0.90 是proxysql所在机器
mysql -h10.0.0.90 -uliyuu -p -P6033 -prompt='proxysql>'
-- 查看各类命令的执行情况
select Command,Total_Time_us,Total_cnt from stats_mysql_commands_counters where Total_cnt >0;
-- 查看各类SQL的执行情况
select * from stats_mysql_query_digest;
通过上面看到,ProxySQL已经正常启动,但是在stats_mysql_query_digest的hostgroup中发现,读和写全部都是走10这个Master的HG,没有用到从库。主要原因就是ProxySQL的核心mysql_query_rules路由表没有配置。proxysql是通过自定义sql路由规则就可以实现读写分离。
定义路由规则,如:这里我们将所有除了select* from update的select全部发送到slave,其他的的语句发送到master。其他的的语句发送到master。
mysql> INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',10,1);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',11,1);
Query OK, 1 row affected (0.00 sec)
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
说明:active表示是否启用这个sql路由项,match_pattern就是我们正则匹配项,destination_hostgroup表示我们要将该类sql转发到哪些mysql上面去,apply为1表示该正则匹配后,将不再接受其他匹配,直接转发。具体的信息看上面的表介绍说明。路由规则添加完成,现在来测试下读写分离,先清空stats_mysql_query_digest统计表:
mysql> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
问题1
2017-07-28 11:24:13 ProxySQL_Admin.cpp:2921:flush_mysql_variables___database_to_runtime(): [WARNING] Impossible to set not existing variable ping_interval_server with value "120000". Deleting. If the variable name is correct, this version doesn't support it
解决:
mysql-ping_interval_server was renamed mysql-ping_interval_server_msec .
This meas that the sample config file needs to be updated.
vim /etc/proxysql.cnf
# ping_interval_server=120000
ping_interval_server_msec=120000
问题2
可能会有内存泄漏
Also note that in due the way jemalloc allocates memory, there is a possibility that these memory increases aren't really a memory leak but jemalloc caching some memory.
ProxySQL 1.4.1 will provide further memory metrics
select * from stats_mysql_connection_pool;
参考
http://seanlook.com/
http://fordba.com/category/mysql/
http://www.mamicode.com/info-detail-1799055.html
其他
根据总执行时间查询前5个查询
SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY sum_time DESC LIMIT 5;
执行最多次的前5个查询
SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY count_star DESC LIMIT 5;
执行最长的时间前5
SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY max_time DESC LIMIT 5;
太多了 自行查阅 http://www.proxysql.com/blog/configure-read-write-split