How to Automate MySQL Backups and Restores with Bash and Cron

Backup (mysqldump)

Implement the following functions:

Backup a specified database.

Delete backup files older than a configurable number of days (default 1 day).

Script example (mysql_bak.sh)

# Database backup root directory
BACKUP_DIR="/usr/local/mysqlbackups/tsmbak/"

# MySQL host
DB_HOSTNAME='hostname'

# MySQL login credentials
DB_USERNAME='root'
DB_PASSWORD='xxxx'

# Database to back up
DB_NAME="xxxx"

# Current date (used in file name)
CURRENT_DATE=$(date +"%Y%m%d%H")

# Number of days before which files are deleted
DEL_DAYS_BEFORE_FILES=1

# Path to mysqldump
MYSQLDUMP_DIR="/usr/bin"

# Directory for today's backup
BASH_PATH=${BACKUP_DIR}/${CURRENT_DATE}

# Create directory if it does not exist
if [ ! -d ${BASH_PATH} ]; then
  echo "Directory does not exist, creating: ${BASH_PATH}"
  mkdir -p ${BASH_PATH}
else
  echo "${BASH_PATH} directory already exists"
fi

echo "Starting backup ${DB_NAME} database"

# Perform the backup
if ${MYSQLDUMP_DIR}/mysqldump -h ${DB_HOSTNAME} -u${DB_USERNAME} -p${DB_PASSWORD} ${DB_NAME} > "${BASH_PATH}/${DB_NAME}_${CURRENT_DATE}.sql"; then
  cd ${BASH_PATH}
  gzip ${DB_NAME}_${CURRENT_DATE}.sql
  echo "---------------------------------------------------------"
  echo "${CURRENT_DATE}--Backup database ${DB_NAME} successfully!"
  echo "---------------------------------------------------------"
else
  echo "----------------------------------------------------------"
  echo "${CURRENT_DATE}--Backup database ${DB_NAME} unsuccessfully"
  echo "----------------------------------------------------------"
fi

# Delete backup files older than N days
find ${BACKUP_DIR} -name "*" -type f -mtime ${DEL_DAYS_BEFORE_FILES} -exec rm -rf {} \;
echo "Deleted backups older than ${DEL_DAYS_BEFORE_FILES} days"

Make the script executable

chmod +x mysql_bak.sh

Run a test

./mysql_bak.sh

If you encounter /bin/bash^M: bad interpreter: No such file or directory, it is likely due to Windows line endings (CRLF). Convert the file with:

sed -i 's/\r$//' mysql_bak.sh

find -mtime explanation

The find command can filter files by modification time using the -mtime option: -mtime n: files modified exactly n days ago. -mtime -n: files modified within the last n days. -mtime +n: files modified more than n days ago (n must be an integer, so +1 means older than 2 days).

Scheduled task (cron)

Create a cron job to run the backup daily at 02:00: crontab -e Add the following line and save:

* 2 * * * /usr/local/mysqlbackups/mysql_bak.sh > /usr/local/mysqlbackups/tsmbak/mysql_bak.log 2>&1

Restore

Backed‑up files are compressed; decompress them first: gunzip xxxxxx_20220303.sql.gz The resulting .sql file contains the original SQL statements. Restore it with MySQL: mysql -u root -p Then execute:

source /usr/local/mysqlbackups/tsmbak/xxxxxxx_20220303.sql

Verify the restored tables with show tables;.

Images illustrating the process:

With these steps you can reliably back up and restore MySQL databases on Linux.