Movatterモバイル変換

MySQL Enterprise Backup 8.4 User's Guide

4.1.2 Grant MySQL Privileges to Backup Administrator

Themysqlbackup command connects to the MySQL server using the credentials supplied with the--user and--password options. The specifieduser needs certain privileges. You can either create a new user with a limited set of privileges, or use an administrative account such as root. Here are the privileges required bymysqlbackup:

The minimum privileges for the MySQL user with whichmysqlbackup connects to the server include:
- SELECT on all databases and tables, for table locks that protect the backups against inconsistency caused by parallel DDL operations.
- BACKUP_ADMIN on all databases and tables.
- RELOAD on all databases and tables.
- SUPER, to enable and disable logging, and to optimize locking in order to minimize disruption to database processing.
- REPLICATION CLIENT, to retrieve thebinary log position, which is stored with the backup.
- PROCESS, to process DDL statements with theALGORITHM = INPLACE clause.
- CREATE,INSERT,DROP, andUPDATE on the tablesmysql.backup_progress andmysql.backup_history, and alsoSELECT andALTER onmysql.backup_history.
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 themysql client program:
```
CREATE USER 'mysqlbackup'@'localhost' IDENTIFIED BY 'password';GRANT SELECT, BACKUP_ADMIN, RELOAD, PROCESS, SUPER, REPLICATION CLIENT ON *.*     TO `mysqlbackup`@`localhost`;GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'mysqlbackup'@'localhost'; GRANT CREATE, INSERT, DROP, UPDATE, SELECT, ALTER ON mysql.backup_history     TO 'mysqlbackup'@'localhost';
```
The following additional privileges are required for using specific features of MySQL Enterprise Backup:
- For usingtransportable tablespaces (TTS) to back up and restore InnoDB tables:
  - LOCK TABLES 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.
- Forcreating tape backups using the System Backup to Tape (SBT) API:
  - CREATE,INSERT,DROP, andUPDATE on themysql.backup_sbt_history table
- Forworking with encrypted InnoDB tables:
  - ENCRYPTION_KEY_ADMIN to enable InnoDB encryption key rotation.
- For backing up and restoring user-created non-InnoDB tables:
  - LOCK TABLES on all schemas containing user-created non-InnoDB tables
- For usingredo log archiving for backups:
  - INNODB_REDO_LOG_ARCHIVE to invoke theinnodb_redo_log_archive_start() function .
- ForSection 5.1.4, “Table-Level Recovery (TLR)” ofnon-TTS backups:
  - INSERT andALTER to update tables
Set those additional privileges if you are using the features that require them. To set all of them, issue statements like the following from themysql client program:
```
GRANT LOCK TABLES, CREATE, DROP, FILE, INSERT, ALTER ON *.* TO 'mysqlbackup'@'localhost';GRANT CREATE, DROP, UPDATE ON mysql.backup_sbt_history TO 'mysqlbackup'@'localhost';GRANT ENCRYPTION_KEY_ADMIN ON *.* TO 'mysqlbackup'@'localhost';GRANT INNODB_REDO_LOG_ARCHIVE ON *.* TO 'mysqlbackup'@'localhost';
```
For privileges required for using MySQL Enterprise Backup with a Group Replication setting, seeChapter 9,Using MySQL Enterprise Backup with Group Replication.
The following additional privileges might also be required after a server upgrade:
- When using MySQL Enterprise Backup 8.4 for the first time on a MySQL Server that has been upgraded from 8.0.18 or earlier and has been backed up by MySQL Enterprise Backup before:
  - ALTER onmysql.backup_progress.
  - CREATE,INSERT, andDROP onmysql.backup_progress_old.
  - CREATE,INSERT,DROP, andALTER onmysql.backup_progress_new.
  Grant these privileges by issuing these sample statements at themysql client:
```
GRANT ALTER ON mysql.backup_progress TO 'mysqlbackup'@'localhost';GRANT CREATE, INSERT, DROP ON mysql.backup_progress_old TO 'mysqlbackup'@'localhost';GRANT CREATE, INSERT, DROP, ALTER ON mysql.backup_progress_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 alsoChapter 9,Using MySQL Enterprise Backup with Group Replication.
  These privileges are for the attempt to migrate themysql.backup_progress table to a newer format (seeAppendix F,Backup Progress Table Update for details), and they are no longer needed after the first backup operation by MySQL Enterprise Backup 8.4 has taken place on the server, by which point they can be revoked.
- When using MySQL Enterprise Backup 8.4 for the first time on a MySQL Server that has been upgraded from 8.0.11 or earlier and has been backed up by MySQL Enterprise Backup before:
  - CREATE,INSERT, andDROP onmysql.backup_history_old.
  - CREATE,INSERT,DROP, andALTER onmysql.backup_history_new.
  Grant these privileges by issuing these sample statements at themysql 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 alsoChapter 9,Using MySQL Enterprise Backup with Group Replication.
  These privileges are for the attempt to migrate themysql.backup_history table to a newer format (seeAppendix D,Backup History Table Update for details), and they are no longer needed after the first backup operation by MySQL Enterprise Backup 8.4 has taken place on the server, by which point they can be revoked.
- When performing for the first time a backupusing the SBT API with MySQL Enterprise Backup 8.4on a MySQL Server that has been upgraded from 8.0.20 or earlier and has been backed up by MySQL Enterprise Backup beforeusing the SBT API:
  - ALTER onmysql.backup_sbt_history.
  - CREATE,INSERT, andDROP onmysql.backup_sbt_history_old.
  - CREATE,INSERT,DROP, andALTER onmysql.backup_sbt_history_new.
  Grant these privileges by issuing these sample statements at themysql client:
```
GRANT ALTER ON mysql.backup_sbt_history TO 'mysqlbackup'@'localhost';GRANT CREATE, INSERT, DROP ON mysql.backup_sbt_history_old TO 'mysqlbackup'@'localhost';GRANT CREATE, INSERT, DROP, ALTER ON mysql.backup_sbt_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 alsoChapter 9,Using MySQL Enterprise Backup with Group Replication.
  These privileges are for the attempt to migrate themysql.backup_sbt_history table to a newer format (seeAppendix E,SBT Backup History Table Update for details), and they are no longer needed after the first backup operation by MySQL Enterprise Backup 8.4 using the SBT API has taken place on the server, by which point they can be revoked.

Note

Make sure that the limitMAX_QUERIES_PER_HOUR is not set for the usermysqlbackup uses to access the server, or backup operations might fail unexpectedly.

PREV HOME UP NEXT