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.