您的位置:威尼斯官方网站 > 威尼斯官方网站登录 > Percona-Tookit工具包之pt-find

Percona-Tookit工具包之pt-find

发布时间:2019-12-30 17:15编辑:威尼斯官方网站登录浏览(137)

     

    Preface

     

        We used to use "find" command in linux or AIX when we need to get a certain file but cannot rember the precise name.We will execute "find /pathtobegin -name xxx" in our OS prompt.Is there any tool can find a specific table of MySQL database which similarly as "find" does?

     

    Introduce

     

    *    pt-find is a very useful tool to find out a sepcific table with a condition you've specified.It even can execute a sql operation when finding it simutaneously.
    *

     

    Procedure

     

    Usage

    1 pt-find [OPTIONS] [DATABASES]
    

     

    Common parameters

     1 Options:
     2 --day-start //Specify the meassure mothed of time when using "--cmin,--mmin,--ctime,--mtime,etc".
     3 --or //Change the combination test behavior as "or" instead of default "and".
     4 
     5 Actions:
     6 --exec //Specify the executing sql statement with each item found.
     7 --exec-plus //Specify the executing sql statement with all items at once.
     8 --print //Print the target database and table name.
     9 --printf //Print with a certain format.
    10 
    11 Tests:
    12 --autoinc //Speicfy a value of auto_increment to test whether has auto_inrcrement column.
    13 --avgrowlen //Specify the average 
    14 --cmin //Specify the target table created n minutes ago.
    15 --ctime //Specify the target table created n days ago.
    16 --mmin //Specify the target table modified n minutes ago.
    17 --mtime //Specify the target table modified n days ago.
    18 --kmim //Specify the target table checked n minutes ago.
    19 --ktime //Specify the target table checked n days ago.
    20 --rowformat //Specify the row format of tables to match pattern.
    21 --rows //Specify the rows the table contains.
    22 --tablesize //Specify the size the table is.
    23 --empty //Specify the talbe which has no rows.
    24 --engines //Specify the engine of tabls.
    

     

    Example

     

    Find out all tables in all databases.

      1 [root@zlm2 07:54:01 /data/mysql/mysql3308/data]
      2 #pt-find -h192.168.1.101 -P3308 -uzlm -pzlmzlm
      3 `mysql`.`columns_priv`
      4 `mysql`.`db`
      5 `mysql`.`engine_cost`
      6 `mysql`.`event`
      7 `mysql`.`func`
      8 `mysql`.`general_log`
      9 `mysql`.`gtid_executed`
     10 `mysql`.`help_category`
     11 `mysql`.`help_keyword`
     12 `mysql`.`help_relation`
     13 `mysql`.`help_topic`
     14 `mysql`.`innodb_index_stats`
     15 `mysql`.`innodb_table_stats`
     16 `mysql`.`ndb_binlog_index`
     17 `mysql`.`plugin`
     18 `mysql`.`proc`
     19 `mysql`.`procs_priv`
     20 `mysql`.`proxies_priv`
     21 `mysql`.`server_cost`
     22 `mysql`.`servers`
     23 `mysql`.`slave_master_info`
     24 `mysql`.`slave_relay_log_info`
     25 `mysql`.`slave_worker_info`
     26 `mysql`.`slow_log`
     27 `mysql`.`tables_priv`
     28 `mysql`.`time_zone`
     29 `mysql`.`time_zone_leap_second`
     30 `mysql`.`time_zone_name`
     31 `mysql`.`time_zone_transition`
     32 `mysql`.`time_zone_transition_type`
     33 `mysql`.`user`
     34 `performance_schema`.`accounts`
     35 `performance_schema`.`cond_instances`
     36 `performance_schema`.`events_stages_current`
     37 `performance_schema`.`events_stages_history`
     38 `performance_schema`.`events_stages_history_long`
     39 `performance_schema`.`events_stages_summary_by_account_by_event_name`
     40 `performance_schema`.`events_stages_summary_by_host_by_event_name`
     41 `performance_schema`.`events_stages_summary_by_thread_by_event_name`
     42 `performance_schema`.`events_stages_summary_by_user_by_event_name`
     43 `performance_schema`.`events_stages_summary_global_by_event_name`
     44 `performance_schema`.`events_statements_current`
     45 `performance_schema`.`events_statements_history`
     46 `performance_schema`.`events_statements_history_long`
     47 `performance_schema`.`events_statements_summary_by_account_by_event_name`
     48 `performance_schema`.`events_statements_summary_by_digest`
     49 `performance_schema`.`events_statements_summary_by_host_by_event_name`
     50 `performance_schema`.`events_statements_summary_by_program`
     51 `performance_schema`.`events_statements_summary_by_thread_by_event_name`
     52 `performance_schema`.`events_statements_summary_by_user_by_event_name`
     53 `performance_schema`.`events_statements_summary_global_by_event_name`
     54 `performance_schema`.`events_transactions_current`
     55 `performance_schema`.`events_transactions_history`
     56 `performance_schema`.`events_transactions_history_long`
     57 `performance_schema`.`events_transactions_summary_by_account_by_event_name`
     58 `performance_schema`.`events_transactions_summary_by_host_by_event_name`
     59 `performance_schema`.`events_transactions_summary_by_thread_by_event_name`
     60 `performance_schema`.`events_transactions_summary_by_user_by_event_name`
     61 `performance_schema`.`events_transactions_summary_global_by_event_name`
     62 `performance_schema`.`events_waits_current`
     63 `performance_schema`.`events_waits_history`
     64 `performance_schema`.`events_waits_history_long`
     65 `performance_schema`.`events_waits_summary_by_account_by_event_name`
     66 `performance_schema`.`events_waits_summary_by_host_by_event_name`
     67 `performance_schema`.`events_waits_summary_by_instance`
     68 `performance_schema`.`events_waits_summary_by_thread_by_event_name`
     69 `performance_schema`.`events_waits_summary_by_user_by_event_name`
     70 `performance_schema`.`events_waits_summary_global_by_event_name`
     71 `performance_schema`.`file_instances`
     72 `performance_schema`.`file_summary_by_event_name`
     73 `performance_schema`.`file_summary_by_instance`
     74 `performance_schema`.`global_status`
     75 `performance_schema`.`global_variables`
     76 `performance_schema`.`host_cache`
     77 `performance_schema`.`hosts`
     78 `performance_schema`.`memory_summary_by_account_by_event_name`
     79 `performance_schema`.`memory_summary_by_host_by_event_name`
     80 `performance_schema`.`memory_summary_by_thread_by_event_name`
     81 `performance_schema`.`memory_summary_by_user_by_event_name`
     82 `performance_schema`.`memory_summary_global_by_event_name`
     83 `performance_schema`.`metadata_locks`
     84 `performance_schema`.`mutex_instances`
     85 `performance_schema`.`objects_summary_global_by_type`
     86 `performance_schema`.`performance_timers`
     87 `performance_schema`.`prepared_statements_instances`
     88 `performance_schema`.`pxc_cluster_view`
     89 `performance_schema`.`replication_applier_configuration`
     90 `performance_schema`.`replication_applier_status`
     91 `performance_schema`.`replication_applier_status_by_coordinator`
     92 `performance_schema`.`replication_applier_status_by_worker`
     93 `performance_schema`.`replication_connection_configuration`
     94 `performance_schema`.`replication_connection_status`
     95 `performance_schema`.`replication_group_member_stats`
     96 `performance_schema`.`replication_group_members`
     97 `performance_schema`.`rwlock_instances`
     98 `performance_schema`.`session_account_connect_attrs`
     99 `performance_schema`.`session_connect_attrs`
    100 `performance_schema`.`session_status`
    101 `performance_schema`.`session_variables`
    102 `performance_schema`.`setup_actors`
    103 `performance_schema`.`setup_consumers`
    104 `performance_schema`.`setup_instruments`
    105 `performance_schema`.`setup_objects`
    106 `performance_schema`.`setup_timers`
    107 `performance_schema`.`socket_instances`
    108 `performance_schema`.`socket_summary_by_event_name`
    109 `performance_schema`.`socket_summary_by_instance`
    110 `performance_schema`.`status_by_account`
    111 `performance_schema`.`status_by_host`
    112 `performance_schema`.`status_by_thread`
    113 `performance_schema`.`status_by_user`
    114 `performance_schema`.`table_handles`
    115 `performance_schema`.`table_io_waits_summary_by_index_usage`
    116 `performance_schema`.`table_io_waits_summary_by_table`
    117 `performance_schema`.`table_lock_waits_summary_by_table`
    118 `performance_schema`.`threads`
    119 `performance_schema`.`user_variables_by_thread`
    120 `performance_schema`.`users`
    121 `performance_schema`.`variables_by_thread`
    122 `sys`.`host_summary`
    123 `sys`.`host_summary_by_file_io`
    124 `sys`.`host_summary_by_file_io_type`
    125 `sys`.`host_summary_by_stages`
    126 `sys`.`host_summary_by_statement_latency`
    127 `sys`.`host_summary_by_statement_type`
    128 `sys`.`innodb_buffer_stats_by_schema`
    129 `sys`.`innodb_buffer_stats_by_table`
    130 `sys`.`innodb_lock_waits`
    131 `sys`.`io_by_thread_by_latency`
    132 `sys`.`io_global_by_file_by_bytes`
    133 `sys`.`io_global_by_file_by_latency`
    134 `sys`.`io_global_by_wait_by_bytes`
    135 `sys`.`io_global_by_wait_by_latency`
    136 `sys`.`latest_file_io`
    137 `sys`.`memory_by_host_by_current_bytes`
    138 `sys`.`memory_by_thread_by_current_bytes`
    139 `sys`.`memory_by_user_by_current_bytes`
    140 `sys`.`memory_global_by_current_bytes`
    141 `sys`.`memory_global_total`
    142 `sys`.`metrics`
    143 `sys`.`processlist`
    144 `sys`.`ps_check_lost_instrumentation`
    145 `sys`.`schema_auto_increment_columns`
    146 `sys`.`schema_index_statistics`
    147 `sys`.`schema_object_overview`
    148 `sys`.`schema_redundant_indexes`
    149 `sys`.`schema_table_lock_waits`
    150 `sys`.`schema_table_statistics`
    151 `sys`.`schema_table_statistics_with_buffer`
    152 `sys`.`schema_tables_with_full_table_scans`
    153 `sys`.`schema_unused_indexes`
    154 `sys`.`session`
    155 `sys`.`session_ssl_status`
    156 `sys`.`statement_analysis`
    157 `sys`.`statements_with_errors_or_warnings`
    158 `sys`.`statements_with_full_table_scans`
    159 `sys`.`statements_with_runtimes_in_95th_percentile`
    160 `sys`.`statements_with_sorting`
    161 `sys`.`statements_with_temp_tables`
    162 `sys`.`sys_config`
    163 `sys`.`user_summary`
    164 `sys`.`user_summary_by_file_io`
    165 `sys`.`user_summary_by_file_io_type`
    166 `sys`.`user_summary_by_stages`
    167 `sys`.`user_summary_by_statement_latency`
    168 `sys`.`user_summary_by_statement_type`
    169 `sys`.`version`
    170 `sys`.`wait_classes_global_by_avg_latency`
    171 `sys`.`wait_classes_global_by_latency`
    172 `sys`.`waits_by_host_by_latency`
    173 `sys`.`waits_by_user_by_latency`
    174 `sys`.`waits_global_by_latency`
    175 `sys`.`x$host_summary`
    176 `sys`.`x$host_summary_by_file_io`
    177 `sys`.`x$host_summary_by_file_io_type`
    178 `sys`.`x$host_summary_by_stages`
    179 `sys`.`x$host_summary_by_statement_latency`
    180 `sys`.`x$host_summary_by_statement_type`
    181 `sys`.`x$innodb_buffer_stats_by_schema`
    182 `sys`.`x$innodb_buffer_stats_by_table`
    183 `sys`.`x$innodb_lock_waits`
    184 `sys`.`x$io_by_thread_by_latency`
    185 `sys`.`x$io_global_by_file_by_bytes`
    186 `sys`.`x$io_global_by_file_by_latency`
    187 `sys`.`x$io_global_by_wait_by_bytes`
    188 `sys`.`x$io_global_by_wait_by_latency`
    189 `sys`.`x$latest_file_io`
    190 `sys`.`x$memory_by_host_by_current_bytes`
    191 `sys`.`x$memory_by_thread_by_current_bytes`
    192 `sys`.`x$memory_by_user_by_current_bytes`
    193 `sys`.`x$memory_global_by_current_bytes`
    194 `sys`.`x$memory_global_total`
    195 `sys`.`x$processlist`
    196 `sys`.`x$ps_digest_95th_percentile_by_avg_us`
    197 `sys`.`x$ps_digest_avg_latency_distribution`
    198 `sys`.`x$ps_schema_table_statistics_io`
    199 `sys`.`x$schema_flattened_keys`
    200 `sys`.`x$schema_index_statistics`
    201 `sys`.`x$schema_table_lock_waits`
    202 `sys`.`x$schema_table_statistics`
    203 `sys`.`x$schema_table_statistics_with_buffer`
    204 `sys`.`x$schema_tables_with_full_table_scans`
    205 `sys`.`x$session`
    206 `sys`.`x$statement_analysis`
    207 `sys`.`x$statements_with_errors_or_warnings`
    208 `sys`.`x$statements_with_full_table_scans`
    209 `sys`.`x$statements_with_runtimes_in_95th_percentile`
    210 `sys`.`x$statements_with_sorting`
    211 `sys`.`x$statements_with_temp_tables`
    212 `sys`.`x$user_summary`
    213 `sys`.`x$user_summary_by_file_io`
    214 `sys`.`x$user_summary_by_file_io_type`
    215 `sys`.`x$user_summary_by_stages`
    216 `sys`.`x$user_summary_by_statement_latency`
    217 `sys`.`x$user_summary_by_statement_type`
    218 `sys`.`x$wait_classes_global_by_avg_latency`
    219 `sys`.`x$wait_classes_global_by_latency`
    220 `sys`.`x$waits_by_host_by_latency`
    221 `sys`.`x$waits_by_user_by_latency`
    222 `sys`.`x$waits_global_by_latency`
    223 `zlm`.`t1`
    224 `zlm`.`t2`
    225 
    226 //It shows all the tables in all databases one line each table.
    

     

    Find out all the MyISAM tables.

     1 [root@zlm2 09:28:33 /data/mysql/mysql3308/data]
     2 #pt-find -h192.168.1.101 -P3308 -uzlm -pzlmzlm --engine=MyISAM
     3 `mysql`.`columns_priv`
     4 `mysql`.`db`
     5 `mysql`.`event`
     6 `mysql`.`func`
     7 `mysql`.`ndb_binlog_index`
     8 `mysql`.`proc`
     9 `mysql`.`procs_priv`
    10 `mysql`.`proxies_priv`
    11 `mysql`.`tables_priv`
    12 `mysql`.`user`
    

     

    *Find out all the tables in database "zlm" which does not have auto_increment column.***

    1 [root@zlm2 09:32:55 /data/mysql/mysql3308/data]
    2 #pt-find -h192.168.1.101 -P3308 -uzlm -pzlmzlm --dblike=zlm --autoinc=0
    3 `zlm`.`t1`
    4 `zlm`.`t2`
    

     

    *Find out all the** ta**bles which is empty.***

     1 [root@zlm2 09:33:14 /data/mysql/mysql3308/data]
     2 #pt-find -h192.168.1.101 -P3308 -uzlm -pzlmzlm --empty
     3 `mysql`.`columns_priv`
     4 `mysql`.`event`
     5 `mysql`.`func`
     6 `mysql`.`ndb_binlog_index`
     7 `mysql`.`plugin`
     8 `mysql`.`procs_priv`
     9 `mysql`.`servers`
    10 `mysql`.`slave_master_info`
    11 `mysql`.`slave_relay_log_info`
    12 `mysql`.`slave_worker_info`
    13 `mysql`.`time_zone`
    14 `mysql`.`time_zone_leap_second`
    15 `mysql`.`time_zone_name`
    16 `mysql`.`time_zone_transition`
    17 `mysql`.`time_zone_transition_type`
    18 `performance_schema`.`metadata_locks`
    19 `performance_schema`.`prepared_statements_instances`
    20 `performance_schema`.`replication_group_member_stats`
    21 `performance_schema`.`replication_group_members`
    

     

    Find out all the tables sorted descendingly by size.

      1 [root@zlm2 09:38:22 /data/mysql/mysql3308/data]
      2 #pt-find -h192.168.1.101 -P3308 -uzlm -pzlmzlm --printf "%Tt%D.%Nn" | sort -rn
      3 1671168    `mysql`.`help_topic`
      4 304624    `mysql`.`proc`
      5 180224    `mysql`.`help_keyword`
      6 81920    `mysql`.`help_relation`
      7 32768    `mysql`.`help_category`
      8 16384    `zlm`.`t2`
      9 16384    `zlm`.`t1`
     10 16384    `sys`.`sys_config`
     11 16384    `mysql`.`time_zone_transition_type`
     12 16384    `mysql`.`time_zone_transition`
     13 16384    `mysql`.`time_zone_name`
     14 16384    `mysql`.`time_zone_leap_second`
     15 16384    `mysql`.`time_zone`
     16 16384    `mysql`.`slave_worker_info`
     17 16384    `mysql`.`slave_relay_log_info`
     18 16384    `mysql`.`slave_master_info`
     19 16384    `mysql`.`servers`
     20 16384    `mysql`.`server_cost`
     21 16384    `mysql`.`plugin`
     22 16384    `mysql`.`innodb_table_stats`
     23 16384    `mysql`.`innodb_index_stats`
     24 16384    `mysql`.`gtid_executed`
     25 16384    `mysql`.`engine_cost`
     26 11110    `mysql`.`tables_priv`
     27 10053    `mysql`.`proxies_priv`
     28 6096    `mysql`.`db`
     29 5012    `mysql`.`user`
     30 4096    `mysql`.`procs_priv`
     31 4096    `mysql`.`columns_priv`
     32 2048    `mysql`.`event`
     33 1024    `mysql`.`ndb_binlog_index`
     34 1024    `mysql`.`func`
     35 0    `sys`.`x$waits_global_by_latency`
     36 0    `sys`.`x$waits_by_user_by_latency`
     37 0    `sys`.`x$waits_by_host_by_latency`
     38 0    `sys`.`x$wait_classes_global_by_latency`
     39 0    `sys`.`x$wait_classes_global_by_avg_latency`
     40 0    `sys`.`x$user_summary_by_statement_type`
     41 0    `sys`.`x$user_summary_by_statement_latency`
     42 0    `sys`.`x$user_summary_by_stages`
     43 0    `sys`.`x$user_summary_by_file_io_type`
     44 0    `sys`.`x$user_summary_by_file_io`
     45 0    `sys`.`x$user_summary`
     46 0    `sys`.`x$statements_with_temp_tables`
     47 0    `sys`.`x$statements_with_sorting`
     48 0    `sys`.`x$statements_with_runtimes_in_95th_percentile`
     49 0    `sys`.`x$statements_with_full_table_scans`
     50 0    `sys`.`x$statements_with_errors_or_warnings`
     51 0    `sys`.`x$statement_analysis`
     52 0    `sys`.`x$session`
     53 0    `sys`.`x$schema_tables_with_full_table_scans`
     54 0    `sys`.`x$schema_table_statistics_with_buffer`
     55 0    `sys`.`x$schema_table_statistics`
     56 0    `sys`.`x$schema_table_lock_waits`
     57 0    `sys`.`x$schema_index_statistics`
     58 0    `sys`.`x$schema_flattened_keys`
     59 0    `sys`.`x$ps_schema_table_statistics_io`
     60 0    `sys`.`x$ps_digest_avg_latency_distribution`
     61 0    `sys`.`x$ps_digest_95th_percentile_by_avg_us`
     62 0    `sys`.`x$processlist`
     63 0    `sys`.`x$memory_global_total`
     64 0    `sys`.`x$memory_global_by_current_bytes`
     65 0    `sys`.`x$memory_by_user_by_current_bytes`
     66 0    `sys`.`x$memory_by_thread_by_current_bytes`
     67 0    `sys`.`x$memory_by_host_by_current_bytes`
     68 0    `sys`.`x$latest_file_io`
     69 0    `sys`.`x$io_global_by_wait_by_latency`
     70 0    `sys`.`x$io_global_by_wait_by_bytes`
     71 0    `sys`.`x$io_global_by_file_by_latency`
     72 0    `sys`.`x$io_global_by_file_by_bytes`
     73 0    `sys`.`x$io_by_thread_by_latency`
     74 0    `sys`.`x$innodb_lock_waits`
     75 0    `sys`.`x$innodb_buffer_stats_by_table`
     76 0    `sys`.`x$innodb_buffer_stats_by_schema`
     77 0    `sys`.`x$host_summary_by_statement_type`
     78 0    `sys`.`x$host_summary_by_statement_latency`
     79 0    `sys`.`x$host_summary_by_stages`
     80 0    `sys`.`x$host_summary_by_file_io_type`
     81 0    `sys`.`x$host_summary_by_file_io`
     82 0    `sys`.`x$host_summary`
     83 0    `sys`.`waits_global_by_latency`
     84 0    `sys`.`waits_by_user_by_latency`
     85 0    `sys`.`waits_by_host_by_latency`
     86 0    `sys`.`wait_classes_global_by_latency`
     87 0    `sys`.`wait_classes_global_by_avg_latency`
     88 0    `sys`.`version`
     89 0    `sys`.`user_summary_by_statement_type`
     90 0    `sys`.`user_summary_by_statement_latency`
     91 0    `sys`.`user_summary_by_stages`
     92 0    `sys`.`user_summary_by_file_io_type`
     93 0    `sys`.`user_summary_by_file_io`
     94 0    `sys`.`user_summary`
     95 0    `sys`.`statements_with_temp_tables`
     96 0    `sys`.`statements_with_sorting`
     97 0    `sys`.`statements_with_runtimes_in_95th_percentile`
     98 0    `sys`.`statements_with_full_table_scans`
     99 0    `sys`.`statements_with_errors_or_warnings`
    100 0    `sys`.`statement_analysis`
    101 0    `sys`.`session_ssl_status`
    102 0    `sys`.`session`
    103 0    `sys`.`schema_unused_indexes`
    104 0    `sys`.`schema_tables_with_full_table_scans`
    105 0    `sys`.`schema_table_statistics_with_buffer`
    106 0    `sys`.`schema_table_statistics`
    107 0    `sys`.`schema_table_lock_waits`
    108 0    `sys`.`schema_redundant_indexes`
    109 0    `sys`.`schema_object_overview`
    110 0    `sys`.`schema_index_statistics`
    111 0    `sys`.`schema_auto_increment_columns`
    112 0    `sys`.`ps_check_lost_instrumentation`
    113 0    `sys`.`processlist`
    114 0    `sys`.`metrics`
    115 0    `sys`.`memory_global_total`
    116 0    `sys`.`memory_global_by_current_bytes`
    117 0    `sys`.`memory_by_user_by_current_bytes`
    118 0    `sys`.`memory_by_thread_by_current_bytes`
    119 0    `sys`.`memory_by_host_by_current_bytes`
    120 0    `sys`.`latest_file_io`
    121 0    `sys`.`io_global_by_wait_by_latency`
    122 0    `sys`.`io_global_by_wait_by_bytes`
    123 0    `sys`.`io_global_by_file_by_latency`
    124 0    `sys`.`io_global_by_file_by_bytes`
    125 0    `sys`.`io_by_thread_by_latency`
    126 0    `sys`.`innodb_lock_waits`
    127 0    `sys`.`innodb_buffer_stats_by_table`
    128 0    `sys`.`innodb_buffer_stats_by_schema`
    129 0    `sys`.`host_summary_by_statement_type`
    130 0    `sys`.`host_summary_by_statement_latency`
    131 0    `sys`.`host_summary_by_stages`
    132 0    `sys`.`host_summary_by_file_io_type`
    133 0    `sys`.`host_summary_by_file_io`
    134 0    `sys`.`host_summary`
    135 0    `performance_schema`.`variables_by_thread`
    136 0    `performance_schema`.`user_variables_by_thread`
    137 0    `performance_schema`.`users`
    138 0    `performance_schema`.`threads`
    139 0    `performance_schema`.`table_lock_waits_summary_by_table`
    140 0    `performance_schema`.`table_io_waits_summary_by_table`
    141 0    `performance_schema`.`table_io_waits_summary_by_index_usage`
    142 0    `performance_schema`.`table_handles`
    143 0    `performance_schema`.`status_by_user`
    144 0    `performance_schema`.`status_by_thread`
    145 0    `performance_schema`.`status_by_host`
    146 0    `performance_schema`.`status_by_account`
    147 0    `performance_schema`.`socket_summary_by_instance`
    148 0    `performance_schema`.`socket_summary_by_event_name`
    149 0    `performance_schema`.`socket_instances`
    150 0    `performance_schema`.`setup_timers`
    151 0    `performance_schema`.`setup_objects`
    152 0    `performance_schema`.`setup_instruments`
    153 0    `performance_schema`.`setup_consumers`
    154 0    `performance_schema`.`setup_actors`
    155 0    `performance_schema`.`session_variables`
    156 0    `performance_schema`.`session_status`
    157 0    `performance_schema`.`session_connect_attrs`
    158 0    `performance_schema`.`session_account_connect_attrs`
    159 0    `performance_schema`.`rwlock_instances`
    160 0    `performance_schema`.`replication_group_member_stats`
    161 0    `performance_schema`.`replication_group_members`
    162 0    `performance_schema`.`replication_connection_status`
    163 0    `performance_schema`.`replication_connection_configuration`
    164 0    `performance_schema`.`replication_applier_status_by_worker`
    165 0    `performance_schema`.`replication_applier_status_by_coordinator`
    166 0    `performance_schema`.`replication_applier_status`
    167 0    `performance_schema`.`replication_applier_configuration`
    168 0    `performance_schema`.`pxc_cluster_view`
    169 0    `performance_schema`.`prepared_statements_instances`
    170 0    `performance_schema`.`performance_timers`
    171 0    `performance_schema`.`objects_summary_global_by_type`
    172 0    `performance_schema`.`mutex_instances`
    173 0    `performance_schema`.`metadata_locks`
    174 0    `performance_schema`.`memory_summary_global_by_event_name`
    175 0    `performance_schema`.`memory_summary_by_user_by_event_name`
    176 0    `performance_schema`.`memory_summary_by_thread_by_event_name`
    177 0    `performance_schema`.`memory_summary_by_host_by_event_name`
    178 0    `performance_schema`.`memory_summary_by_account_by_event_name`
    179 0    `performance_schema`.`hosts`
    180 0    `performance_schema`.`host_cache`
    181 0    `performance_schema`.`global_variables`
    182 0    `performance_schema`.`global_status`
    183 0    `performance_schema`.`file_summary_by_instance`
    184 0    `performance_schema`.`file_summary_by_event_name`
    185 0    `performance_schema`.`file_instances`
    186 0    `performance_schema`.`events_waits_summary_global_by_event_name`
    187 0    `performance_schema`.`events_waits_summary_by_user_by_event_name`
    188 0    `performance_schema`.`events_waits_summary_by_thread_by_event_name`
    189 0    `performance_schema`.`events_waits_summary_by_instance`
    190 0    `performance_schema`.`events_waits_summary_by_host_by_event_name`
    191 0    `performance_schema`.`events_waits_summary_by_account_by_event_name`
    192 0    `performance_schema`.`events_waits_history_long`
    193 0    `performance_schema`.`events_waits_history`
    194 0    `performance_schema`.`events_waits_current`
    195 0    `performance_schema`.`events_transactions_summary_global_by_event_name`
    196 0    `performance_schema`.`events_transactions_summary_by_user_by_event_name`
    197 0    `performance_schema`.`events_transactions_summary_by_thread_by_event_name`
    198 0    `performance_schema`.`events_transactions_summary_by_host_by_event_name`
    199 0    `performance_schema`.`events_transactions_summary_by_account_by_event_name`
    200 0    `performance_schema`.`events_transactions_history_long`
    201 0    `performance_schema`.`events_transactions_history`
    202 0    `performance_schema`.`events_transactions_current`
    203 0    `performance_schema`.`events_statements_summary_global_by_event_name`
    204 0    `performance_schema`.`events_statements_summary_by_user_by_event_name`
    205 0    `performance_schema`.`events_statements_summary_by_thread_by_event_name`
    206 0    `performance_schema`.`events_statements_summary_by_program`
    207 0    `performance_schema`.`events_statements_summary_by_host_by_event_name`
    208 0    `performance_schema`.`events_statements_summary_by_digest`
    209 0    `performance_schema`.`events_statements_summary_by_account_by_event_name`
    210 0    `performance_schema`.`events_statements_history_long`
    211 0    `performance_schema`.`events_statements_history`
    212 0    `performance_schema`.`events_statements_current`
    213 0    `performance_schema`.`events_stages_summary_global_by_event_name`
    214 0    `performance_schema`.`events_stages_summary_by_user_by_event_name`
    215 0    `performance_schema`.`events_stages_summary_by_thread_by_event_name`
    216 0    `performance_schema`.`events_stages_summary_by_host_by_event_name`
    217 0    `performance_schema`.`events_stages_summary_by_account_by_event_name`
    218 0    `performance_schema`.`events_stages_history_long`
    219 0    `performance_schema`.`events_stages_history`
    220 0    `performance_schema`.`events_stages_current`
    221 0    `performance_schema`.`cond_instances`
    222 0    `performance_schema`.`accounts`
    223 0    `mysql`.`slow_log`
    224 0    `mysql`.`general_log`
    

     

    **Find out all the** ta**bles over 16K.**

     1 [root@zlm2 09:44:10 /data/mysql/mysql3308/data]
     2 #pt-find -h192.168.1.101 -P3308 -uzlm -pzlmzlm --tablesize +16
     3 `mysql`.`columns_priv`
     4 `mysql`.`db`
     5 `mysql`.`engine_cost`
     6 `mysql`.`event`
     7 `mysql`.`func`
     8 `mysql`.`gtid_executed`
     9 `mysql`.`help_category`
    10 `mysql`.`help_keyword`
    11 `mysql`.`help_relation`
    12 `mysql`.`help_topic`
    13 `mysql`.`innodb_index_stats`
    14 `mysql`.`innodb_table_stats`
    15 `mysql`.`ndb_binlog_index`
    16 `mysql`.`plugin`
    17 `mysql`.`proc`
    18 `mysql`.`procs_priv`
    19 `mysql`.`proxies_priv`
    20 `mysql`.`server_cost`
    21 `mysql`.`servers`
    22 `mysql`.`slave_master_info`
    23 `mysql`.`slave_relay_log_info`
    24 `mysql`.`slave_worker_info`
    25 `mysql`.`tables_priv`
    26 `mysql`.`time_zone`
    27 `mysql`.`time_zone_leap_second`
    28 `mysql`.`time_zone_name`
    29 `mysql`.`time_zone_transition`
    30 `mysql`.`time_zone_transition_type`
    31 `mysql`.`user`
    32 `sys`.`sys_config`
    33 `zlm`.`t1`
    34 `zlm`.`t2`
    

     

    **Find out table in "zlm" database which name is like "t3" then drop it.***


     1 (zlm@192.168.1.101 3308)[zlm]>create table t3 like t2;
     2 Query OK, 0 rows affected (0.02 sec)
     3 
     4 (zlm@192.168.1.101 3308)[zlm]>show create table t3G
     5 *************************** 1. row ***************************
     6        Table: t3
     7 Create Table: CREATE TABLE `t3` (
     8   `id` int(11) NOT NULL,
     9   `name` char(10) DEFAULT NULL,
    10   PRIMARY KEY (`id`)
    11 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    12 1 row in set (0.00 sec)
    13 
    14 (zlm@192.168.1.101 3308)[zlm]>show tables;
    15 +---------------+
    16 | Tables_in_zlm |
    17 +---------------+
    18 | t1            |
    19 | t2            |
    20 | t3            |
    21 +---------------+
    22 3 rows in set (0.00 sec)
    23 
    24 [root@zlm2 10:11:26 /data/mysql/mysql3308/data]
    25 #pt-find -h192.168.1.101 -P3308 -uzlm -pzlmzlm --print zlm --tbllike=t3 --exec-plus "drop table %s"
    26 `zlm`.`t3`
    27 
    28 (zlm@192.168.1.101 3308)[zlm]>show tables;
    29 +---------------+
    30 | Tables_in_zlm |
    31 +---------------+
    32 | t1            |
    33 | t2            |
    34 +---------------+
    35 2 rows in set (0.00 sec)
    36 
    37 //The table has been droped directly by the last command in pt-find.
    

     

    Summary

    • pt-find is rather useful when retrieving target table with flexible conditions you defined.
    • pt-find has a lot of options.We should be more careful when using ddl statement by "--exec" or "--exec-plus".
    • We can use it to collect the important information about a bunch of tables easily which can increse the working efficiency.

     

    本文由威尼斯官方网站发布于威尼斯官方网站登录,转载请注明出处:Percona-Tookit工具包之pt-find

    关键词: