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 …

I received a phone call from a friend of mine who had some questions about storing image files as blobs in MySQL. He wanted to design a web site that would allow someone to upload an image, and then that image would be stored in the MySQL database for viewing later. He stated that he was going to be storing thousands of images. I mentioned that it might be better to store the images on the file system, and then to just store the location of the image in the database. But, he still wanted to know how to do it and would decide which solution he would incorporate later.

I already had a Perl script that allowed me to upload files to a web site, as I would give out this URL to people that wanted to send me large files. And, I know that you can store images (and other files) in MySQL as a blob, but I wasn’t sure how to display the blob as an image in HTML (once I had retrieved it from the MySQL database). A search on Google provide bits and …

I was trolling the MySQL forums web site and noticed a post regarding someone who was trying to load a 50-gigabyte MySQL dump file. The author of the post had stated that the loading of the file had been running for days, and was wondering if there was a better way to import the file. They did not have access to anything else (i.e. – original database) but this file.

I have had to restore several databases in the past from a single large MySQL dump file – which led me to start backing up each database individually. These databases are for my own personal use and are not updated that often, so I don’t need to have point-in-time recovery – and so a MySQL dump works just fine. If I had a production system, I would invest in the MySQL Enterprise Backup and the MySQL Enterprise Monitor …

I recently visited a client for the purpose of installing and demonstrating MySQL Enterprise Monitor.

If you are unfamiliar with the MySQL Enterprise Monitor – from the MySQL web site: The MySQL Enterprise Monitor continuously monitors your MySQL servers and alerts you to potential problems before they impact your system. Its like having a “Virtual DBA Assistant” at your side to recommend best practices to eliminate security vulnerabilities, improve replication, optimize performance and more. As a result, the productivity of your developers, DBAs and System Administrators is improved significantly.

The MySQL Enterprise Monitor is a distributed web application that is deployed within the safety of your firewall. It is comprised of a centralized Service Manager and lightweight Service Agent this is installed on each monitored MySQL …

Have you ever had a spreadsheet file or a large .csv file that you wanted to manipulate, but you want more power than a spreadsheet program could offer?

Before I started using MySQL, I would usually throw the .csv file into a desktop database program, like FileMaker. FileMaker would allow you to import the .csv file and it would automatically create the column headers for you. Recently, I was given a spreadsheet with 27,000 rows in it. I still use FileMaker for some databases, but I wanted the power of MySQL to manipulate the information contained in this file. So, I could have easily just typed out the database column names manually into a MySQL “create table” statement, guessed at the types and sizes of the columns and then imported the .csv file. Instead, I decided to write a Perl script to do the dirty work for me. Plus, this spreadsheet had 45 columns of varying lengths. Ouch.

Please keep in mind that this was a quick hack. …

As announced on the Maatkit and Aspersa mailing lists, Daniel and I have created a new toolkit that represents the union of the two, and will be focusing efforts on this Percona Toolkit moving forward. The goal is to make them simpler and significantly more powerful, and to create more tools. The tools will continue to be open-source, but will be developed primarily to meet our MySQL support and consulting staff’s needs.

If you’re interested in challenging software engineering in Perl and shell, then please apply online. You can work online from anywhere, but I strongly prefer someone in the Americas timezones.

Further Reading:

• Planned change in Maatkit & Aspersa …

MySQL provides you with a nice utility for creating a backup of your databases. From the mysqldump documentation page: “The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.”

The mysqldump utility doesn’t provide you with a “hot” or live backup like MySQL Enterprise Backup does, and there may be issues with mysqldump locking tables during the backup process. So for critical data, you should consider …

In the two previous posts, we looked at simply connecting to a MySQL database via Python and Perl. In this post, we will:

- use an insert statement to input data into a MySQL table via Perl
- use a select statement to view the same data to confirm our results

For this example, we will use a table named “address”. Here is the SQL statement that we used to create the table:

SET NAMES latin1;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE `address` (
  `name_first` varchar(30) NOT NULL,
  `name_last` varchar(30) NOT NULL,
  `address_01` varchar(40) NOT NULL,
  `address_02` varchar(40) NOT NULL,
  `address_city` varchar(30) NOT NULL,
  `address_state` varchar(20) NOT NULL,
  `address_postal_code` varchar(12) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SET FOREIGN_KEY_CHECKS = 1;

We are only going to insert one address line with our data. You can modify this script to loop the process and insert …

When I was designing web sites, for a long time I wrote my HTML code the “hard” way – by opening a text editor and manually typing in the code (this was before I purchased Adobe DreamWeaver).

During that time of manual HTML writing, I had a project that required forms on a web page, and I needed a place to store the information. After talking with a few tech friends, I decided to use MySQL as my web site database, and Perl as my scripting language.

I had written complex Bourne shell scripts before, but Perl was something entirely new. With a little help from a buddy of mine, after a few hours I was off and running. I was amazed at how easy it was to connect to a MySQL database with Perl.

This example will show you how to use Perl to connect to a …

I hope everyone is enjoying summertime, at least in the northern hemisphere. I’m about to head out to the pool, but before I go, here is a summary of this week’s blog posts.

1. Introduction to Perl interface for Monitis API
Monitis announces a simple way to access its API through Perl, a high-level, general-purpose, interpreted, dynamic programming language. This post demonstrates some examples for using the API with Perl and describes some of the benefits of the programming language. The source can be found on our Github page.

2. 101 Tips to MySQL Tuning and Optimization …