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 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.

• PROCESS, to process DDL statements with the ALGORITHM = INPLACE clause.

To create a MySQL user (mysqlbackup in this example) and set the above-mentioned privileges for the user to connect from localhost, issue statements like the following from the mysql client program:

CREATE USER 'mysqlbackup'@'localhost' IDENTIFIED BY 'new-password';
GRANT RELOAD ON *.* TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, SELECT, DROP, UPDATE ON mysql.backup_history TO 'mysqlbackup'@'localhost';
GRANT REPLICATION CLIENT ON *.* TO 'mysqlbackup'@'localhost';
GRANT SUPER ON *.* TO 'mysqlbackup'@'localhost';
GRANT PROCESS ON *.* TO 'mysqlbackup'@'localhost';

The following additional privileges are required for using specific features of MySQL Enterprise Backup:

• 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 directory.

• For creating tape backups using the System Backup to Tape (SBT) API :

  • CREATE, INSERT, DROP, and UPDATE on the mysql.backup_sbt_history table

Set those additional privileges if you are using the features that require them. To set all of them, issue a statement like the following from the mysql client program:

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