1. Home
  2. Databases
  3. How to Set Up Automated Database Backups on Ubuntu

How to Set Up Automated Database Backups on Ubuntu

You set up automated database backups on Ubuntu servers. Complete the three processes below to create a recurring database backup on your hosting account.

Set Up a MySQL Login Path

Setting up a MySQL login path will keep the connection secure. The file is encrypted and any time the file is used, the connection information (usernames, passwords, etc.) will not be exposed. To set up a MySQL login path, follow these instructions:

  1. Log in to your account using SSH. For information about how to do this, see our article How to Connect to Your Account with SSH
  2. Once successfully logged in to your account, enter the following command into the terminal/command line:

    mysql_config_editor set --login-path=DBDescription --host=DB#.pair.com --user=DBUser --port=3306 --password
    
    DBDescription This can be whatever description you want. Ideally, this is something you can easily remember and identify the database by.
    DB##.pair.com This will be your database server. It will resemble something like this: db12.pair.com. You can find the server in your database management interface.
    DBUser This is the database user that will be used to login to the database. This is not the same as your account username. You can find the server in your database management interface.
  3. You will be prompted to enter your database password
  4. Next, enter the following command to make sure the login-path data was entered correctly:

    mysql_config_editor print --login-path=DBDescription

    The output will resemble this:

    [DBDescription]
     user = DBUSER
     password = *****
     host = DB#.pair.com
     port = 3306

This will create a .mylogin.cnf file in your directory. It will be accessed each time the cron job runs so that you are not prompted for a password each time your database is backed up.

Adding the Shell Script

The next step is to add the shell script. Copy the text below and save it into a file named mysqlback.sh on your account. We recommend saving it in a subdirectory. Be sure to customize the script so that it contains the correct information.

#!/bin/sh

#Set utility paths
MYDUMP=/usr/home/USERNAME/PATH/TO/mysqldump
BACKUPDIR=/usr/home/USERNAME/PATH/TO/BACKUP/DIRECTORY
MYCHMOD=/bin/chmod
MYGZIP=/bin/gzip
MYFIND=/usr/bin/find

#Install database backup
$MYDUMP --login-path=DBDESCRIPTION USERNAME_DATABASENAME > $BACKUPDIR/DATABASENAME.`date '+%m%d%Y'`.sql

#Lock down permissions on backup file
$MYCHMOD 600 $BACKUPDIR/DATABASENAME.`date '+%m%d%Y'`.sql

#Compress backup file
$MYGZIP $BACKUPDIR/DATABASENAME.`date '+%m%d%Y'`.sql

#If you want to backup a second database at the same time
#Make sure you set up a MySQL Login Path for the second database
$MYDUMP --login-path=DBDESCRIPTION USERNAME_DATABASENAME > \
$BACKUPDIR/DATABASENAME.`date '+%m%d%Y'`.sql

#Lock down permissions on backup file
$MYCHMOD 600 $BACKUPDIR/DATABASENAME.`date '+%m%d%Y'`.sql

#Compress backup file
$MYGZIP $BACKUPDIR/DATABASENAME.`date '+%m%d%Y'`.sql

#Clean things up, only keep 8 days (+8d) of back ups around
$MYFIND $BACKUPDIR -ctime +8 -delete
/PATH/TO/BACKUP/DIRECTORY The file path to the directory where you want the backup to be stored
DBDESCRIPTION The name of the database. This must match the description you created for --login-path in the Set Up MySQL Login section.
USERNAME pair Networks account username
DATABASENAME Name of your database

Set Up Cron Job

Cron jobs are used to create recurring processes that will run on your server. For more information about creating a cron job, see our article, How to Create a Cron Job.

When creating a cron job, you will be asked for a Command to Run. This command is the command that will start the backup process. In the Command to Run field, enter this command:

/usr/home/USERNAME/PATH/TO/mysqlback.sh

You’ll need to replace the following item in the command above:

/PATH/TO/mysqlback.sh the file path to the mysqlback.sh file (this allows the cron job to locate and run the script for each backup)
USERNAME pair Networks account username

Save the cron job.

Once you have successfully set up the cron job, .mylogin.cnf file, and the script file, your account will begin to backup your database. You can find your backups in the backup directory on your account.

Updated on September 17, 2018

Was this article helpful?

Related Articles