Master MySQL Backups with mysqldump, Bash Scripts, and Cron

During data operations, errors or database crashes can occur, so regular MySQL backups are essential. This article explains several methods for scheduled MySQL backups using the mysqldump command, Bash scripts, and Linux cron jobs.

1. mysqldump command backup

The mysqldump utility exports database data and structure. Basic usage:

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

2. Common mysqldump examples

Backup all databases (data and structure):

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

Backup only structure (add -d):

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

Backup only data (add -t):

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

Backup a single database:

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

Backup a single database’s structure:

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

Backup a single database’s data:

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

Backup multiple tables:

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

Backup multiple databases at once:

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

3. Restoring MySQL backups

Two ways: using the MySQL client or a shell command.

From command line:

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

Inside MySQL client:

source /data/mysqlDump/mydb.sql

4. Bash script for automated backups

A sample script mysql_dump_script.sh keeps the last 31 daily backups, logs actions, and removes the oldest files when the limit is exceeded.

#!/bin/bash
# keep 31 backups
number=31
backup_dir=/root/mysqlbackup
dd=$(date +%Y-%m-%d-%H-%M-%S)
tool=mysqldump
username=root
password=TankB214
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. Scheduling the script with crontab

Enable the cron daemon and add a crontab entry to run the script daily (example at 02:00):

0 2 * * * /root/mysql_backup_script.sh

Typical crontab syntax is minute hour day-of-month month day-of-week command with special characters * / - ,. Common schedule examples are provided.