I recently discovered how to easily backup an SQL database using Automator set as an Calendar event – reoccurring as often as you like. I chose to save the file in to a Google Drive folder for the added safety of it backing up to the cloud.
You could of course use any cloud storage solution. I chose Google Drive, so installed the app and set up as normal.
Go ahead and open Automator. Select “Calendar Alarm” from the list of options.
Now we need to add action from the library. Search for “shell script” and add “Run Shell Script” to the workflow.
Under ‘cat’ enter the following tailored to your personal setup.
/usr/local/mysql/bin/mysqldump --user=root --password=mYpAssWorD databasename > "/Users/administrator/Google Drive/Your Folder Name/databasename_$(date +%Y-%m-%d).sql"
This is telling mysqldump to do a ‘dump’ of the MySQL database (here labelled ‘databasename’) using the username ‘root’ and it’s password. It’s going to save the .sql file to the Google Drive folder path I have set up. Here are some other variables you can use for the filename:
Once you are happy, do a File > Save As. Once you close Automator, Calendar opens and you can configure how often you would like the script to run. You can of course test the script directly from Automator to check that it runs without any errors.