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:
- 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
- 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.
- You will be prompted to enter your database password
- 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=/bin/gzip MYFIND=/usr/bin/find #Install database 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 (+8) of back ups around $MYFIND $BACKUPDIR -regex ".*[sql.gz]" -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. Pair Networks database names are usually comprised of your username and unique database name. So for example, your database name may look like, "jsmith_mydatabase"|
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:
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.