HiveBrain v1.2.0
Get Started
← Back to all entries
debugsqlMinor

Master/Slave MySQL error in replication

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
errorslavereplicationmysqlmaster

Problem

I have setup a Master/Slave between two separates databases and started working fine until the slave arised an error and do not sync.

Here is the status of the Slave:

+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+------------------+--------------------------------------+----------------------------------------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay

Solution

The slave status you posted says

Error 'Operation CREATE USER failed for 'fetchers'@'localhost'' on query. Default database: ''. Query: 'CREATE USER 'fetchers'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*A516E88562C2C5E0D1EA9875F2910B36584C217A''

SUGGESTION #1

Some have suggested running FLUSH PRIVILEGES. So, in your case, that would be

STOP SLAVE;
FLUSH PRIVILEGES;
START SLAVE;


If the error still comes back on the Slave, you will have to run

STOP SLAVE;
DROP USER 'fetchers'@'localhost'
START SLAVE;


on the Slave.

MySQL 5.7 may have generated a warning on the Master and passed it on the Slave. MySQL 5.7 is becoming a little more strict in its GRANT operations. In the future, please try creating the user first and then use ALTER USER to set the password.
SUGGESTION #2

The user 'fetchers'@'localhost' must already exist on the Slave

If MySQL 5.7 is on Master and Slave, you should run

CREATE USER IF NOT EXISTS 'fetchers'@'localhost'


This will do nothing if 'fetchers'@'localhost' already exists.

Replication would proceed from there.

Code Snippets

STOP SLAVE;
FLUSH PRIVILEGES;
START SLAVE;
STOP SLAVE;
DROP USER 'fetchers'@'localhost'
START SLAVE;
CREATE USER IF NOT EXISTS 'fetchers'@'localhost'

Context

StackExchange Database Administrators Q#151680, answer score: 7

Revisions (0)

No revisions yet.