Automate MySQL Backups with mysqldump and Cron: 31-Day Retention Script

Introduction

Data operations can cause errors or even database crashes; effective scheduled backups protect MySQL databases. This article explains several methods for MySQL timed backup using mysqldump and Linux crontab.

mysqldump Command Overview

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

# MySQLdump common usage
mysqldump -u root -p --databases db1 db2 > xxx.sql

Backup Examples

The following examples use -u root -p123456 and output to /data/mysqlDump/mydb.sql (adjust paths as needed).

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

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

3. Backup all databases data only ( -t ):

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

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

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

6. Backup single database data:

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

7. Backup multiple tables (e.g., t1, t2):

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

8. Backup multiple databases:

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

Restoring MySQL Backups

Two restoration methods:

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

mysql> source /data/mysqlDump/mydb.sql

Automated Backup Script with 31-Day Retention

A Bash script ( mysql_dump_script.sh) combines mysqldump with logic to keep only the latest 31 daily backups. Key steps:

Set parameters: retention count ( number=31), backup directory ( /root/mysqlbackup), timestamp ( dd=`date +%Y-%m-%d-%H-%M-%S`), tool, credentials, target database ( edoctor).

Create backup directory if missing.

Run mysqldump with variables, output to $backup_dir/$database_name-$dd.sql.

Log creation to $backup_dir/log.txt.

Find oldest backup:

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

.

Count current backups:

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

.

If $count -gt $number, delete $delfile and log deletion.

#!/bin/bash

# Number of backups to keep (31 days)
number=31
# Backup storage path
backup_dir=/root/mysqlbackup
# Timestamp
dd=`date +%Y-%m-%d-%H-%M-%S`
# Backup tool
tool=mysqldump
# Credentials
username=root
password=TankB214
# Database to backup
database_name=edoctor

# Create directory if not exists
if [ ! -d $backup_dir ];
then
    mkdir -p $backup_dir;
fi

# Execute backup
$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
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 oldest if exceeding limit
if [ $count -gt $number ]
then
    rm $delfile
    echo "delete $delfile" >> $backup_dir/log.txt
fi

Scheduling with crontab

The cron daemon runs scheduled jobs. Manage the service:

service crond start   # start
service crond stop    # stop
service crond restart # restart
service crond reload  # reload config
service crond status  # check status

crontab syntax (six fields):

minute hour day-of-month month day-of-week command
Valid ranges: 00-59 00-23 01-31 01-12 0-6 (0 is Sunday)

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

Common crontab commands: crontab -l list current crontab -r remove current crontab -e edit (uses $VISUAL/$EDITOR)

Creating a cron job for the backup script

Write script file (e.g., mysqlRollBack.cron).

Add job: crontab mysqlRollBack.cron (replaces user's crontab).

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

Example entry to run daily at 02:00 (ensure script executable): 02 * * * /root/mysql_backup_script.sh Then load: crontab mysqlRollback.cron.

crontab Examples

Every day 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 + 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:

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

Hourly scripts in /etc/cron.hourly: 01 * * * * root run-parts /etc/cron.hourly Daily scripts in /etc/cron.daily: 02 4 * * * root run-parts /etc/cron.daily Weekly scripts in /etc/cron.weekly: 22 4 * * 0 root run-parts /etc/cron.weekly Monthly scripts in /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 Note: run-parts runs all scripts in a directory; omit it to run a single script.

Verification

The article includes a screenshot of the cron execution (every minute for testing) and the resulting log.txt showing backup creation and deletion entries.

References

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

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

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