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

book

Article ID: 200976

calendar_today

Updated On:

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 xx.xx.xx.xx (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 xx.xx.xx.xx

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      | xx.xx.xx.xx                  |
| repluser      | yy.yy.yy.yy                   |
|    |
+---------------+-----------------------------+


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'@'replhostip/name' IDENTIFIED BY 'replpass';    

or for MySQL 8

mysql > GRANT PROCESS ON *.* TO 'repluser'@'replhostip/name'

flush privileges;

After this rerun the script to restart the replication.