Scheduling MySQL Database Backups Using Cron

In this post, I’ll review steps for creating a MySQL database backup script and running it with crontab.

How to get a database dump with MySQL from a CLI

This is the first step to preparing a script. We can get a quick dump with the mysqldump command from a CLI.

mysqldump is a command-line utility that is used to generate the logical backup of the MySQL database. It produces SQL statements that can be used to recreate the database objects and data. The command can also be used to generate the output in XML, delimited text, or CSV format.

Some important parameters are

• -u [user_name]: This is a username to connect to the MySQL server. To generate the backup using mysqldump, use Select to dump the tables, Show View for views, and Trigger for the triggers. If you are not using the single-transaction option, then Lock Tables privileges must be granted to the user.
• -p [password]: This is the valid password of the MySQL user.
• [option]: This is the configuration option to customize the backup.
• [database name]: This refers to the name of the database that you want to make a backup of.
• [table name]: This is an optional parameter. If you want to take the backup-specific tables, then you can specify the names in this command.
• < or >: This character indicates whether we are generating the backup of the database or restoring the database. You can use > to generate the backup and < to restore the backup.
• [dumpfilename.sql]: This is the path and name of the backup file. As I mentioned, we can generate the backup in XML, delimited text, or a SQL file so we can provide the extension of the file accordingly.

You can check other parameters from here.

Create a script for getting periodic MySQL database dumps

There are different ways to run the mysqldump command from a CLI periodically. I prefer writing a bash script.

To properly run this script, you need to replace DB_NAME, DB_USER, and DB_PASSWORD.

If you wish, you can change the backupdir variable to store the backup in a different location.

How to run the backup script periodically

You can use any CI tool, GitHub actions, etc. For basic usage, I prefer crontab.

The crontab is a list of commands that you want to run on a regular schedule, and also the name of the command used to manage that list.

This command is edit crontab.

crontab -e

After that, add this command:

0 8 * * * /ANY_PATH/backup.sh > /var/log/daily-backup.log 2>&1

This command will run every day at 08:00 and put the output in /var/log/daily-backup.log.

You can use crontab.guru to change the time period.

If you get an error, you can use this command to check the cron logs.

grep CRON /var/log/syslog

To learn more, check out these resources:

• ‘crontab’ in Linux with Examples — GeeksforGeeks
• MySQL 8.0 Reference Manual :: 4.5.4 mysqldump — A Database Backup Program
• Why is my crontab not working, and how can I troubleshoot it? — Server Fault
• How to backup and restore MySQL databases using the mysqldump command
• The complete mysqldump guide (with examples)

If you like to get more updates from me, 
please follow me on Medium and subscribe to email alert.

Implementing a Service Layer in PHP

Why did I switch to using Warp Terminal?

Installing Puppeteer to DigitalOcean