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