How to Perform Scheduled MySQL Backups Using mysqldump and Cron

When operating on data, mistakes can cause corruption or even database crashes, so regular backups are essential. This guide presents several ways to schedule MySQL database backups.

1. Using the mysqldump command

The mysqldump utility can export database contents from the command line. A basic usage example is:

#MySQLdump常用
mysqldump -u root -p --databases database1 database2 > xxx.sql

2. Common mysqldump examples

mysqldump -uroot -p123456 -A > /data/mysqlDump/mydb.sql

mysqldump -uroot -p123456 -A -d > /data/mysqlDump/mydb.sql

mysqldump -uroot -p123456 -A -t > /data/mysqlDump/mydb.sql

mysqldump -uroot-p123456 mydb > /data/mysqlDump/mydb.sql

mysqldump -uroot -p123456 mydb -d > /data/mysqlDump/mydb.sql

mysqldump -uroot -p123456 mydb -t > /data/mysqlDump/mydb.sql

mysqldump -uroot -p123456 mydb t1 t2 > /data/mysqlDump/mydb.sql

mysqldump -uroot -p123456 --databases db1 db2 > /data/mysqlDump/mydb.sql

3. Restoring a MySQL backup

Two common ways are using the MySQL client or the source command:

mysql -uroot -p123456 < /data/mysqlDump/mydb.sql

mysql> source /data/mysqlDump/mydb.sql

4. Bash script for rotating backups

The following script backs up a specified database, keeps only the most recent 31 backups, and logs actions:

#!/bin/bash
# Save up to 31 backups
number=31
# Backup directory
backup_dir=/root/mysqlbackup
# Date stamp
dd=`date +%Y-%m-%d-%H-%M-%S`
# Tool and credentials
tool=mysqldump
username=root
password=TankB214
# Database to back up
database_name=edoctor

if [ ! -d $backup_dir ]; then
    mkdir -p $backup_dir;
fi

$tool -u $username -p$password $database_name > $backup_dir/$database_name-$dd.sql

echo "create $backup_dir/$database_name-$dd.dupm" >> $backup_dir/log.txt

delfile=`ls -l -crt $backup_dir/*.sql | awk '{print $9}' | head -1`
count=`ls -l -crt $backup_dir/*.sql | awk '{print $9}' | wc -l`

if [ $count -gt $number ]; then
    rm $delfile
    echo "delete $delfile" >> $backup_dir/log.txt
fi

5. Using crontab to schedule the script

Linux uses the cron daemon to run scheduled tasks. The service can be controlled with:

service crond start   // start service
service crond stop    // stop service
service crond restart // restart service
service crond reload  // reload configuration
service crond status  // view status

The crontab file defines when commands run. Each line contains six fields: minute, hour, day‑of‑month, month, day‑of‑week, and the command.

minute hour day-of-month month day-of-week commands

Special symbols:

* – every possible value

/ – step values (e.g., /5 means every 5 units)

- – range of values

, – list of discrete values

Common crontab options:

-l   // list current crontab
-r   // remove current crontab
-e   // edit current crontab

6. Example cron entries

0 2 * * * /root/mysql_backup_script.sh

0 6 * * * echo "Good morning." >> /tmp/test.txt

0 */2 * * * echo "Have a break now." >> /tmp/test.txt

0 23-7/2,8 * * * echo "Have a good dream" >> /tmp/test.txt

0 11 4 * 1-3 command line

0 4 1 * 1-3 command line

0 4 1 1 * command line

5,15,25,35,45,55 16,17,18 * * * command

0 15 * * 1,3,5 shutdown -r +5

10,40 * * * * innd/bbslink

* * * * * /root/mysql_backup_script.sh

These examples demonstrate how to run commands at specific times, such as hourly, daily, weekly, or monthly, and how to manage backup rotation automatically.