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 | master Uptime | 1 day, 23:42, 1 user, load average: 0.00, 0.00, 0.00 System | 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 f8 Platform | Linux Release | CentOS release 6.5 (Final) Kernel | 2.6.32-431.el6.x86_64 Architecture | CPU = 64-bit, OS = 64-bit Threading | NPTL 2.12 SELinux | Disabled Virtualized | VMWare # Processor ################################################## Processors | physical = 2, cores = 0, virtual = 2, hyperthreading = no Speeds | 2x2592.006 Models | 2xIntel(R) Core(TM) i7-6700HQ CPU @ 2.60GHz Caches | 2x6144 KB # Memory ##################################################### Total | 1.8G Free | 123.4M Used | physical = 1.7G, swap allocated = 3.9G, swap used = 5.8M, virtual = 1.7G Shared | 0.0 Buffers | 40.6M Caches | 905.3M Dirty | 412 kB UsedRSS | 882.9M Swappiness | 60 DirtyPolicy | 20, 10 DirtyStatus | 0, 0 ...
2.pt-mysql-summary #查看mysql的各个统计信息：
[root@master ~]# pt-mysql-summary -S /data/mysql/mysql.sock --user=root --host=localhost --port=3306 --password=123456 Warning: 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/mysqld Has symbols | No # Report On Port 3306 ######################################## User | Time | (CST) Hostname | master Version | 5.7.18-15-57 Percona XtraDB Cluster (GPL), Release rel15, Revision 7693d6e, WSREP version 29.20, wsrep_29.20 Built On | Linux x86_64 Started | (up 1+05:25:54) Databases | 5 Datadir | /data/mysql/data/ Processes | 1 connected, 1 running Replication | Is not a slave, has 0 slaves connected Pidfile | /data/mysql/logs/mysql.pid (exists) # Processlist ################################################ Command COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- Query 1 1 0 0 Sleep 9 0 900000 100000 User COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- root 1 1 0 0 system user 9 0 0 0 Host COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- 9 0 0 0 localhost 1 1 0 0 db COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- NULL 10 1 0 0 State COUNT(*) Working SUM(Time) MAX(Time) ------------------------------ -------- ------- --------- --------- starting 1 1 0 0 wsrep 9 0 0 0 ... # Configuration File ######################################### Config File | /etc/my.cnf [mysqld] user = mysql innodb_buffer_pool_size = 1024M datadir = /data/mysql/data port = 3306 server_id = 12 socket = /data/mysql/mysql.sock pid-file = /data/mysql/logs/mysql.pid log-error = /data/mysql/logs/error.log log_warnings = 2 slow_query_log_file = /data/mysql/logs/slow.log long_query_time = 0.1 sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES wsrep_provider = /usr/lib64/galera3/libgalera_smm.so wsrep_cluster_address = gcomm://192.168.3.12,192.168.3.13,192.168.3.198 binlog_format = ROW default_storage_engine = InnoDB wsrep_slave_threads = 8 wsrep_log_conflicts innodb_autoinc_lock_mode = 2 wsrep_node_address = 192.168.3.12 wsrep_cluster_name = my-pxc-cluster wsrep_node_name = pxc01 pxc_strict_mode = ENFORCING wsrep_sst_method = xtrabackup-v2 wsrep_sst_auth = "sstuser:sstuser" # Memory management library ################################## jemalloc enabled in MySQL config for process with ID 28347 Using 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=123456 10.10.76.96 Version 5.5.24-ucloudrel1-log Server ID 168447072 Uptime 646+02:02:20 (started 2015-09-01T14:09:56) Replication Is not a slave, has 0 slaves connected, is not read_only Filters Binary logging MIXED Slave status Slave mode STRICT Auto-increment increment 1, offset 1 InnoDB 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_table No 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, 1 copy_rows, 10, 0.25 create_triggers, 10, 1 drop_triggers, 10, 1 swap_tables, 10, 1 update_foreign_keys, 10, 1 Altering `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`.
