Automated MySQL backups using LogRotate + monitoring agent on rackspace

mysql-backup-using-logrotate.md

Automated MySQL backups

Create a dedicated backup user

create user 'backup_prod'@'localhost' identified by  '{password}';

grant
    SELECT, RELOAD, FILE, SUPER, LOCK TABLES, SHOW VIEW
on *.* to 'backup_prod'@'localhost'
WITH
    MAX_QUERIES_PER_HOUR 0
    MAX_CONNECTIONS_PER_HOUR 0
    MAX_UPDATES_PER_HOUR 0
    MAX_USER_CONNECTIONS 0
;

Add the credentials to my.cnf.

[mysqldump]
user = backup_prod
password = password

Setting up logrotate

If logrotate is not installed (this seems to be an apache dependency) install it with the following incantation.

$ sudo yum install logrotate

Create the backup directory.

$ sudo mkdir /var/backup/mysql

Use you favourite text editor to create the following in /etc/logrotate.d/mysql-backup.

/var/backup/mysql/db.sql.gz {
    daily
    rotate 8
    nocompress
    create 640 root root
    postrotate
    mysqldump -u {username} {--all-databases --events|databasename} > /var/backup/mysql/db.sql --single-transaction
    gzip -9f /var/backup/mysql/db.sql
    endscript
}

Touch the initial file.

$ sudo touch /var/backup/mysql/db.sql.gz

Test all is gravy.

$ sudo logrotate -f /etc/logrotate.d/mysql-backup

Installing the Rackspace monitoring agent

Install and and bootstrap the Rackspace cloud backup agent.

Setup the cloud backup for your server.

That was it!