Master/Slave MySQL error in replication

Question

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_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+------------------+--------------------------------------+----------------------------------------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
| Waiting for master to send event | 192.168.1.133 | slave_user | 3306 | 60 | mysql-bin.000033 | 154 | iMac-de-MAVA-relay-bin.000002 | 320 | mysql-bin.000001 | Yes | No | | | | | | | 1396 | Error 'Operation CREATE USER failed for 'fetchers'@'localhost'' on query. Default database: ''. Query: 'CREATE USER 'fetchers'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*A516E88562C2C5E0D1EA9875F2910B36584C217A'' | 0 | 1166 | 5614936 | None | | 0 | No | | | | | | NULL | No | 0 | | 1396 | Error 'Operation CREATE USER failed for 'fetchers'@'localhost'' on query. Default database: ''. Query: 'CREATE USER 'fetchers'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*A516E88562C2C5E0D1EA9875F2910B36584C217A'' | | 1 | aae7157c-8bf4-11e6-a79a-4ccc6a4f12cd | /usr/local/mysql-5.7.15-osx10.11-x86_64/data/master.info | 0 | NULL | | 86400 | | | 161007 13:37:26 | | | | | 0 | | | |
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+-------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------+------------------+--------------------------------------+----------------------------------------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
1 row in set (0,00 sec)

The question is that I just need to sync some tables in a database but it seems that try to sync all.

How can I avoid the error for replication go on working?

mysql> show slave status\G;
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 192.168.1.133
 Master_User: slave_user
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000034
 Read_Master_Log_Pos: 68326
 Relay_Log_File: iMac-de-MAVA-relay-bin.000002
 Relay_Log_Pos: 320
 Relay_Master_Log_File: mysql-bin.000001
 Slave_IO_Running: Yes
 Slave_SQL_Running: No
 Replicate_Do_DB: 
 Replicate_Ignore_DB: 
 Replicate_Do_Table: 
 Replicate_Ignore_Table: 
 Replicate_Wild_Do_Table: 
 Replicate_Wild_Ignore_Table: 
 Last_Errno: 1396
 Last_Error: Error 'Operation CREATE USER failed for 'fetchers'@'localhost'' on query. Default database: ''. Query: 'CREATE USER 'fetchers'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*A516E88562C2C5E0D1EA9875F2910B36584C217A''
 Skip_Counter: 0
 Exec_Master_Log_Pos: 1166
 Relay_Log_Space: 5812332
 Until_Condition: None
 Until_Log_File: 
 Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File: 
 Master_SSL_CA_Path: 
 Master_SSL_Cert: 
 Master_SSL_Cipher: 
 Master_SSL_Key: 
 Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
 Last_IO_Errno: 0
 Last_IO_Error: 
 Last_SQL_Errno: 1396
 Last_SQL_Error: Error 'Operation CREATE USER failed for 'fetchers'@'localhost'' on query. Default database: ''. Query: 'CREATE USER 'fetchers'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*A516E88562C2C5E0D1EA9875F2910B36584C217A''
 Replicate_Ignore_Server_Ids: 
 Master_Server_Id: 1
 Master_UUID: aae7157c-8bf4-11e6-a79a-4ccc6a4f12cd
 Master_Info_File: /usr/local/mysql-5.7.15-osx10.11-x86_64/data/master.info
 SQL_Delay: 0
 SQL_Remaining_Delay: NULL
 Slave_SQL_Running_State: 
 Master_Retry_Count: 86400
 Master_Bind: 
 Last_IO_Error_Timestamp: 
 Last_SQL_Error_Timestamp: 161007 13:37:26
 Master_SSL_Crl: 
 Master_SSL_Crlpath: 
 Retrieved_Gtid_Set: 
 Executed_Gtid_Set: 
 Auto_Position: 0
 Replicate_Rewrite_DB: 
 Channel_Name: 
 Master_TLS_Version: 
1 row in set (0,00 sec)
ERROR: 
No query specified

Answer 1

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.

• itsho: Brilliant. Just don't forget, if you run your slave with name/pw as parameters - MySql 5.6+ syntax, you need these here as well. example START SLAVE USER='replicationUserName' PASSWORD='putDragonsOrPasswordHere';
• juliangonzalez: The DROP USER 'fetchers'@'localhost' part did the trick!

Answer 2

if you use mysql v.8 + group replication (and/or innodb cluster) you should do the following

STOP group_replication;
SET GLOBAL super_read_only = 0;
SET GLOBAL read_only=0; 
SET GLOBAL sql_log_bin=OFF; 
DROP USER 'fetchers'@'localhost';
SET GLOBAL super_read_only = 1;
SET GLOBAL read_only=1; 
SET GLOBAL sql_log_bin=ON; 
START group_replication;