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

ogger
用于检测外键报错信息, 需创建下面相关表进行数据存储

CREATE TABLE foreign_key_errors (
 ts datetime NOT NULL,
 error text NOT NULL,
 PRIMARY KEY (ts)
)

语法测试

pt-fk-error-logger h=10.1.1.29 --dest h=10.1.1.29,D=monitor,t=foreign_key_errors --user=terry --ask-pass

注: 用户 terry 除授权对 foreign_key_errors 表具备写操作还需要具备 process 权限

参考下面返回信息

MariaDB [terry]> select * from monitor.foreign_key_errors \G
*************************** 1. row ***************************
   ts: 2013-12-20 10:32:42
error: Transaction:
TRANSACTION 2F1D0, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1
MySQL thread id 1329, OS thread handle 0x7fe89805a700, query id 7612 localhost root update
insert into t4 values (5, 'aaaaa')
Foreign key constraint fails for table `terry`.`t4`:
,
  CONSTRAINT `t4_ibfk_1` FOREIGN KEY (`id`) REFERENCES `t3` (`id`)
Trying to add in child table, in index `id` tuple:
DATA TUPLE: 2 fields;
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 0000000007e9; asc       ;;

But in parent table `terry`.`t3`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 00000002f1cd; asc       ;;
 2: len 7; hex f4000001a90134; asc       4;;
 3: len 1; hex 64; asc d;;

1 row in set (0.00 sec)

/usr/bin/pt-heartbeat
用于监控 ab 复制延时信息，需创建下面表用于信息存储

             CREATE TABLE heartbeat (
               ts                    varchar(26) NOT NULL,
               server_id             int unsigned NOT NULL PRIMARY KEY,
               file                  varchar(255) DEFAULT NULL,    -- SHOW MASTER STATUS
               position              bigint unsigned DEFAULT NULL, -- SHOW MASTER STATUS
               relay_master_log_file varchar(255) DEFAULT NULL,    -- SHOW SLAVE STATUS
               exec_master_log_pos   bigint unsigned DEFAULT NULL  -- SHOW SLAVE STATUS
             );

/usr/bin/pt-index-usage
根据日志分析查询过程中是否使用到索引

/usr/bin/pt-ioprofile
分析并打印最近活跃的 IO 与相关进程信息（只针对 mysql 进程）

[root@mini189a ~]# pt-ioprofile
2013年 12月 20日 星期五 11:27:52 CST
Tracing process ID 44802
     total       read     pwrite      write      fsync       open      close   getdents      lseek  ftruncate filename
  0.190553   0.000000   0.000903   0.000000   0.189650   0.000000   0.000000   0.000000   0.000000   0.000000 /data/ibdata1
  0.107560   0.000000   0.000297   0.000000   0.107263   0.000000   0.000000   0.000000   0.000000   0.000000 /data/ib_logfile0
  0.027070   0.000000   0.000000   0.027070   0.000000   0.000000   0.000000   0.000000   0.000000   0.000000 /var/log/mysqld/mysql5-access.log
  0.015691   0.000000   0.000000   0.000000   0.000000   0.000689   0.000480   0.014522   0.000000   0.000000 /data/189mini/
  0.000362   0.000058   0.000000   0.000159   0.000000   0.000000   0.000000   0.000000   0.000081   0.000064 /tmp/ib4I3wV6
  0.000192   0.000000   0.000000   0.000192   0.000000   0.000000   0.000000   0.000000   0.000000   0.000000 /data/mysql5.000097
  0.000030   0.000000   0.000000   0.000000   0.000000   0.000000   0.000000   0.000000   0.000030   0.000000 /tmp/ibiybk6Q

/usr/bin/pt-kill
用于 kill 某些指定的 SQL 查询

/usr/bin/pt-mext
用于记录固定时间间隔内 status 返回值的变化 (下面例子将会计算 10 秒内的变化)

pt-mext -r -- mysqladmin -p'password' --socket=/var/run/mysqld/mysql5.socket  ext -i10 -c2  | awk '{ if ($3 !~0) print $0}'
Com_select                             19446417         198
Com_show_status                              11           1
Created_tmp_tables                      1015031           1
Handler_commit                         20829923         198
Handler_read_first                     21935275         198
Handler_read_key                       83253660         198
Handler_read_rnd_next               25490412028      257632
Handler_write                          63925147         294
Innodb_buffer_pool_read_requests    25739644905      259716
Innodb_rows_read                    25421266296      257116
Qcache_not_cached                      18701233         198
Queries                                28860341         199
Questions                              22720514         199
Selec

mysqltoolkit用法[备忘](四)