JonCellini.com

I migrated the MySQL database that serves as the backend for this site as part of the recent upgrade to the box hosting this site. I had been putting off upgrading since I knew I was going to have to do this and my MySQL DBA skills are rather weak.

Fortunately after a bit of digging around in the MySQL documentation I figured out the migration steps. They whole process ended up being pretty straightforward and painless.

For the next time I happen to need to do this here is my mini-HOWTO:

Backing Up The Current Database

1. Create a database dump

mysqldump --opt -u USERNAME -p databasename > /path/to/yourbackupdirectory/dump.sql

This prompts you for your MySQL password and then creates the dump file.

2. Verify that dump.sql was created

3. tar/gzip compress the dump.sql

4. Move the compressed dump.sql file to the new server

Restoring The Database

5. untar/unzip uncompress the dump.tar.gz file

6. Create a new database called newdbname

mysqladmin create newdbname

7. Restore your dump file into the newdatabase

mysql -u USERNAME -p newdbname < /path/to/new/dump.sql

This will prompt you for your MySQL password and then import the database dump file into the new database you created in the prior step

8. Double check the users / permissions on the new MySQL server and create any user accounts that are necessary for your application to function.

Now you should be good to go.

Tech