How to back up and restore MySQL database on Linux using cron

1. Make new .sh file on root directory called mysql_backup.sh.

#!/bin/bash
# This script will backup the database
# and store in a specified directory.

# Constants

# Database credentials
USER="dev"
PASS="mypass123!"
DB_NAME="mydb"
HOST="localhost"
BACKUP_DIRECTORY="/root/backup_db"

# Add time stamp ( formated YYYYMMDD + HHMMSS)
# uses for the file name
CURRENT_DATE=$(date "+%Y%m%d-%H%M%S")

# Run mysqldump command
# we will backup the database into a .gz file
mysqldump -h ${HOST} \
-u ${USER} \
-p${PASS} \
${DB_NAME} | gzip - > $BACKUP_DIRECTORY/$DB_NAME\_$CURRENT_DATE.sql.gz

2. Change file permissions

$ chmod 700 /root/mysql_backup.sh
or 
$ chmod u+rwx,g-rwx,o-rwx /root/mysql_backup.sh

3. Add new cron using crontab.

crontab -e

add this command at the end of the file so your database will be backup up every 30 minutes.

*/30 * * * * bash /root/mysql_backup.sh > /dev/null 2>&1 

4. To restore the file into your database you can run this command:

$ gunzip -c ...sql.gz | mysql -h localhost -U DB_NAME -u USER -p

Thank you for reading my post.