MySQL ProxySQL读写分离使用初探
目的在美团点评DBProxy读写分离使用说明文章中已经说明了使用目的本文介绍ProxySQL的使用方法以及和DBProxy的性能差异。具体的介绍可以看官网的相关说明并且这个中间件也是percona推的一款中间件。其特性和其他读写分离的中间件差距不大具体的会在文中介绍。本文大致简单的介绍在使用过程中的一些说明也可以看官方的wiki获得使用帮助。
环境
Distributor ID Ubuntu
Description Ubuntu 14.04.5 LTS
Release 14.04Codename trusty
下载
percona站点
https//www.percona.com/downloads/proxysql/ github/官网
https//github.com/sysown/proxysql/releases安装
1编译安装
http://common.cnblogs.com/images/copycode.gif
1下载wget https//github.com/sysown/proxysql/archive/v1.3.6.tar.gz2安装依赖包
apt-get install automake bzip2 cmake make g++ gcc git openssl debconf-utils3解压、编译makemake install...
Adding system startup for /etc/init.d/proxysql ... /etc/rc0.d/K20proxysql -> ../init.d/proxysql /etc/rc1.d/K20proxysql -> ../init.d/proxysql /etc/rc6.d/K20proxysql -> ../init.d/proxysql /etc/rc2.d/S20proxysql -> ../init.d/proxysql /etc/rc3.d/S20proxysql -> ../init.d/proxysql /etc/rc4.d/S20proxysql -> ../init.d/proxysql /etc/rc5.d/S20proxysql -> ../init.d/proxysql4修改非root启动
添加用户
useradd proxysql
修改相关文件权限
cd /var/lib/chown -R proxysql.proxysql proxysql/cd /etc/chown -R proxysql.proxysql proxysql.cnf
修改启动命令vi /etc/init.d/proxysql ->75行
proxysql $OPTS
改成sudo -u proxysql /usr/bin/proxysql $OPTS5启动&关闭/etc/init.d/proxysql start/etc/init.d/proxysql stop
http://common.cnblogs.com/images/copycode.gif
2deb包安装
http://common.cnblogs.com/images/copycode.gif
1下载wget https//github.com/sysown/proxysql/releases/download/v1.3.6/proxysql_1.3.6-dbg-ubuntu14_amd64.deb2安装
dpkg -i proxysql_1.3.5-1.1.trusty_amd64.deb3非root用户启动参考编译安装部分。
http://common.cnblogs.com/images/copycode.gif
文件说明
通过启动文件 /etc/init.d/proxysql里可以知道ProxySQL安装好的数据目录在 /var/lib/proxysql/配置文件目录在 /etc/proxysql.cnf。
http://common.cnblogs.com/images/copycode.gif
root@proxysql/var/lib/proxysql# pwd/var/lib/proxysql
root@proxysql/var/lib/proxysql# ls -lh
total 48K-rw------- 1 proxysql proxysql33K5月9 1152 proxysql.db-rw------- 1 proxysql proxysql 7.4K5月9 1156 proxysql.log
root@proxysql~# ls -lh /etc/proxysql.cnf
-rw------- 1 proxysql proxysql 4.2K5月9 1145 /etc/proxysql.cnf
http://common.cnblogs.com/images/copycode.gif
启动之后可以看到这些文件proxysql.db是SQLITE的数据文件proxysql配置如后端数据库的账号、密码、路由等存储在这个数据库里面proxysql.log是日志文件。proxysql.pid这个pid文件。proxysql.cnf是ProxySQL的一些静态配置项比如一些启动选项sqlite的数据目录等等。配置文件只在第一次启动的时候读取进行初始化后面只读取db文件。
进程说明
root@proxysql~# ps -ef | grep proxysql
proxysql35740 10 1220 ? 000000 /usr/bin/proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
proxysql35741357400 1220 ? 000000 /usr/bin/proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
和MySQL的很相似我们启动一个进程然后fork出一个子进程父进程负责监控子进程运行状况如果挂了则拉起来子进程负责执行真正的任务。
接口说明
和DBProxy一样ProxySQL也是有管理接口和客户端接口通过配置文件/etc/proxysql.cnf可以看到管理和客户端接口的信息
http://common.cnblogs.com/images/copycode.gif
admin_variables========http://common.cnblogs.com/images/copycode.gif
看到管理接口的端口是6032账号密码是admin(可以动态修改)只能通过本地连接客户端接口的端口是6033账号密码通过管理接口去设置。
库、表说明
通过管理接口登录需要先装好mysql客户端并且设置prompt
prompt = \\u@\\h\\d \\r\\m\\s>
通过管理接口进入ProxySQL
库
http://common.cnblogs.com/images/copycode.gif
root@proxysql~# mysql -uadmin -padmin -h127.0.0.1 -P6032
...
...
admin@127.0.0.1(none) 124308>show databases;+-----+---------+-------------------------------+
| seq | name | file |
+-----+---------+-------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
+-----+---------+-------------------------------+4 rows in set (0.00 sec)
http://common.cnblogs.com/images/copycode.gif
[*] main 内存配置数据库表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容不能通过dml语句修改只能修改对应的不以 runtime_ 开头的在内存里的表然后 LOAD 使其生效 SAVE 使其存到硬盘以供下次重启加载。
[*] disk 是持久化到硬盘的配置sqlite数据文件。
[*] stats 是proxysql运行抓取的统计信息包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等。
[*] monitor 库存储 monitor 模块收集的信息主要是对后端db的健康/延迟检查。
表
1main库
http://common.cnblogs.com/images/copycode.gif
admin@127.0.0.1(none) 020154>show tables from main;+--------------------------------------+
| tables |
+--------------------------------------+
| global_variables |
| mysql_collations |
| mysql_query_rules |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| runtime_global_variables |
| runtime_mysql_query_rules |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_users |
| runtime_scheduler |
| scheduler |
+--------------------------------------+13 rows in set (0.00 sec)
http://common.cnblogs.com/images/copycode.gif
global_variables 设置变量包括监听的端口、管理账号等详细可参考 https//github.com/sysown/proxysql/wiki/Global-variables。通过set来设置如
set mysql-monitor_ping_interval =10001; mysql_collations相关字符集和校验规则。
mysql_query_rules定义查询路由规则。
http://common.cnblogs.com/images/copycode.gif
CREATE TABLE mysql_query_rules (
rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,
username VARCHAR,
schemaname VARCHAR,
flagIN INT NOT NULL DEFAULT 0,
client_addr VARCHAR,
proxy_addr VARCHAR,
proxy_port INT,
digest VARCHAR,
match_digest VARCHAR,
match_pattern VARCHAR,
negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
flagOUT INT,
replace_pattern VARCHAR,
destination_hostgroup INT DEFAULT NULL,
cache_ttl INT CHECK(cache_ttl > 0),
reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
timeout INT UNSIGNED,
retries INT CHECK (retries>=0 AND retries =0) NOT NULL PRIMARY KEY,
reader_hostgroup INT NOT NULL CHECK (reader_hostgroupwriter_hostgroup AND reader_hostgroup>0),
comment VARCHAR, UNIQUE (reader_hostgroup))
定义 hostgroup 的主从关系。ProxySQL monitor 模块会监控 HG 后端所有servers 的 read_only 变量如果发现从库的 read_only 变为0、主库变为1则认为角色互换了自动改写 mysql_servers 表里面 hostgroup 关系达到自动 Failover 效果。
mysql_servers设置后端MySQL的表
http://common.cnblogs.com/images/copycode.gif
CREATE TABLE mysql_servers (
hostgroup_id INT NOT NULL DEFAULT 0,
hostname VARCHAR NOT NULL,
port INT NOT NULL DEFAULT 3306,
status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',
weight INT CHECK (weight >= 0) NOT NULL DEFAULT 1,
compression INT CHECK (compression >=0 AND compression =0) NOT NULL DEFAULT 1000,
max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag =0) NOT NULL DEFAULT 0,
comment VARCHAR NOT NULL DEFAULT '', PRIMARY KEY (hostgroup_id, hostname, port) )
http://common.cnblogs.com/images/copycode.gif
[*] hostgroup_id ProxySQL通过 hostgroup (下称HG) 的形式组织后端db实例。一个 HG 代表同属于一个角色
该表的主键是 (hostgroup_id, hostname, port)可以看到一个 hostnameport 可以在多个hostgroup里面这样可以避免从库全都不可用时依然可以把读请求发到主库上。一个 HG可以有多个实例即多个从库可以通过 weight 分配权重。hostgroup_id 0 是一个特殊的HG路由查询的时候没有匹配到规则则默认选择 HG 0
[*] status
ONLINE 当前后端实例状态正常
SHUNNED 临时被剔除可能因为后端 too many connections error或者超过了可容忍延迟阀值 max_replication_lag
OFFLINE_SOFT “软离线”状态不再接受新的连接但已建立的连接会等待活跃事务完成。
OFFLINE_HARD “硬离线”状态不再接受新的连接已建立的连接或被强制中断。当后端实例宕机或网络不可达会出现。
[*] max_connections 允许连接到该后端实例的最大连接数。不要大于MySQL设置的 max_connections如果后端实例 hostnameport 在多个 hostgroup 里以较大者为准而不是各自独立允许的最大连接数。
[*] max_replication_lag 允许的最大延迟主库不受这个影响默认0。如果 > 0 monitor 模块监控主从延迟大于阀值时会临时把它变为 SHUNNED
[*] max_latency_ms mysql_ping 响应时间大于这个阀值会把它从连接池剔除即使是ONLINE默认0。
[*] comment 备注。
mysql_users配置后端数据库的账号和监控的账号
http://common.cnblogs.com/images/copycode.gif
(active(,)) (use_ssl(,)) (schema_locked(,)) (transaction_persistent(,)) (fast_forward(,)) (backend(,)) (frontend(,)) (max_connections )http://common.cnblogs.com/images/copycode.gif
[*] username, password 连接后端db的用户密码。
这个密码你可以插入明文也可以插入hash加密后的密文proxysql会检查你插入的时候密码是否以 * 开头来判断而且密文要在其它地方使用 PASSWORD()生成。但到 runtime_mysql_users 里都统一变成了密文所以可以明文插入再 SAVE MYSQL USERS TO MEM此时看到的也是HASH密文。
[*] active 是否生效该用户。
[*] default_hostgroup 这个用户的请求没有匹配到规则时默认发到这个 hostgroup默认0
[*] default_schema 这个用户连接时没有指定 database name 时默认使用的schema注意表面上看默认为NULL但实际上受到变量 mysql-default_schema 的影响默认为 information_schema。关于这个参考我所提的 issue #988
[*] transaction_persistent 如果设置为1连接上ProxySQL的会话后如果在一个hostgroup上开启了事务那么后续的sql都继续维持在这个hostgroup上不伦是否会匹配上其它路由规则直到事务结束。虽然默认是0。
[*] frontend, backend 目前版本这两个都需要使用默认的1将来有可能会把 Client -> ProxySQL (frontend) 与 ProxySQL -> BackendDB (backend)的认证分开。从 runtime_mysql_users 表内容看到记录数比 mysql_users 多了一倍就是把前端认证与后端认证独立出来的结果。
[*] fast_forward 忽略查询重写/缓存层直接把这个用户的请求透传到后端DB。相当于只用它的连接池功能一般不用路由规则 .* 就行了。
scheduler调度器是一个类似于cron的实现集成在ProxySQL中具有毫秒的粒度。通过脚本检测来设置ProxySQL。
http://common.cnblogs.com/images/copycode.gif
CREATE TABLE scheduler (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
interval_ms INTEGER CHECK (interval_ms>=100 AND interval_msinsert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(100,'192.168.200.202',3306,1,1000,10,'test proxysql');
Query OK, 1 row affected (0.00 sec)
admin@127.0.0.1 : (none) 12:37:13>insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(1000,'192.168.200.132',3306,1,1000,10,'test proxysql');
Query OK, 1 row affected (0.00 sec)
admin@127.0.0.1 : (none) 12:37:19>select * from mysql_servers;
+--------------+-----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------------+| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |+--------------+-----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------------+| 100 | 192.168.200.202 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | test proxysql || 1000 | 192.168.200.132 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | test proxysql |+--------------+-----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------------+2 rows in set (0.00 sec)
http://common.cnblogs.com/images/copycode.gif
② 配置后端MySQL用户。这个用户需要先在后端MySQL(202,132)里真实存在一个是监控账号、一个是程序账号
http://common.cnblogs.com/images/copycode.gif
-- 监控dba@192.168.200.202 : 12:38:21>GRANT USAGE ON *.* TO 'proxysql'@'192.168.200.24' IDENTIFIED BY 'proxysql';
Query OK, 0 rows affected (0.00 sec)-- 程序dba@192.168.200.202 : 12:38:22>GRANT SELECT, INSERT, UPDATE, DELETE ON `sbtest`.* TO 'sbuser'@'192.168.200.24' identified by 'sbuser';
Query OK, 0 rows affected (0.00 sec)
http://common.cnblogs.com/images/copycode.gif
在后端MySQL里添加完之后再配置ProxySQL这里需要注意default_hostgroup需要和上面的对应。
http://common.cnblogs.com/images/copycode.gif
admin@127.0.0.1 : (none) 12:39:52>insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('sbuser','sbuser',1,100,1);
Query OK, 1 row affected (0.00 sec)
admin@127.0.0.1 : (none) 12:41:07>select * from mysql_users;+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+| sbuser | sbuser | 1 | 0 | 100 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+1 row in set (0.00 sec)
http://common.cnblogs.com/images/copycode.gif
上面看到是一个明文密码也可以用password函数加密后的密码进行代替。
③ 设置健康监测账号
http://common.cnblogs.com/images/copycode.gif
admin@127.0.0.1 : (none) 12:42:38>UPDATE global_variables SET variable_value='proxysql' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)
admin@127.0.0.1 : (none) 12:42:44>UPDATE global_variables SET variable_value='proxysql' WHERE variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.00 sec)
或则还行下面的
admin@127.0.0.1 : (none) 12:44:24>set mysql-monitor_username='proxysql';
Query OK, 1 row affected (0.01 sec)
admin@127.0.0.1 : (none) 12:45:14>set mysql-monitor_password='proxysql';
Query OK, 1 row affected (0.01 sec)
http://common.cnblogs.com/images/copycode.gif
其他变量的修改按照需要可以自行调整。
http://common.cnblogs.com/images/copycode.gif
admin@127.0.0.1 : (none) 12:45:22>set mysql-default_charset = 'utf8mb4';
Query OK, 1 row affected (0.00 sec)
admin@127.0.0.1 : (none) 12:47:38>set mysql-query_retries_on_failure=0;
Query OK, 1 row affected (0.00 sec)
admin@127.0.0.1 : (none) 12:48:00>set mysql-eventslog_filename='queries.log';
Query OK, 1 row affected (0.00 sec)
admin@127.0.0.1 : (none) 12:48:20>set mysql-ping_timeout_server=1500;
Query OK, 1 row affected (0.00 sec)
admin@127.0.0.1 : (none) 12:48:55>set mysql-monitor_connect_timeout=1000;
Query OK, 1 row affected (0.00 sec)
admin@127.0.0.1 : (none) 12:48:55>set mysql-default_max_latency_ms=2000;
Query OK, 1 row affected (0.00 sec)
admin@127.0.0.1 : (none) 12:48:56>set mysql-server_version='5.6.33';
Query OK, 1 row affected (0.00 sec)
http://common.cnblogs.com/images/copycode.gif
④ 加载配置和变量因为修改了servers、users和variables所以加载的时候要执行
http://common.cnblogs.com/images/copycode.gif
-- 应用到线上admin@127.0.0.1 : (none) 12:51:28>load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)
admin@127.0.0.1 : (none) 12:56:27>load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
admin@127.0.0.1 : (none) 12:56:31>load mysql variables to runtime;
Query OK, 0 rows affected (0.01 sec)-- 持久化admin@127.0.0.1 : (none) 12:56:37>save mysql servers to disk;
Query OK, 0 rows affected (0.01 sec)
admin@127.0.0.1 : (none) 12:56:45>save mysql users to disk;
Query OK, 0 rows affected (0.00 sec)
admin@127.0.0.1 : (none) 12:56:50>save mysql variables to disk;
Query OK, 72 rows affected (0.00 sec)
http://common.cnblogs.com/images/copycode.gif
要是是用明文密码设置mysql_users在这里可以用save命令来转换成了hash值的密码
http://common.cnblogs.com/images/copycode.gif
admin@127.0.0.1 : (none) 04:25:23>save mysql users to mem;
Query OK, 0 rows affected (0.00 sec)
admin@127.0.0.1 : (none) 04:25:33>select username,password from mysql_users;+----------+-------------------------------------------+| username | password |+----------+-------------------------------------------+| sbuser | *CA96E56547F43610DDE9EB7B12B4EF4C51CDDFFC |+----------+-------------------------------------------+1 row in set (0.00 sec)
http://common.cnblogs.com/images/copycode.gif
⑤ 连接数据库通过proxysql的客户端接口访问(6033)
http://common.cnblogs.com/images/copycode.gif
~$ mysql -usbuser -psbuser -h192.168.200.24 -P6033
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 3Server version: 5.6.33 (ProxySQL)
...
sbuser@192.168.200.24 : (none) 04:41:34>show databases;+--------------------+| Database |+--------------------+| information_schema || sbtest |+--------------------+2 rows in set (0.00 sec)
http://common.cnblogs.com/images/copycode.gif
写入测试数据
http://common.cnblogs.com/images/copycode.gif
sbuser@192.168.200.24 : sbtest 04:42:35>insert into sb values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3Duplicates: 0Warnings: 0sbuser@192.168.200.24 : sbtest 04:44:12>select * from sb;+------+| id |+------+| 1 || 2 || 3 |+------+3 rows in set (0.00 sec)
sbuser@192.168.200.24 : sbtest 04:44:14>begin;
Query OK, 0 rows affected (0.00 sec)
sbuser@192.168.200.24 : sbtest 04:44:17>insert into sb values(11),(22),(33);
Query OK, 3 rows affected (0.01 sec)
Records: 3Duplicates: 0Warnings: 0sbuser@192.168.200.24 : sbtest 04:44:26>commit;
Query OK, 0 rows affected (0.00 sec)
sbuser@192.168.200.24 : sbtest 04:44:29>select * from sb;+------+| id |+------+| 1 || 2 || 3 || 11 || 22 || 33 |+------+6 rows in set (0.00 sec)
http://common.cnblogs.com/images/copycode.gif
通过proxysql接口正常操作数据从管理接口看看ProxySQL的统计信息
http://common.cnblogs.com/images/copycode.gif
-- 查看各类命令的执行情况admin@127.0.0.1 : (none) 04:52:59>select Command,Total_Time_us,Total_cnt from stats_mysql_commands_counters where Total_cnt >0;+---------+---------------+-----------+| Command | Total_Time_us | Total_cnt |+---------+---------------+-----------+| BEGIN | 314 | 1 || COMMIT| 493 | 1 || INSERT| 5021 | 2 || SELECT| 2224 | 9 || SHOW | 4577 | 5 |+---------+---------------+-----------+http://common.cnblogs.com/images/copycode.gif
-- 查看各类SQL的执行情况admin@127.0.0.1 : (none) 04:54:47>select * from stats_mysql_query_digest;http://images2015.cnblogs.com/blog/163084/201705/163084-20170510221757847-1489129431.png
通过上面看到ProxySQL已经正常启动但是在stats_mysql_query_digest的hostgroup中发现读和写全部都是走100这个Master的HG没有用到从库。主要原因就是ProxySQL的核心mysql_query_rules路由表没有配置。proxysql是通过自定义sql路由规则就可以实现读写分离。
⑥ 定义路由规则如除select * from tb for update的select全部发送到slave其他的的语句发送到master。
http://common.cnblogs.com/images/copycode.gif
-- 发送到Madmin@127.0.0.1 : (none) 04:58:11>INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',100,1);
Query OK, 1 row affected (0.00 sec)-- 发送到Sadmin@127.0.0.1 : (none) 05:08:17>INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',1000,1);
Query OK, 1 row affected (0.00 sec)-- 加载admin@127.0.0.1 : (none) 05:09:37>load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)
admin@127.0.0.1 : (none) 05:09:57>save mysql query rules to disk;
Query OK, 0 rows affected (0.00 sec)
http://common.cnblogs.com/images/copycode.gif
说明active表示是否启用这个sql路由项match_pattern就是我们正则匹配项destination_hostgroup表示我们要将该类sql转发到哪些mysql上面去apply为1表示该正则匹配后将不再接受其他匹配直接转发。具体的信息看上面的表介绍说明。路由规则添加完成现在来测试下读写分离先清空stats_mysql_query_digest统计表
admin@127.0.0.1 : (none) 05:16:21>select * from stats_mysql_query_digest_reset; 写入测试数据
http://common.cnblogs.com/images/copycode.gif
sbuser@192.168.200.24 : sbtest 10:20:27>insert into sb values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3Duplicates: 0Warnings: 0sbuser@192.168.200.24 : sbtest 10:20:29>select * from sb;
...
sbuser@192.168.200.24 : sbtest 10:20:34>start transaction;
Query OK, 0 rows affected (0.01 sec)
sbuser@192.168.200.24 : sbtest 10:20:41>insert into sb values(11),(22),(33);
Query OK, 3 rows affected (0.00 sec)
Records: 3Duplicates: 0Warnings: 0sbuser@192.168.200.24 : sbtest 10:20:46>commit;
Query OK, 0 rows affected (0.00 sec)
sbuser@192.168.200.24 : sbtest 10:20:51>select * from sb;
...
sbuser@192.168.200.24 : sbtest 10:20:56>select * from sb for update;
..
sbuser@192.168.200.24 : sbtest 10:21:11>insert into sb values(111),(222),(333);
Query OK, 3 rows affected (0.00 sec)
Records: 3Duplicates: 0Warnings: 0sbuser@192.168.200.24 : sbtest 10:21:20>select * from sb;+------+| id |+------+| 1 || 2 || 3 || 11 || 22 || 33 || 1 || 2 || 3 || 11 || 22 || 33 ||111 ||222 ||333 |+------+15 rows in set (0.01 sec)
http://common.cnblogs.com/images/copycode.gif
查看统计信息
select * from stats_mysql_query_digest;http://images2015.cnblogs.com/blog/163084/201705/163084-20170510222526316-1874730894.png
看到读写分离已经成功。为了更直观一点使用sysbench测试读写分离是否正常
./bin/sysbench --test=./share/sysbench/oltp_read_write.lua --mysql-host=192.168.200.24 --mysql-port=6033 --mysql-user=sbuser --mysql-password=sbuser --mysql-db=sbtest--report-interval=10--max-requests=0 --time=300 --threads=4 --tables=1--table-size=500000 --skip-trx=on --db-ps-mode=disable --mysql-ignore-errors=1062 prepare/run/clean 为了更好的验证可以在sysbench运行期间可以到后端的从MySQL上查看是否有查询进来。执行完之后来看下统计表
http://common.cnblogs.com/images/copycode.gif
admin@127.0.0.1 : (none) 10:31:52>select hostgroup,schemaname,username,substr(digest_text,120,-120),count_star from stats_mysql_query_digest;+-----------+------------+----------+--------------------------------------------------------------------+------------+| hostgroup | schemaname | username | substr(digest_text,120,-120) | count_star |+-----------+------------+----------+--------------------------------------------------------------------+------------+| 100 | sbtest | sbuser | show processlist | 15 || 100 | sbtest | sbuser | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?) | 123932 || 100 | sbtest | sbuser | DELETE FROM sbtest1 WHERE id=? | 123932 || 100 | sbtest | sbuser | UPDATE sbtest1 SET c=? WHERE id=? | 123932 || 100 | sbtest | sbuser | UPDATE sbtest1 SET k=k+? WHERE id=? | 123932 || 1000 | sbtest | sbuser | SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN ? AND ? | 123932 || 100 | sbtest | sbuser | show tables | 1 || 100 | sbtest | sbuser | select * from sb for update | 1 || 100 | sbtest | sbuser | start transaction | 1 || 1000 | sbtest | sbuser | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c | 123932 || 1000 | sbtest | sbuser | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? | 123932 || 1000 | sbtest | sbuser | SELECT c FROM sbtest1 WHERE id=? | 1239320 || 1000 | sbtest | sbuser | select * from sb | 3 || 1000 | sbtest | sbuser | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c | 123932 || 100 | sbtest | sbuser | drop table sbtest1 | 1 || 100 | sbtest | sbuser | commit | 1 || 100 | sbtest | sbuser | insert into sb values(?),(?),(?) | 3 |+-----------+------------+----------+--------------------------------------------------------------------+------------+http://common.cnblogs.com/images/copycode.gif
从上面的结果可知路由规则已经生效select语句均到从库上执行了。
页:
[1]