by Kenneth Hess

MySQL is the agile relational database server that runs your critical data-backed Web applications for less—less money, less overhead, and less hassle—and it runs them anywhere. MySQL maintains its status as the world’s most popular open source database because of its simplicity, performance, price, and ability to run on almost any type of computer. Binary installable versions available for all major UNIX variants (Solaris, HP-UX, and AIX), Mac OS X, FreeBSD, Linux, and Microsoft Windows provide a worldwide community of users of the ultimate usable database system. Additionally, users can download the source code and compile their own custom versions of MySQL that offer them system specificity and optimizations not possible with generically compiled binary versions. But, it is the availability of those binary versions that makes MySQL the run-anywhere database that, following a disaster, can have you back up, running, and productive with downtimes measured in minutes instead of days.

If you operate a computing environment, you’re going to face disasters from failed backups to complete system breakdowns. Unless you’ve spent a huge amount of money on clustering or other disaster recovery methods that prevent data loss, you need an alternative plan. MySQL’s cross-platform capability offers you the peace of mind you need to recover and restore your services quickly, but you’ll need to know what’s involved in such a recovery and restoration process.

The statement that MySQL is a cross-platform database might not surprise you, as several database systems run on multiple platforms: Oracle, IBM’s DB2, PostgreSQL, and Sybase all run comfortably on multiple platforms. What makes MySQL’s cross-platform capability unique enough that it deserves this extra attention?

What makes MySQL’s cross-platform capability exciting is in its database and table design. To explain, all MySQL databases are simple directories or folders. If you create a new directory under a MySQL data directory, you have created a new database. You can create a new database by using the MySQL client or with a simple file system command. To create tables or any other database object (Table, Index, Key), however, you must use the MySQL client.

For MySQL’s default database engine, MyISAM, three files represent a table and its data. The three files bear the table name and an extension that describes their functions. The frm file is the table format file, MYD represents the data file, and the MYI file is the table index.

To illustrate these concepts, consider a database named addresses. Under the MySQL data folder is the addresses folder. Below the addresses folder lie three table files: contacts.frm, contacts.MYD, and contacts.MYI. The addresses database, at this point, only contains the single table, contacts. If you create a new table in the addresses database, it will also reside in the addresses folder (see Figure 1).

Figure1: The addresses database and contacts table.

Notice that databases and tables created in a Windows environment aren’t case sensitive. Corresponding files created on UNIX or Linux systems are case sensitive. On a UNIX or Linux system, the database Addresses may exist on the same system with a database named addresses, each containing its own set of tables. On a Windows system, you may have only one database named addresses. However, database administrators (DBAs) rarely, if ever, create two databases with similar names, as doing so leads to confusion for users and DBAs alike.

Note: If you’re wondering about the db.opt file, it’s created with every new database in version 5.x and later. It contains information for the database it describes: the default character set and the default collation type.

The significance of the addresses database and its three table files isn’t readily apparent for describing how compelling this architecture is following a disaster. Assume that this database is not a simple addresses database containing a single table but a large database (~200GB) associated with 40 tables. Also, assume that the system providing your database service has crashed and the disks aren’t recoverable or usable. Fortunately, you have a good backup from the previous day.

You discover, after a few heated conversations, that you have no replacement system for the database server that has recently died. Your Windows systems administrators inform you that replacement hardware won’t arrive until the next day and that rebuilding the system will take several hours beyond that. As you reach for your sixth antacid tablet, your eyes glaze over with thoughts of the speech you’ll have to make to your customers and shareholders explaining the failure. When you’ve assumed that you’re looking at a service outage of at least 24 hours, an unexpected voice comes from your otherwise silent gaggle of technical staff saying, “We have a Linux server that isn’t doing anything and we can restore the database to it, rename the system, change its IP address, and we’re back up and running in an hour or so.”

“Will that work?” you ask with a glimmer of hope in your voice.

You hear a muffled, “Yes” as the elated Linux systems administrator darts from the room to his desk to begin his database resuscitation tasks. In about an hour, your database is up and running again. You’re back in business.

Curiosity forces you to ask the self high-fiving Linux administrator what he did and how it worked. And he is all too happy to give you the details of the process. You plead with him to remove the “geek speak” and give it to you in plain English so that you can take notes on the process:

1. Download and install a MySQL binary package from the mysql.com Web site. Install and set all permissions according to the package’s included INSTALL-BINARY file.
2. Retrieve the latest database backup from storage and copy the database to the Linux system. This method assumes a simple file-based backup or an archived backup. Standard MySQL backups should utilize the mysqldump program described in an upcoming section.
3. Copy the database folder to the MySQL server’s data directory.
4. Change the database folder’s permissions to the specifications outlined in the INSTALL-BINARY file.
5. Change the Linux system’s hostname and IP address to that of the fallen server.
6. Start the MySQL service on the Linux system.
7. Test remote database connectivity and local MySQL client access. Change firewall rules to allow network connections to MySQL’s TCP port (3306), if you’re running a firewall on the Linux system.

It is this singular MySQL capability of allowing its database and table files to transcend computer architecture that makes MySQL unique among competing database systems. Using MySQL gives you the opportunity to employ any computer architecture for your databases and essentially enables you to drag and drop databases from one system type to another with no changes to the database or table structure.

This procedure works fine for MyISAM tables, but what if you’re running the transaction-safe InnoDB storage engine? The corresponding process works quite well for InnoDB tables.

Change the failed server scenario discussed earlier to one of real panic when you find that you use the InnoDB storage engine instead of simple MyISAM tables. You, aware of the ability to copy MyISAM files from one system to another, choke out the question to your rapidly typing Linux administrator, “Can we recover from this crash using MySQL and InnoDB?”

