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.30 to 5.7.31, my database backups started encountered this error:
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
The search
Searching, I found a Kanyakonil post which suggested adding PROCESS permissions.
First, what permissions does my user have?
mysql> SHOW GRANTS for rsyncer; +---------------------------------------------------+ | Grants for rsyncer@% | +---------------------------------------------------+ | GRANT SELECT, LOCK TABLES ON *.* TO 'rsyncer'@'%' | +---------------------------------------------------+ 1 row in set (0.00 sec) mysql>
The failed attempt
Don’t do this.
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>
The failed test
Did that work?
[rsyncer@mysql01 ~]$ ~/bin/backup.sh
dumping db_nagiosql_v34
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
dumping fruityext
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
dumping fruityint
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
dumping librenms
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
receiving incremental file list
database-backup/mysql/
database-backup/mysql/db_nagiosql_v34.sql
4,044,624 100% 482.16MB/s 0:00:00 (xfr#1, to-chk=3/8)
database-backup/mysql/fruityext.sql
217,246 100% 20.72MB/s 0:00:00 (xfr#2, to-chk=2/8)
database-backup/mysql/fruityint.sql
282,945 100% 24.53MB/s 0:00:00 (xfr#3, to-chk=1/8)
database-backup/mysql/librenms.sql
43,499,266 100% 109.75MB/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: 48,044,081 bytes
Total transferred file size: 48,044,081 bytes
Literal data: 1,746,089 bytes
Matched data: 46,297,992 bytes
File list size: 276
File list generation time: 0.001 seconds
File list transfer time: 0.000 seconds
Total bytes sent: 62,158
Total bytes received: 1,783,529
sent 62,158 bytes received 1,783,529 bytes 1,230,458.00 bytes/sec
total size is 48,044,081 speedup is 26.03
For more information on that script, see below.
The successful attempt
No. Hmm, let’s try this instead:
mysql> SHOW GRANTS for rsyncer@localhost; +-----------------------------------------------------------+ | Grants for rsyncer@localhost | +-----------------------------------------------------------+ | GRANT SELECT, LOCK TABLES ON *.* TO 'rsyncer'@'localhost' | +-----------------------------------------------------------+ 1 row in set (0.00 sec) mysql> GRANT PROCESS, SELECT, LOCK TABLES ON *.* TO 'rsyncer'@'localhost'; Query OK, 0 rows affected (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
4,044,624 100% 428.58MB/s 0:00:00 (xfr#1, to-chk=3/8)
database-backup/mysql/fruityext.sql
217,246 100% 20.72MB/s 0:00:00 (xfr#2, to-chk=2/8)
database-backup/mysql/fruityint.sql
282,945 100% 26.98MB/s 0:00:00 (xfr#3, to-chk=1/8)
database-backup/mysql/librenms.sql
43,508,966 100% 116.23MB/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: 48,053,781 bytes
Total transferred file size: 48,053,781 bytes
Literal data: 497,069 bytes
Matched data: 47,556,712 bytes
File list size: 274
File list generation time: 0.001 seconds
File list transfer time: 0.000 seconds
Total bytes sent: 62,697
Total bytes received: 534,719
sent 62,697 bytes received 534,719 bytes 398,277.33 bytes/sec
total size is 48,053,781 speedup is 80.44
[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.
