MuckNet

Using root for your MySQL backups is a bad idea boys and girls. You should dedicate a user to doing your backups. Below are a few options for setting up minimum permissions for your dedicated mysql backup user:

Using mysqldump with –single-transaction

GRANT SELECT

ON *.*

TO  'MysqlBackupUser'@'localhost'

IDENTIFIED BY 'MySQLBackupUserPassword'

Using mysqldump with –single-transaction (–flush-logs) –master-data=1

–flush-logs only requires RELOAD, –master-data requires RELOAD and REPLICATION CLIENT

GRANT SELECT, RELOAD, REPLICATION CLIENT

ON *.*

TO  'MysqlBackupUser'@'localhost'

IDENTIFIED BY 'MySQLBackupUserPassword'

Using ec2-consistent-snapshot – which freezes the file XFS filesystem, while issuing an Amazon EC2 API call to snapshot the EBS Volume. The pertinent commands from the script are:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
SYSTEM xfs_freeze -f /vol
UNLOCK TABLES;
EXIT

Everything except the SHOW MASTER STATUS can be accomplished with RELOAD:

GRANT RELOAD, REPLICATION CLIENT

ON *.*

TO  'MysqlBackupUser'@'localhost'

IDENTIFIED BY 'MySQLBackupUserPassword'

If you want to go completely hog wild, and do things like purge binary logs, or you’re concerned that you’ll run out of max_connections you can use SUPER. But be careful, because it also does things like allow you to write to a read_only set server.

GRANT SELECT, LOCK TABLES, RELOAD, REPLICATION CLIENT, SUPER

ON *.*

TO  'MysqlBackupUser'@'localhost'

IDENTIFIED BY 'MySQLBackupUserPassword'

And in All cases after you make GRANT changes you’ll need to:

FLUSH PRIVILEGES;

I suggest taking a look at AutoMySQLBackup for simple daily, weekly, and monthly mysql backup rotations. It is not the most complete system, but its easy, and works well. It can even email you logs every night if you want. I have mine setup to go to syslog-ng/SEC, where I watch for errors, or the lack of a success.