How to Back Up MySQL Databases with AutoMySQLBackup

Introduction

MySQL provides a command line utility, mysqldump, for exporting databases as raw SQL. Instead of running mysqldump manually, you can install a script that will automatically run mysqldump every day on all of your databases. One of the most popular of these scripts is automysqlbackup.

Step One: Install AutoMySQLBackup

You can install AutoMySQLBackup from the Ubuntu repositories by running the following command as root:

sudo apt-get install automysqlbackup

Once installed, the script will automatically run once a day. Backups will be stored in the directory:

/var/lib/automysqlbackup

Step Two: Verify AutoMySQLBackup

To verify AutoMySQLBackup was configured properly, you can run it manually rather than waiting for it to be run the first time.

To run AutoMySQLBackup manually, execute the following command as root:

sudo automysqlbackup

Now list all of the files under /var/lib/automysqlbackup:

$ ls -R /var/lib/automysqlbackup/
/var/lib/automysqlbackup/:
daily  monthly  weekly

/var/lib/automysqlbackup/daily:
exampledb  information_schema   performance_schema  test  wordpress

/var/lib/automysqlbackup/daily/exampledb:
exampledb_2014-08-03_06h48m.Sunday.sql.gz

...

If everything worked correctly, you will see .sql.gz files in the listings as shown above.

You should also regularly verify your database backup files. You may also consider regularly copying your database backups to another server.

Alert: For Control Panel Help & Tutorials, click here: Panel Tutorials