I've just configured MySQL replication master-slave. I have into master instance 3 user: root (with all privileges and grant), admin(with all privileges) and slaveUser(with replication salve privilege). I've configured slave server to, and if I modify (with root user) in my master instance some resource this are replicated into slave. Well, if I modify a resource, in master instance, with admin user this resource isn't modified into slave and I've this error

Error 'Can't find any matching row in the user table' on query. Default database: 'my_db'. Query: 'GRANT ALL PRIVILEGES ON . TO 'admin'@'%''

I've configured this on the same machine with 2 docker instances.

Any suggestions??

EDIT

This is my users on MASTER

+-----------+-----------+
| user      | host      |
+-----------+-----------+
| admin     | %         |
| root      | %         |
| userSlave | %         |
| mysql.sys | localhost |
+-----------+-----------+

and this is users on SLAVE

+-----------+-----------+
| user      | host      |
+-----------+-----------+
| root      | %         |
| mysql.sys | localhost |
+-----------+-----------+

Which kind of permission does admin need to write replication on slave??

EDIT add SHOW SLAVE STATUS

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.3
                  Master_User: userSlave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 317959980
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 5834
        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: 1133
                   Last_Error: Error 'Can't find any matching row in the user table' on query. Default database: 'mydb'. Query: 'GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%''
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 5621
              Relay_Log_Space: 317961680
              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: 1133
               Last_SQL_Error: Error 'Can't find any matching row in the user table' on query. Default database: 'mydb'. Query: 'GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%''
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 100
                  Master_UUID: 8527301e-9765-11e5-a957-0242ac110003
             Master_Info_File: /var/lib/mysql/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: 151215 20:26:46
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
1 row in set (0.00 sec)

ERROR: 
No query specified

Not ON ., rather ON dbname.* (good idea for applications) or ON *.* (might be appropriate for admin)

  • I've just updated my question. Do I need to create admin user on slave to? – Claudio Pomo Dec 17 '15 at 22:38
  • SHOW SLAVE STATUS; I want to make sure the Slave is connecting as userSlave, not admin. – Rick James Dec 18 '15 at 5:03
  • OK, I've edited my question – Claudio Pomo Dec 18 '15 at 10:12
  • I did not think you needed GRANT USAGE TO 'admin'@'%';, but try that on the Slave, then START SLAVE; – Rick James Dec 18 '15 at 17:05
  • Slave is already up...If I modify some table in master with user root these are replicated into slave – Claudio Pomo Dec 18 '15 at 17:12

The error says in error you do not have 'admin'@'%' on slaves. If you need these users on the slave, you should create 'admin'@'%'. Or if you no need these users on slaves you may use:

  1. STOP SLAVE;
  2. SET GLOBAL sql_slave_skip_counter = 1;
  3. START SLAVE;

Hope it helps you.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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