AutoMySQLBackup: Regularly Backup Your MySQL

If you have blogs or websites, your database is one of the most important things. Your data will be stored in your database, for this case it is MySQL. MySQL is a free database management system (DBMS) and bundled with Linux as LAMP (Linux, Apache, MySQL and PHP). Because it is so important, you have to regularly backup your data, as a disaster recovery step.

Actually to backup MySQL data is not hard. There are many tools provided for free to backup with a single click. But sometimes you forget to backup and then when disaster comes, you don’t have the latest backup. Of course this can be bad. Data lost is a nightmare!

AutoMySQLBackup is one of the great tools to backup multiple databases, compress the backups, back up remote databases, and email the logs. AutoMySQLBackup lets you take daily, weekly and monthly backups of your MySQL databases using mysqldump (MySQL function to backup the databases).

Install AutoMySQLBackup:

1. Install AutoMySQLBackup:

sudo apt-get install AutoMySQLBackup

2. Configure AutoMySQLBackup:

vim /etc/default/automysqlbackup

3. See the configuration below:

[...]
# Host name (or IP address) of MySQL server e.g localhost
#For remote database, fill the domain or ip address
DBHOST=localhost
[...]
# List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"
# The following is a quick hack that will find the names of the databases by
# reading the mysql folder content. Feel free to replace by something else.
#DBNAMES="db_ispconfig web1 web2 web3"
DBNAMES=`find /var/lib/mysql -mindepth 1 -maxdepth 1 -type d | cut -d'/' -f5 | grep -v ^mysql$ | tr rn , `
[...]
# Backup directory location e.g /backups
# Folders inside this one will be created (daily, weekly, etc.), and the
# subfolders will be database names.
#BACKUPDIR="/var/lib/automysqlbackup"
#BACKUPDIR="/path/to/backup/folder/"
[...]
# Email Address to send mail to? ([email protected])
MAILADDR="[email protected]"
[...]

4. Run AutoMySQLBackup:

automysqlbackup

5. Open the backup folder (default: /var/lib/automysqlbackup)

ls -l /var/lib/automysqlbackup

6. You will see the backup files.

7. Add it to cron to run it automatically.