mysql DB in replication but users created on maste

Question

In master-slave replication we are replicating few DBs on slave server with mysql DB. I created an user on master, unfortunately it's not replicated on slave server.

Replicate_Do_DB: app1,app2,mysql

User creation commands:

GRANT SELECT on *.* to 'user1'@'localhost' identified by 'user1';

GRANT SELECT on *.* to 'user1'@'%' identified by 'user1';

GRANT SELECT on *.* to 'user1'@'10.10.10.10' identified by 'user1';

User account successfully created on master but not replicated on slave.

Why this is happening if mysql db is in replication?

Answer 1

Hmm. I'm going to take a punt here and say that you are probably using STATEMENT based replication as opposed to ROW BASED replication and that your MySQL version is 5.1 or above...

You can tell which type you are running by running the following SQL on your slave:

select variable_value
from information_schema.session_variables
where upper(variable_name) = 'BINLOG_FORMAT';

As you have correctly identified privileges are only replicated if the mysql schema is included in replication

However, the "gotcha!" here is the --replicate-do-db option. If you are using statement based replication then you will need to specify the mysql database as your default database before running the grants since:

The effects of this option depend on whether statement-based or row-based replication is in use.

Statement-based replication. Tell the slave SQL thread to restrict replication to statements where the default database (that is, the one selected by USE) is db_name.

That said try running:

USE MYSQL;

GRANT SELECT on *.* to 'user1'@'localhost' identified by 'user1';

GRANT SELECT on *.* to 'user1'@'%' identified by 'user1';

GRANT SELECT on *.* to 'user1'@'10.10.10.10' identified by 'user1';

It might work. If it doesn't then look at another answer!

Answer 2

In MySQL 5.7, if you execute the query from Tom Mac's answer, you will get the following error:

ERROR 3167 (HY000): The 'INFORMATION_SCHEMA.SESSION_VARIABLES' feature is disabled; see the documentation for 'show_compatibility_56'

You should query performance_schema instead. Run the following:

SELECT variable_value FROM performance_schema.session_variables WHERE upper(variable_name) = 'BINLOG_FORMAT';