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
, 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.
To set these privileges for a MySQL user
(mysqlbackup
in this example) connecting from
localhost, issue statements like the following from the
mysql
client program:
GRANT RELOAD ON *.* TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'mysqlbackup'@'localhost';
GRANT CREATE, INSERT, SELECT, ALTER, DROP, UPDATE ON mysql.backup_history
TO 'mysqlbackup'@'localhost';
GRANT REPLICATION CLIENT ON *.* TO 'mysqlbackup'@'localhost';
GRANT SUPER ON *.* TO 'mysqlbackup'@'localhost';
The following additional privileges are required 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 director.
To set these privileges, issue a statement like the following
from the mysql
client program:
GRANT LOCK TABLES, SELECT, CREATE, DROP, FILE ON *.* TO 'mysqlbackup'@'localhost';