Automate MySQL Backups with mysqldump and Cron: Complete Guide

MySQL Scheduled Backup with mysqldump and Cron

During data operations, errors or database crashes can occur; effective scheduled backups protect the database. This article covers several methods for MySQL scheduled backup.

1. mysqldump Command Backup

MySQL provides the mysqldump command-line tool for exporting database data and structure. Basic syntax:

mysqldump -u root -p --databases db1 db2 > xxx.sql

2. Common mysqldump Examples

Eight practical examples demonstrate different backup scopes:

Backup all databases (data + structure): mysqldump -uroot -p123456 -A > /data/mysqlDump/mydb.sql Backup all databases structure only (-d):

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

Backup all databases data only (-t):

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

Backup single database (data + structure): mysqldump -uroot -p123456 mydb > /data/mysqlDump/mydb.sql Backup single database structure:

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

Backup single database data:

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

Backup multiple tables (data + structure):

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

Backup multiple databases:

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

3. Restoring MySQL Backups

Two restoration methods:

System command line: mysql -uroot -p123456 < /data/mysqlDump/mydb.sql Inside MySQL shell using source:

mysql> source /data/mysqlDump/mydb.sql

4. Bash Script for 31-Day Backup Rotation

A Bash script ( mysql_dump_script.sh) automates daily backups and retains only the most recent 31 days. The script:

Sets parameters: backup count (31), backup directory (/root/mysqlbackup), timestamp, tool (mysqldump), credentials, target database (edoctor).

Creates backup directory if missing.

Executes mysqldump and saves output to a timestamped .sql file.

Logs creation to log.txt.

Finds the oldest backup file via ls -l -crt and awk.

Counts current .sql files.

If count exceeds 31, deletes the oldest file and logs deletion.

#!/bin/bash
# Save backup count, keep 31 days
number=31
backup_dir=/root/mysqlbackup
dd=`date +%Y-%m-%d-%H-%M-%S`
tool=mysqldump
username=root
password=TankB214
database_name=edoctor
# Create directory if not exists
if [ ! -d $backup_dir ]; then
    mkdir -p $backup_dir;
fi
# Execute mysqldump
$tool -u $username -p$password $database_name > $backup_dir/$database_name-$dd.sql
# Log creation
echo "create $backup_dir/$database_name-$dd.dupm" >> $backup_dir/log.txt
# Find oldest backup to delete
delfile=`ls -l -crt $backup_dir/*.sql | awk '{print $9 }' | head -1`
# Count backups
count=`ls -l -crt $backup_dir/*.sql | awk '{print $9 }' | wc -l`
# Delete if exceeds limit
if [ $count -gt $number ]; then
    # Delete oldest backup
    rm $delfile
    # Log deletion
    echo "delete $delfile" >> $backup_dir/log.txt
fi

5. Scheduling with Crontab

Cron is a Linux daemon for scheduled tasks. Service management:

service crond start
service crond stop
service crond restart
service crond reload
service crond status

Crontab Syntax

Each line has six fields: minute, hour, day-of-month, month-of-year, day-of-week, command.

minute hour day-of-month month-of-year day-of-week commands
Valid values: 00-59 00-23 01-31 01-12 0-6 (0 is sunday)

Special symbols: * (all values), / (step), - (range), , (list).

Crontab Commands

crontab -l

: list current crontab crontab -r: delete current crontab crontab -e: edit crontab (uses VISUAL/EDITOR)

Creating a Cron Script

Write script file (e.g., mysqlRollback.cron). Example: 15,30,45,59 * * * * echo "xgmtest....." >> xgmtest.txt (runs every 15 minutes).

Install with crontab mysqlRollback.cron (replaces user's crontab).

Verify with crontab -l or check /var/spool/cron.

Schedule the backup script (ensure executable permission): 0 2 * * * /root/mysql_backup_script.sh Install with crontab mysqlRollback.cron.

Crontab Examples

13 practical scheduling patterns:

Daily 6:00 AM: 0 6 * * * echo "Good morning." >> /tmp/test.txt Every 2 hours: 0 */2 * * * echo "Have a break now." >> /tmp/test.txt 11 PM to 8 AM every 2 hours plus 8 AM:

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

4th of month and Mon-Wed 11 AM: 0 11 4 * 1-3 command line Jan 1 4:00 AM with environment:

0 4 1 1 * SHELL=/bin/bash PATH=/sbin:/bin:/usr/sbin:/usr/bin MAILTO=root HOME=/ command line

Hourly run-parts /etc/cron.hourly: 01 * * * * root run-parts /etc/cron.hourly Daily run-parts /etc/cron.daily: 02 4 * * * root run-parts /etc/cron.daily Weekly run-parts /etc/cron.weekly: 22 4 * * 0 root run-parts /etc/cron.weekly Monthly run-parts /etc/cron.monthly: 42 4 1 * * root run-parts /etc/cron.monthly Complex: 4-6 PM at 5,15,25,35,45,55 min: 5,15,25,35,45,55 16,17,18 * * * command Mon/Wed/Fri 3:00 PM reboot: 0 15 * * 1,3,5 shutdown -r +5 Hourly at 10 and 40 min: 10,40 * * * * innd/bbslink Hourly at 1 min: 1 * * * * bin/account Note: run-parts executes all scripts in a directory; omit it to run a single script.

Practical Test

Test run every minute: * * * * * /root/mysql_backup_script.sh Resulting backup files:

Log.txt records each backup operation:

References

MySQLdump common commands: www.cnblogs.com/smail-bao/p/6402265.html

Shell script for MySQL backup: www.cnblogs.com/mracale/p/7251292.html

Linux Crontab detailed explanation: www.cnblogs.com/longjshz/p/5779215.html