For most backup operations, the mysqlbackup
command connects to the MySQL server using the credentials
supplied with the
--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:
RELOADon all databases and tables.
UPDATEon the tables
mysql.backup_history, and also
SELECTand ALTER on
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 = INPLACEclause.
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
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';
Upgrading MySQL Enterprise Backup from 4.1.1 or earlier:
When upgrading to 4.1.2 or later, make sure that you also
mysql.backup_history_new. They are for
mysql.backup_history table to
a newer format, which happens when the first full backup takes
place after the upgrade. Grant the 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';
These privileges are no longer needed after the first backup operation has taken place after the upgrade, 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:
SELECTfor backing up tables.
CREATEfor restoring tables.
DROPfor dropping tables if the restore fails for some reasons.
FILEfor restoring tables in external tablespaces outside of the server's data directory.
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.