If you have ever taken a MySQL backup, chances are you thought about storing the backup somewhere. At that moment, a few thoughts could have crossed your head, and most probably one of those thoughts was offsite storage for your MySQL backups.
What is Offsite Storage?
Offsite storage is a strategy of sending backup data out of the main location (“off-site”) – simply put, offsite storage is the replication of data to a medium that is in a different location than the production system.
Offsite Storage Advantages and Disadvantages
If your MySQL backups reside in, for example, a private cloud and your production systems do not, they are located offsite. The main benefits of storing your MySQL backups offsite include:
- Data can be accessed via FTP or via Internet – data cannot be destroyed by natural disasters either;
- In most cases, offsite storage solutions are scalable;
- Offsite and cloud storage is affordable – in most cases you will only pay for what you use.
However, offsite storage also has its own disadvantages:
- The security and integrity of the data must be ensured at all times. If you use a cloud storage provider, make sure to choose one that follows all security regulations.
- Your internet speed can determine how fast you will acquire data – if your internet is slow, data retrieval may also be slow.
Offsite Storage and MySQL Backups – mysqldump
If your MySQL backups reside in a remote location away from your business, they are considered to be stored offsite. To store your backups offsite, you can store them in a remote server or also store them in the cloud. To store your backups in a remote server with, for example, mysqldump, run the following command via ssh:
$ ssh 22.214.171.124 mysqldump -h126.96.36.199 -u -p --all-databases > /home/backups/mysqlbackup.sql
Here, 188.8.131.52 is the IP of your remote server and
184.108.40.206 is the IP of your local server. If the remote server has MySQL installed, mysqldump will execute correctly. To set up the command to run automatically on a specified time, consider setting up a cron job. Before setting one up though, note that if you give your backups a file name that includes dates, for example, backup_`date +%Y%m%d`.sql, the percentage signs might need to be escaped with a backslash (\), otherwise, the cronjob might not work as supposed. With the percentage signs escaped, your cronjob might look something like the following:
* * * * * ssh 220.127.116.11 “mysqldump -h18.104.22.168 -u -p --all-databases | gzip > /home/backups/backup_`date +\%Y\%m\%d`.sql.gz”
Make sure you have a passwordless SSH connection to the server 22.214.171.124, otherwise, it will fail due to an authentication issue.
Offsite Storage and MySQL Backups – Cloud Storage
The clouds aren’t so simple – to store your backups in a cloud, you would first need to decide what cloud solution you would use, then set up automated backups to that cloud solution. To set up a backup on Amazon S3 using MySQL Enterprise Backup, use:
$ mysqlbackup --cloud-service=s3 --cloud-aws-region=region --cloud-access-key-id=accesskeyid --cloud-secret-access-key=secretaccesskey --cloud-bucket=bucketname --cloud-object-key=cloudobjectkey --backup-dir=/home/backups/amazons3backups --backup-image=- backup-to-image
All MySQL Enterprise Backup cloud backup examples can be seen on MySQL’s documentation page.
You can also store your backups in the cloud using ClusterControl. It allows you to upload backups to the cloud when the “Upload Backup to the cloud” option is selected:
In MySQL, offsite storage can be achieved in numerous ways – we have discussed the usage of mysqldump, MySQL Enterprise Backup, and ClusterControl. Each of those options have their own distinct advantages and disadvantages, so before choosing any tool, make sure you understand it well and choose the option most suitable for you.