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.
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
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
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.
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