0

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 `

Binlog Format is Mixed on master

Your Answer

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

Browse other questions tagged or ask your own question.