1

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?

  • What versions of mysql on Master and Slave? – Rick James Sep 16 '16 at 5:05
  • slave has 5.7.15-0ubuntu0.16.04.1 and master has 5.7.12, why? – knocte Sep 16 '16 at 19:32
-1

Mysql Replication is pretty hard to manage, if anyone executed wrong query it'll affect the slave too. Use the below command to fix.

Stop slave;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

If you want to exclude this error all time, add the below line in my.cnf file.

If the error code is 1396 then

slave-skip-errors = 1396

  • alright, so I asked in my question if just ignoring 1396 would not be hiding other important errors? are you sure that 1396 is only related to updating passwords? is there any documentation out there? – knocte Sep 15 '16 at 12:15
  • also, I've tried your first solution, and that makes replication not work at all (I've tried to insert a row in master, and slave doesn't receive it) – knocte Sep 16 '16 at 19:10
  • This is a workaround, not an answer. – Tom Jul 24 '17 at 19:54

Your Answer

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

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