Lets use AutoMySQLBackup to create backups of the MySQL-databases.
Installation
Download the latest version from AutoMySQLBackup, extract the archive and follow the installation instructions.
At the moment the most recent version is 3.0-rc6:
mkdir /tmp/amb
cd /tmp/amb
wget http://netcologne.dl.sourceforge.net/project/automysqlbackup/AutoMySQLBackup/AutoMySQLBackup%20VER%203.0/automysqlbackup-v3.0_rc6.tar.gz
tar xvfz automysqlbackup-v3.0_rc6.tar.gz
/tmp/amb/install.sh
Configuration
Create a folder to store the backups:
mkdir -p /srv/backup/db
and edit the configuration file /etc/automysqlbackup/automysqlbackup.conf to include the line:
CONFIG_backup_dir='/srv/backup/db'
Set the MySQL user credentials (using the same file):
CONFIG_mysql_dump_username='user' # Probably 'root'
CONFIG_mysql_dump_password='password'
Schedule backups
Add the backupscript to the crontab of root (crontab -e):
# m h dom mon dow command
0 4 * * * /usr/local/bin/automysqlbackup >> /var/log/cron.log
Pitfalls
You could get the following error:
mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES
This has to do with the MySQL version (>5.5) and the performance_schema database. Adding --skip-lock-tables to the mysqldump command will fix this. However, since mysqldump is called by automysqlbackup, this is easier said than done. So, we’re going to edit /etc/mysql/my.cnf and add these lines to the mysqldump section:
[mysqldump]
skip-lock-tables