Xtrabackup,error：Error: failed to execute query SHOW ENGINE INNODB STATUS: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation
今天在使用非【root】用户执行【xtrabackup】的时候遇到了如题所示的错误；
错误原因很明显，使用的用户权限不够。
具体错误的现场如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
[root@mysql1 log]# cat backup_20190818_235113.log xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --server-id=1 --log_bin=binlog --log_bin=/var/lib/mysql/mysql-bin --parallel=4 xtrabackup: recognized client arguments: 190818 23:51:13 innobackupex: Starting the backup operation IMPORTANT: Please check that the backup run completes successfully. At the end of a successful backup run innobackupex prints "completed OK!". 190818 23:51:14 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;host=127.0.0.1;port=3306;mysql_socket=/var/lib/mysql/mysql.sock' as 'xtrabk' (using password: YES). 190818 23:51:14 version_check Connected to MySQL server 190818 23:51:14 version_check Executing a version check against the server... 190818 23:51:14 version_check Done. 190818 23:51:14 Connecting to MySQL server host: 127.0.0.1, user: xtrabk, password: set, port: 3306, socket: /var/lib/mysql/mysql.sock Using server version 5.7.26-29-log Error: failed to execute query SHOW ENGINE INNODB STATUS: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation [root@mysql1 log]# |
以【root】用户登录数据库，看看我的这个用户的当前状态：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
[root@mysql1 ~]# mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.7.26-29-log Percona Server (GPL), Release 29, Revision 11ad961 Copyright (c) 2009-2019 Percona LLC and/or its affiliates Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@db 00:12: [(none)]> root@db 00:13: [(none)]>select user,host from mysql.user; +---------------+-----------+ | user | host | +---------------+-----------+ | repl | % | | xtrabk | % | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+-----------+ 5 rows in set (0.00 sec) root@db 00:14: [(none)]> root@db 00:14: [(none)]>show grants for xtrabk@'%'; +------------------------------------+ | Grants for xtrabk@% | +------------------------------------+ | GRANT USAGE ON *.* TO 'xtrabk'@'%' | +------------------------------------+ 1 row in set (0.00 sec) root@db 00:14: [(none)]> |
可以看到，xtrabk用户的权限是创建时候的默认权限状态
因此，上面xtrabackup的报错中提到的【show engine innodb status】肯定也是没有权限查看的：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
[root@mysql1 ~]# mysql --login-path=xtrlogin Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 5.7.26-29-log Percona Server (GPL), Release 29, Revision 11ad961 Copyright (c) 2009-2019 Percona LLC and/or its affiliates Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. xtrabk@db 00:12: [(none)]>SHOW ENGINE INNODB STATUS; ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation xtrabk@db 00:12: [(none)]> |
解决问题：
为xtrabackup的执行用户分配需要的权限：
MySQL数据库用户的权限：
- MyISAM引擎：Xtrabackup中的Inoobackup备份MyISAM的时候需要用到权限：RELOAD / LOCK TABLES
- 查看二进制文件（BINLOG）写到了哪里，需要的权限：REPLICATION CLIENT
- 查看MySQL相关连接的进程信息，需要的权限：PROCESS
- 在复制环境中，如果需要执行【stop slave / start slave】，则需要权限：SUPER
- Xtrabackup自身会在MySQL中创建一些对它本身有用的表对象，需要的权限：create / insert / select；可选权限：create tablespace
Linux操作系统用户的权限：
- 对相关【备份目录】，应该具有写入与读取的权限【wrx】
- 对MySQL的【数据目录 / datadir】，应该需要可读权限【rx】
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
root@db 00:14: [(none)]>grant RELOAD,LOCK TABLES,REPLICATION CLIENT,PROCESS,SUPER,CREATE,INSERT,SELECT on *.* to xtrabk@'%'; Query OK, 0 rows affected (0.00 sec) root@db 00:14: [(none)]>flush privileges; Query OK, 0 rows affected (0.00 sec) root@db 00:14: [(none)]> root@db 00:28: [(none)]>show grants for xtrabk@'%'; +--------------------------------------------------------------------------------------------------------------+ | Grants for xtrabk@% | +--------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, INSERT, CREATE, RELOAD, PROCESS, SUPER, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'xtrabk'@'%' | +--------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) root@db 00:28: [(none)]> |
然后，再次用【xtrabk】去执行【show engine innodb status】：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 |
xtrabk@db 00:28: [(none)]>select user(); +------------------+ | user() | +------------------+ | xtrabk@localhost | +------------------+ 1 row in set (0.00 sec) xtrabk@db 00:28: [(none)]> xtrabk@db 00:28: [(none)]>SHOW ENGINE INNODB STATUS\G *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2019-08-19 00:28:05 0x7efe047f8700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 18 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 2 srv_active, 0 srv_shutdown, 25416 srv_idle srv_master_thread log flush and writes: 25418 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 16 OS WAIT ARRAY INFO: signal count 16 RW-shared spins 0, rounds 7, OS waits 3 RW-excl spins 0, rounds 0, OS waits 0 RW-sx spins 0, rounds 0, OS waits 0 Spin rounds per wait: 7.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx ------------ TRANSACTIONS ------------ Trx id counter 2319 Purge done for trx's n:o < 2319 undo n:o < 0 state: running but idle History list length 4 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421104853691752, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 421104853690624, not started 0 lock struct(s), heap size 1136, 0 row lock(s) -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 0 266 OS file reads, 80 OS file writes, 20 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 34673, node heap has 0 buffer(s) Hash table size 34673, node heap has 0 buffer(s) Hash table size 34673, node heap has 0 buffer(s) Hash table size 34673, node heap has 0 buffer(s) Hash table size 34673, node heap has 0 buffer(s) Hash table size 34673, node heap has 0 buffer(s) Hash table size 34673, node heap has 0 buffer(s) Hash table size 34673, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 2573764 Log flushed up to 2573764 Pages flushed up to 2573764 Last checkpoint at 2573755 Max checkpoint age 80826164 Checkpoint age target 78300347 Modified age 0 Checkpoint age 9 0 pending log flushes, 0 pending chkp writes 18 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 139722752 Dictionary memory allocated 153728 Internal hash tables (constant factor + variable factor) Adaptive hash index 2252736 (2219072 + 33664) Page hash 139112 (buffer pool 0 only) Dictionary cache 708496 (554768 + 153728) File system 830704 (812272 + 18432) Lock system 335144 (332872 + 2272) Recovery system 0 (0 + 0) Buffer pool size 8191 Buffer pool size, bytes 134201344 Free buffers 7922 Database pages 269 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 234, created 35, written 50 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 269, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB 0 RW transactions active inside InnoDB Process ID=7781, Main thread ID=139629495830272, state: sleeping Number of rows inserted 53, updated 4, deleted 0, read 86 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.00 sec) xtrabk@db 00:28: [(none)]> |
可以看到，这次就没问题了。
然后，再做一次文首的xtrabackup的备份，就没问题了；
备份执行完成后的xtrabackup的日志如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 |
[root@mysql1 log]# pwd /mysqlbackup/mysql1.adamhuan.com/history/log [root@mysql1 log]# [root@mysql1 log]# ls -ltr total 72 -rw-r--r-- 1 root root 48 Aug 18 06:00 backup_20190818_060001.log -rw-r--r-- 1 root root 48 Aug 18 06:01 backup_20190818_060114.log -rw-r--r-- 1 root root 48 Aug 18 06:08 backup_20190818_060804.log -rw-r--r-- 1 root root 48 Aug 18 06:08 backup_20190818_060830.log -rw-r--r-- 1 root root 48 Aug 18 06:09 backup_20190818_060909.log -rw-r--r-- 1 root root 48 Aug 18 17:24 backup_20190818_172454.log -rw-r--r-- 1 root root 59 Aug 18 17:27 backup_20190818_172742.log -rw-r--r-- 1 root root 1118 Aug 18 23:51 backup_20190818_235113.log -rw-r--r-- 1 root root 35053 Aug 19 00:15 backup_20190819_001516.log -rw-r--r-- 1 root root 3739 Aug 19 00:15 backup_db.log [root@mysql1 log]# [root@mysql1 log]# cat backup_20190819_001516.log | wc -l 601 [root@mysql1 log]# [root@mysql1 log]# cat backup_20190819_001516.log （过多的输出，...） 190819 00:15:18 [01] Streaming ./sys/x@0024user_summary_by_statement_type.frm to <STDOUT> 190819 00:15:18 [01] ...done 190819 00:15:18 [01] Streaming ./sys/user_summary_by_statement_latency.frm to <STDOUT> 190819 00:15:18 [01] ...done 190819 00:15:18 [01] Streaming ./sys/x@0024user_summary_by_statement_latency.frm to <STDOUT> 190819 00:15:18 [01] ...done 190819 00:15:18 [01] Streaming ./sys/x@0024host_summary_by_file_io_type.frm to <STDOUT> 190819 00:15:18 [01] ...done 190819 00:15:18 [01] Streaming ./sys/x@0024host_summary_by_statement_type.frm to <STDOUT> 190819 00:15:18 [01] ...done 190819 00:15:18 [01] Streaming ./sys/host_summary_by_statement_latency.frm to <STDOUT> 190819 00:15:18 [01] ...done 190819 00:15:18 [01] Streaming ./sys/x@0024host_summary_by_statement_latency.frm to <STDOUT> 190819 00:15:18 [01] ...done 190819 00:15:18 [01] Streaming ./sys/wait_classes_global_by_avg_latency.frm to <STDOUT> 190819 00:15:18 [01] ...done 190819 00:15:18 [01] Streaming ./sys/x@0024wait_classes_global_by_avg_latency.frm to <STDOUT> 190819 00:15:18 [01] ...done 190819 00:15:18 [01] Streaming ./sys/wait_classes_global_by_latency.frm to <STDOUT> 190819 00:15:18 [01] ...done 190819 00:15:18 [01] Streaming ./sys/x@0024wait_classes_global_by_latency.frm to <STDOUT> 190819 00:15:18 [01] ...done 190819 00:15:18 [01] Streaming ./sys/x@0024waits_by_user_by_latency.frm to <STDOUT> 190819 00:15:18 [01] ...done 190819 00:15:18 [01] Streaming ./sys/x@0024waits_by_host_by_latency.frm to <STDOUT> 190819 00:15:18 [01] ...done 190819 00:15:18 [01] Streaming ./sys/x@0024waits_global_by_latency.frm to <STDOUT> 190819 00:15:18 [01] ...done 190819 00:15:18 [01] Streaming ./sys/x@0024processlist.frm to <STDOUT> 190819 00:15:18 [01] ...done 190819 00:15:18 [01] Streaming ./sys/x@0024session.frm to <STDOUT> 190819 00:15:18 [01] ...done 190819 00:15:18 [01] Streaming ./sys/session_ssl_status.frm to <STDOUT> 190819 00:15:18 [01] ...done 190819 00:15:18 [01] Streaming ./adamhuan/db.opt to <STDOUT> 190819 00:15:18 [01] ...done 190819 00:15:18 [01] Streaming ./adamhuan/people.frm to <STDOUT> 190819 00:15:18 [01] ...done 190819 00:15:18 Finished backing up non-InnoDB tables and files 190819 00:15:18 Executing LOCK BINLOG FOR BACKUP... 190819 00:15:18 [00] Streaming <STDOUT> 190819 00:15:18 [00] ...done 190819 00:15:18 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS... xtrabackup: The latest check point (for incremental): '2573704' xtrabackup: Stopping log copying thread. .190819 00:15:18 >> log scanned up to (2573755) 190819 00:15:18 Executing UNLOCK BINLOG 190819 00:15:18 Executing UNLOCK TABLES 190819 00:15:18 All tables unlocked 190819 00:15:18 [00] Streaming ib_buffer_pool to <STDOUT> 190819 00:15:18 [00] ...done 190819 00:15:18 Backup created in directory '/mysqlbackup/mysql1.adamhuan.com/tmp/' MySQL binlog position: filename 'mysql-bin.000004', position '626', GTID of the last change '61006e5f-beff-11e9-9d5e-000c2998ef22:1-3, aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-8' 190819 00:15:18 [00] Streaming <STDOUT> 190819 00:15:18 [00] ...done 190819 00:15:18 [00] Streaming <STDOUT> 190819 00:15:18 [00] ...done xtrabackup: Transaction log of lsn (2573704) to (2573755) was copied. 190819 00:15:18 completed OK! [root@mysql1 log]# |
Finished，…
