What do you do when you're in a situation where you REALLY need your
favorite Maatkit tools, but are not allowed to load software on the
server running MySQL, AND you're not allowed to install software on the
provided workstation, especially a workstation that's running software
from a large corporation in Redmond? Portable Maatkit to the rescue!
DISCLAIMER:
I've only tested a few maatkit tools, namely mk-find and
mk-query-digest, but not completely. Some features and some tools may
not function, such as mk-audit. Use your best judgment. I'm not
responsible if your workstation or server looses all your data, your
USB key bursts into flames, or your significant other leaves you. YMMV,
use at YOUR OWN RISK!
Strawberry Perl
has been out for some time now, and recently, a portable version of
Strawberry Perl has been available. Download the the latest 5.10
version of Portable Strawberry Perl and unzip it onto your USB key. Also download and unzip to your USB key the latest copy of Maatkit. After unzipping everything, your USB key should look something like:
#999999; padding: 5px; overflow: auto; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; color: #000000; background-color: #eeeeee; font-size: 12px; line-height: 14px; width: 100%;">E:\>dir
Volume in drive E has no label.
Volume Serial Number is BEEF-FEED
Directory of E:\
01/29/2009 11:13 PM 116 Strawberry Perl Website.url
01/29/2009 11:13 PM 50 Win32 Perl Wiki.url
01/29/2009 11:14 PM <DIR> cpan
01/29/2009 10:29 PM <DIR> licenses
01/29/2009 11:14 PM <DIR> perl
01/29/2009 11:09 PM <DIR> ppm
07/23/2009 01:20 PM <DIR> maatkit-4047
01/29/2009 11:13 PM 51 CPAN Search.url
01/29/2009 11:13 PM 52 Perl 5.10.0 Documentation.url
10/16/2008 09:01 PM 1,861 portable.perl
12/24/2007 03:44 PM 1,406 Strawberry Perl Website.ico
01/29/2009 11:14 PM <DIR> c
8 File(s) 55,836 bytes
6 Dir(s) 1,848,025,088 bytes free
The
portable version ships with DBI installed, but not DBD::mysql. To
install DBD::mysql, use the Perl Package Manager (PPM) that comes with
Strawberry Perl.
#999999; padding: 5px; overflow: auto; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; color: #000000; background-color: #eeeeee; font-size: 12px; line-height: 14px; width: 100%;">E:\> cd perl\bin
E:\perl\bin> ppm
PPM interactive shell (0.01_01) - type 'help' for available commands.
PPM> help
Commands:
exit - leave the program.
help [command] - prints this screen, or help on 'command'.
install PACKAGES - installs specified PACKAGES.
quit - leave the program.
query [options] - query information about installed packages.
remove PACKAGES - removes the specified PACKAGES from the system.
search [options] - search information about available packages.
set [options] - set/display current options.
verify [options] - verifies current install is up to date.
version - displays PPM version number
PPM> search mysql
Packages available from http://cpan.uwinnipeg.ca/PPMPackages/10xx:
DBD-mysql [4.005] A MySQL driver for the Perl5 Database Interface (DBI)
Packages available from http://ppm.activestate.com/PPMPackages/5.10-windows:
ApacheMysql [0.3 ] Initiate a persistent database connection
to Mysql
DBIx-Mysql-InformationSchema [0.04] Perl module to access the mysql
INFORMATION_SCHEMA view, which contains
database metadata.
Install it:
PPM> install DBD-mysql
Install package 'DBD-mysql?' (y/N): y
Installing package 'DBD-mysql'...
Bytes transferred: 1018351
Files found in blib\arch: installing files in blib\lib into architecture depende
nt library tree
Installing E:\perl\site\lib\auto\DBD\mysql\mysql.bs
Installing E:\perl\site\lib\auto\DBD\mysql\mysql.dll
Installing E:\perl\site\lib\auto\DBD\mysql\mysql.exp
Installing E:\perl\site\lib\auto\DBD\mysql\mysql.lib
Installing E:\perl\site\lib\auto\DBD\mysql\mysql.pdb
Installing E:\perl\site\lib\Bundle\DBD\mysql.pm
Installing E:\perl\site\lib\DBD\mysql.pm
Installing E:\perl\site\lib\DBD\mysql\GetInfo.pm
Installing E:\perl\site\lib\DBD\mysql\INSTALL.pod
PPM>
Change to the maatkit directory on your USB key, and prepare it for installation:
#999999; padding: 5px; overflow: auto; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; color: #000000; background-color: #eeeeee; font-size: 12px; line-height: 14px; width: 100%;">E:\maatkit-4047> e:\perl\bin\perl Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for maatkit
E:\maatkit-4047>
I
know this really didn't do much, but it's a good check to make sure all
dependancies have been met. Don't install maatkit at this point! You
don't need to.
Now, take your USB key to the workstation and
plug it in. Once it has been recognized, open a command prompt by going
to the start button, click run, then enter cmd into the dropdown box.
Navigate to the your maatkit\bin directory.
I really needed mk-find for my task. Here's how to run mk-find with portable Strawberry Perl:
#999999; padding: 5px; overflow: auto; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; color: #000000; background-color: #eeeeee; font-size: 12px; line-height: 14px; width: 100%;">
F:\maatkit-4047\bin> f:\perl\bin\perl mk-find --help
mk-find searches for MySQL tables and executes actions, like GNU find. The
default action is to print the database and table name. For more details,
please use the --help option, or try 'perldoc mk-find' for complete
documentation.
Usage: mk-find [option]... [database...]
Options:
--ask-pass Prompt for a password when connecting to MySQL
--case-insensitive Specifies that all regular expression searches are
case-insensitive
--charset -A Default character set
--config Read this comma-separated list of config files; if
specified, this must be the first option on the command
line
--day-start Measure times (for --mmin, etc) from the beginning of
today rather than from the current time
--defaults-file -F Only read mysql options from the given file
--help Show help and exit
--host -h Connect to host
--or Combine tests with OR, not AND
--password -p Password to use when connecting
--port -P Port number to use for connection
--[no]quote Quotes MySQL identifier names with MySQL's standard
backtick character (default yes)
--set-vars Set these MySQL variables (default wait_timeout=10000)
--socket -S Socket file to use for connection
--user -u User for login if not current user
--version Show version and exit
F:\maatkit-4047\bin>
Nice, no errors! Since Maatkit isn't installed.
Lets try an example:
#999999; padding: 5px; overflow: auto; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; color: #000000; background-color: #eeeeee; font-size: 12px; line-height: 14px; width: 100%;">F:\maatkit-4047\bin> f:\perl\bin\perl mk-find -hmysqlserver -u user --ask-pass --print
Enter password:
`mysql`.`columns_priv`
`mysql`.`db`
`mysql`.`func`
`mysql`.`help_category`
`mysql`.`help_keyword`
`mysql`.`help_relation`
`mysql`.`help_topic`
`mysql`.`host`
`mysql`.`proc`
`mysql`.`procs_priv`
`mysql`.`tables_priv`
`mysql`.`time_zone`
`mysql`.`time_zone_leap_second`
`mysql`.`time_zone_name`
`mysql`.`time_zone_transition`
`mysql`.`time_zone_transition_type`
`mysql`.`user`
Another example:
#999999; padding: 5px; overflow: auto; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; color: #000000; background-color: #eeeeee; font-size: 12px; line-height: 14px; width: 100%;">F:\maatkit-4047\bin>f:\perl\bin\perl mk-find -hmysqlserver \
-u user --ask-pass --ctime +1 --engine MyISAM
Enter password:
`mysql`.`columns_priv`
`mysql`.`db`
`mysql`.`func`
`mysql`.`help_category`
`mysql`.`help_keyword`
`mysql`.`help_relation`
`mysql`.`help_topic`
`mysql`.`host`
`mysql`.`proc`
`mysql`.`procs_priv`
`mysql`.`tables_priv`
`mysql`.`time_zone`
`mysql`.`time_zone_leap_second`
`mysql`.`time_zone_name`
`mysql`.`time_zone_transition`
`mysql`.`time_zone_transition_type`
`mysql`.`user`
F:\maatkit-4047\bin>
Yet another example:
#999999; padding: 5px; overflow: auto; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; color: #000000; background-color: #eeeeee; font-size: 12px; line-height: 14px; width: 100%;">F:\maatkit-4047\bin> f:\perl\bin\perl mk-find -hmysqlserver \
-u user --ask-pass --printf "%T\t%D.%N\n"
Enter password:
1024 `mysql`.`columns_priv`
4972 `mysql`.`db`
1024 `mysql`.`func`
23988 `mysql`.`help_category`
90417 `mysql`.`help_keyword`
19909 `mysql`.`help_relation`
281940 `mysql`.`help_topic`
1024 `mysql`.`host`
4628 `mysql`.`proc`
1024 `mysql`.`procs_priv`
1024 `mysql`.`tables_priv`
1024 `mysql`.`time_zone`
1024 `mysql`.`time_zone_leap_second`
1024 `mysql`.`time_zone_name`
1024 `mysql`.`time_zone_transition`
1024 `mysql`.`time_zone_transition_type`
2472 `mysql`.`user`
I
can also report that mk-query-digest also worked without an error. I
can't show you the output to demonstrate, I just analyzed using
mk-query-digest using default settings to process the slow log of a
server. I'm sure most options will work, with the exception of daemon
mode since this is a windows workstation.
It's incredible the
flexibility Open Source tools gives a person. Portable Strawberry Perl
and now Portable Maatkit will be in my toolbox. Much thanks to the
Strawberry Perl and Maatkit teams!
