This article is a copy/paste/modify of mysqldump: Error: ‘Access denied; you need (at least one of) the PROCESS privilege(s) for this operation’ when trying to dump tablespaces.
See also Creating read-only PostgreSQL database users for pg_dump and pg_dumpall. I’m linking this because I use both articles on a regular basis.
The error
After a recent upgrade of MySQL server from 5.7.40 to 8.0.32, my database backups started encountered this error:
mysqldump: Couldn't execute 'FLUSH TABLES': Access denied; you need (at least one of) the RELOAD or FLUSH_TABLES privilege(s) for this operation (1227)
The search
Searching, I found a mysqldump.guru post which suggested adding RELOAD, PROCESS permissions.
First, what permissions does my user have?
mysql> SHOW GRANTS for rsyncer; +---------------------------------------------------+ | Grants for rsyncer@% | +---------------------------------------------------+ | GRANT SELECT, LOCK TABLES ON *.* TO `rsyncer`@`%` | | GRANT SHOW_ROUTINE ON *.* TO `rsyncer`@`%` | +---------------------------------------------------+ 2 rows in set (0.00 sec) mysql>
That’s interesting, because if you look at my previous post on this, the user had PROCESS privileges.
OK, let’s try adding the suggested RELOAD.
mysql> GRANT RELOAD, PROCESS ON *.* TO 'rsyncer'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS for rsyncer; +------------------------------------------------------------+ | Grants for rsyncer@% | +------------------------------------------------------------+ | GRANT SELECT, PROCESS, LOCK TABLES ON *.* TO `rsyncer`@`%` | | GRANT SHOW_ROUTINE ON *.* TO `rsyncer`@`%` | +------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql>
The failed attempt
Don’t do this. See below.
Let’s try their suggestion:
mysql> GRANT PROCESS, SELECT, LOCK TABLES ON *.* TO 'rsyncer'@'%'; Query OK, 0 rows affected (0.04 sec) mysql> SHOW GRANTS for rsyncer; +------------------------------------------------------------+ | Grants for rsyncer@% | +------------------------------------------------------------+ | GRANT SELECT, PROCESS, LOCK TABLES ON *.* TO 'rsyncer'@'%' | +------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
For MySQL 8.0, I did this:
The failed test
Did that work?
[rsyncer@mysql01 ~]$ ~/bin/backup.sh
dumping db_nagiosql_v34
mysqldump: Couldn't execute 'FLUSH TABLES': Access denied; you need (at least one of) the RELOAD or FLUSH_TABLES privilege(s) for this operation (1227)
backup of db_nagiosql_v34 failed
dumping fruityext
mysqldump: Couldn't execute 'FLUSH TABLES': Access denied; you need (at least one of) the RELOAD or FLUSH_TABLES privilege(s) for this operation (1227)
backup of fruityext failed
dumping fruityint
mysqldump: Couldn't execute 'FLUSH TABLES': Access denied; you need (at least one of) the RELOAD or FLUSH_TABLES privilege(s) for this operation (1227)
backup of fruityint failed
dumping librenms
mysqldump: Couldn't execute 'FLUSH TABLES': Access denied; you need (at least one of) the RELOAD or FLUSH_TABLES privilege(s) for this operation (1227)
backup of librenms failed
receiving incremental file list
database-backup/mysql/
database-backup/mysql/db_nagiosql_v34.sql
793 100% 774.41kB/s 0:00:00 (xfr#1, to-chk=3/8)
database-backup/mysql/fruityext.sql
787 100% 768.55kB/s 0:00:00 (xfr#2, to-chk=2/8)
database-backup/mysql/fruityint.sql
787 100% 768.55kB/s 0:00:00 (xfr#3, to-chk=1/8)
database-backup/mysql/librenms.sql
786 100% 767.58kB/s 0:00:00 (xfr#4, to-chk=0/8)
Number of files: 8 (reg: 5, dir: 3)
Number of created files: 0
Number of deleted files: 0
Number of regular files transferred: 4
Total file size: 3,153 bytes
Total transferred file size: 3,153 bytes
Literal data: 0 bytes
Matched data: 3,153 bytes
File list size: 267
File list generation time: 0.001 seconds
File list transfer time: 0.000 seconds
Total bytes sent: 166
Total bytes received: 475
sent 166 bytes received 475 bytes 1,282.00 bytes/sec
total size is 3,153 speedup is 4.92
For more information on that script, see below.
The successful attempt
See above where I’m using @’%’ in the GRANT command? Let’s use localhost instead.
No. Hmm, let’s try this instead:
mysql> GRANT FLUSH_TABLES ON *.* TO 'rsyncer'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS for rsyncer@localhost; +--------------------------------------------------------------------+ | Grants for rsyncer@localhost | +--------------------------------------------------------------------+ | GRANT SELECT, PROCESS, LOCK TABLES ON *.* TO `rsyncer`@`localhost` | | GRANT FLUSH_TABLES,SHOW_ROUTINE ON *.* TO `rsyncer`@`localhost` | +--------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql>
The successful test
And then:
[rsyncer@mysql01 ~]$ ~/bin/backup.sh
dumping db_nagiosql_v34
dumping fruityext
dumping fruityint
dumping librenms
receiving incremental file list
database-backup/mysql/
database-backup/mysql/db_nagiosql_v34.sql
13,794,729 100% 57.70MB/s 0:00:00 (xfr#1, to-chk=3/8)
database-backup/mysql/fruityext.sql
216,847 100% 916.73kB/s 0:00:00 (xfr#2, to-chk=2/8)
database-backup/mysql/fruityint.sql
282,546 100% 1.14MB/s 0:00:00 (xfr#3, to-chk=1/8)
database-backup/mysql/librenms.sql
28,300,394 100% 38.28MB/s 0:00:00 (xfr#4, to-chk=0/8)
Number of files: 8 (reg: 5, dir: 3)
Number of created files: 0
Number of deleted files: 0
Number of regular files transferred: 4
Total file size: 42,594,516 bytes
Total transferred file size: 42,594,516 bytes
Literal data: 42,591,716 bytes
Matched data: 2,800 bytes
File list size: 275
File list generation time: 0.001 seconds
File list transfer time: 0.000 seconds
Total bytes sent: 166
Total bytes received: 42,602,571
sent 166 bytes received 42,602,571 bytes 28,401,824.67 bytes/sec
total size is 42,594,516 speedup is 1.00
[rsyncer@mysql01 ~]$
Success!
Backing out my error.
You can ignore this if you didn’t follow my error above.
Let’s back out that first command:
mysql> REVOKE PROCESS ON *.* FROM 'rsyncer'@'%'; Query OK, 0 rows affected (0.00 sec) mysql> SHOW GRANTS for rsyncer; +---------------------------------------------------+ | Grants for rsyncer@% | +---------------------------------------------------+ | GRANT SELECT, LOCK TABLES ON *.* TO 'rsyncer'@'%' | +---------------------------------------------------+ 1 row in set (0.00 sec) mysql>
Testing ~/bin/backup.sh again, still works.
Backup scripts
You can find the mysql backup script mentioned above in my sundry scripts repo. Look for:
- rsyncer-backup.sh – runs on the MySQL server, but could run remotely. I prefer to dump on the server, then rsync somewhere else.
- rsync-backup-from-mysql01.sh – run on the dbclone server (the one which receives all the backups). It is invoked from the MySQL server, by the same user who ran the backup).
Thank you.
