This article is just a how-to for setting up Perl on Windows in order to use the perl scripts provided with MySQL, such as mysqldumpslow.pl or mysql_explain_log.pl.
Now, you might say there is a section on this topic in the MySQL manual, to which I’d agree. But, this was for ActiveState Perl 5.6. The latest ActiveState Perl is 5.12, so thoese instructions are a bit out-of-date. Also, there are some helpful “User Comments” on that same page, but again, those are slightly out-of-date in the ActiveState 5.12. So, I thought I’d just post the steps I took in order to set this up.
First, download and install ActivePerl. Click the “Download ActivePerl 5.12.4 for Windows” button (choose either 32-bit or 64-bit). You’ll be prompted to save an msi file. Save it, and double-click on it to install it, following the prompts.
Next, install Perl DBI and DBD:MySQL:
Open Perl Package Manager (PPM) GUI (which was installed from above step). In the PPM GUI (for ActivePerl 5.10+) you have to follow these steps to obtain DBI and DBD-mysql
1. In the PPM GUI, navigate to “Edit -> Preferences” (Ctrl+P)
2. Click on “Repositories” tab, select “uwinnipeg :: University of Winnipeg” from the “Suggested” drop-down, and click “Add”
This will add a number of packages to your ActivePerl (and you should at least see DBI now).
You *may* also find DBD-mysql (worked in 5.10 days, but not in 5.12 days).
3. If you don’t have it, open a dos command line prompt and run the following (this basically adds a second repository from the University of Winnipeg, but one that does contain the DBD-mysql):
ppm rep add http://theoryx5.uwinnipeg.ca/ppms/
Here is my session output, for reference:
C:\Users\Chris>ppm rep add http://theoryx5.uwinnipeg.ca/ppms/ Downloading theoryx5.uwinnipeg.ca packlist...done Updating theoryx5.uwinnipeg.ca database...done Repo 3 added.
4. Then run this (also from the dos command line):
ppm install dbd-mysql
Again, the session output:
Downloading DBD-mysql-4.019...done Unpacking DBD-mysql-4.019...done Generating HTML for DBD-mysql-4.019...done Updating files in site area...done 12 files installed
5. Now, in PPM, once you “Refresh all Data” (F5), then you should see DBD-mysql in the list.
6. If for some reason you’re not sure about DBI, just run:
ppm install DBI
In my case, it was already installed (at step #2), so the output showed this:
C:\Users\Chris>ppm install DBI No missing packages to install
Just showing there’s no harm in either of these “ppm install” commands.
Great! Now that that’s installed, let’s test somthing out, say mysqldumpslow, since it is so useful and common.
(Note for this step, I already have the slow query log enabled, and have run at least one slow query so that there is something in the log. Also, note the -v option is “verbose”, -s is sorting, and the ‘at’ is how it sorts – average query time, where ‘at’ is the default. Run mysqldumpslow –help for more details.)
C:>mysqldumpslow.pl -v -s at ..\data\Chris-PC-slow.log Reading mysql slow query log from ..\data\Chris-PC-slow.log Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=5.0 (5), root[root]@localhost select * from t; C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld-nt, Version: N.N.N-nt-log (MySQL Server). started with: TCP Port: N, Named Pipe: MySQL Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts C:\Program Files\MySQL\MySQL Server N.N\bin\mysqld-nt, Version: N.N.N-nt-log (MySQL Server). started with: TCP Port: N, Named Pipe: (null) # Time: N N:N:N # User@Host: root[root] @ localhost [N.N.N.N] # Query_time: N Lock_time: N Rows_sent: N Rows_examined: N use test99; select * from t
And here is an invocation of mysql_explain_log (run against the general query log):
C:>mysql_explain_log.pl --user=root --password=mysql < ..\data\Chris-PC.log explain_log provided by http://www.mobile.de =========== ================================ Index usage ------------------------------------ Queries causing table scans ------------------- Sum: 0 table scans Summary --------------------------------------- Select: 0 queries Update: 0 queries Init: 0 times Field: 0 times Refresh: 0 times Query: 24 times Statistics: 0 times Logfile: 59 lines Started: Wed Sep 14 14:13:30 2011 Finished: Wed Sep 14 14:13:31 2011
So there you have it.
Hope this helps any users out there needing to get Perl working on Windows for MySQL-bundled scripts.