Step by step guide for automated MySQL database backup

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

  • Create a Shell script file in the current user's home directory:

touch ~/mysql-backup.sh

  • Open the Shell script using vim:

vim ~/mysql-backup.sh

  • Copy and paste the content below to ~/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:

  • DB_BACKUP_PATH: where you want to store the backup file. if you are not sure, just replace you-ssh-user with your current SSH user's name.
  • MYSQL_HOST: MySQL host, this is normally localhost.
  • MYSQL_PORT: MySQL port number, this is normally 3306.
  • MYSQL_USER: MySQL user name.
  • MYSQL_PASSWORD: MySQL database username's password.
  • DATABASE_NAME: MySQL database that you want to backup.
  • BACKUPRETAINDAYS: Number of days to keep the backup file.

  • Grant correct permission to the backup Shell script:

chmod +x ~/mysql-backup.sh

  • Test out the backup Shell script by running:

~/mysql-backup.sh

  • You should see a directory containing a MySQL backup file inside the directory defined in step 3 (DBBACKUPPATH). 

ls /home/you-ssh-user/backup

  • Schedule a cron job to run the backup every day at midnight:

crontab -e

  • Add following entry:

0 0 * * * you-ssh-user /home/you-ssh-user/mysql-backup.sh

  • Done. Now your MySQL database will be automatically backed up every day at midnight.

Sharing is loving, share this page to help more developers.