Getting an MYSQLDump error

I upgrade MySQL DB from 5.6 to 5.7 version not too long now with no issues happened after. But at that time I did not use mysqldump command. Now I tried to backup the database and gettting this error message:

mysqldump: Error: ‘Access denied; you need (at least one of) the PROCESS privilege(s) for this operation’ when trying to dump tablespaces

How to fix this error.

ANSWER

The PROCESS privilege requirement was added only in MySQL version 5.7.31. This is an expected behavior and affects mysqldump utility. There is a report here and some discussions –

https://bugs.mysql.com/bug.php?id=100219

If your user has no admin privilege to the MySQL database, the quick workaround is to add the –no-tablespaces option. The usage like below.

mysqldump --no-tablespaces -u user -p DB_NAME > BACKUP_FILE.sql

On the other hand, if you have admin access, then grant that user the PROCESS privilege like this:

GRANT PROCESS ON *.* TO user@localhost;

Be warned that such privilege is usually reserved for server administrator users. It works on a global context, so it cannot be specified on a per database approach. Granting just any user this PROCESS privilege is not a good idea. It may cause “data leaks” when sensitive queries gets exposed because this privilege can allow a user to see queries being executed in active sessions. This becomes more critical when the server instance is shared.

mysql has two pid files

I just finished re-building my (5.7) slave from a hot backup. When I issue

 systemctl start mysqld

it just “sat” there for over 20 minutes. It usually takes less than 10 seconds for the service to start. When I look at my server status, I see two mysql processes

mysql     1938     1  0 15:25 ?        00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
mysql     1940     1 99 15:25 ?        00:11:48 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
root      4024  3122  0 15:37 pts/0    00:00:00 grep --color=auto mysqld

I stopped and restarted my service and there are two different PIDs showing up again

 [root@bi-db002-prd ~]# systemctl status mysqld -l
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: activating (start) since Mon 2020-08-17 15:46:45 UTC; 45s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 7066 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
  Control: 7088 (mysqld)
   CGroup: /system.slice/mysqld.service
           ├─7088 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
           └─7092 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Aug 17 15:46:45 bi-db002-prd systemd[1]: Starting MySQL Server...

I have never seen that. Is there something incorrect with my configuration?

Go to Source
Author: A B