Automate MySQL Backups and Restores with Bash, Cron, and Find

Backup Script (mysql_bak.sh)

The script defines variables for the backup root directory, MySQL host, credentials, target database, and the location of mysqldump. It builds a date‑based subdirectory, creates it if missing, and runs mysqldump to export the database to a .sql file, which is then compressed with gzip. Success or failure messages are printed.

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

# MySQL host
DB_HOSTNAME='hostname'
# MySQL user
DB_USERNAME='root'
# MySQL password
DB_PASSWORD='xxxx'
# Database to back up
DB_NAME="xxxx"

# Current date variable
CURRENT_DATE=$(date "%Y%m%d%H")
# Delete files older than N days (default 1)
DEL_DAYS_BEFORE_FILES=1
# mysqldump location
MYSQLDUMP_DIR="/usr/bin"

BASH_PATH=${BACKUP_DIR}/${CURRENT_DATE}

if [ ! -d ${BASH_PATH} ]; then
  echo "Directory not found, creating: ${BASH_PATH}"
  mkdir -p ${BASH_PATH}
else
  echo "${BASH_PATH} already exists"
fi

echo "Starting backup ${DB_NAME} database"

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 "${CURRENT_DATE}--Backup database ${DB_NAME} successfully!"
else
  echo "${CURRENT_DATE}--Backup database ${DB_NAME} unsuccessfully"
fi

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

Make the Script Executable and Test

Grant execution permission with chmod +x mysql_bak.sh and run it using ./mysql_bak.sh. If the script fails with a “/bin/bash^M: bad interpreter” error, it is caused by Windows line endings (CRLF). Convert the file by removing \r characters, e.g., sed -i 's/\r$//', then retry.

Understanding find -mtime

The -mtime n option selects files based on modification time measured in whole days. n means exactly n days ago, -n means within the last n days, and +n means older than n days (rounded up to the next whole day). Examples: -mtime 0: modified within the last 24 hours -mtime 1: modified between 24 and 48 hours ago -mtime +1: modified more than 48 hours ago -mtime -1: modified within the last 24 hours

Scheduling with Cron

Open the crontab editor with crontab -e and add a line to run the backup script daily at 02:00 and redirect logs:

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

Save and exit (Esc, then :wq) to activate the scheduled job.

Restoring a Backup

Decompress the .gz file with gunzip filename_YYYYMMDD.sql.gz. Then, using the MySQL client:

Connect: mysql -u root -p and enter the password.

Optionally create a new database: create database test01; Select the target database: use databaseName; Execute the dump: source /path/to/filename_YYYYMMDD.sql.

Verify tables: show tables; After these steps the database is fully restored.