1.现象

从库报如下错误
slave1 [localhost:21224] {msandbox} ((none)) > show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: rsandbox
Master_Port: 21223
Connect_Retry: 60
Master_Log_File: mysql-bin.000035
Read_Master_Log_Pos: 1367
Relay_Log_File: mysql-relay.000077
Relay_Log_Pos: 1242
Relay_Master_Log_File: mysql-bin.000035
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1396
Last_Error: Error ‘Operation CREATE USER failed for ‘test_h’@‘10.10.10.10’’ on query. Default database: ‘mysql’. Query: ‘CREATE USER ‘test_h’@‘10.10.10.10’ IDENTIFIED WITH ‘mysql_native_password’ AS ‘*E5613FB516F8DEBDABFA09B2DB855873A5F92D33’’
…

2.验证

在主库上按如下操作：
master [localhost:21223] {root} ((none)) > CREATE USER ‘test_h’@‘10.10.10.10’ identified WITH ‘mysql_native_password’ BY ‘test_h’;
Query OK, 0 rows affected (0.01 sec)

master [localhost:21223] {root} ((none)) > use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
master [localhost:21223] {root} (mysql) >
master [localhost:21223] {root} (mysql) > delete from user where user=‘test_h’;
Query OK, 1 row affected (0.00 sec)

master [localhost:21223] {root} (mysql) >
master [localhost:21223] {root} (mysql) > delete from db where user=‘test_h’;
Query OK, 0 rows affected (0.00 sec)

master [localhost:21223] {root} (mysql) > CREATE USER ‘test_h’@‘10.10.10.10’ identified WITH ‘mysql_native_password’ BY ‘test_h’;
ERROR 1396 (HY000): Operation CREATE USER failed for ‘test_h’@‘10.10.10.10’
这个时候，再执行一次create user ,可以执行成功。
master [localhost:21223] {root} (mysql) > CREATE USER ‘test_h’@‘10.10.10.10’ identified WITH ‘mysql_native_password’ BY ‘test_h’;
Query OK, 0 rows affected (0.01 sec)
然后到从库，就报 1396错误，和上面的现象一样。这个是mysql8.0.22的版本，但是在mysql5.7.26上，如上操作，多次运行CREATE USER 是不成功的，没成功的话，就不会记录到binlog,也就是说从库不会有问题。

其实我们create user时，会对mysql.user，mysql.db 进行操作。授权和回收权限时，会对mysql.tables_priv，mysql.db进行操作。

当主库执行了flush privileges时，从库是可以正常复制的
master [localhost:21223] {root} (mysql) > CREATE USER ‘test_h2’@‘10.10.10.10’ identified WITH ‘mysql_native_password’ BY ‘test_h’;
Query OK, 0 rows affected (0.01 sec)

master [localhost:21223] {root} (mysql) >
master [localhost:21223] {root} (mysql) > use mysql
Database changed
master [localhost:21223] {root} (mysql) >
master [localhost:21223] {root} (mysql) > delete from user where user=‘test_h2’;
Query OK, 1 row affected (0.00 sec)

master [localhost:21223] {root} (mysql) >

master [localhost:21223] {root} (mysql) > FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

master [localhost:21223] {root} (mysql) > CREATE USER ‘test_h2’@‘10.10.10.10’ identified WITH ‘mysql_native_password’ BY ‘test_h’;
Query OK, 0 rows affected (0.00 sec)

如果只删mysql.user表中的数据，看看能不能创建用户成功。
master [localhost:21223] {root} (mysql) > drop user ‘test_h2’@‘10.10.10.10’;
Query OK, 0 rows affected (0.01 sec)

当执行创建用户并授权时，删除user表中的数据。

master [localhost:21223] {root} (mysql) > CREATE USER test_h3@10.10.10.10;
Query OK, 0 rows affected (0.00 sec)

master [localhost:21223] {root} (mysql) > GRANT ALL PRIVILEGES ON test_db.* TO test_h3@10.10.10.10;
Query OK, 0 rows affected (0.04 sec)

master [localhost:21223] {root} (mysql) > DELETE FROM mysql.user WHERE user=‘test_h3’;
Query OK, 1 row affected (0.00 sec)

master [localhost:21223] {root} (mysql) > SELECT user,host FROM mysql.user WHERE user=‘test_h3’;
Empty set (0.00 sec)

