Backing up a Jamf Pro database hosted in Amazon Web Services’ RDS service to an S3 bucket

For those using Amazon Web Services to host Jamf Pro, one of the issues you may run into is how to get backups of your Jamf Pro database which you can access. AWS’s RDS service makes backups of your database to S3, but you don’t get direct access to the S3 bucket where they’re stored.

Note: This instance will need to have enough free space to store a complete backup of your database, so I recommend looking at the size of your database and choose the appropriate amount of disk space when you’re setting up the new instance.

Note: If you’re running Jamf Pro in AWS and you’re hosting your database in RDS, you likely have a security group like this set up already. Otherwise, your Jamf Pro server wouldn’t be able to communicate with the database.

7. Add the EC2 instance to the VPC Security Group which allows access to your RDS database.

Once all of the preparation work has been completed, use the following procedure to set up the backup process:

Note: For the purposes of this post, I’m using Red Hat Enterprise Linux (RHEL) as the Linux distro. If using another Linux distro, be aware that you may need to make adjustments for application binaries being stored in different locations than they are on RHEL.

Running this command should create a file named .mylogin.cnf in root’s home directory. To see the contents of the MySQL connection file and verify that it’s set up correctly, run the following command:

Note: The reason for creating the MySQL connection is so we don’t need to store the database password as plaintext in the script.

Creating the backup script

1. Once the MySQL connection has been created, copy the script below and store it as /usr/local/bin/aws_mysql_database_backup.sh.

This script has several variables that will need to be edited. For example, if your Jamf Pro database is named jamfprodb, the S3 bucket you created is named jamfpro-database-backup and the MySQL connection you set up is named local, the following variables would look like this:

# Enter name of the RDS database being backed up
database_name=jamfprodb
# Enter name of the S3 bucket
S3_bucket=jamfpro-database-backup
# Enter the MySQL connection name
mysql_connection_name=local

2. Make the script executable by running the following command with root privileges:

chmod 755 /usr/local/bin/aws_mysql_database_backup.sh

3. Ensure that root owns the file by running the following command with root privileges:

chown root:root /usr/local/bin/aws_mysql_database_backup.sh

Note: The mysqldump command used in the script is set up with the following options:

– -max-allowed-packet=1024M

– -single-transaction

– -routines

– -triggers

– -max-allowed-packet=1024M: This specifies a max_allowed_packet value of 1 GB for mysqldump. This allows the packet buffer limit for mysqldump to grow beyond its default 4 MB limit to the 1 GB limit specified by the max_allowed_packet value.

– -single-transaction: Generates a checkpoint that allows the dump to capture all data prior to the checkpoint while receiving incoming changes. Those incoming changes do not become part of the dump. That ensures the same point-in-time for all tables.

– -routines: Dumps all stored procedures and stored functions.

– -triggers: Dumps all triggers for each table that has them.

These options are designed for use with InnoDB tables and provides an exact point-in-time snapshot of the data in the database. These options also do not require the MySQL tables to be locked, which in turn allows the Jamf Pro database to continue to work normally while the backup is taking place.

Scheduling the database backup:

You can set up a nightly database backup using cron. For example, if you wanted to set up a database backup to run daily at 11:30 PM, you can use the procedure below to set that up.

1. Export existing crontab by running the following command with root privileges:

crontab -l > /tmp/crontab_export

2. Export new crontab entry to exported crontab file by running the following command with root privileges: