How to Backup MySQL Database with AutoMySQLBackup on Ubuntu Linux

AutoMySQLBackup is a free, open-source tool that automatically backs up all the databases hosted on a MySQL server at varying intervals.

It’s a great tool that may come in handy in times of trouble. It can back up MySQL databases automatically and comes with many features like email notifications, incremental backups, backup compression, and encryption.

You may want to try this if you’re looking for a simple yet powerful tool to back up MySQL databases automatically. You probably won’t regret it.

This brief tutorial will show you how to install and configure it on Ubuntu servers.

To get it installed and configured, follow the steps below

Install AutoMySQLBackup

This post assumes you already have a MySQL server installed and have a few databases to back up. If you haven’t done that, please search this blog for easy tutorials on installing MySQL or MariaDB servers.

When you have a MySQL server installed and a few databases created, continue below to install AutoMySQLBackup.

Run the commands below to install the tool.

sudo apt-get update
sudo apt-get install automysqlbackup

You’ll be prompted to choose a mail configuration, as shown below, during the installation. Select Internet Site if you’re going to be setting up email notifications. If not, then select No configuration.

Please select the mail server configuration type that best meets your needs.
No configuration:
Should be chosen to leave the current configuration unchanged.
Internet site:
Mail is sent and received directly using SMTP.
Internet with smarthost:
Mail is received directly using SMTP or by running a utility such
as fetchmail. Outgoing mail is sent using a smarthost.
Satellite system:
All mail is sent to another machine, called a 'smarthost', for delivery.
Local only:
The only delivered mail is the mail for local users. There is no network.
General type of mail configuration:

                    No configuration
                    Internet Site
                    Internet with smarthost
                    Satellite system
                     Local only
         <Ok>                                       <Cancel> 

 

After selecting your options, select Ok to continue with the installation.

Configure AutoMySQLBackup

Now that the tool is installed go and configure it. I couldn’t find any configuration file created, so I had to create one myself. The default configuration directory for AutoMySQLBack is at:

/etc/automysqlbackup/

Create a new configuration file with the parameters as shown below. Run the commands below to create a new configuration file.

sudo nano /etc/automysqlbackup/automysqlbackup.conf

Then add the content below into the file and save.

CONFIG_mysql_dump_username='root'
CONFIG_mysql_dump_password='root_password'
CONFIG_mysql_dump_host='localhost'
CONFIG_backup_dir='/var/backup/db'
CONFIG_do_monthly="01"
CONFIG_do_weekly="5"
CONFIG_rotation_daily=6
CONFIG_rotation_weekly=35
CONFIG_rotation_monthly=150
CONFIG_mysql_dump_port=3306
CONFIG_mysql_dump_compression='gzip'

Save the file

Now to run the backup manually, type the commands below.

sudo automysqlbackup

To automatically run the backup daily, run the commands below.

sudo ln -s /usr/sbin/automysqlbackup /etc/cron.daily/

That’s it!

Also, look in the folder below for backup jobs run manually.

/var/lib/automysqlbackup

Summary:

This post shows students and new users an easy want to automatic MySQL backup on Ubuntu. The AuthMySQLBackup tool can set a backup job to run daily, weekly, or monthly.

The job will back up all MySQL databases and store them in the specified location.

Enjoy!