How to Automate MySQL Database Backups in Linux

Automated backup of MySQL databases can be a serious task. The proper way to automate backups involves the following steps:

  1. Create a database backup
  2. Zip the backup
  3. Encrypt the compressed file
  4. Send the backup to storage over the Internet, FTP, Dropbox, AWS, Google Drive, etc.
  5. Receive email notifications concerning backup results
  6. Create a backup schedule
  7. Delete old backups

This article will consider the following ways of carrying out these steps:

  1. Create a bash script
  2. Use AutoMySQLBackup
  3. Use SqlBak

Bash script

In most cases a bash script is a good solution. However, not all storage locations allow you to upload files using shell commands. But 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
The following is the simplest implementation of a bash script for performing regular backups. At the beginning of the script, we specify the backup storage directory, the notification email address, and the backup storage period.
# Backup storage directory 
backupfolder=/var/backups

# Notification email address 
recipient_email=<username@mail.com>

# 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.

Carry out the following steps to do this
sudo crontab -e
And add the script path to the end of the string
30 22 * * * /home/user/script/mysql_backup.sh
Thus, your script will be executed every day at 10:30 PM.

Summary

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

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.

Installation

If your Linux distribution is Debian, then AutoMySQLBackup is available in the repository,
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.

  1. Download the latest version of AutoMySQLBackup:
  2. Create a directory and unzip the downloaded archive
    mkdir /opt/automysqlbackup
    mv AutoMySQLBackup-master.zip 
    cd /opt/automysqlbackup
    tar -zxvf AutoMySQLBackup-master.zip
  3. Then install AutoMySQLBackup
    ./install.sh

AutoMySQLBackup Settings

To configure AutoMySQLBackup, you must edit the file automysqlbackup.conf:
sudo nano /etc/automysqlbackup/automysqlbackup.conf
AutoMySQLBackup contains many settings, and you can find the full list in the official
documentation.

Please note the following:

  1. Path to the configuration file
    CONFIG_configfile="/etc/automysqlbackup/automysqlbackup.conf"
  2. The path to the backup storage directory
    CONFIG_backup_dir='/var/backup/db'
  3. Information for connecting to MySQL
    CONFIG_mysql_dump_username='root'
    CONFIG_mysql_dump_password='my_password'
    CONFIG_mysql_dump_host='localhost'
  4. List of databases that you need or do not need to back up
    CONFIG_db_names=('my_db')
    CONFIG_db_exclude=('information_schema')
  5. Email notification address
    CONFIG_mail_address='mail@google.com'
  6. Options for scheduling
    CONFIG_rotation_daily=6
    CONFIG_rotation_weekly=35
    CONFIG_rotation_monthly=150
  7. Run 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:
    #!/bin/bash 
    # 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.

Running

To run AutoMySqlBackup execute the following command:
automysqlbackup /etc/automysqlbackup/automysqlbackup.conf

Summary

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

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 such steps as: creating, compressing, encrypting, and sending data to cloud storage locations, sending notifications, and running a restore to another computer.

This service allows you to create not only MySQL backups, but also ones of 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.

After installation, you need to enter the app registration command on sqlbak.com (substitute the guid from the download page for the zeros)
sudo sqlbak -r -k 00000000-0000-0000-0000-000000000000
After registration, your server will appear on the Dashboard page.

Then you should add a connection to the MySQL database:
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 Database section you can select the databases that you want to back up.

And in the Store backups in destination 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
execution log.

Summary:

SqlBak provides a wide range of options for automating MySQL backups. One distinctive feature of this solution is its ease of configuration and support. and it also allows you to centrally manage backups for multiple servers. This solution not only automates the download of backups to various cloud storages, but it also automates the removal of old backups.

Conclusion

Bash scripts

Advantages:

  1. Ideally, you can customize a solution that takes all of your specific requirements into account

Disadvantages:

  1. If you want to create a more complicated solution, you need to possess serious knowledge of how to write bash scripts
  2. It’s difficult to configure the sending of backups to the cloud

AutoMySQLBackup

Advantages:

  1. Built-in incremental backups

Disadvantages:

  1. You may only specify a folder as the backup storage location
  2. No integrated solution for removing old backups

SqlBak app

Advantages:

  1. Easy to use and maintain
  2. Able to send backups to cloud storage
  3. Able to automatically restore databases
  4. Able to back up folders along with databases
  5. Automated deletion of old backups

Disadvantages:

  1. No support for incremental backups

Leave a Reply

Your email address will not be published. Required fields are marked *