Backing Up and Restoring MySQL Databases on your PC

MySQLRecently it became obvious that I really didn’t understand how MySQL stores databases on my PC and although I backup frequently, the files I thought were the databases were only the table structures. Not only that but restoring them was not a matter of simply copying the files back to my hard drive.

I was able to restore the databases, but discovered there are some pro’s & con’s to relying on a typical pc backup.

Here’s what I discovered:

Where are the databases stored?
MySQL stores databases in the data folder which is a sub folder of the mysql folder. The file name and location of  this folder will vary depending on your installation. Common locations are: xampp/mysql or wamp/bin/mysql

Each database will have it’s own sub-folder (folder name will match the database name) that stores the tables and database description, but DOES NOT contain the actual data.

The data for ALL databases appears to be stored in a single  file named id_data1, so be sure to back up this file and all the other files in the data folder.

The downside I found to this type of backup is that you can’t restore a single database or table by simply copying the files from your backup on to your PC. I tried and it just causes mysql to crash.

How to restore databases – fondly known by me as the “all or nothing method”
I lied earlier –  you can simply copy the files from your backup onto your pc, but you have to replace the entire data folder. If you try to copy individual files it will cause mysql to crash. Also, before you copy the files be sure to stop the mysql server and then restart it.

The downside to this is you have to restore all the databases not just the ones you are having problems with. To selectively restore a single table or database try exporting the current database to an SQL file, then import what you need from the SQL file after your restore the backup folder or restore the backup on a different system and export what you want to an SQL file then import it to your current system.

Conclusion
This backup method is fast, simple and reliable, but it may not be a good solution if you need to restore selective tables or databases.

Let me know if you have a better simple backup method.

About this article
This article is specifically focused on using a typical pc style backup. There are many other methods which give more options, but in most cases are not as fast or simple. The most common method is with phpMyAdmin. This is a great open source tool and is provided on most shared hosting plans. Check out this tutorial:  phpMyAdmin Backup Tutorial


DLL Written by: Don Lyckman
Don Lyckman is a Web Developer/Designer
MySQL

If you enjoyed this post, please consider leaving a comment or subscribe to the feed and get future articles delivered to your feed reader.

Comments

4 Responses to “Backing Up and Restoring MySQL Databases on your PC”

Leave Comment

(required)

(required)