Master MySQL Backups: Automated Scripts and Cron Scheduling Explained

1. MySQL Data Backup

1.1 mysqldump command backup

MySQL provides the mysqldump utility for exporting database data and structure directly from the command line.

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

1.2 Common mysqldump examples

1. Backup all databases (data and structure)

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

2. Backup all databases structure only (add -d)

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

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

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

4. Backup a single database (data and structure)

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

5. Backup a single database structure only

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

6. Backup a single database data only

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

7. Backup multiple tables from a database

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

8. Backup several databases at once

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

1.3 Restoring MySQL backups

Two common methods:

From the system shell:

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

Inside the MySQL client using source:

mysql> source /data/mysqlDump/mydb.sql

2. Script to Maintain Backup Files

In Linux, a Bash script combined with crontab can automate daily backups and keep only the most recent 31 days of dump files.

2.1 Bash script for fixed‑number backups

Save the following script as mysql_dump_script.sh and make it executable.

#!/bin/bash

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

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

# Perform the dump
$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

# Identify the oldest backup to delete
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 ]; then
    rm $delfile
    echo "delete $delfile" >> $backup_dir/log.txt
fi

The script sets parameters, runs mysqldump, logs the operation, and removes the oldest dump when the number of files exceeds the limit.

2.2 Using crontab to schedule the backup script

The cron daemon executes scheduled tasks. Basic service commands:

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

Crontab file syntax (minute hour day‑of‑month month day‑of‑week command):

minute hour day-of-month month day-of-week command

Example: run the backup script every minute * * * * * /root/mysql_backup_script.sh Other common examples (daily at 2 AM, every 2 hours, etc.) are omitted for brevity.

The accompanying log.txt records each creation and deletion action.