What is AutoMySQLBackup?

AutoMySQLBackup is a shell script that helps to take an incremental backup of databases daily, weekly, and monthly. MySQL usually provides a command-line utility for exporting databases as raw SQL, named ‘mysqldump.’ Instead of running this utility manually, you can install AutoMySQLBackup script on your system to automatically run this utility everyday on all your databases. This script depends upon the ‘mysqldump’ command that comes with the mysql-client package. This script backs up the databases at the interval you have set to compressed tarballs. If you want, you can copy these tarballs to other media and encrypt them. Please note that during the time of creating the backup, the script locks the databases. The required time to complete the script execution can vary from a few seconds to minutes based on the size of the databases.

Installation of AutoMySQLBackup Script

Ubuntu

The installation of the AutoMySQLBackup script on Ubuntu is an easy task. For the same, execute the following command from the terminal.

# apt-get -y install automysqlbackup

After the installation, the AutoMySQLBackup script runs once a day automatically. The backups of the database created using the script gets stored in the following directory.

/var/lib/automysqlbackup

To run the script manually, execute the following command.

# automysqlbackup

The above script stores the backup as a .sql.gz file in the /var/lib/automysqlbackup directory.


CentOS

The AutoMySQLBackup script is not available in the CentOS repository by default. To install the same on CentOS, follow the below steps:

1. First, download the tar file for AutoMySQLBackup script using the following command.

# wget https://sourceforge.net/projects/automysqlbackup/files/latest/download?source=navbar

2. After the download, create a directory in /opt to store the installation files.

# mkdir /opt/automysqlbackup

3. Extract the downloaded tar file into the newly created directory by executing the following commands.

# mv automysqlbackup-v3.0_rc6.tar.gz /opt/automysqlbackup

# cd /opt/automysqlbackup

# tar -xzvf automysqlbackup-v3.0_rc6.tar.gz

4. Execute the following command to install the AutoMySQlBackup script.

# ./install.sh

5. After the installation, open the configuration file of the script using any of your preferred text editors.

# nano /etc/automysqlbackup/automysqlbackup.conf

Uncomment the following set of configurations in the file, and after that, save and close the file. Please set the configuration values as per your desire.

CONFIG_mysql_dump_username=‘rootuser’

CONFIG_mysql_dump_password=‘Password’

CONFIG_mysql_dump_host=‘your_host’

CONFIG_backup_dir=‘/backup/automysqlbackup’

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’

6. Execute the following command to create a directory to store the backup generated by the script.

# mkdir /backup/automysqlbackup

7. To manually run the AutoMySQLBackup script, execute the following command.

# automysqlbackup

8. Create a cronjob to run the AutoMySQLBackup script daily at a fixed time automatically. You can set the time interval as per your desire, in this case the script gets executed daily at 3 AM.

# crontab -e

0 3 * * * /usr/local/bin/automysqlbackup /etc/automysqlbackup/automysqlbackup.conf

After the execution of the script, it stores the backup file in .sql.gz format under the mentioned directory.