Using automysqlbackup to create regular backups of your database


This works on Debian and Ubuntu, but also on other distributions.
The package can be installed via apt: apt-get install automysqlbackup
For yum (Fedora, CentOS, RedHat, etc.) there's usually also a package provided.

Next you need to edit the configuration file /etc/default/automysqlbackup. There are other options, the most important ones are:

USERNAME=root
PASSWORD=……..
DBHOST=localhost
DBNAMES=`mysql –defaults-file=/etc/mysql/debian.cnf –execute="SHOW DATABASES" | awk '{print $1}' | grep -v ^Database$ | grep -v ^mysql$ | grep -v ^performance_schema$ | grep -v ^information_schema$ | tr \\\r\\\n ,\ `
BACKUPDIR="/var/lib/automysqlbackup"
LATEST=yes

DBNAMES can be a list of space-separated database names, the above command tries to find all available databases except the mysql default ones (mysql, information_schema, performance_schema).
On Debian by default there are commands to find the username and password of the debian-sys-maint user.

When finished you can simply call automysqlbackup from the command line.

The tool organizes your backups in /var/lib/automysqlbackup (if not changed above), in the subfolders daily, weekly, monthly (and if you set LATEST to yes in the configuration file it will keep an extra copy of the most recent backup in the latest subfolder).

You now can just create a cron job that executes /usr/sbin/automysqlbackup from time to time.

In order to save your backups to an external storage, you might want to check out this.