How to Back Up MySQL Databases with AutoMySQLBackup
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:
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:
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.