blog.dt.org

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.

Tags: cygwin, dbi, ec2, mysql, perl

Enterprise Cloud Computing – What is it, exactly?

I am often asked to define cloud computing. The overwhelming marketing hype attached to the term at the moment has obscured the benefits, in both technology and economics, that are available if you look closely. My typical response goes something like this: cloud computing does not allow you to do anything you could not do before, but it does allow you to do those things faster, with better response times, and with a different and mostly better cost model. These benefits, when applied to enterprise software deployments, make the deployment and maintenance of enterprise software faster, easier, and less expensive.

For example, a cloud-based virtualization service like Amazon’s Elastic Compute Cloud (EC2) gives you virtual hardware instances that can be provisioned via an API. The virtual hardware, in and of itself, is no different than real hardware running in a data center. However, a new virtual instance in EC2 can be started in minutes, and a new box running in a data center would need to be purchased, installed, and configured over a period that is measured in hours, if not days. The reduction of the capacity provisioning response time to minutes has a profound effect on the ability of an application to scale in response to load, since new capacity can be called up in response to load, rather than in anticipation of it. And this benefit is not limited to cloud-based virtualization services alone; the collection of cloud based services for data, messaging, and applications all benefit from the ability to respond quickly to capacity scaling requests. This is one of the key benefits of cloud computing relative to traditional deployment approaches based on actual hardware.

In economic terms, the value proposition of cloud computing resides in its cost model. Cloud-based services, and cloud-based virtualization services in particular, have adopted a pay-as-you-go pricing model without initial costs. This is fundamentally different from the traditional enterprise software cost model in which the majority of costs are incurred immediately prior to deployment, and it changes the causality relationship between cost and revenue for enterprise application deployments. In a cloud-based service, cost trails revenue, and cost increases only in response to increasing load, rather than in advance of it. Additionally, cloud-based services can scale down in response to decreasing load, and this provides additional cost benefits that are simply unattainable in the traditional enterprise deployment model. The economic benefits of this pricing model will likely become an irresistible force in the marketplace for enterprise applications.

Tags: amazon, enterprise cloud computing