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 Find all tables created more than a day ago, which use the MyISAM
10 engine, and print their names:
11 mk-find --ctime +1 --engine MyISAM
13 Find InnoDB tables that haven't been updated in a month, and convert
15 them to MyISAM storage engine (data warehousing, anyone?):
16 mk-find --mtime +30 --engine InnoDB --exec "ALTER TABLE %D.%N ENGINE=MyISAM"
18 Find tables created by a process that no longer exists, following the
20 name_sid_pid naming convention, and remove them.
21 mk-find --connection-id '\D_\d+_(\d+)$' --server-id '\D_(\d+)_\d+$' --exec-plus "DROP TABLE %s"
23 Find empty tables in the test and junk databases, and delete them:
25 mk-find --empty junk test --exec-plus "DROP TABLE %s"
27 Find tables more than five gigabytes in total size:
29 mk-find --tablesize +5G
31 Find all tables and print their total data and index size, and sort
33 largest tables first (sort is a different program, by the way).
34 mk-find --printf "%T\t%D.%N\n" | sort -rn
36 As above, but this time, insert the data back into the database for
38 posterity:
39 mk-find --noquote --exec "INSERT INTO sysdata.tblsize(db, tbl, size) VALUES('%D', '%N', %T)"
41
43 The following section is included to inform users about the potential
44 risks, whether known or unknown, of using this tool. The two main
45 categories of risks are those created by the nature of the tool (e.g.
46 read-only tools vs. read-write tools) and those created by bugs.
47 mk-find only reads and prints information by default, but "--exec" and
49 "--exec-plus" can execute user-defined SQL. You should be as careful
50 with it as you are with any command-line tool that can execute queries
51 against your database.
52 At the time of this release, we know of no bugs that could cause
54 serious harm to users.
55 The authoritative source for updated information is always the online
57 issue tracking system. Issues that affect this tool will be marked as
58 such. You can see a list of such issues at the following URL:
59 http://www.maatkit.org/bugs/mk-find <http://www.maatkit.org/bugs/mk-
60 find>.
61 See also "BUGS" for more information on filing bugs and getting help.
63
65 mk-find looks for MySQL tables that pass the tests you specify, and
66 executes the actions you specify. The default action is to print the
67 database and table name to STDOUT.
68 mk-find is simpler than GNU find. It doesn't allow you to specify
70 complicated expressions on the command line.
71 mk-find uses SHOW TABLES when possible, and SHOW TABLE STATUS when
73 needed.
74
76 There are three types of options: normal options, which determine some
77 behavior or setting; tests, which determine whether a table should be
78 included in the list of tables found; and actions, which do something
79 to the tables mk-find finds.
80 mk-find uses standard Getopt::Long option parsing, so you should use
82 double dashes in front of long option names, unlike GNU find.
83
85 --ask-pass
86 Prompt for a password when connecting to MySQL.
87 --case-insensitive
89 Specifies that all regular expression searches are case-
90 insensitive.
91 --charset
93 short form: -A; type: string
94 Default character set. If the value is utf8, sets Perl's binmode
96 on STDOUT to utf8, passes the mysql_enable_utf8 option to
97 DBD::mysql, and runs SET NAMES UTF8 after connecting to MySQL. Any
98 other value sets binmode on STDOUT without the utf8 layer, and runs
99 SET NAMES after connecting to MySQL.
100 --config
102 type: Array
103 Read this comma-separated list of config files; if specified, this
105 must be the first option on the command line.
106 --day-start
108 Measure times (for "--mmin", etc) from the beginning of today
109 rather than from the current time.
110 --defaults-file
112 short form: -F; type: string
113 Only read mysql options from the given file. You must give an
115 absolute pathname.
116 --help
118 Show help and exit.
119 --host
121 short form: -h; type: string
122 Connect to host.
124 --or
126 Combine tests with OR, not AND.
127 By default, tests are evaluated as though there were an AND between
129 them. This option switches it to OR.
130 Option parsing is not implemented by mk-find itself, so you cannot
132 specify complicated expressions with parentheses and mixtures of OR
133 and AND.
134 --password
136 short form: -p; type: string
137 Password to use when connecting.
139 --pid
141 type: string
142 Create the given PID file. The file contains the process ID of the
144 script. The PID file is removed when the script exits. Before
145 starting, the script checks if the PID file already exists. If it
146 does not, then the script creates and writes its own PID to it. If
147 it does, then the script checks the following: if the file contains
148 a PID and a process is running with that PID, then the script dies;
149 or, if there is no process running with that PID, then the script
150 overwrites the file with its own PID and starts; else, if the file
151 contains no PID, then the script dies.
152 --port
154 short form: -P; type: int
155 Port number to use for connection.
157 --[no]quote
159 default: yes
160 Quotes MySQL identifier names with MySQL's standard backtick
162 character.
163 Quoting happens after tests are run, and before actions are run.
165 --set-vars
167 type: string; default: wait_timeout=10000
168 Set these MySQL variables. Immediately after connecting to MySQL,
170 this string will be appended to SET and executed.
171 --socket
173 short form: -S; type: string
174 Socket file to use for connection.
176 --user
178 short form: -u; type: string
179 User for login if not current user.
181 --version
183 Show version and exit.
184 TESTS
186 Most tests check some criterion against a column of SHOW TABLE STATUS
187 output. Numeric arguments can be specified as +n for greater than n,
188 -n for less than n, and n for exactly n. All numeric options can take
189 an optional suffix multiplier of k, M or G (1_024, 1_048_576, and
190 1_073_741_824 respectively). All patterns are Perl regular expressions
191 (see 'man perlre') unless specified as SQL LIKE patterns.
192 Dates and times are all measured relative to the same instant, when mk-
194 find first asks the database server what time it is. All date and time
195 manipulation is done in SQL, so if you say to find tables modified 5
196 days ago, that translates to SELECT DATE_SUB(CURRENT_TIMESTAMP,
197 INTERVAL 5 DAY). If you specify "--day-start", if course it's relative
198 to CURRENT_DATE instead.
199 However, table sizes and other metrics are not consistent at an instant
201 in time. It can take some time for MySQL to process all the SHOW
202 queries, and mk-find can't do anything about that. These measurements
203 are as of the time they're taken.
204 If you need some test that's not in this list, file a bug report and
206 I'll enhance mk-find for you. It's really easy.
207 --autoinc
209 type: string; group: Tests
210 Table's next AUTO_INCREMENT is n. This tests the Auto_increment
212 column.
213 --avgrowlen
215 type: size; group: Tests
216 Table avg row len is n bytes. This tests the Avg_row_length
218 column. The specified size can be "NULL" to test where
219 Avg_row_length IS NULL.
220 --checksum
222 type: string; group: Tests
223 Table checksum is n. This tests the Checksum column.
225 --cmin
227 type: size; group: Tests
228 Table was created n minutes ago. This tests the Create_time
230 column.
231 --collation
233 type: string; group: Tests
234 Table collation matches pattern. This tests the Collation column.
236 --column-name
238 type: string; group: Tests
239 A column name in the table matches pattern.
241 --column-type
243 type: string; group: Tests
244 A column in the table matches this type (case-insensitive).
246 Examples of types are: varchar, char, int, smallint, bigint,
248 decimal, year, timestamp, text, enum.
249 --comment
251 type: string; group: Tests
252 Table comment matches pattern. This tests the Comment column.
254 --connection-id
256 type: string; group: Tests
257 Table name has nonexistent MySQL connection ID. This tests the
259 table name for a pattern. The argument to this test must be a Perl
260 regular expression that captures digits like this: (\d+). If the
261 table name matches the pattern, these captured digits are taken to
262 be the MySQL connection ID of some process. If the connection
263 doesn't exist according to SHOW FULL PROCESSLIST, the test returns
264 true. If the connection ID is greater than mk-find's own
265 connection ID, the test returns false for safety.
266 Why would you want to do this? If you use MySQL statement-based
268 replication, you probably know the trouble temporary tables can
269 cause. You might choose to work around this by creating real
270 tables with unique names, instead of temporary tables. One way to
271 do this is to append your connection ID to the end of the table,
272 thusly: scratch_table_12345. This assures the table name is unique
273 and lets you have a way to find which connection it was associated
274 with. And perhaps most importantly, if the connection no longer
275 exists, you can assume the connection died without cleaning up its
276 tables, and this table is a candidate for removal.
277 This is how I manage scratch tables, and that's why I included this
279 test in mk-find.
280 The argument I use to "--connection-id" is "\D_(\d+)$". That finds
282 tables with a series of numbers at the end, preceded by an
283 underscore and some non-number character (the latter criterion
284 prevents me from examining tables with a date at the end, which
285 people tend to do: baron_scratch_2007_05_07 for example). It's
286 better to keep the scratch tables separate of course.
287 If you do this, make sure the user mk-find runs as has the PROCESS
289 privilege! Otherwise it will only see connections from the same
290 user, and might think some tables are ready to remove when they're
291 still in use. For safety, mk-find checks this for you.
292 See also "--server-id".
294 --createopts
296 type: string; group: Tests
297 Table create option matches pattern. This tests the Create_options
299 column.
300 --ctime
302 type: size; group: Tests
303 Table was created n days ago. This tests the Create_time column.
305 --datafree
307 type: size; group: Tests
308 Table has n bytes of free space. This tests the Data_free column.
310 The specified size can be "NULL" to test where Data_free IS NULL.
311 --datasize
313 type: size; group: Tests
314 Table data uses n bytes of space. This tests the Data_length
316 column. The specified size can be "NULL" to test where Data_length
317 IS NULL.
318 --dblike
320 type: string; group: Tests
321 Database name matches SQL LIKE pattern.
323 --dbregex
325 type: string; group: Tests
326 Database name matches this pattern.
328 --empty
330 group: Tests
331 Table has no rows. This tests the Rows column.
333 --engine
335 type: string; group: Tests
336 Table storage engine matches this pattern. This tests the Engine
338 column, or in earlier versions of MySQL, the Type column.
339 --function
341 type: string; group: Tests
342 Function definition matches pattern.
344 --indexsize
346 type: size; group: Tests
347 Table indexes use n bytes of space. This tests the Index_length
349 column. The specified size can be "NULL" to test where
350 Index_length IS NULL.
351 --kmin
353 type: size; group: Tests
354 Table was checked n minutes ago. This tests the Check_time column.
356 --ktime
358 type: size; group: Tests
359 Table was checked n days ago. This tests the Check_time column.
361 --mmin
363 type: size; group: Tests
364 Table was last modified n minutes ago. This tests the Update_time
366 column.
367 --mtime
369 type: size; group: Tests
370 Table was last modified n days ago. This tests the Update_time
372 column.
373 --procedure
375 type: string; group: Tests
376 Procedure definition matches pattern.
378 --rowformat
380 type: string; group: Tests
381 Table row format matches pattern. This tests the Row_format
383 column.
384 --rows
386 type: size; group: Tests
387 Table has n rows. This tests the Rows column. The specified size
389 can be "NULL" to test where Rows IS NULL.
390 --server-id
392 type: string; group: Tests
393 Table name contains the server ID. If you create temporary tables
395 with the naming convention explained in "--connection-id", but also
396 add the server ID of the server on which the tables are created,
397 then you can use this pattern match to ensure tables are dropped
398 only on the server they're created on. This prevents a table from
399 being accidentally dropped on a slave while it's in use (provided
400 that your server IDs are all unique, which they should be for
401 replication to work).
402 For example, on the master (server ID 22) you create a table called
404 scratch_table_22_12345. If you see this table on the slave (server
405 ID 23), you might think it can be dropped safely if there's no such
406 connection 12345. But if you also force the name to match the
407 server ID with "--server-id '\D_(\d+)_\d+$'", the table won't be
408 dropped on the slave.
409 --tablesize
411 type: size; group: Tests
412 Table uses n bytes of space. This tests the sum of the Data_length
414 and Index_length columns.
415 --tbllike
417 type: string; group: Tests
418 Table name matches SQL LIKE pattern.
420 --tblregex
422 type: string; group: Tests
423 Table name matches this pattern.
425 --tblversion
427 type: size; group: Tests
428 Table version is n. This tests the Version column.
430 --trigger
432 type: string; group: Tests
433 Trigger action statement matches pattern.
435 --trigger-table
437 type: string; group: Tests
438 "--trigger" is defined on table matching pattern.
440 --view
442 type: string; group: Tests
443 CREATE VIEW matches this pattern.
445 ACTIONS
447 The "--exec-plus" action happens after everything else, but otherwise
448 actions happen in an indeterminate order. If you need determinism,
449 file a bug report and I'll add this feature.
450 --exec
452 type: string; group: Actions
453 Execute this SQL with each item found. The SQL can contain escapes
455 and formatting directives (see "--printf").
456 --exec-dsn
458 type: string; group: Actions
459 Specify a DSN in key-value format to use when executing SQL with
461 "--exec" and "--exec-plus". Any values not specified are inherited
462 from command-line arguments.
463 --exec-plus
465 type: string; group: Actions
466 Execute this SQL with all items at once. This option is unlike
468 "--exec". There are no escaping or formatting directives; there is
469 only one special placeholder for the list of database and table
470 names, %s. The list of tables found will be joined together with
471 commas and substituted wherever you place %s.
472 You might use this, for example, to drop all the tables you found:
474 DROP TABLE %s
476 This is sort of like GNU find's "-exec command {} +" syntax. Only
478 it's not totally cryptic. And it doesn't require me to write a
479 command-line parser.
480 --print
482 group: Actions
483 Print the database and table name, followed by a newline. This is
485 the default action if no other action is specified.
486 --printf
488 type: string; group: Actions
489 Print format on the standard output, interpreting '\' escapes and
491 '%' directives. Escapes are backslashed characters, like \n and
492 \t. Perl interprets these, so you can use any escapes Perl knows
493 about. Directives are replaced by %s, and as of this writing, you
494 can't add any special formatting instructions, like field widths or
495 alignment (though I'm musing over ways to do that).
496 Here is a list of the directives. Note that most of them simply
498 come from columns of SHOW TABLE STATUS. If the column is NULL or
499 doesn't exist, you get an empty string in the output. A %
500 character followed by any character not in the following list is
501 discarded (but the other character is printed).
502 CHAR DATA SOURCE NOTES
504 ---- ------------------ ------------------------------------------
505 a Auto_increment
506 A Avg_row_length
507 c Checksum
508 C Create_time
509 D Database The database name in which the table lives
510 d Data_length
511 E Engine In older versions of MySQL, this is Type
512 F Data_free
513 f Innodb_free Parsed from the Comment field
514 I Index_length
515 K Check_time
516 L Collation
517 M Max_data_length
518 N Name
519 O Comment
520 P Create_options
521 R Row_format
522 S Rows
523 T Table_length Data_length+Index_length
524 U Update_time
525 V Version
526
528 These DSN options are used to create a DSN. Each option is given like
529 "option=value". The options are case-sensitive, so P and p are not the
530 same option. There cannot be whitespace before or after the "=" and if
531 the value contains whitespace it must be quoted. DSN options are
532 comma-separated. See the maatkit manpage for full details.
533 · A
535 dsn: charset; copy: yes
537 Default character set.
539 · D
541 dsn: database; copy: yes
543 Default database.
545 · F
547 dsn: mysql_read_default_file; copy: yes
549 Only read default options from the given file
551 · h
553 dsn: host; copy: yes
555 Connect to host.
557 · p
559 dsn: password; copy: yes
561 Password to use when connecting.
563 · P
565 dsn: port; copy: yes
567 Port number to use for connection.
569 · S
571 dsn: mysql_socket; copy: yes
573 Socket file to use for connection.
575 · u
577 dsn: user; copy: yes
579 User for login if not current user.
581
583 You can download Maatkit from Google Code at
584 <http://code.google.com/p/maatkit/>, or you can get any of the tools
585 easily with a command like the following:
586 wget http://www.maatkit.org/get/toolname
588 or
589 wget http://www.maatkit.org/trunk/toolname
590 Where "toolname" can be replaced with the name (or fragment of a name)
592 of any of the Maatkit tools. Once downloaded, they're ready to run; no
593 installation is needed. The first URL gets the latest released version
594 of the tool, and the second gets the latest trunk code from Subversion.
595
597 The environment variable "MKDEBUG" enables verbose debugging output in
598 all of the Maatkit tools:
599 MKDEBUG=1 mk-....
601
603 You need the following Perl modules: DBI and DBD::mysql.
604
606 For list of known bugs see http://www.maatkit.org/bugs/mk-find
607 <http://www.maatkit.org/bugs/mk-find>.
608 Please use Google Code Issues and Groups to report bugs or request
610 support: <http://code.google.com/p/maatkit/>. You can also join
611 #maatkit on Freenode to discuss Maatkit.
612 Please include the complete command-line used to reproduce the problem
614 you are seeing, the version of all MySQL servers involved, the complete
615 output of the tool when run with "--version", and if possible,
616 debugging output produced by running with the "MKDEBUG=1" environment
617 variable.
618
620 This program is copyright 2007-2010 Baron Schwartz. Feedback and
621 improvements are welcome (see "BUGS").
622 THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
624 WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
625 MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.
626 This program is free software; you can redistribute it and/or modify it
628 under the terms of the GNU General Public License as published by the
629 Free Software Foundation, version 2; OR the Perl Artistic License. On
630 UNIX and similar systems, you can issue `man perlgpl' or `man
631 perlartistic' to read these licenses.
632 You should have received a copy of the GNU General Public License along
634 with this program; if not, write to the Free Software Foundation, Inc.,
635 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA.
636
638 Baron Schwartz
639
641 This tool is part of Maatkit, a toolkit for power users of MySQL.
642 Maatkit was created by Baron Schwartz; Baron and Daniel Nichter are the
643 primary code contributors. Both are employed by Percona. Financial
644 support for Maatkit development is primarily provided by Percona and
645 its clients.
646
648 This manual page documents Ver 0.9.23 Distrib 6839 $Revision: 6831 $.
649perl v5.12.1 2010-08-01 MK-FIND(1)