MySQL Data Backup and Automated Backup Script with Bash and Crontab

The article introduces MySQL data backup using the command‑line utility mysqldump , describing its basic syntax and several common usage examples such as backing up all databases, only structures, specific databases, and multiple tables.

Typical mysqldump commands are shown:

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

mysqldump -uroot -p123456 -A > /data/mysqlDump/mydb.sql          # all databases (data + structure)
mysqldump -uroot -p123456 -A -d > /data/mysqlDump/mydb.sql       # only structure
mysqldump -uroot -p123456 -A -t > /data/mysqlDump/mydb.sql       # only data
mysqldump -uroot -p123456 mydb > /data/mysqlDump/mydb.sql       # single database (data + structure)
mysqldump -uroot -p123456 mydb -d > /data/mysqlDump/mydb.sql    # single database structure only
mysqldump -uroot -p123456 mydb -t > /data/mysqlDump/mydb.sql    # single database data only
mysqldump -uroot -p123456 mydb t1 t2 > /data/mysqlDump/mydb.sql   # multiple tables
mysqldump -uroot -p123456 --databases db1 db2 > /data/mysqlDump/mydb.sql  # multiple databases

Restoring a backup can be done either from the system shell:

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

or inside the MySQL client using the source command:

mysql> source /data/mysqlDump/mydb.sql

The second part presents a Bash script ( mysql_dump_script.sh ) that automates daily backups, keeps only the most recent 31 files, logs actions, and removes the oldest files when the limit is exceeded.

#!/bin/bash
# Save up to 31 backup files
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 | wc -l`
if [ $count -gt $number ]; then
    rm $delfile
    echo "delete $delfile" >> $backup_dir/log.txt
fi

To run the script automatically, the article explains how to create a crontab entry. It describes the cron service, the crontab file format, and provides examples such as running the backup script at 2 AM every day:

0 2 * * * /root/mysql_backup_script.sh

Additional crontab examples illustrate scheduling commands at specific minutes, hours, or days, and show how to manage cron jobs with crontab -l , crontab -e , crontab -r , etc.

Finally, the article shows a test entry that executes the backup script every minute:

* * * * * /root/mysql_backup_script.sh

Overall, the guide provides a complete workflow for MySQL backup, restoration, script‑based automation, and cron scheduling.