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

t_scan 19915354 199 Table_locks_immediate 21238032 19

/usr/bin/pt-mysql-summary
给当前数据库进行一些数据统计，不一一对统计进行讨论
注: 执行该命令时，需要调用 mysqldump, 需具备 mysqldump 命令执行路径

pt-mysql-summary --socket=/var/run/mysqld/mysql5.socket  --password='password'

  Database            Tables Views SPs Trigs Funcs   FKs Partn
  189mini                266    10
  crazy                   14
  login                    1
  monitor                  1
  mysql                   24
  #mysql50#lost+found
  openne                  18
  test
  xszl                    29                          60

  Database            InnoDB MyISAM CSV
  189mini                266     10
  crazy                   14
  login                    1
  monitor                  1
  mysql                          22   2
  #mysql50#lost+found
  openne                  18
  test
  xszl                    29

  Database            BTREE
  189mini               280
  crazy                  14
  login                   1
  monitor                 1
  mysql                  31
  #mysql50#lost+found
  openne                 25
  test
  xszl                  149

/usr/bin/pt-online-schema-change
允许在不锁定表条件下修改表

/usr/bin/pt-query-digest
从日志中进行 SQL 分析

/usr/bin/pt-show-grants
必须要以管理员身份登录，能够把数据库之前授权信息进行打印

pt-show-grants -p'mini189!QAZ'  --socket=/var/run/mysqld/mysql5.socket
-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 5.5.34-debug-log at 2013-12-20 14:43:22
-- Grants for 'crazy'@'10.1.1.%'
GRANT USAGE ON *.* TO 'crazy'@'10.1.1.%' IDENTIFIED BY PASSWORD '*4C1B9FACE717B2947CB8D52B32C3CFE8DA8DD8CB';
GRANT ALL PRIVILEGES ON `crazy`.* TO 'crazy'@'10.1.1.%';
-- Grants for 'mini145'@'%'
GRANT ALL PRIVILEGES ON *.* TO 'mini145'@'%' IDENTIFIED BY PASSWORD '*AA2442B2AE9D3C29F895E57F366092819A3F6738';
-- Grants for 'mini145'@'10.1.1.%'
GRANT ALL PRIVILEGES ON *.* TO 'mini145'@'10.1.1.%' IDENTIFIED BY PASSWORD '*AA2442B2AE9D3C29F895E57F366092819A3F6738';
-- Grants for 'minibackup'@'10.1.1.%'
GRANT REPLICATION SLAVE, SUPER ON *.* TO 'minibackup'@'10.1.1.%' IDENTIFIED BY PASSWORD '*D5A75D5F1208A6763F64193744F94E57373C128E';

/usr/bin/pt-slave-delay
令从服务器延时复制

/usr/bin/pt-slave-find
校验 mysql 从服务器信息

/usr/bin/pt-slave-restart
监控并重启从服务器

/usr/bin/pt-summary
统计计算机信息，如内存，CPU，网卡，分区，文件节点，IP地址，网线连接状态，端口连接情况，网络连接状态

/usr/bin/pt-summary
# Percona Toolkit System Summary Report ######################
        Date | 2013-12-20 07:03:47 UTC (local TZ: CST +0800)
    Hostname | mini189a
      Uptime | 42 days, 15:11,  2 users,  load average: 0.49, 0.47, 0.39
      System | HP; ProLiant DL380p Gen8; vNot Specified (Rack Mount Chassis)
 Service Tag | 6CU304WPZN
    Platform | Linux
     Release | Red Hat Enterprise Linux Server release 6.3 (Santiago)
      Kernel | 2.6.32-358.23.2.el6.centos.plus.x86_64
Architecture | CPU = 64-bit, OS = 64-bit
   Threading | NPTL 2.12
    Compiler | GNU CC version 4.4.7 20120313 (Red Hat 4.4.7-4).
     SELinux | Disabled
 Virtualized | No virtualization detected
# Processor ##################################################
  Processors | physical = 2, cores = 12, virtual = 24, hyperthreading = yes
      Speeds | 24x1994.866
      Models | 24xIntel(R) Xeon(R) CPU E5-2620 0 @ 2.00GHz
      Caches | 24x15360 KB

/usr/bin/pt-table-checksum
MySQL 主从复制校验

/usr/bin/pt-table-sync
进行两个主机中的 MySQL 数据库，或者表数据同步
注，目标服务器中必须存在与源服务器一样的数据库及表，否则，不存在的不表无法进行同步
pt-table-sync --execute h=192.168.200.163 --database terry h=192.168.200.171 --user=root --password=123

/usr/bin/pt-variable-advisor
给与对数据库参数定义的建议

pt-variable-advisor h=localhost -p'password' --socket=/var/run/mysqld/mysql5.socket
# WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# WARN innodb_log_file_size: The InnoDB log file size is set to its default value, which is not usable on production systems.

# NOTE log_warnings-2: Log_warnings must be set greater than 1 to log u

mysqltoolkit用法[备忘](五)