How to backup MySQL database

I was looking for a solution to back up my MySQL database in a automatic manner. I found that the easiest solution would be to utilize mysqldump which would be run by a cron job. I had a few struggles making that work, so today in this short article, I will guide you on how to set it up in an Ubuntu environment.

#1 install mysqldump

You should install the latest version of mysqldump. To check if you have one, paste into terminal mysqldump --version . If not, install it using the following: apt-get install mysqlclient . That command will install all the necessary packages. Confirm the installation with mysqldump --version command.

#2 perform a trial backup

Before we continue, prepare connection data to your database. We will need database user, password, host (the address) and database name, that you would like to backup.

Copy and paste the command. Make sure to fill in your connection details:

mysqldump -u DB_USERNAME -h DB_HOST -p DB_NAME > db_dump.sql 

When you hit enter, you will be asked to provide password. Enter it, and then backup will start. Depending on how big is your database, the process may take a while.

If succeeded (I bet it went okay!) you should be able to see an sql file in your directory. But now, how can we automate database backup?

#3 create a shell script

You probably noticed, that in previous step we had to manually fill in database password. That complicate things, but there is an easy solution. Create a file called .my.cnf in your home directory, and then open it in your text editor of choice. I'm using nano .

touch .my.cnf
nano .my.cnf

Paste in the following:

[mysqldump]
password=DB_PASSWORD

Save and close the file.

Next up, change the CHMOD of that file to 600 chmod 600 .my.cnf

You can now test if the solution works with the command:

mysqldump -u DB_USERNAME -h DB_HOST DB_NAME > db_dump.sql 

Notice that I've removed the -p parameter.

Okay, now lets create a shell script. Create a file for example touch /usr/local/bin/backup_database.sh . Straight away, make the file executable, so chmod +x /usr/local/bin/backup_database.sh . Open it and paste the following bash commands.

#!/bin/bash

DB_USER="db_user"
DB_HOST="db_host"
DB_NAME="db_name"
# Backup dir - full path e.g
BACKUP_DIR="home/ubuntu/backups/database"
DATE=$(date +%Y%m%d%H%M)

# Creates backup dir if not exists
mkdir -p ${BACKUP_DIR}

# specify output file names with dir
DUMP_FILE="${BACKUP_DIR}/${DB_NAME}_backup_${DATE}.sql"
TAR_FILE="${BACKUP_DIR}/${DB_NAME}_backup_${DATE}.tar.gz"

# execute backup
mysqldump -h ${DB_HOST} -u ${DB_USER} ${DB_NAME} > ${DUMP_FILE}

# checking if success
if [ $? -eq 0 ]; then
  echo "Database backup successful, compressing the file..."

  tar -czf ${TAR_FILE} -C ${BACKUP_DIR} $(basename ${DUMP_FILE})

  # delete non-compressed file
  rm ${DUMP_FILE}

  echo "Backup completed: ${TAR_FILE}"
else
  echo "Backup failed!"
fi

I briefly explained each line in the comments. Now let's execute the script!

. /usr/local/bin/backup-database.sh and you should see that backup was successful.

#4 schedule a cron job

Of course, we don't want to run that script manually. We would prefer to schedule it somewhere for the middle of the night 😄. Here is a quick cron tutorial for you.

Type crontab -e and hit enter - your text editor will open. Add to the very end of file the following:

10 0 * * * /usr/local/bin/backup-database.sh

Save and close the text editor. And that's it! You've just scheduled an automatic job to backup your MySQL database everyday at 0.10am!

Conclusion

In this article, I showed you how to set up a fully automatic MySQL database backup in a Linux environment in four steps. Try it now, and remember to backup your precious data!