MySQL potpourri (Backup, restore, and migration)

2008-03-16 22:35 by Ian

If you are using a PHP application then you almost always have support for MySQL. All else being held equal, MySQL will be a much better choice for anyone using PHP. MySQL is open and fast. It does not have some of the nifty features MSSQL has, but for the transparency, wide support, configurable deployment and low cost, I would highly recommend it over the closed and pricey MSSQL. The rest of this digression will focus on MySQL, because I am an unrepentant open-source partisan. :-)

All low-level work on the database will be aided by some sort of front-end program. My personal preference is HeidiSQL:

But there is also MySQLYOG:

And also MySQLAdministrator (which should be considered mandatory if you are working with MySQL DBs):

MySQLAdministrator is useful because it can give you some statistics and tools that are either absent or buried in the other two tools. My general pattern of use is to use MySQLAdmin for user manipulation and testing, and then use HeidiSQL for running scripts, migrating DBs and editing the DB itself.

Migrating a MySQL DB between versions:
This will also be the procedure for moving or creating a backup of a MySQL database. I will use MySQLAdministrator for this demonstration, as this procedure is easiest in that package.

The first step is to open MySQLAdmin and connect to the DB that you want to use as your source. You can use the MySQL Admin tool in the control center to find the four necessary parameters for doing this (host, DB, user, password)...

After connecting, you should see a screen that looks something like this…

Second, we will export the database to a SQL file. In the left hand menu, choose “backup” and then create a new backup project. You will see your database in the lefthand column. Click it and click the right-facing arrow to tell MySQLAdmin that this is the DB you want backed up. Select whatever tables you want included. Your screen should now look something like this…

If you feel like learning more, you can also set advanced options or even a schedule to do this regularly. This is one of the reasons that you should like MySQL more than I do: You don’t need to pay $5 to backup or restore the DB. Additionally, you don’t have to worry about the convoluted user permissions that routinely cause customers to call support for re-associations that they cannot accomplish on their own. I keep a backup of my DB in this fashion (since before I became an employee), and have never had to call support for help with it. Because that’s just the way MySQL rolls.

After you have your options set the way you want them, click the execute backup now button. You will be prompted for a filename. After it is given, your selected DB tables will be copied to the specified file.

What the resulting file represents is an executable script that creates tables and inserts data into them. You can run this script in a DB front-end (like HeidiSQL) to acheive a restore. Most users will want to use the same software that created the file to do the restore, but just be aware that you are not constrained with respect to the package you use to act on the resulting SQL file.

If your only intent was to create a backup of your DB, you can stop reading. The SQL file is the backup.
If you are migrating a DB, you are half-way done. The next step would be to create the new target database (MySQL5). Do this in the control center and make note of the values that you need to use to connect to it. Repeat the earlier steps for connecting to the target DB.

You should at this point be connected to your new (empty) target database. Instead of using backup, now we will use the restore tool. All this tool will do is run the SQL file against your new DB, creating and populating all the tables in the process. Select your SQL file. If the target DB has the same name as the source DB, you can use all the defaults. If, on the other hand, your target DB is named diferently from your source, you should specify the name of the target DB in the “Target Schema” option.

Click “Start Restore” to let the restore run.

You have just migrated your database. If you are just trying to restore a backup, you should follow essentially the same procedure, but your target DB will be the same as your source DB that made the backup file in the first place.