The automatic backup of MySQL Server databases is very important for preventing data loss. In order to properly automate backups, you must follow the following steps:
- Create a database backup
- Zip the backup
- Encrypt the compressed file
- Send the backup to Internet storage using FTP, Dropbox, AWS, Google Drive, etc.
- Receive email notification concerning backup results
- Create a backup schedule
- Delete old backups
This article will consider the following ways of carrying out these steps:
In most cases, a bash script is a good solution. However, not all storage locations allow you to upload files using shell commands. However, if all that you want to do is save backups to a directory, then you can accomplish this using a simple script.
To set up this solution, we need to install postfix mailutils. In Ubuntu we can do this as follows:
sudo apt-get update sudo apt-get install postfix mailutils
# Backup storage directory backupfolder=/var/backups # Notification email address recipient_email=<firstname.lastname@example.org> # MySQL user user=<user_name> # MySQL password password=<password> # Number of days to store the backup keep_day=30 sqlfile=$backupfolder/all-database-$(date +%d-%m-%Y_%H-%M-%S).sql zipfile=$backupfolder/all-database-$(date +%d-%m-%Y_%H-%M-%S).zip # Create a backup sudo mysqldump -u $user -p$password --all-databases > $sqlfile if [ $? == 0 ]; then echo 'Sql dump created' else echo 'mysqldump return non-zero code' | mailx -s 'No backup was created!' $recipient_email exit fi # Compress backup zip $zipfile $sqlfile if [ $? == 0 ]; then echo 'The backup was successfully compressed' else echo 'Error compressing backup' | mailx -s 'Backup was not created!' $recipient_email exit fi rm $sqlfile echo $zipfile | mailx -s 'Backup was successfully created' $recipient_email # Delete old backups find $backupfolder -mtime +$keep_day -delete
Cron allows you to schedule this script to run regularly. In order to facilitate this, do as follows:
sudo crontab -e
30 22 * * * /home/user/script/mysql_backup.sh
Thereafter, your script will be executed every day at 10:30 PM.
Creating a bash script is a good and simple solution if you know bash and how to support it. However, this solution also entails certain disadvantages. You will find that it is difficult to configure the saving of backups to Google Drive or DropBox. Cloud storage rarely supports the CLI interface. In addition, it can be a daunting task to implement the automatic removal of old backups.
AutoMySQLBackup is a command-line utility that is distributed under the GNU license. This
utility allows you to automate the backup process for the MySQL database, and it also allows you to perform incremental backups.
and you can install it simply by running the following command:
sudo apt-get install automysqlbackup
If your Linux distribution is RPM-based, the setup process will be a little bit more involved.
- Download the latest version of AutoMySQLBackup
- Create a directory and unzip the downloaded archive
mkdir /opt/automysqlbackup mv AutoMySQLBackup-master.zip cd /opt/automysqlbackup tar -zxvf AutoMySQLBackup-master.zip
- Then install AutoMySQLBackup
sudo nano /etc/automysqlbackup/automysqlbackup.conf
AutoMySQLBackup contains many settings, and you can find the full list in the official
Please note the following:
- Path to the configuration file
- Path to the backup storage directory
- Information for connecting to MySQL
CONFIG_mysql_dump_username='root' CONFIG_mysql_dump_password='my_password' CONFIG_mysql_dump_host='localhost'
- List of databases that you need or do not need to back up
- Email notification address
- Options for scheduling
CONFIG_rotation_daily=6 CONFIG_rotation_weekly=35 CONFIG_rotation_monthly=150
- Running the bash script after backup
POSTBACKUP="/etc/mysql-backup-post"If you plan to store backups for a limited number of days, then you can delete backups by configuring the POSTBACKUP script as follows:
# Delete backups that are older than 30 days find /var/backup/db -mtime +30 -delete
By default, AutoMySQLBackup will compress the files using “gzip.” You can change this by editing the CONFIG_mysql_dump_compression parameter. If you want to add encryption, then use the CONFIG_encrypt and CONFIG_encrypt_password parameters.
This solution can be a good alternative if you are not a bash power user and want a simple solution for backing up MySQL databases to a folder. One particularly notable feature is the ability to create incremental backups. The disadvantages of this solution include lack of support for integrated FTP and cloud storage locations. Even if you just want to upload to FTP, you need to write a separate bash script.
SqlBak is a cloud backup service. This is a service that provides you with a wide range of options to automatically back up your MySQL database. The process of creating a backup in SqlBak may include not only creating, compressing, encrypting, and sending data to cloud storage locations, but also sending notifications and running a restore to another computer.
This service allows you to create not only MySQL backups, but also ones for PostgreSQL
and MS SQL Server databases.
On Linux, this service is free if you are connecting fewer than 5 servers.
Installation and Registration
You can install the app using the standard package managers that come with your operating system. You can find the package installation and registration instructions for your OS here.
The installation of the SqlBak app on Linux is very simple, more details on how to do it can be found here.
After registration, your server will appear on the Dashboard page.
sudo sqlbak --add-connection --db-type=mysql --user=root
Now you can create a backup job. This can be done by clicking “Add new job” and then “Create job.”
In the “Select databases” section, you can select the databases that you want to back up.
Then, in the “Store backups in destinations” section, you can choose where to send files. You can choose Folder, FTP, Amazon S3, GoogleDrive, DropBox, Azure Blob Storage, or OneDrive. Note: You can select several destinations at once, such as, for example, a Folder and Amazon S3.
In the Schedule backups section, you can specify the time when to run backup jobs each
day. You can change the interval and specify other launch rules in Advanced settings.
In the Send email confirmation section, you can click on the “Test” button to test the sending of email notifications to the entered email address.
On the backup job settings page, you can find a host of other interesting settings, such as
Execute After & Before Scripts, Encryption, and Backup Folder.
The Restore Tasks section deserves special attention. This section allows you to restore a
newly created file to another database (for example, to the development server).
When you click the “Run Now” button, it will immediately run the job and display the job
SqlBak provides a wide range of options for automating MySQL backups. One distinctive feature of this solution is its ease of configuration and support. It also allows you to centrally manage backups for multiple servers. This solution not only automates the download of backups to various cloud storage locations, but also automates the removal of old backups.
- Ideally, you can customize a solution that takes all of your specific requirements into account
- If you want to create a more complicated solution, you need to have advanced knowledge of how to write bash scripts
- It’s difficult to configure the sending of backups to the cloud
- Built-in incremental backups
- You may only specify a folder as the backup storage location
- No integrated solution for removing old backups
- Easy to use and maintain
- Able to send backups to cloud storage
- Able to automatically restore databases
- Able to back up folders along with databases
- Automated deletion of old backups
- No support for incremental backups