Documentation Home
MySQL Enterprise Backup 4.1 User's Guide
Related Documentation Download this Manual
PDF (US Ltr) - 1.3Mb
PDF (A4) - 1.3Mb
HTML Download (TGZ) - 194.0Kb
HTML Download (Zip) - 223.4Kb


MySQL Enterprise Backup 4.1 User's Guide  /  ...  /  Grant MySQL Privileges to Backup Administrator

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. Here are the privileges required by mysqlbackup:

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

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

    • SELECT on performance_schema.replication_group_members, to know whether the server instance is part of a Group Replication setup and, if so, to gather information on the group members (required by release 4.1.2 and later).

    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, ALTER 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';
    GRANT SELECT ON performance_schema.replication_group_members TO 'mysqlbackup'@'localhost';
  • The following additional privileges are required when using MySQL Enterprise Backup 4.1.2 for the first time :

    • CREATE, INSERT, and DROP on mysql.backup_history_old.

    • CREATE, INSERT, DROP, and ALTER on mysql.backup_history_new.

    Grant these privileges by issuing these sample statements at the mysql client:

    GRANT CREATE, INSERT, DROP ON mysql.backup_history_old TO 'mysqlbackup'@'localhost';
    GRANT CREATE, INSERT, DROP, ALTER ON mysql.backup_history_new TO 'mysqlbackup'@'localhost';
    Note

    If you are working with a multiprimary Group Replication setting, make sure these privileges are granted on all primary nodes; see also Chapter 8, Using MySQL Enterprise Backup with Group Replication.

    These privileges are for the attempt to migrate the mysql.backup_history table to a newer format (see Appendix D, Backup History Table Update for details), and they are no longer needed after the first backup operation by MySQL Enterprise Backup 4.1.2 has taken place on the server, by which point they can be revoked.

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

    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';
  • For privileges required for using MySQL Enterprise Backup with a Group Replication setting, see Chapter 8, Using MySQL Enterprise Backup with Group Replication.


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Jason Mallory on November 9, 2017
GRANT ALTER ON mysql.backup_history TO 'mysqlbackup'@'localhost';
ERROR 1146 (42S02): Table 'mysql.backup_history' doesn't exist
  Posted by Mike Worthley on October 2, 2018
This works. Just tuck the ALTER statement in with the rest of the privileges.

GRANT CREATE, INSERT, SELECT, DROP, UPDATE, ALTER ON mysql.backup_history TO 'mysqlbackup'@'localhost';
Sign Up Login You must be logged in to post a comment.