oreilly.comSafari Books Online.Conferences.


AddThis Social Bookmark Button

Web Apps with Tiger: Backups and Speed

by Kevin Hemenway
Your Life in Web Apps

After our maiden voyage, we paused for some peace of mind and data, then explored a type of software called a wiki, which allows anyone to edit anything. Whither does our journey take us today? The in-depth dangers of a content management system? The untamed wilds of forum software? The upselling madness of an ecommerce site? No, dear adventurer: today, we return to the helper monkeys.

Maintaining and Optimizing Your Databases

With all of this production-quality stuff going on, it's a good idea to make sure things are always smelling as fresh as a summer breeze. If you've run a Windows machine in the past, you may recall the need (nay, recommendation) for regular defrags or registry cleaning. I dunno about you, but even though nothing seemed better, I sure loved looking at all those colored blocks being moved around with Norton's Speed Disk. Thankfully, we don't have to worry about this with OS X, but we can do some similar "preventative optimization" with our databases (though, sadly, with no purdy colors).

A word of ambivalence: you don't have to run these commands, any more than you have to defrag a Windows hard drive on a regular basis. In most cases, these are placebos and panaceas: fun to do and fun to say, but they usually attempt to solve a problem that rarely exists. Running them regularly certainly doesn't harm anything, however, so I'll always err on the side of false hope and great expectations.

To get started, we're going to use the included mysqlcheck utility--you can get a mouthful of its capabilities by running it with no additional parameters. Be sure that your MySQL server is started, as mysqlcheck will only function when it is. (Another utility, myisamchk, can do similar things with the server shutdown, but is not described here.) Our first attempt will repair (-r) all of the tables of all of the databases (-A) on our server by issuing a MySQL REPAIR TABLE statement:

morbus@:~ > mysqlcheck -p -A -r
manifest_drupal.access                             OK
manifest_drupal.accesslog                          OK
manifest_drupal.aggregator_category                OK
manifest_drupal.aggregator_category_feed           OK
. . .

Another flag to mysqlcheck I use frequently is -o, which uses the OPTIMIZE TABLE SQL statement, explained thusly: "OPTIMIZE TABLE should be used if you have deleted a large part of a table or ... have made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB, or TEXT columns) ... You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file." Unless you know databases in depth, you probably won't fully understand what that means. That's quite alright--just run the following, and magical things will happen:

morbus@:~ > mysqlcheck -p -A -o
manifest_drupal.access                             OK
manifest_drupal.accesslog                          OK
manifest_drupal.aggregator_category                OK
manifest_drupal.aggregator_category_feed           OK
. . .

Dumping Database Backups

I am going to assume, nay, implicitly trust, that you've some sort of backup scheme already in place. Whether it's rsync, Backup 3, FTPing to another server, or weekly burns, you need something to prevent your production web server (or in-progress sandbox) from being destroyed by an errant failure or lack of judgment. Yeah, yeah, I know: you've heard this a thousand times before. Even so, it amazes me how many folks still don't check for cancer nor replace default config files that admonish against their use (we covered that in part two). Meatware, software, or hardware: bad things happen. Ensure they don't. This has been a public service announcement: Make Mine Morbus!

With that said, backing up your MySQL databases, whether those of your own creation or the one we made for MediaWiki, isn't as simple. Yes, you could add /usr/local/mysql to your backup, which would cover the entire installation plus the databases, but that's less than ideal. Here, the backed-up databases are the binary versions: suitable for the specific version of MySQL you've installed, but not much else.

The proper approach is with a utility called mysqldump, which was installed along with MySQL. This utility generates a text file of all of the SQL statements that were used to create your database. Anytime you create data with an application such as MediaWiki, you're sending dozens of SQL statements to your database. mysqldump spits out all of these statements, creating a picture-perfect and plain text version of your content, suitable for importing back into MySQL at a later date.

You can issue mysqldump --help in your Terminal to see the boatload of options available to you. The manual (or "manpage") is accessible with man mysqldump. The quickest way to get started is with the command below, which exports all of the SQL statements of db_name into a file called db_name.sql.

morbus@:~ > mysqldump -p db_name > db_name.sql

In MySQL 4.1 (the version we installed), passing no other option to mysqldump defaults to using the flag --opt, which is just perfect for our needs (as it's a shortcut to a number of common options). Open up the newly created db_name.sql and take a look: you'll see hundreds and hundreds of lines of SQL that make up your entire database. If the need ever arises, you can re-import this text file back into MySQL, creating an exact duplicate of the database at the time you ran the mysqldump command (naturally, this that means having up-to-date dumps will be important):

morbus@:~ > mysql -p db_name < db_name.sql

Other possibilities include exporting all of your MySQL databases (as opposed to just one, above) to a single file, narrowing down the export to just a single table, or compressing the results:

morbus@:~ > mysqldump -p -A > all_databases.sql
morbus@:~ > mysqldump -p db_name table_name > db_name.table_name.sql
morbus@:~ > mysqldump -p -A | gzip -9 > all_databases.sql.gz
Your Life in Web Apps

Essential Reading

Your Life in Web Apps
By Giles Turnbull

Have you dreamed of a simpler life where web apps and a browser meet all of your computing needs? All you need is a network connection. In this PDF Giles Turnbull introduces you to a day of web apps-only, then he surveys the best and most innovative web apps from the current crop available right now. He also addresses practicality, security issues, and backup strategies for living the web app life. Is it really possible? This PDF will help you decide.

Read Online--Safari
Search this book on Safari:

Code Fragments only

Pages: 1, 2

Next Pagearrow