If you wandered here, then I don’t need to explain to you why performing regular backups of MySQL databases is crucial to prevent any data loss. The key question many people ask is where to store these backups. Especially when dealing with larger databases, which require plenty of storage space Object Storage is top of mind solution due to its scalability and low cost.
In this article we will show you how to perform backups of your MySQL database to Contabo S3-compatible Object Storage directly from the command line. This is possible thanks to Contabo Object Storage being compatible with AWS CLI (Amazon Web Services Command Line Interface).
Don’t be surprised that this guide is much shorter than many other guides you can find online. This is thanks to AWS CLI which makes configuration of Contabo Object Storage much easier than working with other storage solutions.
Also, thanks to the abovementioned S3-compatibility it’s so easy to transfer backup files from Linux server to an Object Storage.
If you never heard of Contabo Object Storage, you can learn more here.
Prerequisites/Requirements
In order to perform a backup of your MySQL Database, the following things are required:
- An S3-compatible Object Storage with these credentials at hand:
- Your Access Key
- Your Secret Key
- Your S3 URL
- AWS CLI already installed (and configured) on your server where MySQL database is running.
If you want to know how to install and configure AWS CLI on your server, check out our documentation here.
Creating a backup of Your Database
Before you can upload the backup of your MySQL database to your Object Storage you need to create a local backup first.
Luckily this can be done with this built-in command of MySQL:
mysqldump –u [username] -p[password] [database_name] > backup.sqlDepending on the size of your database, this process can take a while.
Replace [username] and [password] with your login data to your MySQL instance.
Replace [database_name] with the name of the database you want to back up.
The “backup.sql” is the name of your backup. You can give it a different name if you want.
Uploading Your Database Backup
To upload your local backup of your database to Contabo Object Storage use the following command:
aws --profile eu2 --region default --endpoint-url [your_s3_url] s3 cp [name_of_your_backup_file] s3://[bucket_name]Schedule Regular Database Backups using Cron
To schedule regular backups of a certain database you need to do two things:
1. Create a shell script
First, we will create a shell script which will create a local backup of a certain database and will upload this backup to Contabo Object Storage using the AWS CLI.
To do so, use this command to create a script:
nano database-to-os.shAnd paste in the following content:
#!/bin/bash
mysqldump –u [username] -p[password] [database_name] > backup.sql
aws --profile eu2 --region default --endpoint-url [your_s3_url] s3 cp [name_of_your_backup_file] s3://[bucket_name]Save the script with [CTRL]+O and exit the editor with [CTRL]+X
Now make the script executable with this command:
chmod +x database-to-os.sh2. Edit the Crontab File
Now open the Crontab file where you can schedule the backups with this command:
crontab -eAnd paste in the following content:
0 3 * * * /[path_to_script]/databse-to-os.shWith this script a backup of your database will be performed every day at 3:00 AM. If you want a different schedule, just change the pasted content. More information about the syntax of Cron can be found in the file itself.
Conclusion
In conclusion, backing up MySQL databases to object storage using AWS CLI is a simple and efficient process that can provide an additional layer of protection for important data. With the help of the AWS CLI and the commands outlined in this article, users can easily automate the backup process, schedule regular backups, and securely store the backups in object storage.