Setting up Perl on Windows for MySQL Scripts (mysqldumpslow, mysql_explain_log, etc.)

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 or

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

Here is my session output, for reference:

C:\Users\Chris>ppm rep add
Downloading packlist...done
Updating 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:> -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:> --user=root --password=mysql < ..\data\Chris-PC.log
explain_log     provided by
===========     ================================

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.