How to Set Up Automated Database Backups on FreeBSD

Shared

You set up automated database backups on FreeBSD 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 help 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/local/bin/mysqldump 
BACKUPDIR=/usr/home/USERNAME/PATH/TO/BACKUP/DIRECTORY
MYCHMOD=/bin/chmod
MYGZIP=/usr/bin/gzip
MYFIND=/usr/bin/find


#Install db backup
$MYDUMP --login-path=DBDESCRIPTION DATABASENAME --no-tablespaces > $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 DATABASENAME --no-tablespaces > \
$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 backups around
$MYFIND $BACKUPDIR -regex ".*[sql.gz]" -ctime +8d -delete

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 items in the command above:

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.