mysql master-slave-master-slave replication

Question

I have two phisical servers. I copied some databases from server1 to server2 using command:

server1$ mysqldump -u root -q -p --delete-master-logs --flush-logs --extended-insert --master-data=1 --single-transaction --databases db1 db2 db3 db4>to_server2.sql

and then imported them as usual

server2$ mysql -u root -p <to_server2.sql

my.cnf on server2 includes:

server-id               = 2
log_bin
binlog_format           = mixed
replicate_do_db         = db1,db2
transaction-isolation=READ-COMMITTED

my.cnf on server1 includes:

server-id               = 1
log_bin
binlog_format           = mixed
replicate_do_db         = db3,db4
transaction-isolation=READ-COMMITTED

then I issue change master on both servers to point to each other; SHOW SLAVE STATUS already includes Read_Master_Log_Pos on server2 but anyway no data changes have been made during this operations.

Then started slave on both servers. SHOW SLAVE STATUS looks good on both server1 and server2.

The problem: Whenever data is changed on any server1 or server2 data appears in server's binlog and in other server's mysqld-relay-bin log fies BUT not appears in slave database. Read_Master_Log_Pos changes but not data in slave database. Any idea why?

You probably want to ask this on serverfault.com instead since replication is more of a sysadmin question than programming. — Rich Adams
Maybe. But there are still a lot of people mastering mysql... Maybe the problem is with coma i'm trying to separate database names with coma which is wrong because database name can contain coma... — Dmytro Leonenko

Dmytro Leonenko Dmytro Leonenko · Accepted Answer · 2011-01-20T23:14:18

The answer is: it is really because of coma in replicate_do_db betwin database names. The solution was to make replicate_do_db statement in my.cnf for each database to be replicated

mysql master-slave-master-slave replication

1 Answers