Mysql – Master/Slave MySQL error in replication

MySQLmysql-5.7permissionsreplication

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

Best Answer

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.

Related Question