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

ゝ一纸荒年。 2022-03-18 21:28 268阅读 0赞

安装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 \#显示和系统相关的基本信息：

![复制代码][copycode.gif]

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

[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 ####################################################

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

# 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

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

[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`.

![复制代码][copycode.gif]

[copycode.gif]: /images/20220319/2ba2fa4ac5be48b5bc1bf2e015d01cdb.png