[ACCEPTED]-mysql DB in replication but users created on master are not replicating on slave server-mysql
Hmm. I'm going to take a punt here and say 19 that you are probably using STATEMENT based 18 replication as opposed to ROW BASED replication 17 and that your MySQL version is 5.1 or above...
You 16 can tell which type you are running by running 15 the following SQL on your slave:
select variable_value
from information_schema.session_variables
where upper(variable_name) = 'BINLOG_FORMAT';
As you have 14 correctly identified privileges are only 13 replicated if the mysql schema is included in replication
However, the "gotcha!" here 12 is the --replicate-do-db option. If you are using statement 11 based replication then you will need to 10 specify the mysql database as your default 9 database before running the grants since:
The 8 effects of this option depend on whether 7 statement-based or row-based replication 6 is in use.
Statement-based replication. Tell 5 the slave SQL thread to restrict replication 4 to statements where the default database 3 (that is, the one selected by USE) is 2 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 1 it doesn't then look at another answer!
In MySQL 5.7, if you execute the query from 4 Tom Mac's answer, you will get the following error:
ERROR 3 3167 (HY000): The 'INFORMATION_SCHEMA.SESSION_VARIABLES' feature 2 is disabled; see the documentation for 'show_compatibility_56'
You 1 should query performance_schema instead. Run the following:
SELECT variable_value FROM performance_schema.session_variables WHERE upper(variable_name) = 'BINLOG_FORMAT';
More Related questions
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.