7

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
Improve this question
0

2 Answers 2

Reset to default
8

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.

Improve this answer
2
  • 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';
    – itsho
    Commented Sep 14, 2018 at 13:04
  • 1
    The DROP USER 'fetchers'@'localhost' part did the trick!
    – juliangonzalez
    Commented Jun 24, 2019 at 0:10
1

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;
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.