In this article, we share how to create an automated remote database backup for your database. Click each panel's title to view its details. We hope you find this guide useful.
Login to your database server via SSH. You can do so from your terminal.
ssh you-ssh-user@your-server-ip
touch ~/mysql-backup.sh
vim ~/mysql-backup.sh
#!/bin/bash
export PATH=/bin:/usr/bin:/usr/local/bin
TODAY=`date +"%d%b%Y"`
################## Update below values ########################
DB_BACKUP_PATH='/home/you-ssh-user/backup'
MYSQL_HOST='localhost'
MYSQL_PORT='3306'
MYSQL_USER='root'
MYSQL_PASSWORD='mysecret'
DATABASE_NAME='mydb'
BACKUP_RETAIN_DAYS=30
#################################################################
mkdir -p ${DB_BACKUP_PATH}/${TODAY}
echo "Backup directory creation done"
mysqldump -h ${MYSQL_HOST} \
-P ${MYSQL_PORT} \
-u ${MYSQL_USER} \
-p${MYSQL_PASSWORD} \
${DATABASE_NAME} | gzip > ${DB_BACKUP_PATH}/${TODAY}/${DATABASE_NAME}-${TODAY}.sql.gz
if [ $? -eq 0 ]; then
echo "Database backup done"
else
echo "Error while backup"
exit 1
fi
DBDELDATE=`date +"%d%b%Y" --date="${BACKUP_RETAIN_DAYS} days ago"`
if [ ! -z ${DB_BACKUP_PATH} ]; then
cd ${DB_BACKUP_PATH}
if [ ! -z ${DBDELDATE} ] && [ -d ${DBDELDATE} ]; then
rm -rf ${DBDELDATE}
fi
fi
Update the value of the following variables in the script:
chmod +x ~/mysql-backup.sh
~/mysql-backup.sh
ls /home/you-ssh-user/backup
crontab -e
0 0 * * * you-ssh-user /home/you-ssh-user/mysql-backup.sh
Sharing is loving, share this page to help more developers.