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

Tags: , , , , , , , , , , , ,

4 Responses to “Setting up Perl on Windows for MySQL Scripts (mysqldumpslow, mysql_explain_log, etc.)”

  1. Wlad says:

    Don’t you think ActiveState became an obsolete distribution by now? They used to be the only one on Windows, and setups like this were necessary for trivial thing. But there is an easier way now, Strawberry perl rocks, and it has DBI/DBD out of the box.

  2. chris says:

    Wlad, thanks so much for the input! Actually, I wasn’t too familiar with Strawberry Perl before your comment (had heard the name, but that’s about it). I’m sure other users will appreciate knowing this too. :)

  3. A. Karl Kornel says:

    I was also going to suggest Strawberry Perl. ActiveState is still around, and it is definitely the place to go if you want a Perl for Windows that can be backed by commercial support (think Red Hat vs. CentOS). The other big difference is that ActiveState uses pre-built modules, and Strawberry Perl gives you the GCC toolchain, so that you can build your own modules from source (this includes downloading any prerequisite libraries).

  4. chris says:

    Karl, thanks for the extra details! Looks like I’ll be testing out Strawberry Perl next week, and posting a new tutorial .. though sounding like it’ll be much shorter. ;)


Period Panties by Period Panteez Menstrual Underwear Menstruation PMS Panty