Percona Toolkit Study (iii) (Pt-duplicate-key-checker,)

Pt-duplicate-key-checker

The function is to find duplicate indexes and foreign keys from the MySQL table, the tool lists the duplicate indexes and foreign keys, and generates a statement to delete the duplicate index, which is very convenient   find out the extra index: pt-duplicate-key-checker --user= Root --password=db123 --socket=/var/tmp/mysql.sock --database=game Results: # ################### ###################################################### game.tb_xxx                                                # ######################################################################### idx_tb _core_url_rewrite_id_path is a left-prefix of unq_tb_core_url_rewrite_id_path_is_ system_store_id# key definitions:#   key  ' Idx_tb_core_url_rewrite_id_path '   (' Id_path '),#   unique key  ' unq_tb_core_url_rewrite_id_path_is_system_store_id '   (' Id_path ', ' Is_system', ' store_id '),# column types:#          ' Id_path '   varchar (255)  default null comment  ' Id path ' #           ' Is_system '  smallint (5)  unsigned default  ' 1 '  comment  ' defines  is rewrite system ' #          ' store_id '   smallint (5)  unsigned not null default  ' 0 '  comment  ' store id ' # to  remove this duplicate index, execute:ALTER TABLE  ' game '. ' Tb_xxx '  drop  INDEX  ' Idx_tb_core_url_rewrite_id_path '; # #################################################### ##################### summary of indexes                                                         # #########################################################################  Size Duplicate Indexes   768# Total Duplicate Indexes   1# total indexes            157

Pt-fingerprint

function amount, do not know what is the use.

Example: Pt-fingerprint--query "Select a, B, c from users where id = $ and create_time between ' 2015-01-01 ' and 2015-02-02 '" knot Result: Select a, B, c from users where id =? and create_time between? and? '

Pt-fk-error-logger

Usagept-fk-error-logger [OPTION ...] Source_dsnpt-fk-error-logger extracts and saves information about the most recent foreign key errors in a MySQL server. Print out Key error pt-fk-error-logger-uroot-p123456 h=127.0.0.1 to save the foreign key errors to the table, you need to create the tables, the structure of the proposed table is as follows: pt-fk-error-logger-uroot-p123456 h= 127.0.0.1--dest h=127.0.0.1,d=db,t=foreign_key_errorscreate TABLE foreign_key_errors (ts datetime not NULL, error text N OT NULL, PRIMARY KEY (TS))

Pt-fifo-split

Feature split file to divide large files into small files by condition

Example:

Pt-fifo-split--lines 1000000 hugefile.txt while [-e/tmp/pt-fifo-split]; Do cat/tmp/pt-fifo-split; Done Click on the example to import the data more quickly. Pt-fifo-split infile.txt--fifo/tmp/my-fifo--lines 1000000while [-e/tmp/my-fifo]; Do mysql-e "set foreign_key_checks=0; Set sql_log_bin=0; Set unique_checks=0; Load data local infile '/tmp/my-fifo ' into table load_test fields terminated by ' \ t ' lines terminated by ' \ n ' (col1, col2)   ;" Sleep 1;done

Pt-find

pt-find [option ...]  [database ...] pt-find  finds the MySQL table and executes the specified command, similar to the GNU find command 

Find InnoDB tables created before the day in this machine pt-find --user=root --password=123456 -h127.0.0.1 --ctime +1  --engine innodb  find  Myisam  table   and   turn into  innnodb:pt-find --user=root -- password=123456 -h127.0.0.1 --engine myisam --exec  "ALTER TABLE %D.%N  Engine=innodb " find tables created by a process that no longer  exists, following the name_sid_pid naming convention, and remove  them.pt-find -uroot -p123456 -h127.0.0.1 --connection-id  ' \d_\d+_ (\d+) $ '  - -server-id  ' \d_ (\d+) _\d+$ '  --exec-plus  "drop table %s" Find the empty table in junk and test and delete Pt-find  -uroot -p123456 -h127.0.0.1 --empty junk test --exec-plus  "drop  table %s "Find tables in the library that are larger than 5G pt-find --tablesize +5g find all the table and index sizes and print the sort pt-find --user=root -- password=123456 -h127.0.0.1 --printf  "%t\t%d.%n\n"  | sort -rn as described above, but this time, after inserting data into the database Pt-find --noquote  --exec  "Insert into sysdata.tblsize (db, tbl, size)  values ('%d ',  '%N ',  %T) "

This article is from the "tireless learning ..." Blog, be sure to keep this source http://jonyisme.blog.51cto.com/3690784/1758959

Percona Toolkit Study (iii) (Pt-duplicate-key-checker,)