Automated backup of MySQL databases to Google Drive

So I had a hard drive crash due to a power outage. The disk was partly functional and I was able to pull most of the .ibd files and spent 15 hours rebuilding the database table by table. I recovered most of what I needed but not having an automated backup really bit me in the ass. I have been planning on doing it for years and now here it is.

It was really quite simple and took 40 minutes to setup. Including all the research to figure out how to automate this. It relies on two pieces of software. A script called AutoMySQLBackup and rclone.

AutoMySQLBackup

I downloaded and extracted the file from the link above. Ran the install.sh file (after I reviewed the script) and modified the config file (located in /etc/AutoMySQLBackup/myserver.cfg) to my liking. There are a ton of settings so I will try to keep it brief.

I configured AutoMySQLBackup to keep 7 days for daily backups, 8 weeks for weekly backups and 2 years for monthly backups. These all go to a folder “/backup” the folder structure there is daily, weekly, monthly, and latest as configured within each of those except for “latest” is a folder name of each database, within those folders are the timestamped and compressed sql dumps. Once AutoMySQLBackup is configured I wrote a script based off the README file.

    #!/bin/sh

    /usr/local/bin/automysqlbackup /etc/automysqlbackup/myserver.conf

    chown root.root /root/SQL_backups/* -R

    find /backup/* -type f -exec chmod 400 {} \;

    find /backup/* -type d -exec chmod 700 {} \;

Made it executable chmod +x backup.sh

Then I symbolically linked the script to /etc/cron.daily/backup.sh

ln -s /opt/SQL_backup/backup.sh /etc/cron.daily/backup.sh

RCLONE

After installing rclone from the EPEL repo you run “rclone config” and selected the options I wanted in my case I am copying files to my google drive. I named my backup location “Remote” and the script is below.

#!/bin/bash
rclone copy /backup/ Remote:backup

To quickly break this down you invoke the rclone copy command give it a source and a destination in this case “Remote:backup” Remote is my google drive root folder and backup is the folder it is going into.

Made the script executable and linked it to cron.daily and tada cloud backups of MySQL server.