William Harris @ Powered By Geek

So my first project with PBG is to get a more automated backup system going. I find out that they’re basically using cpanel and other variants to do manual backups on development projects. This works well, but it takes a lot of time, and you have to do it every day. Computers were designed to solve these kinds of problems…in fact, I hope that some day software can solve ALL of my problems, leaving me with nothing else in my life but blogging about the accomplishments of my software like the lady next door does with her kid. Unfortunately, that day is not here yet, so I’m still forced to do at least SOME work.

So, how does MySQL compare to MSSQL in backup strategy?

In MSSQL, you have a few options…you can use MSSQL’s built in backup and restore manually via the GUI or TSQL, you can detach and copy the databases in the file system, you can set up a maintenance plan, or you can use a third party backup. You can also get exotic and replicate your database around for instant failover and various other benefits of replication goodness.

MySQL has pretty much all of these options available, but like many things in the unix world, what exists are pieces of the puzzle which you as the admin are encouraged to assemble. It helps to think of Windows administration like a prefab piece of office furniture. There are a lot of parts, but in the end your destiny is fulfilled in Redmond years before you configure your system. The system is designed to work in a certain way. This way is good, and very smart people spend years designing it…but it doesn’t always work in every situation. Unix is more like a giant box of legos. You have to apply yourself creatively, but you can do anything you want. MySQL, for the most part, follows this design philosophy.

Sure, there are missing features that you might have thought every RDBMS should have, but don’t forget that Oracle and DB2 guys say the same thing about MSSQL, so don’t get cocky.

My first task is to use the web and any documentation to find the answer to a single question that should be first on the list of anyone who is working with a system for the first time :

What are my options?

It turns out that in MySQL, we have the following methods of backup :

• Use mysqldump. Almost everyone uses this option, and this is typically the type of file you receive when using things like cpanel or MySQL management tools to do your backups. This interesting utility dumps a .sql file containing the ANSI SQL statements required to recreate your entire database from the ground up (with the exception of the actual creation of the database, which must be manually edited into the SQL file or completed manually using CREATE DATABASE ) This is a lot like using SSMS to “script database to” file, but it actually includes the data too! So cool!! This feature kicks major ass, and you can do all kinds of interesting things on restore using unix tools like grep…wow! One word of warning with this software, the restore takes forEVER…we’re talking hours on a server class machine for a database only 1GB in size. Don’t use this if you can’t deal with downtime.
• Use mysqlhotcopy. This unix only script comes installed with MySQL, and requires Perl (a programming language commonly used on unix systems.) This script essentially performs lots of locks on the tables and databases, and copies the files in that way. This option is significantly faster than mysqldump in both backup and restore, but it does do a lot of locking, so if you have a flow of constant updates this is not a good choice.
• Stop the MySQL services and copy the database files manually, or you could write a shell script.
• Replicate your data to another server! This one is way out of the scope of this article, but its a really cool solution.

Obviously things are a little more complicated than my distillation above, but I think that my evaluation of the backup possibilities for most admins after going through hundreds of discussion threads, newsgroups, and webpages is fairly complete. That being said, I’d love to be corrected, and I’ll even give anyone who offers some good information full credit on this page.

So, back to my problem, and since its all about me…I wanted to use mysqldump because I don’t want excessive lockouts during my backups, and we are web based so downtime isn’t a huge problem in the event of necessity for a restore. I wanted daily, weekly, and monthly history, and I wanted some kind of reporting so I could keep an eye on the backups without having to log into the machine every day.

In MSSQL, this is pretty easy. We create 3 maintenance plans with automated cleanup and use dbmail to send out notifications in the event of a problem. In MySQL, this is even easier.

You download an awesome script written by a dude named, for some reason, wipeout. It is called automysqlbackup and you can find it here. This script gets modified just a bit (full instructions inside the file) and copied to your daily cron jobs folder on your MySQL machine. I changed mine a little to include some FTP copying so that it’d move the files off the local machines, but the script even has support for pre script and post script commands. I encourage anyone using this script to send a donation, I’ve sent mine!! Go to the sourceforge link above and click the donate button to send the author a thanks.

Once you’ve done that you probably want to run through the process manually, and check out the layout of the files mysqldump and mysqlhotcopy generate.

Check out the documentation for mysqlhotcopy if you’re interested, but lets run through a quick mysqldump exercise. Note that the mysqldump documentation is also really valuable.

Open a terminal window and type the following command…this works on both Windows and Unix:

mysqldump -u username -p databasename

note that the -p does not have a password following it…this will prompt you for a password. If you want, you can include the password there, but you have to use the –databases option to specify databases. If you’re on Windows and you’re not sure what your username is, then you probably remember putting in a password during the setup of MySQL, but not a username!! The username is “root” Please go create a new user in MySQL asap and use that one instead! If you left the root password blank, fix that.

This will output the .SQL file to your terminal window, if you have a small dataset (like a few rows in a DB you use for testing) this is perfect, for anything bigger, send the output to a file like so :

mysqldump -u username -p databasename > backup.sql

To restore, use the following command :

mysql -u username -p databasename < “path”

At this point, you can check out the contents of the SQL file, and even edit it to include additional data. This is good stuff…MySQL isn’t half bad!

Tags: backups

This entry was posted on Saturday, October 6th, 2007 at 1:12 pm and is filed under MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.