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.
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:
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.