So I built a master-slave Mysql replication architecture in production because of a database FOO that I wanted to query (slave node) without affecting the performance of the main site (master node).
The thing is, I was afraid of someone breaking replication by inserting data in the slave which would conflict with data coming from master, so as soon as I created the slave node, I removed all privileged users (e.g. dba1, dba2) in the slave node and added a single readonly one (with the SELECT grant): readonlyUser.
The problem I have now is that suddenly dba1 has changed his password in the master node, and the result is this replication error:
Replication reported error 1396:
Error 'Operation ALTER USER failed for 'dba1'@'localhost'' on query.
Default database: 'mysql'.
Query: 'ALTER USER 'dba1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*DAB3A57F75AD41285552E115420DA2EFD756CD19''
Should I prevent the replication of the mysql DB then? I'm afraid that, if I do this, any structure changes for my FOO database will not be replicated, because the structure of the tables is stored in the mysql database, right?
Or should I just ignore errors of type 1396? Is error1396 always related to users or is it more broad?
Best Answer
Mysql Replication is pretty hard to manage, if anyone executed wrong query it'll affect the slave too. Use the below command to fix.
If you want to exclude this error all time, add the below line in my.cnf file.
If the error code is 1396 then