MySQL ProxySQL读写分离实践
--初始#查询路由命中信息admin@127.0.0.1 : (none) 02:44:52>select * from stats_mysql_query_rules; +---------+------+| rule_id | hits |+---------+------+| 20 | 0 || 48 | 0 || 49 | 0 || 50 | 0 || 60 | 0 || 61 | 0 || 62 | 0 || 63 | 0 || 64 | 0 || 65 | 0 || 66 | 0 || 67 | 0 || 68 | 0 || 69 | 0 || 70 | 0 || 71 | 0 || 72 | 0 || 1000 | 0 || 1001 | 0 || 9999 | 0 |+---------+------+20 rows in set (0.00 sec)
#查询统计信息
admin@127.0.0.1 : (none) 02:45:09>select * from stats_mysql_query_digest;
Empty set (0.00 sec)--操作~$ mysql -umtest -pmtest -h192.168.200.24 -P6033 -A
...
mtest@192.168.200.24 : (none) 02:45:27>use M1 Database changed
mtest@192.168.200.24 : M1 02:45:31>show tables;+--------------+| Tables_in_M1 |+--------------+| mtest1 |+--------------+1 row in set (0.00 sec)
mtest@192.168.200.24 : M1 02:45:33>select * from mtest1;+-------+| id |+-------+| 1 || 11 || 111 ||1111 || 11111 |+-------+5 rows in set (0.00 sec)
mtest@192.168.200.24 : M1 02:45:37>select * from mtest1 order by id;+-------+| id |+-------+| 1 || 11 || 111 ||1111 || 11111 |+-------+5 rows in set (0.00 sec)
mtest@192.168.200.24 : M1 02:45:46>select * from mtest1 order by id;+-------+| id |+-------+| 1 || 11 || 111 ||1111 || 11111 |+-------+5 rows in set (0.01 sec)----以上执行了2次order by id和1此没有order by id的查询去查询统计应该得到的值是3次没有order by id的查询。--验证#查询统计信息查看没有order by id的SQL出现了3次没有出现有order by id的SQL
admin@127.0.0.1 : (none) 02:49:49>select hostgroup,schemaname,digest_text,count_star from stats_mysql_query_digest;+-----------+--------------------+----------------------------------+------------+| hostgroup | schemaname | digest_text | count_star |+-----------+--------------------+----------------------------------+------------+| 1000 | M1 | select * from mtest1 | 3 || 100 | M1 | show tables | 1 || 0 | information_schema | SELECT DATABASE() | 1 || 0 | information_schema | select USER() | 1 || 0 | information_schema | select @@version_comment limit ? | 1 |+-----------+--------------------+----------------------------------+------------+5 rows in set (0.00 sec)
#重写查询的路由命中了2次
admin@127.0.0.1 : (none) 02:50:12>select * from stats_mysql_query_rules;+---------+------+| rule_id | hits |+---------+------+| 20 | 1 || 48 | 2 || 49 | 0 || 50 | 1 || 60 | 1 || 61 | 3 || 62 | 0 || 63 | 0 || 64 | 0 || 65 | 0 || 66 | 0 || 67 | 1 || 68 | 0 || 69 | 0 || 70 | 0 || 71 | 0 || 72 | 0 || 1000 | 0 || 1001 | 0 || 9999 | 1 |+---------+------+20 rows in set (0.00 sec)
页:
[1]