1MK-FIND(1)            User Contributed Perl Documentation           MK-FIND(1)
2
3
4

NAME

6       mk-find - Find MySQL tables and execute actions, like GNU find.
7

SYNOPSIS

9       Usage: mk-find [OPTION...] [DATABASE...]
10
11       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
14       Find all tables created more than a day ago, which use the MyISAM
15       engine, and print their names:
16
17         mk-find --ctime +1 --engine MyISAM
18
19       Find InnoDB tables that haven't been updated in a month, and convert
20       them to MyISAM storage engine (data warehousing, anyone?):
21
22         mk-find --mtime +30 --engine InnoDB --exec "ALTER TABLE %D.%N ENGINE=MyISAM"
23
24       Find tables created by a process that no longer exists, following the
25       name_sid_pid naming convention, and remove them.
26
27         mk-find --connection-id '\D_\d+_(\d+)$' --server-id '\D_(\d+)_\d+$' --exec-plus "DROP TABLE %s"
28
29       Find empty tables in the test and junk databases, and delete them:
30
31         mk-find --empty junk test --exec-plus "DROP TABLE %s"
32
33       Find tables more than five gigabytes in total size:
34
35         mk-find --tablesize +5G
36
37       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
40         mk-find --printf "%T\t%D.%N\n" | sort -rn
41
42       As above, but this time, insert the data back into the database for
43       posterity:
44
45         mk-find --noquote --exec "INSERT INTO sysdata.tblsize(db, tbl, size) VALUES('%D', '%N', %T)"
46

RISKS

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
53       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
58       At the time of this release, we know of no bugs that could cause
59       serious harm to users.
60
61       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
66       See also "BUGS" for more information on filing bugs and getting help.
67

DESCRIPTION

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
73       mk-find is simpler than GNU find.  It doesn't allow you to specify
74       complicated expressions on the command line.
75
76       mk-find uses SHOW TABLES when possible, and SHOW TABLE STATUS when
77       needed.
78

OPTION TYPES

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
85       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

OPTIONS

