MySQL Scheduled Backup and Restoration Using mysqldump and Crontab

In the process of handling data, errors or database crashes can occur, so regular backups are essential. This article introduces several methods for MySQL scheduled backup.

1. mysqldump command backup

MySQL provides the mysqldump utility for exporting database data and structure via the command line. Common usage examples include:

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

2. Common mysqldump operation examples

Examples for backing up all databases, structures only, data only, single databases, and specific tables:

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 MySQL backup

Two ways to restore: using the MySQL command line or the source command inside the MySQL client.

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

mysql> source /data/mysqlDump/mydb.sql

4. Bash script for automated backup

A Bash script ( mysql_dump_script.sh ) can automate daily backups, keep a fixed number of backup files, and log operations.

#!/bin/bash
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
# Delete oldest backup if exceeding limit
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 backup script

Linux uses the cron daemon to run scheduled tasks. The crontab file defines the timing and commands.

Typical crontab syntax: minute hour day-of-month month day-of-week command . Example 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 * command line SHELL=/bin/bash PATH=/sbin:/bin:/usr/sbin:/usr/bin MAILTO=root /...

Additional examples show how to run scripts hourly, daily, weekly, or monthly using run-parts .

References

The article cites several online resources for mysqldump commands, shell backup scripts, and crontab usage.