Quickly backup and restore your MySQL/MariaDB database


It's very important that you have a backup plan of your MySQL database. Loosing even a single day's worth of data can be frustrating and critical to your business. If you have shell (command-line) access to your database server, here is a very easy and quick backup method: Use mysqldump.

Creating the backup

To create the dump, use this command from your shell/command-line:

mysqldump -uusername -ppassword -–default-character-set=utf8 -N database > backup.sql

Where:
  • username is your MySQL username.
  • password is your MySQL password.
  • database is the name of the database you want to backup.
  • backup.sql is the full path to the mysql dump file that will contain the backup.
The above command is very efficient and takes very little time to execute. Notice that the character set specified is UTF8. Even if your database is encoded in another character set, I recommend using UTF8 as I found it to work fine with any database without having to make any guesses.

Restoring the database from your backup

Restoring a database from a backup file is pretty easy. First, empty the database to avoid error messages, then run this command:

mysql -uusername -ppassword -–default-character-set=utf8 database < backup.sql

Where:
  • username is your MySQL username.
  • password is your MySQL password.
  • database is the name of the database you want to restore.
  • backup.sql is the full path to the mysql dump file that you want to restore from.
You can also use the above command to copy your database from one server to another. This command is very efficient as well and executes in a very short time.

Automating the backup

If your server is a linux server and you have edit access to the crontab file, you can automate the backup so it's performed daily at a scheduled time (preferably a time when your server has no active users) .. Here is an example line you could add to your cron:

8 5 * * * mysqldump -uusername -ppassword --default-character-set=utf8 -N database > /db-backup/backup.sql

The above line would automatically create a backup of your database every day at 5:08am and save it into the directory /db-backup on your server (you should create that directory and make sure its owner is the same user editing the crontab file).

The above cron line would create only one backup file that will be updated daily. You could take that approach a step further and create a weekly rotating backup. That is, you can create a scheduled backup that creates a separate backup file for each day of the week so that you can restore your database to its state up to 7 days ago. Here is the crontab code you can use to do this:

8 5 * * 0 mysqldump -uusername -ppassword --default-character-set=utf8 -N database > /db-backup/backup.sunday.sql
8 5 * * 1 mysqldump -uusername -ppassword --default-character-set=utf8 -N database > /db-backup/backup.monday.sql
8 5 * * 2 mysqldump -uusername -ppassword --default-character-set=utf8 -N database > /db-backup/backup.tuesday.sql
8 5 * * 3 mysqldump -uusername -ppassword --default-character-set=utf8 -N database > /db-backup/backup.wednesday.sql
8 5 * * 4 mysqldump -uusername -ppassword --default-character-set=utf8 -N database > /db-backup/backup.thursday.sql
8 5 * * 5 mysqldump -uusername -ppassword --default-character-set=utf8 -N database > /db-backup/backup.friday.sql
8 5 * * 6 mysqldump -uusername -ppassword --default-character-set=utf8 -N database > /db-backup/backup.saturday.sql

Now you can rest assured that if anything goes wrong with your database, you can always restore it to any point in the past up to 7 days old. And it's all being done automatically! Now you have no excuses! Start making a regular backup of your database from today!

Migrating your database to a new server

This tutorial also applies when you wish to migrate to a new server. Simply back up the database on your old server, and upload the SQL dump file to the new server. Create a destination database on the new server, then execute the uploaded dump file in the new database. You should also recreate users and permissions from the old MySQL server to the new one.