One of the most important parts of retaining the integrity of your data is the MySQL backup procedure for your database.
Say for example, you are backing up your website files as part of a regular process. A simple image of the site won’t help if you don’t have the database files stored as a flat file. A backup will not have a pure record of what is in your database and its current state. While technically it might be possible to recover a database from the image, there’s an extremely high likelihood that it would become corrupt and unusable. Best practices mean converting your MySQL database into a format that can be stored and read and easily reloaded for a MySQL backup.
Below is a brief guide on how to manually create a backup of your MySQL database, scheduling it, and then restoring it whenever the necessity to do so arises for a MySQL backup.
Creating the backup file
Typically, you will need to convert your MySQL backup database into a flat file. Doing this with a MySQL database isn’t terrifically difficult. First of all you need to create a mysqldump file. At the most basic level, you can do this using a graphical interface tool such as phpMyAdmin, or MySQL Workbench, which is a relatively simple process.
There are several point and click tools within these that make it easy to create a MySQL Backup dump file on the fly. However, it’s generally a good idea to be familiar with the command-line, to enable including these in shell scripts or batch files. In *nix and Mac systems, the MySQL dump process uses the following syntax (we’ll get into windows systems a little later, but the command line is the same, except for the datetime stamp):
$ mysqldump -u [uname] -p [pass] db_name > db_backup-$(date +%d-%m-%Y %H %M %S).sql
This will create a mysqldump file with the current structure and contents of your MySQL backup, with the datetime stamp identifying when it was created. The created SQL file can easily be reloaded at will.
All you need to do is to replace [uname] with your username and [pass] with your password, and db_name with the name of your database. Pretty easy, right? If you are testing this out on a local database, and you have no password set, you can just remove the -p option. Try it out for this MySQL backup!
There are a number of other useful options for mysqldump you can use in your command line. For instance if you have multiple databases you’d like to backup instead of passing the db_name as above, you instead could use the option -all-databases which will include all databases in your dump file. This way, if you have more than one database you’d like to backup, you can take care of them all at once. This makes some sense as if something happens to your server, it might affect all of your databases; this way if you have one file you can load everything back very quickly. (If you are concerned about being able to restore all or none, you can have multiple processes that can be run at various intervals; there’s really no reasonable limit. But we’re getting ahead of myself here; we’ll cover the timing below.)
One of the best, and most powerful options to use, is –opt. (It’s actually enabled by default)
This is essentially shorthand for the following:
–add-drop-table – creates a drop table before each insert statement (this will prevent accidental duplicates, or stop your load if a table already exists; though be sure you are actually including the data before doing this)
–set-charset – sets the default charset
–add-locks – creates faster inserts for reloading the database
–create-options – create table options
and several more (to see more documentation on these, you can find these in the official MySQL documentation: https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html
While –opt is enabled by default these can be overridden easily by using –skip-opt to turn off several default settings.
Scheduling Your MySQL Backups
Of course, this will only happen point-in-time, so you will likely need to have this done at regular intervals. The easiest way to do this is by using a cronjob. You can create a shell script by placing above code into a blank file and name it backup.sh
To run it every day at 2 AM, you’d just need to create cronjob and use the following syntax:
0 2 * * * /bin/sh backup.sh
In whichever folder or directory you’ve chosen to save these files, you will see a different file (that’s why we chose the datestamp) for each dump. You can choose this to run more often if you are concerned with losing a day’s worth of data.
Also, remember what we talked about before regarding multiple versus one database? You can run multiple cronjobs in the same crontab file. Or if you prefer, you can put all of the backups in your shell script; you can choose to configure it any way you like. The only limit is your imagination with MySQL backups.
You can run cronjobs in *nix systems as well as Mac. If you are running a windows server, the process is a little bit different, but the basic commands are the same.
Simply create a batch file, by opening a text file, and entering something like this into it (note that the date formats are a little different for Windows):
cd C:\backups
$ mysqldump -u [uname] -p [pass] db_name > db_backup%DATE:~10,4%%DATE:~4,2%%DATE:~7,2%%time:~0,2%_%time:~3,2%_%time:~6,2%.sql
Then name the file something like backup.bat. You can test it out by double-clicking on it, and you’ll see it creates a file with the sqldump in the specified directory. Instead of cronjobs, you can use the Windows task scheduler, to specify when it will run. And yes, you can do the same thing with different jobs at different times, just like with cronjobs. Just remember that for our purposes here, shell script = Windows batch file. Crontab = Windows Task Scheduler.
Restoring your Database
“Okay great,” you say to yourself. “I have a copy of my database. Now what?” Reloading your MySQL backup database from your mysqldump file is a relatively simple process.
There are a number of ways of handling this; whichever one is your choice, and depending on whether or not you included a create database option
If you are using the shell, and you need to first create the database, you use this:
And then (or if you already have a database created):
Alternately, if you are already logged into mysql you can use the following:
Those are the basics for manually backing, setting a schedule and recovering your database
There are a few other things you might want to consider, such as regularly moving copies of the mysqldump file to another location other than the server itself, so that if something were to occur to the device, you would still have a copy. Also, depending on the size of the MySQL backup database, you may wish to clear out old copies of the database so that it doesn’t take up too much space. Small databases typically take up less than a Mb, but even these can add up over time. In never hurts to do regular maintenance. How far back you wish to save files is up to you.
As you can see, backing up a MySQL backup database is not particularly difficult. This is just one of the ways of handling it, but this should give you a basic idea of how to get started.
