MySQL Scheduled Backup and Restoration Using mysqldump, Bash Scripts, and Cron

When operating on MySQL data, regular backups prevent data loss and database crashes; this guide presents several methods for scheduled MySQL backups.

1. mysqldump command backup

The mysqldump utility exports database contents to a dump file. Basic usage examples include:

#MySQLdump
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 all databases structure only:

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

Backup all databases data only:

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

Backup a single database (data and structure):

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

Backup a single database structure only:

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

Backup a single database data only:

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

Backup multiple tables from a database:

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 to restore a dump:

From the command line: mysql -uroot -p123456 < /data/mysqlDump/mydb.sql

Inside the MySQL client using source /data/mysqlDump/mydb.sql

4. Automated backup script

A Bash script ( mysql_dump_script.sh ) creates a backup directory, records the date, runs mysqldump , logs actions, and keeps only the most recent 31 backups by deleting the oldest files.

#!/bin/bash
# Save 31 days of 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
# Log the operation
echo "create $backup_dir/$database_name-$dd.sql" >> $backup_dir/log.txt
# Delete oldest if exceeding limit
count=$(ls -1 $backup_dir/*.sql | wc -l)
if [ $count -gt $number ]; then
  delfile=$(ls -1 $backup_dir/*.sql | head -1)
  rm $delfile
  echo "delete $delfile" >> $backup_dir/log.txt
fi

5. Scheduling with crontab

The cron daemon runs scheduled tasks. A crontab entry consists of six fields (minute, hour, day‑of‑month, month, day‑of‑week, command). Example entries:

# Every day at 6:00 AM
0 6 * * * echo "Good morning." >> /tmp/test.txt

# Every two hours
0 */2 * * * echo "Have a break now." >> /tmp/test.txt

# At 4:00 AM on the 1st of each month
0 4 1 * * /root/mysql_backup_script.sh

Additional examples show how to run scripts hourly, daily, weekly, or monthly, and how to include custom commands.

References are provided for further reading on mysqldump usage, Bash backup scripts, and crontab syntax.