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 include:
For MySQL Enterprise Backup 8.0.16 and later:
BACKUP_ADMIN
on all databases and tables.SELECT
onperformance_schema.variables_info
andperformance_schema.log_status
.
RELOAD
on all databases and tables.CREATE
,INSERT
,DROP
, andUPDATE
on the tablesmysql.backup_progress
andmysql.backup_history
, and alsoSELECT
andALTER
onmysql.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 theALGORITHM = INPLACE
clause.SELECT
onperformance_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 8.0.12 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 themysql
client program:CREATE USER 'mysqlbackup'@'localhost' IDENTIFIED BY 'password'; GRANT BACKUP_ADMIN ON *.* TO 'mysqlbackup'@'localhost'; # For release 8.0.16 and later GRANT SELECT ON performance_schema.variables_info TO 'mysqlbackup'@'localhost'; # For release 8.0.16 and later GRANT SELECT ON performance_schema.log_status TO 'mysqlbackup'@'localhost'; # For release 8.0.16 and later GRANT RELOAD 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'; 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 8.0.12 or later 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 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';
NoteIf you are working with a multiprimary Group Replication setting, make sure these privileges are granted on all primary nodes; see also Chapter 9, 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 8.0.12 or later 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:
For using transportable tablespaces (TTS) to back up and restore InnoDB tables:
LOCK TABLES
andSELECT
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
, andUPDATE
on themysql.backup_sbt_history
table
For working with encrypted InnoDB tables:
ENCRYPTION_KEY_ADMIN
to enable InnoDB encryption key rotation.
For MySQL Enterprise Backup 8.0.16 and later: For backing up and restoring user-created non-InnoDB tables:
SELECT
on all user-created non-InnoDB tablesLOCK TABLES
on all schemas containing user-created non-InnoDB tables
For MySQL Enterprise Backup 8.0.17 and later: For using redo log archiving for backups:
to invoke the user-defined functionINNODB_REDO_LOG_ARCHIVE
.innodb_redo_log_archive_start()
Set those additional privileges if you are using the features that require them. To set all of them, issue statements 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'; GRANT ENCRYPTION_KEY_ADMIN ON *.* TO 'mysqlbackup'@'localhost'; GRANT SELECT ON non-InnoDB_tbl TO 'mysqlbackup'@'localhost'; # For release 8.0.16 and later GRANT INNODB_REDO_LOG_ARCHIVE ON *.* TO 'mysqlbackup'@'localhost'; # For release 8.0.17 and later
For privileges required for using MySQL Enterprise Backup with a Group Replication setting, see Chapter 9, Using MySQL Enterprise Backup with Group Replication.