Monthly Archives: December 2014

使用q和pt-find了解机器上的库表

当DBA换工作到新环境时,肯定需要大概了解下各台机器有什么库表,如下sql就能打印这台机器上所有非系统自带的库名和表名:

select TABLE_SCHEMA,TABLE_NAME from information_schema.tables where TABLE_SCHEMA not in ('mysql','information_schema','performance_schema');

但有这样一种情况–机器因为分库分表虽然有几十个库和成百上千的表,但大部分库表类型其实是相同的,如下所示:

+--------------+-------------------+
| db_a01     | tab_c01         |  
| db_a02     | tab_c02         |  
............
............
| db_b10    | tab_d09         |  
| db_b10    | tab_d10         |  
+--------------+-------------------+

上诉表格虽然有20个库(db_a01~db_a10db_b01~db_b10)、每个库有20个表(tab_c01~tab_c10tab_d01~tab_d10),一共400张表,直接这么看很眼花,但其实只有两类数据库–db_adb_b,两类表–tab_ctab_d;使用pt-find找出机器上所有库表然后通过q做合并就能简化显示结果:

pt-find -h IP -u USER -p PASSWORD --noquote --dbregex "[^(mysql\. | performance_schema\. | information_schema\.)]" | q -d '.' "select distinct(rtrim(c1,'0|1|2|3|4|5|6|7|8|9') || '.' || rtrim(c2,'0|1|2|3|4|5|6|7|8|9')) from -"

最终400个表简化成4种类型的库.表:

+--------------+-------------------+
| db_a.tab_c          | 
| db_a.tab_d          | 
| db_b.tab_c          | 
| db_b.tab_d          | 
+--------------+-------------------+

因为q基于SQLite,不像MariaDB支持REGEXP_REPLACE()函数用正则匹配数字,否则上述命令能更简洁~

MyAWR : MySQL Workload Report V3 release


myawr-v3 release now !

history of myawr:

————————–
v1: create by noodba

mysql load information and system information 

v2: create by louis liu

myawr_v2 add :

1 more os information module.

2 multiple instance support (extend table by db_port)

3 modified some bugs (mistake value)

4 add different statistics views

v3: create by louis liu

base on myawr_v2

myawr_v3 add sys schema information

(statment statistics/index&table stat/IO latency/file&table IO stat and so on)

including performance schema and information schema

so you need open performance schema on your mysql database

myawr_v3 now support mysql 5.6 GA and will continue support mysql feature version

before install myawr_v3 you need run sys_schema to create formatted views for data collecting.

Overview: Myawr_v3 mysql performance web show

Download:https://github.com/ylouis83/myawr/tree/master