Backup Your MySQL Database

MySQL Server

There are times when it is necessary to backup your database. It should be considered good practice to occasionally backup your database for no other reason than to have a good backup sitting around. Data corruption and data loss are no laughing matter, and can happen at any time for any number of reasons.

Other than backing up your database, it is usually a good idea to have a off-site backup around somewhere (encrypted in the cloud, or on a thumb-drive are not bad ideas). Some server hosts

will backup your data for you on a schedule, and that’s great, but you should also have a copy sitting around yourself as a precaution.

We’re not going to get into encrypting your database, that is your choice, but is probably a good decision if the database holds secure data you don’t want anyone else to have. We’re not going to get into where to put your data either, again, that’s your decision. What we are going to do is back up the database.

One more reason to backup your database yourself is because it’s so easy. Just copy the code below, and have it ready to go in a library, or come back here and get it any time you may need it.

The code explained

CREATE TABLE `projects_04_06_15` LIKE `projects`;
INSERT INTO `projects_04_06_15`
SELECT *
FROM `projects`

What this bit of code says is: Create a table called projects_03_15_14. This is the backup table we’re going to use. It’s the table name, with the date of the backup. This is a good way to keep version control on your database if you ever have to go back and look for something specific. It is also extremely helpful when cleaning up your stash of backups.

The LIKE directive is telling MySQL to create the table identical in every way to the projects table, from keys to overall structure and column names, but without any of the data. The semi-colon tells the system to stop. Then the second part INSERT INTO.... tells MySQL to dump all of the data from the projects table into the backup table projects_04_06_15.

That’s it. And now you have a backup. A new database will appear in your MySQL tables page/column, and you can access it just like you would any other database. At this point, it’s a good idea to Export the database to your computer and stash it wherever it is you choose to stash it.

There’s another way!

An even easier way to do this, if you have local install of MySQL, or have ssh access into the database, is to do a dump.

Dumping your database on the command line is even quicker than the above method and requires fewer steps. After you ssh into the server, or get on the command line use the following code:

mysqldump -u userName -p projects > projects_04_06_15.sql

The mysqldump -u -p tells the system you’re logging into mysql to create a dump-file. projects is the database we’re accessing to create the dump. > projects_04_06_15.sql says to dump the database into this file.

And that’s it. That is how you backup a MySQL database. After you’ve done it a few times, it will become second nature. I cannot express the importance of backing up all of your hard work.

The snippets

CREATE TABLE <table_name_date> LIKE <table_being_copied>;
INSERT INTO <table_name_date>
SELECT *
FROM <table_being_copied>
mysqldump -u <username> -p <table_being_compied> > <path/to/file/name_date>.sql
This entry was posted in Development and tagged , , , , , , . Bookmark the permalink.

Leave a Reply