How to set up MySQL user privileges for backup and restore

This article is for Zmanda Recovery Manager For MySQL (ZRM)

We show in the example below the steps for granting a MySQL database user required privileges to perform backups and restores for all tables in all databases of a MySQL server. If the user specified does not already exist, it will be created. Similarly, two different database users could be used: one for backups and one for restores. Please see Required Privileges for the MySQL Account in the ZRM for MySQL User Manual for more details and the most current list of privileges required each for backups and restores.

Example Environment

Backup user name:backup-user
Backup user password:zrmbackup
ZRM backup server host:quartz.zmanda.com
MySQL server host:db2.zmanda.com
MySQL server version5.1.x

Steps

  1. Login to MySQL server.
  2. Connect to MySQL as root user.db2:/ # mysql -u root -p
    Enter password:
    Welcome to the MySQL monitor. Commands end with ; or g.
    Your MySQL connection id is 1 to server version: 5.1.08
    Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
    mysql>
  3. Grant privileges for user backup-user to connect from ZRM server quartz.zmanda.com with password zrmbackup.Note: localhost can be used instead of quartz.zmanda.com when the MySQL server is local to the ZRM server.mysql> GRANT SELECT, INSERT, UPDATE, CREATE, DROP, RELOAD, SHUTDOWN, FILE, INDEX, ALTER, SUPER, LOCK TABLES, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, TRIGGER, CREATE ROUTINE, DELETE, EVENT, ALTER ROUTINE ON *.* TO 'backup-user'@'quartz.zmanda.com' IDENTIFIED BY 'zrmbackup';
    Query OK, 0 rows affected (0.01 sec)
  4. Flush Privileges.mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec) using password