← ServerPilot Docs

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.

If you would like to change configuration options, you may edit this file:

/etc/default/automysqlbackup

Here, you may configure databases you wish to back up or exclude from the backup process, et cetera.

To change the frequency automysqlbackup is run, you can adjust its cron job.

Additional information on automysqlbackup configuration can be found here:

https://manpages.ubuntu.com/manpages/bionic/man8/automysqlbackup.8.html

Last updated March 25, 2019

Launch your first site in 5 minutes