Perl DBI and DBD::mysql on Cygwin — Connecting to a Native Windows Build of MySQL on a Windows 2003 AMI Within Amazon EC2

In my ongoing project involving Amazon’s EC2 service, I had a frustrating problem to solve this past weekend. I have an EC2 instance running Windows 2003, and on that instance I have a native Windows version of MySQL 5 and Cygwin. I wanted to use the mysqlhotcopy Perl script from the Cygwin command line against the Windows-native MySQL instance. Once again, I would have expected this to be a simple job with a simple solution, but in the end it turned into an extensive hacking session. Here is a quick roadmap of what I did.

My initial thought was that this should just work: MySQL and its scripts should not care if they are running in native Windows mode or in Cygwin, and mysqlhotcopy is just a Perl script that should run fine in either Cygwin or Windows…wrong! The native Windows version of MySQL does not ship with the mysqlhotcopy script, probably because that script uses Perl and DBI and there is no guarantee that Perl/DBI will be available on Windows. So I grabbed the mysqlhotcopy script from a UNIX box and attempted to run it via Cygwin. I got this Perl error saying that the DBI module was not found:

Can't locate DBI.pm in @INC (@INC contains: /usr/lib/perl5/5.10/i686-cygwin /usr/lib/perl5/5.10 /usr/lib/perl5/site_perl/5.10/i686-cygwin /usr/lib/perl5/site_perl/5.10 /usr/lib/perl5/vendor_perl/5.10/i686-cygwin /usr/lib/perl5/vendor_perl/5.10 /usr/lib/perl5/vendor_perl/5.10 /usr/lib/perl5/site_perl/5.8 /usr/lib/perl5/vendor_perl/5.8 .) at ./mysqlhotcopy line 8.
BEGIN failed--compilation aborted at ./mysqlhotcopy line 8.

So I guess I just need to get DBI installed for Perl and we should be good to go…right? Perl modules can be installed on Cygwin using cpan, so I ran:

cpan DBI

This command completed without errors. Let’s try the mysqlhotcopy script again…it runs without errors and prints out the usage page. Progress! So now let’s test it out with a real call to take a hot copy of the database:

mysqlhotcopy -u <username> -p <password> <database> <backup directory>

This command gives me the following error, complaining about DBD::mysql (the MySQL driver used by DBI to actually connect to MySQL):

install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC contains: /usr/lib/perl5/5.10/i686-cygwin /usr/lib/perl5/5.10 /usr/lib/perl5/site_perl/5.10/i686-cygwin /usr/lib/perl5/site_perl/5.10 /usr/lib/perl5/vendor_perl/5.10/i686-cygwin /usr/lib/perl5/vendor_perl/5.10 /usr/lib/perl5/vendor_perl/5.10 /usr/lib/perl5/site_perl/5.8 /usr/lib/perl5/vendor_perl/5.8 .) at (eval 9) line 3.
Perhaps the DBD::mysql perl module hasn't been fully installed, or perhaps the capitalisation of 'mysql' isn't right.
Available drivers: DBM, ExampleP, File, Gofer, Proxy, Sponge.  at ./mysqlhotcopy line 182

So we just need to install the DBD::mysql module and we should be good to go, right?. I ran the following command:

cpan DBD::mysql

This command failed with a build error:

Can't exec "mysql_config": No such file or directory at Makefile.PL line 76.

The DBD::mysql module is compiled locally, using the mysql_config script to find the location of the local MySQL installation. But the native Windows version of MySQL does not contain the mysql_config script. Ugh. I tried copying this file over from a UNIX box, but the output from the script (which is just configuration info for the MySQL installation and the settings in my.ini) looked a little screwy. So I guess I need to figure out what mysql_config is used for within the mysqlhotcopy script.

After some digging, it appears that the crux of the problem is that the MySQL client libraries are not available in the native Windows MySQL installation, and these libraries are required to build DBD::mysql. So if we can figure out a way to get these libraries to work in Cygwin, then we should have a working solution. Luckily, I found a note in the DBD::mysql readme file that pointed me in the right direction. Here is what I ultimately did:

0) Download and unzip the MySQL source code (I grabbed mysql 5.1.34).

1) Build the MySQL client libraries (without the server) via:
./configure --without-server --prefix=/usr/local/mysql-5.1.34
make

The build halts with an error for the file sys/ttypdefaults.h (not found), so I copied that file from /usr/include/sys/ttydefaults.h on a UNIX box into /usr/include/sys within Cygwin. Running make again completes the build after this file is in place. There is little of consequence in this file, so I am hoping that copying it from a UNIX box into Cygwin won’t have any serious side effects.

2) Once the MySQL build has finally completed (and this takes a while), run a manual build of the cpan download of DBD::mysql in the .cpan cache directory, using parameters for the location of the MySQL client libraries (which eliminates the need for mysql_config to be used to find them):

cd ~/.cpan/build/DBD-mysql-4.011-ynTTNR
perl Makefile.PL --libs="-L/usr/local/mysql-5.1.34/lib/mysql -lmysqlclient -lz" --cflags=-I/usr/local/mysql-5.1.34/include/mysql --testhost=127.0.0.1make
make install

So now we are ready to try mysqlhotcopy again. The MySQL client build installed a copy of mysqlhotcopy in /usr/local/mysql-5.1.34/bin, so let’s use that one instead of the one that was copied in from a UNIX box. Here’s the command:

/usr/bin/mysql-5.1.34/bin/mysqlhotcopy -u <username> -p <password> <database> <backup directory>

Still no joy; now we get this error:

DBI connect(';host=localhost;mysql_read_default_group=mysqlhotcopy','<database>',...) failed: Can't connect to local MySQL server through socket '/tmp/mysql.sock'(2) at /usr/local/mysql-5.1.34/bin/mysqlhotcopy line 177

This looks to me like DBI (using DBD::mysql) is trying to connect to a UNIX socket on the local machine instead of using TCP. Given that we’re on Windows, it will probably be a pain in the neck to figure out how to get the native Windows version of MySQL to listen on a local UNIX socket. Luckily, I’ve spent some time looking at the Perl code in mysqlhotcopy and it turns out that if you specify an IP address via the -h command, then this will override the use of the UNIX socket and will force DBI to use TCP to connect to MySQL. So let’s try the localhost loopback address (127.0.0.1) to see if that works:

/usr/bin/mysql-5.1.34/bin/mysqlhotcopy -h '127.0.0.1' -u <username> -p <password> <database> <backup directory>

Success! The command runs to completion without errors, and I can verify that the backup has taken place.

Hope this helps.