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
Top comments (0)