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
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
Install and and bootstrap the Rackspace cloud backup agent.