Mysql 5.7-31 and MySql 8.0.xx replicator user not enough privileges

Products

• CA API Gateway
• API SECURITY
• CA API Gateway Precision API Monitoring Module for API Gateway (Layer 7)
• CA API Gateway Enterprise Service Manager (Layer 7)
• STARTER PACK-7
• CA Microgateway

Issue/Introduction

The replication user for Mysql does not have enough privileges in the latest version of Mysql 5.7-31 and MySql 8.0-xxx  which is included in the latest platform

(Layer7_API_PlatformUpdate_64bit_v9.X-CentOS-2020-09-23.L7P ) .

After upgrading MySQL to 5.7.31 the create_slave.sh script to setup the slave replication or restart the replication fails on copying the database to the slave with the following error.

Do you want to clone a database from ###.###.###.### (yes or no)? [no] yes
Enter name of database to clone: [ssg]

W A R N I N G
  About to drop the ssg database on localhost
  and copy from ###.###.###.###

Are you sure you want to do this? [N] yes
mysql: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

Environment

Release : 9.4 10.x

Component : API GATEWAY

Resolution

The user used for the replication is created with the script "add_slave_user.sh" which does not include the PROCESS privilege as it was not required before MySQL 5.7.31.

The workaround is to grant the replication users the required PROCESS privilege manual .

To do this go into a command shell and run mysql.

in MySQL do the following :

mysql> use mysql;

list all users to identify your replication users.

mysql> SELECT user,host FROM mysql. user;

+---------------+---------------------------------+
| user          | host                            |
+---------------+---------------------------------+
| repluser      | ###.###.###.###             |
| repluser      | ###.###.###.###             |
|    |
+---------------+-----------------------------+


add the replication users the PROCESS privileges by default there are two users called 'repluser' assign both the process privileges .

mysql > GRANT PROCESS ON *.* TO 'repluser'@'<HostName>' IDENTIFIED BY 'replpass';    

or for MySQL 8

mysql > GRANT PROCESS ON *.* TO 'repluser'@'<HostName>'

flush privileges;

After this rerun the script to restart the replication.