master [localhost:21223] {root} (mysql) > SELECT user,host,db FROM mysql.db WHERE user=‘test_h3’;
±--------±------------±--------+
| user | host | db |
±--------±------------±--------+
| test_h3 | 10.10.10.10 | test_db |
±--------±------------±--------+
1 row in set (0.01 sec)

master [localhost:21223] {root} (mysql) > FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

master [localhost:21223] {root} (mysql) > CREATE USER test_h3@10.10.10.10;
ERROR 1396 (HY000): Operation CREATE USER failed for ‘test_h3’@‘10.10.10.10’
master [localhost:21223] {root} (mysql) > CREATE USER test_h3@10.10.10.10;
ERROR 1396 (HY000): Operation CREATE USER failed for ‘test_h3’@‘10.10.10.10’
master [localhost:21223] {root} (mysql) > CREATE USER test_h3@10.10.10.10;
ERROR 1396 (HY000): Operation CREATE USER failed for ‘test_h3’@‘10.10.10.10’
以上创建用户并不成功，这个sql也就不会写binlog,不写binlog从库也就不会有问题。

再执行drop user.看看能不能成功。
master [localhost:21223] {root} (mysql) > DROP USER test_h3@10.10.10.10;
Query OK, 0 rows affected (0.00 sec)

master [localhost:21223] {root} (mysql) > sELECT user,host,db FROM mysql.db WHERE user=‘test_h3’;
Empty set (0.00 sec)

master [localhost:21223] {root} (mysql) > cREATE USER test_h3@10.10.10.10;
Query OK, 0 rows affected (0.00 sec)
通过drop user后，再创建用户是没问题的。

再检查一下主的binlog日志与从库的relay log
主库：
master [localhost:21223] {root} (mysql) > SHOW BINLOG EVENTS IN ‘mysql-bin.000035’ FROM 6733 \G
*************************** 1. row ***************************
Log_name: mysql-bin.000035
Pos: 6733
Event_type: Query
Server_id: 21223
End_log_pos: 6850
Info: use mysql; DROP USER test_h3@10.10.10.10 /* xid=110 /
*************************** 2. row ***************************
Log_name: mysql-bin.000035
Pos: 6850
Event_type: Gtid
Server_id: 21223
End_log_pos: 6927
Info: SET @@SESSION.GTID_NEXT= ‘00021223-1111-1111-1111-111111111111:566’
*************************** 3. row ***************************
Log_name: mysql-bin.000035
Pos: 6927
Event_type: Query
Server_id: 21223
End_log_pos: 7090
Info: use mysql; CREATE USER ‘test_h3’@‘10.10.10.10’ IDENTIFIED WITH ‘caching_sha2_password’ / xid=112 */
3 rows in set (0.00 sec)

从库：
slave1 [localhost:21224] {msandbox} ((none)) > show RELAYLOG EVENTS IN ‘mysql-relay.000078’ FROM 5824 \G
*************************** 1. row ***************************
Log_name: mysql-relay.000078
Pos: 5824
Event_type: Query
Server_id: 21223
End_log_pos: 6850
Info: use mysql; DROP USER test_h3@10.10.10.10 /* xid=110 /
*************************** 2. row ***************************
Log_name: mysql-relay.000078
Pos: 5941
Event_type: Gtid
Server_id: 21223
End_log_pos: 6927
Info: SET @@SESSION.GTID_NEXT= ‘00021223-1111-1111-1111-111111111111:566’
*************************** 3. row ***************************
Log_name: mysql-relay.000078
Pos: 6018
Event_type: Query
Server_id: 21223
End_log_pos: 7090
Info: use mysql; CREATE USER ‘test_h3’@‘10.10.10.10’ IDENTIFIED WITH ‘caching_sha2_password’ / xid=112 */
3 rows in set (0.00 sec)

可以看到在主库执行的DROP USER test_h3@10.10.10.10;，cREATE USER test_h3@10.10.10.10;在从库执行了。

3.小结

1.在创建用户与删除用户时，不要直接操作授权表，用create user,与drop user操作。
2.如果操作授权表时，一定需要flush privileges;
3.从库报1396的错误，只有在从库跳过该事务，重新按规范创建用户即可。