1MK-FIND(1) User Contributed Perl Documentation MK-FIND(1)
2
6 mk-find - Find MySQL tables and execute actions, like GNU find.
7
9 Usage: mk-find [OPTION...] [DATABASE...]
10 mk-find searches for MySQL tables and executes actions, like GNU find.
12 The default action is to print the database and table name.
13 Find all tables created more than a day ago, which use the MyISAM
15 engine, and print their names:
16 mk-find --ctime +1 --engine MyISAM
18 Find InnoDB tables that haven't been updated in a month, and convert
20 them to MyISAM storage engine (data warehousing, anyone?):
21 mk-find --mtime +30 --engine InnoDB --exec "ALTER TABLE %D.%N ENGINE=MyISAM"
23 Find tables created by a process that no longer exists, following the
25 name_sid_pid naming convention, and remove them.
26 mk-find --connection-id '\D_\d+_(\d+)$' --server-id '\D_(\d+)_\d+$' --exec-plus "DROP TABLE %s"
28 Find empty tables in the test and junk databases, and delete them:
30 mk-find --empty junk test --exec-plus "DROP TABLE %s"
32 Find tables more than five gigabytes in total size:
34 mk-find --tablesize +5G
36 Find all tables and print their total data and index size, and sort
38 largest tables first (sort is a different program, by the way).
39 mk-find --printf "%T\t%D.%N\n" | sort -rn
41 As above, but this time, insert the data back into the database for
43 posterity:
44 mk-find --noquote --exec "INSERT INTO sysdata.tblsize(db, tbl, size) VALUES('%D', '%N', %T)"
46
48 The following section is included to inform users about the potential
49 risks, whether known or unknown, of using this tool. The two main
50 categories of risks are those created by the nature of the tool (e.g.
51 read-only tools vs. read-write tools) and those created by bugs.
52 mk-find only reads and prints information by default, but "--exec" and
54 "--exec-plus" can execute user-defined SQL. You should be as careful
55 with it as you are with any command-line tool that can execute queries
56 against your database.
57 At the time of this release, we know of no bugs that could cause
59 serious harm to users.
60 The authoritative source for updated information is always the online
62 issue tracking system. Issues that affect this tool will be marked as
63 such. You can see a list of such issues at the following URL:
64 <http://www.maatkit.org/bugs/mk-find>.
65 See also "BUGS" for more information on filing bugs and getting help.
67
69 mk-find looks for MySQL tables that pass the tests you specify, and
70 executes the actions you specify. The default action is to print the
71 database and table name to STDOUT.
72 mk-find is simpler than GNU find. It doesn't allow you to specify
74 complicated expressions on the command line.
75 mk-find uses SHOW TABLES when possible, and SHOW TABLE STATUS when
77 needed.
78
80 There are three types of options: normal options, which determine some
81 behavior or setting; tests, which determine whether a table should be
82 included in the list of tables found; and actions, which do something
83 to the tables mk-find finds.
84 mk-find uses standard Getopt::Long option parsing, so you should use
86 double dashes in front of long option names, unlike GNU find.
87
89 This tool accepts additional command-line arguments. Refer to the
90 "SYNOPSIS" and usage information for details.
91 --ask-pass
93 Prompt for a password when connecting to MySQL.
94 --case-insensitive
96 Specifies that all regular expression searches are case-
97 insensitive.
98 --charset
100 short form: -A; type: string
101 Default character set. If the value is utf8, sets Perl's binmode
103 on STDOUT to utf8, passes the mysql_enable_utf8 option to
104 DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any
105 other value sets binmode on STDOUT without the utf8 layer, and runs
106 SET NAMES after connecting to MySQL.
107 --config
109 type: Array
110 Read this comma-separated list of config files; if specified, this
112 must be the first option on the command line.
113 --day-start
115 Measure times (for "--mmin", etc) from the beginning of today
116 rather than from the current time.
117 --defaults-file
119 short form: -F; type: string
120 Only read mysql options from the given file. You must give an
122 absolute pathname.
123 --help
125 Show help and exit.
126 --host
128 short form: -h; type: string
129 Connect to host.
131 --or
133 Combine tests with OR, not AND.
134 By default, tests are evaluated as though there were an AND between
136 them. This option switches it to OR.
137 Option parsing is not implemented by mk-find itself, so you cannot
139 specify complicated expressions with parentheses and mixtures of OR
140 and AND.
141 --password
143 short form: -p; type: string
144 Password to use when connecting.
146 --pid
148 type: string
149 Create the given PID file. The file contains the process ID of the
151 script. The PID file is removed when the script exits. Before
152 starting, the script checks if the PID file already exists. If it
153 does not, then the script creates and writes its own PID to it. If
154 it does, then the script checks the following: if the file contains
155 a PID and a process is running with that PID, then the script dies;
156 or, if there is no process running with that PID, then the script
157 overwrites the file with its own PID and starts; else, if the file
158 contains no PID, then the script dies.
159 --port
161 short form: -P; type: int
162 Port number to use for connection.
164 --[no]quote
166 default: yes
167 Quotes MySQL identifier names with MySQL's standard backtick
169 character.
170 Quoting happens after tests are run, and before actions are run.
172 --set-vars
174 type: string; default: wait_timeout=10000
175 Set these MySQL variables. Immediately after connecting to MySQL,
177 this string will be appended to SET and executed.
178 --socket
180 short form: -S; type: string
181 Socket file to use for connection.
183 --user
185 short form: -u; type: string
186 User for login if not current user.
188 --version
190 Show version and exit.
191 TESTS
193 Most tests check some criterion against a column of SHOW TABLE STATUS
194 output. Numeric arguments can be specified as +n for greater than n,
195 -n for less than n, and n for exactly n. All numeric options can take
196 an optional suffix multiplier of k, M or G (1_024, 1_048_576, and
197 1_073_741_824 respectively). All patterns are Perl regular expressions
198 (see 'man perlre') unless specified as SQL LIKE patterns.
199 Dates and times are all measured relative to the same instant, when mk-
201 find first asks the database server what time it is. All date and time
202 manipulation is done in SQL, so if you say to find tables modified 5
203 days ago, that translates to SELECT DATE_SUB(CURRENT_TIMESTAMP,
204 INTERVAL 5 DAY). If you specify "--day-start", if course it's relative
205 to CURRENT_DATE instead.
206 However, table sizes and other metrics are not consistent at an instant
208 in time. It can take some time for MySQL to process all the SHOW
209 queries, and mk-find can't do anything about that. These measurements
210 are as of the time they're taken.
211 If you need some test that's not in this list, file a bug report and
213 I'll enhance mk-find for you. It's really easy.
214 --autoinc
216 type: string; group: Tests
217 Table's next AUTO_INCREMENT is n. This tests the Auto_increment
219 column.
220 --avgrowlen
222 type: size; group: Tests
223 Table avg row len is n bytes. This tests the Avg_row_length
225 column. The specified size can be "NULL" to test where
226 Avg_row_length IS NULL.
227 --checksum
229 type: string; group: Tests
230 Table checksum is n. This tests the Checksum column.
232 --cmin
234 type: size; group: Tests
235 Table was created n minutes ago. This tests the Create_time
237 column.
238 --collation
240 type: string; group: Tests
241 Table collation matches pattern. This tests the Collation column.
243 --column-name
245 type: string; group: Tests
246 A column name in the table matches pattern.
248 --column-type
250 type: string; group: Tests
251 A column in the table matches this type (case-insensitive).
253 Examples of types are: varchar, char, int, smallint, bigint,
255 decimal, year, timestamp, text, enum.
256 --comment
258 type: string; group: Tests
259 Table comment matches pattern. This tests the Comment column.
261 --connection-id
263 type: string; group: Tests
264 Table name has nonexistent MySQL connection ID. This tests the
266 table name for a pattern. The argument to this test must be a Perl
267 regular expression that captures digits like this: (\d+). If the
268 table name matches the pattern, these captured digits are taken to
269 be the MySQL connection ID of some process. If the connection
270 doesn't exist according to SHOW FULL PROCESSLIST, the test returns
271 true. If the connection ID is greater than mk-find's own
272 connection ID, the test returns false for safety.
273 Why would you want to do this? If you use MySQL statement-based
275 replication, you probably know the trouble temporary tables can
276 cause. You might choose to work around this by creating real
277 tables with unique names, instead of temporary tables. One way to
278 do this is to append your connection ID to the end of the table,
279 thusly: scratch_table_12345. This assures the table name is unique
280 and lets you have a way to find which connection it was associated
281 with. And perhaps most importantly, if the connection no longer
282 exists, you can assume the connection died without cleaning up its
283 tables, and this table is a candidate for removal.
284 This is how I manage scratch tables, and that's why I included this
286 test in mk-find.
287 The argument I use to "--connection-id" is "\D_(\d+)$". That finds
289 tables with a series of numbers at the end, preceded by an
290 underscore and some non-number character (the latter criterion
291 prevents me from examining tables with a date at the end, which
292 people tend to do: baron_scratch_2007_05_07 for example). It's
293 better to keep the scratch tables separate of course.
294 If you do this, make sure the user mk-find runs as has the PROCESS
296 privilege! Otherwise it will only see connections from the same
297 user, and might think some tables are ready to remove when they're
298 still in use. For safety, mk-find checks this for you.
299 See also "--server-id".
301 --createopts
303 type: string; group: Tests
304 Table create option matches pattern. This tests the Create_options
306 column.
307 --ctime
309 type: size; group: Tests
310 Table was created n days ago. This tests the Create_time column.
312 --datafree
314 type: size; group: Tests
315 Table has n bytes of free space. This tests the Data_free column.
317 The specified size can be "NULL" to test where Data_free IS NULL.
318 --datasize
320 type: size; group: Tests
321 Table data uses n bytes of space. This tests the Data_length
323 column. The specified size can be "NULL" to test where Data_length
324 IS NULL.
325 --dblike
327 type: string; group: Tests
328 Database name matches SQL LIKE pattern.
330 --dbregex
332 type: string; group: Tests
333 Database name matches this pattern.
335 --empty
337 group: Tests
338 Table has no rows. This tests the Rows column.
340 --engine
342 type: string; group: Tests
343 Table storage engine matches this pattern. This tests the Engine
345 column, or in earlier versions of MySQL, the Type column.
346 --function
348 type: string; group: Tests
349 Function definition matches pattern.
351 --indexsize
353 type: size; group: Tests
354 Table indexes use n bytes of space. This tests the Index_length
356 column. The specified size can be "NULL" to test where
357 Index_length IS NULL.
358 --kmin
360 type: size; group: Tests
361 Table was checked n minutes ago. This tests the Check_time column.
363 --ktime
365 type: size; group: Tests
366 Table was checked n days ago. This tests the Check_time column.
368 --mmin
370 type: size; group: Tests
371 Table was last modified n minutes ago. This tests the Update_time
373 column.
374 --mtime
376 type: size; group: Tests
377 Table was last modified n days ago. This tests the Update_time
379 column.
380 --procedure
382 type: string; group: Tests
383 Procedure definition matches pattern.
385 --rowformat
387 type: string; group: Tests
388 Table row format matches pattern. This tests the Row_format
390 column.
391 --rows
393 type: size; group: Tests
394 Table has n rows. This tests the Rows column. The specified size
396 can be "NULL" to test where Rows IS NULL.
397 --server-id
399 type: string; group: Tests
400 Table name contains the server ID. If you create temporary tables
402 with the naming convention explained in "--connection-id", but also
403 add the server ID of the server on which the tables are created,
404 then you can use this pattern match to ensure tables are dropped
405 only on the server they're created on. This prevents a table from
406 being accidentally dropped on a slave while it's in use (provided
407 that your server IDs are all unique, which they should be for
408 replication to work).
409 For example, on the master (server ID 22) you create a table called
411 scratch_table_22_12345. If you see this table on the slave (server
412 ID 23), you might think it can be dropped safely if there's no such
413 connection 12345. But if you also force the name to match the
414 server ID with "--server-id '\D_(\d+)_\d+$'", the table won't be
415 dropped on the slave.
416 --tablesize
418 type: size; group: Tests
419 Table uses n bytes of space. This tests the sum of the Data_length
421 and Index_length columns.
422 --tbllike
424 type: string; group: Tests
425 Table name matches SQL LIKE pattern.
427 --tblregex
429 type: string; group: Tests
430 Table name matches this pattern.
432 --tblversion
434 type: size; group: Tests
435 Table version is n. This tests the Version column.
437 --trigger
439 type: string; group: Tests
440 Trigger action statement matches pattern.
442 --trigger-table
444 type: string; group: Tests
445 "--trigger" is defined on table matching pattern.
447 --view
449 type: string; group: Tests
450 CREATE VIEW matches this pattern.
452 ACTIONS
454 The "--exec-plus" action happens after everything else, but otherwise
455 actions happen in an indeterminate order. If you need determinism,
456 file a bug report and I'll add this feature.
457 --exec
459 type: string; group: Actions
460 Execute this SQL with each item found. The SQL can contain escapes
462 and formatting directives (see "--printf").
463 --exec-dsn
465 type: string; group: Actions
466 Specify a DSN in key-value format to use when executing SQL with
468 "--exec" and "--exec-plus". Any values not specified are inherited
469 from command-line arguments.
470 --exec-plus
472 type: string; group: Actions
473 Execute this SQL with all items at once. This option is unlike
475 "--exec". There are no escaping or formatting directives; there is
476 only one special placeholder for the list of database and table
477 names, %s. The list of tables found will be joined together with
478 commas and substituted wherever you place %s.
479 You might use this, for example, to drop all the tables you found:
481 DROP TABLE %s
483 This is sort of like GNU find's "-exec command {} +" syntax. Only
485 it's not totally cryptic. And it doesn't require me to write a
486 command-line parser.
487 --print
489 group: Actions
490 Print the database and table name, followed by a newline. This is
492 the default action if no other action is specified.
493 --printf
495 type: string; group: Actions
496 Print format on the standard output, interpreting '\' escapes and
498 '%' directives. Escapes are backslashed characters, like \n and
499 \t. Perl interprets these, so you can use any escapes Perl knows
500 about. Directives are replaced by %s, and as of this writing, you
501 can't add any special formatting instructions, like field widths or
502 alignment (though I'm musing over ways to do that).
503 Here is a list of the directives. Note that most of them simply
505 come from columns of SHOW TABLE STATUS. If the column is NULL or
506 doesn't exist, you get an empty string in the output. A %
507 character followed by any character not in the following list is
508 discarded (but the other character is printed).
509 CHAR DATA SOURCE NOTES
511 ---- ------------------ ------------------------------------------
512 a Auto_increment
513 A Avg_row_length
514 c Checksum
515 C Create_time
516 D Database The database name in which the table lives
517 d Data_length
518 E Engine In older versions of MySQL, this is Type
519 F Data_free
520 f Innodb_free Parsed from the Comment field
521 I Index_length
522 K Check_time
523 L Collation
524 M Max_data_length
525 N Name
526 O Comment
527 P Create_options
528 R Row_format
529 S Rows
530 T Table_length Data_length+Index_length
531 U Update_time
532 V Version
533
535 These DSN options are used to create a DSN. Each option is given like
536 "option=value". The options are case-sensitive, so P and p are not the
537 same option. There cannot be whitespace before or after the "=" and if
538 the value contains whitespace it must be quoted. DSN options are
539 comma-separated. See the maatkit manpage for full details.
540 · A
542 dsn: charset; copy: yes
544 Default character set.
546 · D
548 dsn: database; copy: yes
550 Default database.
552 · F
554 dsn: mysql_read_default_file; copy: yes
556 Only read default options from the given file
558 · h
560 dsn: host; copy: yes
562 Connect to host.
564 · p
566 dsn: password; copy: yes
568 Password to use when connecting.
570 · P
572 dsn: port; copy: yes
574 Port number to use for connection.
576 · S
578 dsn: mysql_socket; copy: yes
580 Socket file to use for connection.
582 · u
584 dsn: user; copy: yes
586 User for login if not current user.
588
590 You can download Maatkit from Google Code at
591 <http://code.google.com/p/maatkit/>, or you can get any of the tools
592 easily with a command like the following:
593 wget http://www.maatkit.org/get/toolname
595 or
596 wget http://www.maatkit.org/trunk/toolname
597 Where "toolname" can be replaced with the name (or fragment of a name)
599 of any of the Maatkit tools. Once downloaded, they're ready to run; no
600 installation is needed. The first URL gets the latest released version
601 of the tool, and the second gets the latest trunk code from Subversion.
602
604 The environment variable "MKDEBUG" enables verbose debugging output in
605 all of the Maatkit tools:
606 MKDEBUG=1 mk-....
608
610 You need the following Perl modules: DBI and DBD::mysql.
611
613 For a list of known bugs see <http://www.maatkit.org/bugs/mk-find>.
614 Please use Google Code Issues and Groups to report bugs or request
616 support: <http://code.google.com/p/maatkit/>. You can also join
617 #maatkit on Freenode to discuss Maatkit.
618 Please include the complete command-line used to reproduce the problem
620 you are seeing, the version of all MySQL servers involved, the complete
621 output of the tool when run with "--version", and if possible,
622 debugging output produced by running with the "MKDEBUG=1" environment
623 variable.
624
626 This program is copyright 2007-2011 Baron Schwartz. Feedback and
627 improvements are welcome (see "BUGS").
628 THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
630 WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
631 MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
632 This program is free software; you can redistribute it and/or modify it
634 under the terms of the GNU General Public License as published by the
635 Free Software Foundation, version 2; OR the Perl Artistic License. On
636 UNIX and similar systems, you can issue `man perlgpl' or `man
637 perlartistic' to read these licenses.
638 You should have received a copy of the GNU General Public License along
640 with this program; if not, write to the Free Software Foundation, Inc.,
641 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
642
644 Baron Schwartz
645
647 This tool is part of Maatkit, a toolkit for power users of MySQL.
648 Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the
649 primary code contributors. Both are employed by Percona. Financial
650 support for Maatkit development is primarily provided by Percona and
651 its clients.
652
654 This manual page documents Ver 0.9.23 Distrib 7540 $Revision: 7477 $.
655perl v5.30.1 2020-01-29 MK-FIND(1)