4.1.2 Grant MySQL Privileges to Backup Administrator

For most backup operations, the mysqlbackup command connects to the MySQL server using the credentials supplied with the --user and --password options. The specified user needs certain privileges. You can either create a new user with a minimal set of privileges, or use an administrative account such as root.

The minimum privileges for the MySQL user with which mysqlbackup connects to the server are:

• RELOAD on all databases and tables.

• CREATE, INSERT, DROP, and UPDATE on the tables mysql.backup_progress and mysql.backup_history, and also SELECT and ALTER on mysql.backup_history.

• SUPER, to enable and disable logging, and to optimize locking in order to minimize disruption to database processing.

• REPLICATION CLIENT, to retrieve the binary log position, which is stored with the backup.

To set these privileges for a MySQL user (mysqlbackup in this example) connecting from localhost, issue statements like the following from the mysql client program:

GRANT RELOAD ON *.* TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, SELECT, ALTER, DROP, UPDATE ON mysql.backup_history 
    TO 'mysqlbackup'@'localhost';
GRANT REPLICATION CLIENT ON *.* TO 'mysqlbackup'@'localhost';
GRANT SUPER ON *.* TO 'mysqlbackup'@'localhost';

The following additional privileges are required for using transportable tablespaces (TTS) to back up and restore InnoDB tables:

• LOCK TABLES and SELECT for backing up tables.

• CREATE for restoring tables.

• DROP for dropping tables if the restore fails for some reasons.

• FILE for restoring tables in external tablespaces outside of the server's data director.

To set these privileges, issue a statement like the following from the mysql client program:

GRANT LOCK TABLES, SELECT, CREATE, DROP, FILE ON *.* TO 'mysqlbackup'@'localhost';