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:
/var/lib/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