89       This tool accepts additional command-line arguments.  Refer to the
90       "SYNOPSIS" and usage information for details.
91
92       --ask-pass
93           Prompt for a password when connecting to MySQL.
94
95       --case-insensitive
96           Specifies that all regular expression searches are case-
97           insensitive.
98
99       --charset
100           short form: -A; type: string
101
102           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
108       --config
109           type: Array
110
111           Read this comma-separated list of config files; if specified, this
112           must be the first option on the command line.
113
114       --day-start
115           Measure times (for "--mmin", etc) from the beginning of today
116           rather than from the current time.
117
118       --defaults-file
119           short form: -F; type: string
120
121           Only read mysql options from the given file.  You must give an
122           absolute pathname.
123
124       --help
125           Show help and exit.
126
127       --host
128           short form: -h; type: string
129
130           Connect to host.
131
132       --or
133           Combine tests with OR, not AND.
134
135           By default, tests are evaluated as though there were an AND between
136           them.  This option switches it to OR.
137
138           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
142       --password
143           short form: -p; type: string
144
145           Password to use when connecting.
146
147       --pid
148           type: string
149
150           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
160       --port
161           short form: -P; type: int
162
163           Port number to use for connection.
164
165       --[no]quote
166           default: yes
167
168           Quotes MySQL identifier names with MySQL's standard backtick
169           character.
170
171           Quoting happens after tests are run, and before actions are run.
172
173       --set-vars
174           type: string; default: wait_timeout=10000
175
176           Set these MySQL variables.  Immediately after connecting to MySQL,
177           this string will be appended to SET and executed.
178
179       --socket
180           short form: -S; type: string
181
182           Socket file to use for connection.
183
184       --user
185           short form: -u; type: string
186
187           User for login if not current user.
188
189       --version
190           Show version and exit.
191
192   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
200       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
207       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
212       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
215       --autoinc
216           type: string; group: Tests
217
218           Table's next AUTO_INCREMENT is n.  This tests the Auto_increment
219           column.
220
221       --avgrowlen
222           type: size; group: Tests
223
224           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
228       --checksum
229           type: string; group: Tests
230
231           Table checksum is n.  This tests the Checksum column.
232
233       --cmin
234           type: size; group: Tests
235
236           Table was created n minutes ago.  This tests the Create_time
237           column.
238
239       --collation
240           type: string; group: Tests
241
242           Table collation matches pattern.  This tests the Collation column.
243
244       --column-name
245           type: string; group: Tests
246
247           A column name in the table matches pattern.
248
249       --column-type
250           type: string; group: Tests
251
252           A column in the table matches this type (case-insensitive).
253
254           Examples of types are: varchar, char, int, smallint, bigint,
255           decimal, year, timestamp, text, enum.
256
257       --comment
258           type: string; group: Tests
259
260           Table comment matches pattern.  This tests the Comment column.
261
262       --connection-id
263           type: string; group: Tests
264
265           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
274           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
285           This is how I manage scratch tables, and that's why I included this
286           test in mk-find.
287
288           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
295           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
300           See also "--server-id".
301
302       --createopts
303           type: string; group: Tests
304
305           Table create option matches pattern.  This tests the Create_options
306           column.
307
308       --ctime
309           type: size; group: Tests
310
311           Table was created n days ago.  This tests the Create_time column.
312
313       --datafree
314           type: size; group: Tests
315
316           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
319       --datasize
320           type: size; group: Tests
321
322           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
326       --dblike
327           type: string; group: Tests
328
329           Database name matches SQL LIKE pattern.
330
331       --dbregex
332           type: string; group: Tests
333
334           Database name matches this pattern.
335
336       --empty
337           group: Tests
338
339           Table has no rows.  This tests the Rows column.
340
341       --engine
342           type: string; group: Tests
343
344           Table storage engine matches this pattern.  This tests the Engine
345           column, or in earlier versions of MySQL, the Type column.
346
347       --function
348           type: string; group: Tests
349
350           Function definition matches pattern.
351
352       --indexsize
353           type: size; group: Tests
354
355           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
359       --kmin
360           type: size; group: Tests
361
362           Table was checked n minutes ago.  This tests the Check_time column.
363
364       --ktime
365           type: size; group: Tests
366
367           Table was checked n days ago.  This tests the Check_time column.
368
369       --mmin
370           type: size; group: Tests
371
372           Table was last modified n minutes ago.  This tests the Update_time
373           column.
374
375       --mtime
376           type: size; group: Tests
377
378           Table was last modified n days ago.  This tests the Update_time
379           column.
380
381       --procedure
382           type: string; group: Tests
383
384           Procedure definition matches pattern.
385
386       --rowformat
387           type: string; group: Tests
388
389           Table row format matches pattern.  This tests the Row_format
390           column.
391
392       --rows
393           type: size; group: Tests
394
395           Table has n rows.  This tests the Rows column.  The specified size
396           can be "NULL" to test where Rows IS NULL.
397
398       --server-id
399           type: string; group: Tests
400
401           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
410           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
417       --tablesize
418           type: size; group: Tests
419
420           Table uses n bytes of space.  This tests the sum of the Data_length
421           and Index_length columns.
422
423       --tbllike
424           type: string; group: Tests
425
426           Table name matches SQL LIKE pattern.
427
428       --tblregex
429           type: string; group: Tests
430
431           Table name matches this pattern.
432
433       --tblversion
434           type: size; group: Tests
435
436           Table version is n.  This tests the Version column.
437
438       --trigger
439           type: string; group: Tests
440
441           Trigger action statement matches pattern.
442
443       --trigger-table
444           type: string; group: Tests
445
446           "--trigger" is defined on table matching pattern.
447
448       --view
449           type: string; group: Tests
450
451           CREATE VIEW matches this pattern.
452
453   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
458       --exec
459           type: string; group: Actions
460
461           Execute this SQL with each item found.  The SQL can contain escapes
462           and formatting directives (see "--printf").
463
464       --exec-dsn
465           type: string; group: Actions
466
467           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
471       --exec-plus
472           type: string; group: Actions
473
474           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
480           You might use this, for example, to drop all the tables you found:
481
482              DROP TABLE %s
483
484           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
488       --print
489           group: Actions
490
491           Print the database and table name, followed by a newline.  This is
492           the default action if no other action is specified.
493
494       --printf
495           type: string; group: Actions
496
497           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
504           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
510              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

DSN OPTIONS

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
541       ·   A
542
543           dsn: charset; copy: yes
544
545           Default character set.
546
547       ·   D
548
549           dsn: database; copy: yes
550
551           Default database.
552
553       ·   F
554
555           dsn: mysql_read_default_file; copy: yes
556
557           Only read default options from the given file
558
559       ·   h
560
561           dsn: host; copy: yes
562
563           Connect to host.
564
565       ·   p
566
567           dsn: password; copy: yes
568
569           Password to use when connecting.
570
571       ·   P
572
573           dsn: port; copy: yes
574
575           Port number to use for connection.
576
577       ·   S
578
579           dsn: mysql_socket; copy: yes
580
581           Socket file to use for connection.
582
583       ·   u
584
585           dsn: user; copy: yes
586
587           User for login if not current user.
588

DOWNLOADING

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
594          wget http://www.maatkit.org/get/toolname
595          or
596          wget http://www.maatkit.org/trunk/toolname
597
598       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

ENVIRONMENT

604       The environment variable "MKDEBUG" enables verbose debugging output in
605       all of the Maatkit tools:
606
607          MKDEBUG=1 mk-....
608

SYSTEM REQUIREMENTS

610       You need the following Perl modules: DBI and DBD::mysql.
611

BUGS

613       For a list of known bugs see <http://www.maatkit.org/bugs/mk-find>.
614
615       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
619       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

COPYRIGHT, LICENSE AND WARRANTY

626       This program is copyright 2007-2011 Baron Schwartz.  Feedback and
627       improvements are welcome (see "BUGS").
628
629       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
633       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
639       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

AUTHOR

644       Baron Schwartz
645

ABOUT MAATKIT

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

VERSION

654       This manual page documents Ver 0.9.23 Distrib 7540 $Revision: 7477 $.
655
656
657
658perl v5.28.0                      2011-06-08                        MK-FIND(1)
Impressum