mysqltoolkit用法[备忘] - 数据库编程

tuaction idx: GEN_CLUST_INDEX lock_type: RECORD lock_mode: X wait_hold: w victim: 0 query: update index_species_situaction s set s.new_version= '13121603410024300001' where s.new_version != 'DELETE' and s.species_id ='W6021601' *************************** 2. row *************************** server: 10.1.1.29 ts: 2013-12-16 03:41:05 thread: 25270 txn_id: 0 txn_time: 5 user: mini189 hostname: ip: 10.1.1.25 db: 189mini tbl: index_species_situaction idx: GEN_CLUST_INDEX lock_type: RECORD lock_mode: X wait_hold: w victim: 1 query: update INDEX_SPECIES_SITUACTION h set h.new_version='DELETE' where h.specification_id = NAME_CONST('_specification_id',_utf8'S5F60811' COLLATE 'utf8_general_ci') and h.species_id= NAME_CONST('_species_id',_utf8'W6045FF3' COLLATE 'utf8_general_ci') 2 rows in set (0.00 sec)

/usr/bin/pt-diskstats
调用 /proc/diskstats 文件用于监控磁盘io
直接执行命令则返回下面信息

/usr/bin/pt-diskstats
  #ts device    rd_s rd_avkb rd_mb_s rd_mrg rd_cnc   rd_rt    wr_s wr_avkb wr_mb_s wr_mrg wr_cnc   wr_rt busy in_prg    io_s  qtime stime
  1.0 sda        0.0     0.0     0.0     0%    0.0     0.0     7.0     8.0     0.1    50%    0.1     8.6   4%      0     7.0    5.7   2.9
  1.0 sda2       0.0     0.0     0.0     0%    0.0     0.0     7.0     8.0     0.1    50%    0.1     8.6   4%      0     7.0    5.7   2.9
  1.0 dm-0       0.0     0.0     0.0     0%    0.0     0.0     0.0     0.0     0.0     0%    0.0     0.0   0%      0     0.0    0.0   0.0
  1.0 dm-2       0.0     0.0     0.0     0%    0.0     0.0     4.0     4.0     0.0     0%    0.1    20.0   3%      0     4.0   11.2   8.8
  1.0 dm-3       0.0     0.0     0.0     0%    0.0     0.0    10.0     4.0     0.0     0%    0.2    19.0   4%      0    10.0   14.9   4.1
  1.0 dm-4       0.0     0.0     0.0     0%    0.0     0.0     0.0     0.0     0.0     0%    0.0     0.0   0%      0     0.0    0.0   0.0
  1.0 dm-6       0.0     0.0     0.0     0%    0.0     0.0     0.0     0.0     0.0     0%    0.0     0.0   0%      0     0.0    0.0   0.0
  1.0 drbd0      0.0     0.0     0.0     0%    0.0     0.0     0.0     0.0     0.0     0%    0.0     0.0   0%      0     0.0    0.0   0.0

  1.0 sda        0.0     0.0     0.0     0%    0.0     0.0     4.0     6.0     0.0    33%    0.0     5.3   3%      0     4.0    0.0   5.3
  1.0 sda2       0.0     0.0     0.0     0%    0.0     0.0     4.0     6.0     0.0    33%    0.0     5.3   3%      0     4.0    0.0   5.3
  1.0 dm-0       0.0     0.0     0.0     0%    0.0     0.0     3.0     4.0     0.0     0%    0.0     7.3   1%      0     3.0    2.7   4.7
  1.0 dm-2       0.0     0.0     0.0     0%    0.0     0.0     0.0     0.0     0.0     0%    0.0     0.0   0%      0     0.0    0.0   0.0
  1.0 dm-3       0.0     0.0     0.0     0%    0.0     0.0     0.0     0.0     0.0     0%    0.0     0.0   0%      0     0.0    0.0   0.0
  1.0 dm-4       0.0     0.0     0.0     0%    0.0     0.0     0.0     0.0     0.0     0%    0.0     0.0   0%      0     0.0    0.0   0.0
  1.0 dm-6       0.0     0.0     0.0     0%    0.0     0.0     3.0     4.0     0.0     0%    0.0    10.0   2%      0     3.0    4.0   6.0
  1.0 drbd0      0.0     0.0     0.0     0%    0.0     0.0     0.0     0.0     0.0     0%    0.0     0.0   0%      0     0.0    0.0   0.0

/usr/bin/pt-duplicate-key-checker
能够快速校验某个数据库或表具有多少个索引

[root@mini189a ~]# pt-duplicate-key-checker h=10.1.1.29 --database=189mini --user=terry --password=terry123
# ########################################################################
# Summary of indexes
# ########################################################################

# Total Indexes  262

/usr/bin/pt-find
类似 linux 下 find 功能，能够根据需要对数据库中表进行过滤，搜索

查询表大小

[root@mini189a ~]#  pt-find  --socket=/var/run/mysqld/mysql5.socket --user=terry --password=terry123 --tablesize +1M
`189mini`.`pre_ware_broswers`
`189mini`.`system_regions`
`crazy`.`sys_log_bs`

最近 3 天内创建的表

[root@mini189a ~]#  pt-find  --socket=/var/run/mysqld/mysql5.socket --user=terry --password=terry123 --ctime -3
`monitor`.`deadlocks`
`xszl`.`appreleasetable`
`xszl`.`bulletintable`
`xszl`.`companytable`

最近 35 分钟内修改过的表

[root@mini189a ~]#  pt-find  --socket=/var/run/mysqld/mysql5.socket --user=terry --password=terry123 --mmin -35
`mysql`.`db`
`mysql`.`user`

数据库中空的表

[root@mini189a ~]# pt-find  --socket=/var/run/mysqld/mysql5.socket --user=terry --password=terry123  --empty
`189mini`.`ware_tag_assign`
`189mini`.`ware_tag_content`
`monitor`.`deadlocks`
`mysql`.`columns_priv`
`mysql`.`event`

/usr/bin/pt-fk-error-l

mysqltoolkit用法[备忘](二)