Bug #29064 MySQL replication - database not marked for replication also gets replicated

• Submitted: 13 Jun 2007 7:13
• Modified: 13 Jun 2007 8:32
• Reporter: Jayadevan M
• Status: Not a Bug
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 5.0.41
• OS: Windows
• CPU Architecture: Any

[13 Jun 2007 7:13] Jayadevan M

Description:
I set up replication of only one database from master to slave. I login to master server, do a 'use' for that database and then create a user, the user gets replicated to the slave. Since creating a user changes data in the mysql database, why is the user getting replicated?

How to repeat:
1) Have 3 instances of MySQL running - MySQL, MYSQL2 and MYSQL3. Now all of them have only the default databses - i.e. MySQL and test.
2) On MYSQL - create a nwe database mynewdb.
3) Create a few tables and populate some data 
4) Start replication from MySQL to MYSQL2 by zipping the entire data folder in MYSQL, and unzipping it in MYSQL2. Here we have full database replication from MYSQL to MYSQL2
5) Ue mysqldump to create a dump of mynewdb of MYSQL. Create that db in MYSQL3. Load the dump file.
6) Set up replication from MYSQL to MYSQL3 so that only mynewdb gets replicated (using the option replicate-do-db = mynewdb)
7) Login to the master server. Do
use mynewdb;
create user abc identified by 'abc';

This user gets replicated in both MYSQL2 and MYSQL3. I expected this to be repliacted in MYSQL2 only.
8) Do
use mysql;
create user xyz identified by 'xyz';
This user gets replicated in only in MYSQL2.

Since users, at the time of creation, are not linked to any particual database, why was the user abc foud in both the slaves?

[13 Jun 2007 7:26] Sveta Smirnova

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read carefully at http://dev.mysql.com/doc/refman/5.0/en/replication-options.html how option --replicate-do-db works

[13 Jun 2007 8:32] Jayadevan M

Hi,
User xyz is not found in MYSQL3. That is fine. I understand that. 
My query was - Why is abc found in MYSQL3? Creating a user, did not change the data in mynewdb in anyway. 
I tested further. I tried something else - I did a 'use newdb' and created a new database. Now this db got replicated only to MYSQL2 - behaviour is as expected. It did not get replicated to MYSQL3.
I hope it is clear. The bug is not that a user did not get replicated, the bug is that a user did get replicated.