0

I have mysql database replication setup with the slave on a cloud provider and the master on a dedicated server with cPanel / WHM installed.

My replication stalled with a series of error messages. I managed to clear each individually, however, I'm concerned that these errors even appeared to begin with.

I have about 40 individual databases being replicated, all listed in the my.cnf file as binlog_do_db = dbname and all working so far as expected. I have several other databases that are not being replicated from that server as well for reference.

Here's a list of the errors I've received. All of these have appeared after I updated the mysql root password on the Master server.

Error 'Can't find any matching row in the user table' on query. Default database: 'dbname'. Query: 'SET PASSWORD FOR 'root'@'mywebsite.com'='xxx''

Error 'Can't find any matching row in the user table' on query. Default database: 'dbname'. Query: 'SET PASSWORD FOR 'root'@'127.0.0.1'='xxx''

Error 'Unknown column 'Password' in 'field list'' on query. Default database: ''. Query: 'UPDATE mysql.user SET Password=PASSWORD('xxx') WHERE User='root''

Prior to these errors, I had another error:

Error 'Can't find any matching row in the user table' on query. Default database: ''. Query: 'SET PASSWORD FOR 'username'@'localhost'='xxx''

This first error prompted me to reset passwords as an extra precaution as the mysql user username hadn't been updated by me on the master server. By updating the other passwords (including root), the remaining errors were triggered.

To clear the first error, I had to create a mysql user for username on my slave mysql server before it would clear. This means that effectively, anytime I update a user in cPanel on the master server, it's trying to update that user with the same credentials on the slave server.

Why is this happening and how do I disable it?

For reference, I used this tutorial to setup the master - slave relationship.

  • "This means that effectively, anytime I update a user in cPanel on the master server, it's trying to update that user with the same credentials on the slave server." Well... replication is designed to replicate changes from one server to another, including privileges. If the users don't match, the concept of object definers and program invokers and db/table/column and execute grants... privileges... fall apart. It isn't clear why you find replication of grant-related statements unexpected. – Michael - sqlbot Apr 13 '18 at 10:58
  • Because apparently I misunderstood the way it works. I thought I'm only replicating specific databases, not MySQL users, especially some not related to the databases I want to replicate – TH1981 Apr 13 '18 at 12:13
  • 1
    Partial replication is one of those things which, the better you understand it, the more you try to avoid it. Unless there are security/compliance/privacy/regulatory concerns, replicating everything is usually the best plan. The server does not have a way of knowing whether common resources should be replicated or not, and even binlog_do_db probably does not do exactly what you expect. Partial replication requires a very disciplined environment in the way the application uses the database, in addition to low level (in the sense of "advanced") DBA skills. – Michael - sqlbot Apr 13 '18 at 12:47
  • What version of MySQL (on Master and Slave)? RBR? SBR? – Rick James May 2 '18 at 3:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.