Web Apps with Tiger: Backups and Speedby Kevin Hemenway
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:
mysqlcheck -p -A -rmanifest_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:
mysqlcheck -p -A -omanifest_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
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):
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:
mysqldump -p -A > all_databases.sqlmorbus@:~ >
mysqldump -p db_name table_name > db_name.table_name.sqlmorbus@:~ >
mysqldump -p -A | gzip -9 > all_databases.sql.gz
Pages: 1, 2