Backing up your database

While we're on the topic of cron jobs, one system cron job you should consider is backing up your Magento database. As usual, this advice is based on hard-earned experience. In the course of working with Magento, and especially if you have clients who have permissions to add extensions and change configuration, you may need to restore the Magento database to a previous working state.

While most server hosting providers provide backup services, restoring just the Magento database can take a long time as server backups restore all the files on the server, not just your database. Some providers do provide specific database back-ups, but to be safe, it doesn't hurt to schedule your server to do your own back-ups. Additionally, learning how to quickly and easily back-up and restore your database will come in handy, since you should always do a back-up of your database before installing new extensions or significantly changing configurations.

The built-in back-up

Magento does have a back-up function in the backend, under Store | Tools | Backups. While this is one way of backing up your Magento database, there is no built-in restore function. Furthermore, I have had problems in the past using the back-up file to restore, due to foreign-key conflicts. For extra, extra safety, it wouldn't hurt to use this function to create a back-up — especially if you do not have SSH access to your server account — but it is not as easy or quick as the following method.

Using MySQLDump

If you have SSH access to your server, using MySQL's built-in backup and restore functions are both quick and easy. You do need a username and password for your database (which you should have if you installed Magento):

To back up your database, access your server via SSH using a terminal program.
At the prompt, enter mysqldump –u [username] –p[password] –h [hostname] [databasename] > [filename].sql.
In very short order, MySQL will create a file containing a complete restoration script, including all of your database records.

To provide an example for the previous command, let's assume the following:

MySQL username: mageuser1
MySQL password: magepassword1
Host name: localhost
Name of Magento database: magento_db
File name for the dump: magento_db

Using these example values, your command would look like this:

> mysqldump –u mageuser1 –pmagepassword1 –h localhost magento_db > magento_db.sql

To restore your database, a slightly different format is used: mysql –u [username] –p[password] –h [hostname] [databasename] < [filename].sql, or the following to use our above examples:

> mysql –u mageuser1 –pmagepassword1 –h localhost magento_db < magento_db.sql

It's as easy as that!

Setting a cron for back-up

Now that you know how MySQL can dump a back-up of your database, you can use your server cron to do a dump every night, every week, and/or every month, depending on how fervent you want to be about preserving backups.

Every developer will have their own back-up strategies; so, naturally, do I. While our hosting provider does a complete server back-up every night and preserves back-ups for 7 days, I like to augment that with my own database rolling back-up strategy:

Daily backups for each day of the week. Monday's next backup will replace the current Monday backup. Therefore, we will have one backup for each day of the week, but not more than seven at any one time.
Alternating weekly backups. In essence, we save a backup every other week, so that we have two weekly backups.
One monthly backup. So far, after 15+ years of doing this, we've never had to revert to a monthly backup, but I certainly like having it there nonetheless.

Note

One note here: if you can, you may want to consider dumping these to a computer other than your Magento server. Since our hosting provider does off-site backups, I feel quite comfortable backing up our Magento databases onto a local machine at our office. The chances of all three locations, our hosting provider's facility, the off-site back-up facility, and our office, all burning down at the same time (I'm sure)' is pretty darn remote, especially since all three facilities are spread across a continent.

To schedule your dumps as crontabs, invoke the crontab, as you did earlier in this chapter, and create as many jobs as you feel you need (if you're dumping to another server, you need to, of course, schedule these jobs on the machine to which the backup is to be dumped).

Create a new schedule according to your desired frequency (every day, week, month, and so on) and enter your MySQL dump command, as used previously. For the file name, though, use a name unique to the purpose of the dump. For example, for a dump on Monday, you might use magento_db_Mon.sql; for week 1, magento_db_week_1.sql. In this manner, the next dump for a given script will overwrite the previous dump without filling up your hard drive with dump files.

Table of Contents for Magento 2 - Build World-Class online stores

Backing up your database

The built-in back-up

Using MySQLDump

Setting a cron for back-up

Note

Table of Contents for
Magento 2 - Build World-Class online stores