Jul 212020
 

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.

Website Pin Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Premium Responsive