Backup Jamf MySQL database over SSL connection

[prbsparx]

When we moved our MySQL database to a separate server, we were required to enable communication over SSL and I noticed that the JSS Database Util doesn't have the ability to backup the database anymore...

I wrote a custom script that runs from our master server using mysqldump, but that appears to require stopping all changes to the MySQL server during the backup... and the backup takes a while (I run a mysql check too)

What are others using for automated backups?
How are you backing up databases that are over ssl?

(SSL = TLS in this case, so used to just calling it SSL)

[Hugonaut]

Backups Workflow (this is what we use) - https://www.jamf.com/jamf-nation/articles/126/backing-up-the-database-using-the-jamf-pro-database-utility

We have a dedicated sql server as well - Tomcat /Casper share are on a Macintosh & SQL is on a RHEL box.
I made a script that runs at midnight on the Macintosh, after the RHEL Box runs a scheduled sql backup, and I RSYNC the backups directory to a the Mac Machine and 2 External Backup Drives (Call me crazy but I do NOT want to lose that database lol!)

I have a crazy concept....kinda mickey mouse but what the hay. why not have some fun.

MySql Server = Device 1 (LOCAL Intranet)
Web Server = Device 2 (LOCAL Intranet)
Backups Final Destination = Device 3 (OVER SSL)

Workflow -- Device 1 - runs BackUp.

Device 2 - Rsyncs or Downloads Backup Directory & Runs Script removing all but the latest backup. This download is placed in a directory accessible via web via https:// (with protection of some sort of course so only privileged users can access)

Device 3 - Downloads dedicated backup directory from Device 2

-- End Workflow

Could configure the parsing of database backups a million different ways so you are only downloading 1 backup at a time but this is just for example.

This is obviously a concept but I think you could manage to make something like this work. I havent researched SSL backups nor have I ever done an SSL backup so I might be totally off the wall with this concept but theres my 2 cents.

[lockwojo]

@prbsparx I am successfully using the Jamf Database Util via a cron job to automatically backup the database over SSL. The database server (and client) are actually MariaDB which is a 100% compatible drop-in replacement for MySQL.

See - https://www.jamf.com/jamf-nation/articles/175/backing-up-the-database-from-the-command-line-using-the-jamf-pro-database-utility for instructions on how to setup a backup schedule.

I added the SSL certs file paths to the my.cnf [client] section in /etc/mysql/my.cnf

The databases are simply databases, all the SSL is doing is encrypting the traffic as it goes across the network, so as long as your MySQL client and server are correctly configured to use SSL the backup simply works and you end up with a normal backup file.

The JSS Database Util seems to simply call the MySQL nee MariaDB client which obeys the my.cnf configuration file and hence uses SSL. I know it is using SSL because when we recently changed the certificate on the server before changing it on the JSS client the backup stopped working. Once I updated the client as well backups started working again. It does not seem to use Tomcat JDBC which needs configuring quite differently to use SSL. My guess is that somewhere behind the scenes the Jamf script is calling the standard mysqldump command.

[prbsparx]

@lockwojo have you looked at using the jamf-pro cli tool insead of the JamfDatabaseUtil.jar?

[lockwojo]

@prbsparx As I am running JAMF on an Ubuntu server this means no GUI interface so I can only run the command line tool as per the document I linked to.

[prbsparx]

@lockwojo i think you misunderstood my question.
Jamf currently has two “database utilities”:
JamfDatabaseUtil.jar that can be run from CLI.
jamf-pro command line tool that is new. I think it released with 10.6? Or 10.7.

Have you tested the second tool?