mysql管理工具percona-toolkit-3简单使用介绍

安装percona-toolkit-3

# yum localinstall percona-toolkit-3.0.3-1.el6.x86_64.rpm perl-TermReadKey.x86_64 0:2.30-13.el6 -y

 

1.pt-summary #显示和系统相关的基本信息：

[root@master ~]# pt-summary # Percona Toolkit System Summary Report ######################Date | 2017-06-08 07:44:12 UTC (local TZ: CST +0800)Hostname | masterUptime | 1 day, 23:42, 1 user, load average: 0.00, 0.00, 0.00System | VMware, Inc.; VMware Virtual Platform; vNone (Other)Service Tag | VMware-56 4d c6 90 e2 74 18 85-89 15 70 77 f4 f3 33 f8Platform | LinuxRelease | CentOS release 6.5 (Final)Kernel | 2.6.32-431.el6.x86_64Architecture | CPU = 64-bit, OS = 64-bitThreading | NPTL 2.12SELinux | DisabledVirtualized | VMWare# Processor ##################################################Processors | physical = 2, cores = 0, virtual = 2, hyperthreading = noSpeeds | 2x2592.006Models | 2xIntel(R) Core(TM) i7-6700HQ CPU @ 2.60GHzCaches | 2x6144 KB# Memory #####################################################Total | 1.8GFree | 123.4MUsed | physical = 1.7G, swap allocated = 3.9G, swap used = 5.8M, virtual = 1.7GShared | 0.0Buffers | 40.6MCaches | 905.3MDirty | 412 kBUsedRSS | 882.9MSwappiness | 60DirtyPolicy | 20, 10DirtyStatus | 0, 0...

 

2.pt-mysql-summary #查看mysql的各个统计信息：

[root@master ~]# pt-mysql-summary -S /data/mysql/mysql.sock --user=root --host=localhost --port=3306 --password=123456Warning: Using a password on the command line interface can be insecure.# Percona Toolkit MySQL Summary Report #######################System time | 2017-06-08 07:47:23 UTC (local TZ: CST +0800)# Instances ##################################################Port Data Directory Nice OOM Socket===== ========================== ==== === ======3306 /data/mysql/data 0 0 /data/mysql/mysql.sock# MySQL Executable ###########################################Path to executable | /usr/sbin/mysqldHas symbols | No# Report On Port 3306 ########################################User | Time | (CST)Hostname | masterVersion | 5.7.18-15-57 Percona XtraDB Cluster (GPL), Release rel15, Revision 7693d6e, WSREP version 29.20, wsrep_29.20Built On | Linux x86_64Started | (up 1+05:25:54)Databases | 5Datadir | /data/mysql/data/Processes | 1 connected, 1 runningReplication | Is not a slave, has 0 slaves connectedPidfile | /data/mysql/logs/mysql.pid (exists)# Processlist ################################################Command COUNT(*) Working SUM(Time) MAX(Time)------------------------------ -------- ------- --------- ---------Query 1 1 0 0Sleep 9 0 900000 100000User COUNT(*) Working SUM(Time) MAX(Time)------------------------------ -------- ------- --------- ---------root 1 1 0 0system user 9 0 0 0Host COUNT(*) Working SUM(Time) MAX(Time)------------------------------ -------- ------- --------- ---------9 0 0 0localhost 1 1 0 0db COUNT(*) Working SUM(Time) MAX(Time)------------------------------ -------- ------- --------- ---------NULL 10 1 0 0State COUNT(*) Working SUM(Time) MAX(Time)------------------------------ -------- ------- --------- ---------starting 1 1 0 0wsrep 9 0 0 0...# Configuration File #########################################Config File | /etc/my.cnf[mysqld]user = mysqlinnodb_buffer_pool_size = 1024Mdatadir = /data/mysql/dataport = 3306server_id = 12socket = /data/mysql/mysql.sockpid-file = /data/mysql/logs/mysql.pidlog-error = /data/mysql/logs/error.loglog_warnings = 2slow_query_log_file = /data/mysql/logs/slow.loglong_query_time = 0.1sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESwsrep_provider = /usr/lib64/galera3/libgalera_smm.sowsrep_cluster_address = gcomm://192.168.3.12,192.168.3.13,192.168.3.198binlog_format = ROWdefault_storage_engine = InnoDBwsrep_slave_threads = 8wsrep_log_conflictsinnodb_autoinc_lock_mode = 2wsrep_node_address = 192.168.3.12wsrep_cluster_name = my-pxc-clusterwsrep_node_name = pxc01pxc_strict_mode = ENFORCINGwsrep_sst_method = xtrabackup-v2wsrep_sst_auth = "sstuser:sstuser"# Memory management library ##################################jemalloc enabled in MySQL config for process with ID 28347Using jemalloc from /usr/lib64/libjemalloc.so.1# The End ####################################################

 

3.pt-slave-find #查找和显示指定的Master 有多少个Slave：

# pt-slave-find --host=10.10.76.96 --port=3306 --user=root --password=12345610.10.76.96Version 5.5.24-ucloudrel1-logServer ID 168447072Uptime 646+02:02:20 (started 2015-09-01T14:09:56)Replication Is not a slave, has 0 slaves connected, is not read_onlyFilters Binary logging MIXEDSlave status Slave mode STRICTAuto-increment increment 1, offset 1InnoDB version 1.1.8

 

4. pt-query-digest 慢日志查询：

# pt-query-digest /data/mysql/master-log.000007

 

5.pt-online-schema-change
#在线DDL操作改变表结构并且不阻塞，正常情况下在线ALTER一个表增加一个字段、一个索引的话MySQL会锁表，正常的流程如下：

①按照原始表（original_table）的表结构和DDL语句，新建一个不可见的临时表（tmp_table）
②在原表上加write lock，阻塞所有更新操作（insert、delete、update等）
③执行insert into tmp_table select * from original_table
④rename original_table和tmp_table，最后drop original_table
⑤释放 write lock

但是在线上直接操作的话会导致大量的写入阻塞，临时表可能会导致磁盘空间占满等问题，因此要使用pt-online-schema-change更新表结果的过程如下：
创建一个和原表相同表结构的表，比如table_tmp
执行表结构修改，并从原表copy数据到修改后的表
在原表上创建触发器，将copy过程中产生的数据更新到新表
copy完成，将新表rename成当前使用的表，删除旧表

使用pt-online-schema-change在线更新表，注意是在线、在线、在线更新表结构不停止业务使用的在线更新表结构：

# pt-online-schema-change --user=root --host=localhost --port=3306 --password=123456 --execute --alter "ADD COLUMN Job VARCHAR(20)" D=testdatabase,t=testtable2

 

6.修改表结构并保留原表
pt-online-schema-change, test111库的tbl_app_table新增app_status字段默认为0

[root@test7_chat_api_im ~]# pt-online-schema-change --host=10.10.191.89 --user=root --password=123456 --port=3306 --no-drop-old-table --execute --alter "add column app_status char(1) default 0" D=test111,t=tbl_app_tableNo slaves found. See --recursion-method if host qas01 has slaves.Not checking slave lag because no slaves were found and --check-slave-lag was not specified.Operation, tries, wait:analyze_table, 10, 1copy_rows, 10, 0.25create_triggers, 10, 1drop_triggers, 10, 1swap_tables, 10, 1update_foreign_keys, 10, 1Altering `test111`.`tbl_app_table`...Creating new table...Created new table test111._tbl_app_table_new OK.Altering new table...Altered `test111`.`_tbl_app_table_new` OK.2017-06-08T17:31:51 Creating triggers...2017-06-08T17:31:51 Created triggers OK.2017-06-08T17:31:51 Copying approximately 847 rows...2017-06-08T17:31:51 Copied rows OK.2017-06-08T17:31:51 Analyzing new table...2017-06-08T17:31:51 Swapping tables...2017-06-08T17:31:51 Swapped original and new tables OK.Not dropping old table because --no-drop-old-table was specified.2017-06-08T17:31:51 Dropping triggers...2017-06-08T17:31:51 Dropped triggers OK.Successfully altered `test111`.`tbl_app_table`.