Your Linux administrator, without a glance in your direction, responds with a simple, “Yes.”

Again, you respectfully request that he remove the jargon and the computer-ese from the description of his actions as you take notes on the recovery:

1. Download and install a MySQL binary package from the mysql.com Web site. Install and set all permissions according to the package’s included INSTALL-BINARY file.
2. Retrieve the latest database backup from storage and copy the database to the Linux system. Database backups, particularly those of the InnoDB type, should use the mysqldump command. Often, a simple file restore won’t work for recreating InnoDB databases; therefore, the mysqldump backup is the preferred method (detailed description in the next section). These steps describe how to restore using the file method on a newly installed MySQL instance. If this method doesn’t work and you have mysqldump backups, use them.
3. Copy the database folder, ibdataX files, and ib_logfileX files to the MySQL server’s data directory. X represents all possible numbers (0 to n) that currently exist on the system.
4. Change the database folder’s permissions to the specifications outlined in the INSTALL-BINARY file.
5. Change the Linux system’s hostname and IP address to that of the fallen server.
6. Start the MySQL service on the Linux system.
7. Test remote database connectivity and local MySQL client access. Change firewall rules to allow network connections to MySQL’s TCP port (3306), if you’re running a firewall on the Linux system.

You read back the simple instructions in amazement wondering if the recovery could play out in such an uncomplicated fashion.

The Linux administrator reports that, “We’re back up and running.”

What if you’d had a currently running production database on this system and not a new install, would this process work the same way? For MyISAM tables, yes (except for a MySQL service restart on Step 6), the process wouldn’t change. For InnoDB, however, it’s more complicated. Assume, as the question proposes, that you have two database servers that run different sets of databases. One fails.

1. Retrieve the latest database backup from storage and copy the database to the running system.
2. Create a current backup of your running InnoDB databases using the mysqldump command. This is a precautionary measure in case something goes wrong with the restore. Always make a backup of your working databases prior to making any changes at the file system level.
3. Create the databases that you need to restore using a file system command (mkdir) or by logging into the MySQL database and issuing the CREATE DATABASE DBName SQL statement (the preferred method). This restore procedure won’t restore the databases themselves—only the tables under them; you’ll have to restore the databases manually.
4. Import the restored databases from the “dump” files copied from backup:# mysql –uroot –proot_passwd DBName < DBName.sql
5. If you created the databases by using the mkdir command, adjust the permissions of the database folder(s) according to the INSTALL-BINARY file that ships with the MySQL binary package.
6. Test remote database connectivity and local MySQL client access. Change firewall rules to allow network connections to MySQL’s TCP port (3306), if you’re running a firewall on the Linux system.
   

   Note: This recovery procedure assumes that you taken proper InnoDB database backups with the mysqldump command. For example:

   # mysqldump -uroot -proot_passwd –opt DBName > DBName_export.sql

   (root and root_passwd refer to the MySQL server root user and its password and not the Linux system root password)

   Each database on the system must use this method. You can, and should, make multiple backups per day using this method for your production databases.

Of course, the best defense against failures is hardware redundancy and verified backups. Not even MySQL can save you from poor planning in those areas. MySQL can save your data and have you recovered quickly should a disaster occur, but you also have to do your part. As you can see, if you do your part, MySQL might save your day and your data.

Currently tested cross-platform restore/recover scenarios include Solaris to Linux, Linux to Solaris, Linux to Windows, and Windows to Linux.

Regular MySQL database backups are your best defense against prolonged downtime should a major failure occur. The mysqldump command, presented earlier, provides you with a native, easy-to-use method for making quick database backups. For scripted automation, you’ll have to specify each database in a separate mysqldump command. For example, if you want to back up the contacts and the resources databases, you would use the following commands in a script:

mysqldump -uroot -proot_passwd –opt Contacts > Contacts.sql

mysqldump -uroot -proot_passwd –opt Resources > Resources.sql

On Windows systems, you’d create a .cmd (batch) file, insert the mysqldump commands, and schedule its execution periodically throughout the day to create current backups of busy databases. For UNIX and Linux systems, you’d create a shell command file with execute permissions and use the native CRON scheduler for your periodic backups. Fortunately, MySQL’s developers had multiple OSs in mind when they designed its utilities. Their design enables you to create scripts with the same syntax for any OS with which you work. This adheres to the “write once, run anywhere” philosophy that’s so prevalent in the MySQL project.

MySQL provides you with a free, open source, enterprise-capable relational database system. You can also purchase support and commercial licenses for MySQL through the Oracle Corporation. This overview has demonstrated MySQL’s flexibility, OS independence, and cross-platform capability with actual examples of three different failure and recovery scenarios. Explore for yourself with the free community version and find out first hand why MySQL is the run-anywhere database system.

You can learn more about MySQL and its features by visiting the MySQL Web site at mysql.com.

About the Author

Ken Hess has worked for EDS (now HP) in various capacities, including LAMP developer, .NET developer, capacity and performance engineer, and as a consulting engineer in the enterprise Web hosting division. Ken began his professional freelance writing career in 2002 writing for computer magazines. Since that time, he's expanded his writing to books (SQL Power! The Definitive Guide, Access 2007: The L Line and Practical Virtualization Solutions), columns for Linux Magazine (Desktop, Virtualization and System Administration), white papers, regular contributions to all major Linux publications, Frugal Server Admin column for ServerWatch.com, and the Linux and Open Source blog/column on Daniweb.com. Ken also hosts a technical podcast show, The Frugal Tech Show, which is one of the most popular and widely sought-after venues for companies of all sizes. For more information, please visit Ken's Web site at www.kenhess.com.