How to take MySQL database backup in Linux? Automate Daily, Weekly and Monthly backups of MySQL databases in Linux with AutoMySQLBackup.

AutoMySQLBackup is a shell script that helps to automate incremental daily, weekly and monthly backups of MySQL databases in Linux. It’s an easy to configure utility which brings you the flexibility to customize the database backups to meet your specific needs, that too without having the trouble of creating complex backup scripts. Let’s get into the steps:

1) Download and install AutoMySQLBackup

1.1) If your Linux distribution is Debian based like Ubuntu, then AutoMySQLBackup is available in the repository by default, and the installation is pretty simple. Just run the following command:

apt-get install automysqlbackup

1.2) If your Linux distribution is RPM-based like CentOS, then AutoMySQLBackup is not available in the repository by default.

1.2.1) Either download it manually from Sourceforge or use the wget command to get it downloaded.

1.2.2) Create a new folder ‘automysqlbackup‘ in /opt and move the downloaded tar file, automysqlbackup-vX.X_XXX.tar.gz to /opt/automysqlbackup.

mv automysqlbackup-vX.X_XXX.tar.gz /opt/automysqlbackup

1.2.3) Extract the downloaded tar file to the new folder by executing the following commands:

cd /opt/automysqlbackup
tar -xzvf automysqlbackup-vX.X_XXX.tar.gz

1.2.4) Install AutoMySQLBackup by running the following command:

./install.sh

2) Configure AutoMySQLBackup script

Open the configuration file to set the values for backup:

nano /etc/automysqlbackup/automysqlbackup.conf

Most of the settings will be commented by default. You can customize the configuration by uncommenting and setting the values as you need.

2.1) Settings to access the MySQL server

CONFIG_mysql_dump_username='root' 
CONFIG_mysql_dump_password='password' 
CONFIG_mysql_dump_host='localhost'

Note that you can use ROOT or any other existing MySQL user to connect to the MySQL server. Just that the given user must have access to the database(s) you want to back up. CONFIG_mysql_dump_host is the host name or the IP address of your MySQL server.

2.2) Create a new directory for the backup storage and specify the path here

CONFIG_backup_dir='/opt/backup/db/'

AutoMySQLBackup will automatically create folders for storing daily, weekly and monthly backups in ‘/opt/backup/db/‘.

2.3) Specify the names of databases to backup

CONFIG_db_names=('my_database1')

You can specify as many database names as you need e.g. ( ‘DB1’ ‘DB2’ ‘DB3’ … ) If you want backup of all databases, just set it to (), i.e. empty.

2.4) Specify the database(s) that do not need to back up

CONFIG_db_exclude=( 'information_schema' )

2.5) Specify the rotation settings – which day you want monthly and weekly backups and how long you want to keep those.

CONFIG_do_monthly="15"
CONFIG_do_weekly="4"

This creates monthly backup on 15th of every month and weekly backup on Thursdays.

The value of CONFIG_do_monthly can be any number between 01 and 31. If the given value is greater than the last day of the month, it will be done on the last day of the month. Set it to 0 if you want to disable monthly backups.

Similarly, CONFIG_do_weekly accepts any value between 1 and 7 where 1 is Monday. Set it to 0 if you want to disable weekly backups.

CONFIG_rotation_daily=3
CONFIG_rotation_weekly=14
CONFIG_rotation_monthly=30

With this setting, daily backups are kept for 3 days, weekly backups are kept for 14 days, and monthly backups for 30 days.

2.6) Set the port for the mysql server connection

CONFIG_mysql_dump_port=3306

2.7) Choose the compression type for backup files

CONFIG_mysql_dump_compression='gzip'

2.8) Provide an email address if you want the backup files sent as email [optional].

CONFIG_mail_address='db-backups@example.com'

That’s all you need to do to set up AutoMySQLBackup script. Just make sure that you have created the backup storage directory as mentioned in section 2.2

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

automysqlbackup

When run for the first time, AutoMySQLBackup script will create folders for storing daily, weekly and monthly backup files in the backup storage directory.

To run the script daily at a fixed time automatically, set a cronjob as shown below:

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

This will execute the AutoMySQLBackup script daily at 2 AM.