复制中的mysql DB，但在主服务器上创建的用户未在从属服务器上复制 [英] mysql DB in replication but users created on master are not replicating on slave server

问题描述

在主从复制中，我们使用mysql DB在从属服务器上复制了几个DB. 我在主服务器上创建了一个用户，不幸的是它没有在从服务器上复制.

Replicate_Do_DB:app1，app2，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';

已在主服务器上成功创建但未在从服务器上复制的用户帐户.

如果正在复制mysql db，为什么会发生这种情况?

解决方案

嗯.我要在这里打个比方说，您可能正在使用基于STATEMENT的复制，而不是基于ROW的复制，并且您的MySQL版本是5.1或更高版本...

您可以通过在从属服务器上运行以下SQL来确定正在运行的类型:

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

由于您已经正确地确定了特权，只有在包含 mysql模式时，才会复制特权复制中

但是，陷阱"！这是 --replicate-do-db 选项.如果您使用的是基于语句的复制，那么在运行授权之前，您将需要指定mysql数据库作为默认数据库，因为:

此选项的效果取决于基于语句还是基于语句 正在使用基于行的复制.

基于语句的复制.告诉从属SQL线程限制 复制到默认数据库所在的语句(即一个 由USE选择)是db_name.

表示尝试运行:

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';

可能有效.如果没有，请再看一个答案！

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?

解决方案

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!

这篇关于复制中的mysql DB，但在主服务器上创建的用户未在从属服务器上复制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！