Description:
Normally in mysql replication Data Control Language (DCL) statements won’t replicate into slave if we are configuring replication with replicate-ignore-db=mysql or replicate-wild-ingore-table=mysql.%
We have replication setup from master mysql 5.6 to slave mysql 8. And when we create a user on master it's replicating on slave with error message
Last_Errno: 1064
Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD '*492A2EE35388132D999A7819EA5E8CD0A96DBB49'' at line 1' on query. Default database: 'mysql'. Query: 'CREATE USER 'testing'@'localhost' IDENTIFIED BY PASSWORD '*492A2EE35388132D999A7819EA5E8CD0A96DBB49''
How to repeat:
On Master
use mysql;
CREATE USER 'testing'@'localhost' IDENTIFIED BY 'T3st7@asdM&';
flush privileges;
On Slave :
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: db1.%,db2.%,db3.%
Replicate_Wild_Ignore_Table:
Last_Errno: 1064
Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD '*492A2EE35388132D999A7819EA5E8CD0A96DBB49'' at line 1' on query. Default database: 'mysql'. Query: 'CREATE USER 'testing'@'localhost' IDENTIFIED BY PASSWORD '*492A2EE35388132D999A7819EA5E8CD0A96DBB49'
AFTER USING
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: db1.%,db2.%,db3.%
Replicate_Wild_Ignore_Table: mysql.%
Last_Errno: 1064
Last_Error: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD '*492A2EE35388132D999A7819EA5E8CD0A96DBB49'' at line 1' on query. Default database: 'mysql'. Query: 'CREATE USER 'testing'@'localhost' IDENTIFIED BY PASSWORD '*492A2EE35388132D999A7819EA5E8CD0A96DBB49'
Suggested fix:
Tried By using below commands on master :
IDENTIFIED WITH mysql_native_password BY
Again on making changes on slave configuration
[mysqld]
default-authentication-plugin=mysql_native_password
