2

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

2 Answers 2

0

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

5
  • I've just updated my question. Do I need to create admin user on slave to? Dec 17, 2015 at 22:38
  • SHOW SLAVE STATUS; I want to make sure the Slave is connecting as userSlave, not admin.
    – Rick James
    Dec 18, 2015 at 5:03
  • OK, I've edited my question Dec 18, 2015 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, 2015 at 17:05
  • Slave is already up...If I modify some table in master with user root these are replicated into slave Dec 18, 2015 at 17:12
0

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 agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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