debugsqlMinor
Master/Slave MySQL error in replication
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
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
If the error still comes back on the Slave, you will have to run
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
SUGGESTION #2
The user
If MySQL 5.7 is on Master and Slave, you should run
This will do nothing if
Replication would proceed from there.
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 SlaveIf 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.