Slave_SQL_Running: No: MySQL replication stopped working

Replication on my slave database (MySQL 5.7.12) stopped working suddenly. When I run SHOW SLAVE STATUS\G I see the following error:

Last_Errno: 1396
Last_Error: Error 'Operation CREATE USER failed for 'user'@'ip'' on query. Default database: ''. Query: 'CREATE USER 'user'@'ip' IDENTIFIED WITH 'mysql_native_password' AS '*password''

I had recently created 2 new read-only users on both my master and my slave so I'm certain it's because of this.

I also see this in the status:

Slave_IO_Running: Yes
Slave_SQL_Running: No

Any suggestions on how to fix this?

Thank you.

Solution 1:

TL;DR

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; 
START SLAVE;

Now the slightly longer version:

if you have manually created the user on your slave first and on your master second, the CREATE USER command executed on the master has been replicated to the slave. Attempted subsequent execution of the statement failed, because the user was already present. So my suggestions to fix "this":

• Tell the replication engine to skip the statement and move on
• Do not mess with slaves in a replication setup in the future
• Make sure to read the official documentation whenever doing things you haven't done before