Backing up MySQL database on Linux with rclone and Azure Blob Storage
MySQL and MariaDB contain an utility called
mysqldump which can be used for creating backup of a database or systems of databases. This utility creates a logical backup, which allows for granual recovery scenarios. Full backups are recommended for full disaster recovery scenarios.
This guide uses Azure storage emulator for testing purposes and CentOS 7.
How the database backup functions
As an example, here’s a command for manually backing up all of the databases in the system.
mysqldump --all-databases --single-transaction --lock-tables=false > full-backup-$(date +%F).sql -u root -p
Breakdown of the options:
--all-databasesdumps all databases.
--single-transactionoption provides a way of making an online backup if InnoDB storage engine is used.
--lock-tables=falseby default, mysqldump will lock the table of your database during the dump process to make sure there will not have new data added during this time-frame. This may impact your apps during the db dump. For InnoDB, use
$(date +%F)specifies the timestamp formatting.
%Fdisplays the full date.
Setting up config file to not expose MySQL database password
As stated in the MySQL End-User Guidelines for Password Security manual, our best option is to store the password in an option file. Putting the password in
crontab in convenient but insecure, as every time the command runs, the password can be visible in
Create a new user where you’ll be running the crontab and rclone. In the user’s home directory, create a new
.your_filename_here.cnf and restrict it by running the command
chmod 600 /home/user/.your_filename_here.cnf.
Example .cnf file:
[client] user = root password = mysql_root_pwd
We can then add
--defaults-extra-file=/home/bckusr/.my.cnf when using the mysqldump command for logging in
(Optional) Setting up the default editor for crontab
A bit controvensional, but if you don’t like
vi and prefer
nano instead, you can change the default editor for a specific user by editing
.bash_profile. To change the editor, paste this into your bash profile and relog (exit from the SSH session as well):
export VISUAL="nano" export EDITOR="nano"
Setting up RClone
Follow the RClone Microsoft Azure Blob Storage configuration documentation for setting up RClone. Due of me using the storage emulator, the configuration will greatly vary.
Setting up crontab
crontab -e as the user you want to run backups from. You can use Crontab Guru for quickly setting up the cron schedule execution.
Here’s how the crontab for daily backup that runs at 1 AM looks like.
0 1 * * * /usr/bin/mysqldump --defaults-extra-file=/home/bckusr/.my.cnf -u root --single-transaction --lock-tables=false --all-databases > full-backup-$(date +\%F).sql && rclone copy /home/bckusr/full-backup-*.sql azure:backup && rm -rf /home/bckusr/full-backup-*.sql
This creates a new database backup, when it runs correctly, it copies the backup to blob storage and after successful copy, it cleans up after itself.
I would heavily suggest using Azure Blob Storage Lifecycle Management.
Since Azure Blob storage is priced per GB, if you have large databases, the price can quickly creep up. You can utilize
gzip compression to save up some space.
0 1 * * * /usr/bin/mysqldump --defaults-extra-file=/home/bckusr/.my.cnf -u root --single-transaction --lock-tables=false --all-databases | gzip > full-backup-$(date +\%F).sql.gz && rclone copy /home/bckusr/full-backup-*.sql.gz azure:backup && rm -rf /home/bckusr/full-backup-*.sql.gz
This creates a new database backup and compresses it into a gunzip archive, when it runs correctly, it copies the backup to blob storage and after successful copy, it cleans up after itself.
Setting up Azure storage emulator
If you want to test out things yourself without having to spend money on Azure blob storage, follow the official Use the Azure storage emulator for development and testing guide on how to get the storage emulator running.
You will require Microsoft SQL Server 2012 Express installed at the current date this document was written. Because RClone is assuming that the emulator runs locally, you’ll have to fiddle with rclone or use Azurite Azure server emulator for Linux instead.
Depending on which version you installed, you might get away with supplying a SAS link with
127.0.0.1 changed to the address of the server.