MySQL Percona innobackupex 备份帐号需要的权限

..

MySQL 要备份数据库,可以使用 Percona 的 Tookit 的 innobackupex (xtrabackup) 来备份。

要为 innobackupex 建立一个备份专用的使用者,需要哪些权限呢?

MySQL Percona innobackupex 备份帐号需要的权限

Percona 使用 innobackupex、xtrabackup 来做 MySQL 资料的备份,需要开哪些权限才能备份?

官方文件可见此篇:Connection and Privileges Needed - Percona XtraBackup

innobackupex 里面包著 xtrabackup 在执行,需要的权限是一样的,需要的权限:

  • RELOAD
  • LOCK TABLES
    • RELOAD and LOCK TABLES (unless the --no-lock option is specified) in order to FLUSH TABLES WITH READ LOCK prior to start copying the files and
  • PROCESS
    • PROCESS in order to run SHOW ENGINE INNODB STATUS
  • REPLICATION CLIENT
    • REPLICATION CLIENT in order to obtain the binary log position,
  • 下述可选用:
    • CREATE TABLESPACE in order to import tables (see Restoring Individual Tables)
    • SUPER in order to start/stop the slave threads in a replication environment.

Percona innobackupex 授权(Grant)语法

  1. mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cret';
  2. mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'bkpuser'; # 先将所有权限移掉
  3. mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON . TO 'bkpuser'@'localhost';
  4. mysql> FLUSH PRIVILEGES;

Percona innobackupex、xtrabackup 操作方式

  • $ innobackupex --user=DBUSER --password=SECRET /path/to/backup/dir/
  • $ innobackupex --user=LUKE --password=US3TH3F0RC3 --stream=tar ./ | bzip2 -
  • $ xtrabackup --user=DVADER --password=14MY0URF4TH3R --backup --target-dir=/data/bkps/

相关网页

想在手机阅读更多MySQL资讯?下载【香港硅谷】Android应用
分享到Facebook