Published on MacDevCenter (
 See this if you're having trouble printing code examples

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

Accelerating Execution

Author's Note: Shortly before this article went to press, Apple released Xcode 2.2. Although I have not yet verified it myself, I've been told the update fixes the php_version.h problem I workaround in the text. If your php_version.h looks like the one I've included, you can continue installing eAccelerator as normal.

There's an insane number of factors involved when determining how "quick" your website is: hardware, network, software configuration, page and code design, user expectations, and more. We can score a number of Apache-related tips from its own performance tuning page, but there are dozens of articles with suggestions on writing better PHP. Instead of better coding, we're going to focus on faster PHP execution, regardless of the code's quality.

To do this, we're going to install a PHP module called eAccelerator. This module (and others like it) "increases the performance of PHP scripts by caching them in a compiled state, so that the overhead of compiling is almost completely eliminated." If you've used PHP caching systems before, eAccelerator is a continuation of Turck MMCache, no longer in development. Go ahead and grab the latest source code for eAccelerator (0.9.3 as of this writing) and save it to your desktop.

Before we can compile eAccelerator, however, we have to fix a minor bug within the Tiger Developer Tools (up to and including Xcode 2.2 Developer Preview 1). See, eAccelerator is going to be compiled with information from /usr/include/php/main/php_version.h, but that file suggests that we have PHP 4.3.10, when we actually have version 4.3.11. We'll need to change this to represent the correct information. First, we'll make a backup, and then twiddle the file's "write" bit:

morbus@:~ > cd /usr/include/php/main/
morbus@:~ > sudo cp php_version.h php_version.h.bak
morbus@:~ > sudo chmod 644 php_version.h

Next, we'll need to change the file so that it looks like the below:

/* automatically generated by configure */
/* edit to change version number */
#define PHP_VERSION "4.3.11"

You'll need some sort of authenticating text editor (I will always prefer BBEdit) to save your changes. With those tweaks done, we can go ahead and compile eAccelerator. Move to your desktop, or wherever else you downloaded the source to, and run through the following:

morbus@:~ > tar xvzf eaccelerator-0.9.3.tar.gz
morbus@:~ > cd eaccelerator-0.9.3
morbus@:~ > sudo /usr/bin/phpize
morbus@:~ > ./configure --enable-accelerator=shared
morbus@:~ > make
morbus@:~ > sudo make install

Installing shared extensions: 

These commands compile and install eAccelerator, but we still have to tell PHP that it should load and configure this new module. A few articles ago, we replaced our /etc/php.ini file with a more secure version, and we'll be going back to this file to make some eAccelerator-related changes. Open that file up in your favorite text editor, look for extension_dir, and make the following change (you'll notice we're setting extension_dir to the same value that our make install reported above):

; Directory in which the loadable extensions (modules) reside.
; extension_dir = "./"
extension_dir = "/usr/lib/php/extensions/no-debug-non-zts-20020429/"

Next, look for a block of configuration that starts with:

; Dynamic Extensions ;

And add the following somewhere after it:


Save the file. Astute readers will notice /tmp/eaccelerator as a value for the eaccelerator.cache_dir parameter, and we'll need to manually create that directory and give it proper permissions:

mkdir /tmp/eaccelerator
chmod 0777 /tmp/eaccelerator

Restart Apache (with sudo apachectl restart) and reload the MediaWiki site we created last article. You'll know our new eAccelerator is working when ls /tmp/eaccelerator/ returns a number of files of the following form--these are cached compilations of the various bits of PHP code that power MediaWiki:


Final Thoughts

With a few more helper monkeys in our entourage, what's next? Our treasure map suggests "content management systems:" far more than blog software, but far less than handcoding everything yourself. Until then, don't hesitate to ask questions, wax poetic about future desires, or lament the errors.

Kevin Hemenway is the coauthor of Mac OS X Hacks, author of Spidering Hacks, and the alter ego of the pervasively strange Morbus Iff, creator of, which bills itself as "content for the discontented."

Return to the Mac DevCenter

Copyright © 2009 O'Reilly Media, Inc.