DEV Community

Daniel Pepuho
Daniel Pepuho

Posted on Edited on

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

#linux #mysql #tutorial #beginners

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
Enter fullscreen mode Exit fullscreen mode

2. Change file permissions 

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

3. Add new cron using crontab. 

crontab -e
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Thank you for reading my post.

Top comments (0)