MySQL Master-Slave Replication Cross Database

Question

I am setting up Replication for cross Database as i have set 2 variables in Slave' s my.cnf as replicate-do-db=database_name and Replicate-wild-do-table=database_name.% ,But i am not able to get cross Database updates from master to slave . Please Help me to resolve the issue.

Answer 1

I addressed something like back on June 14, 2012 : Configured MySQL replication but its not working

The basic problem may be the queries you are using

If you have a query that looks like this

INSERT INTO db1.tb SELECT * FROM db2.tb;

You could be potentially blocking queries of this nature

Queries like this:

USE db1
INSERT INTO tb SELECT * FROM db2.tb;

should work if you have replicate-do-db=db1 and Replicate-wild-do-table=db.%

To be sure, check out the replication filtering rules to make sure your queries are allowing your current settings to work on the Slave.

According to the replication filtering rules

To make it easier to determine what effect an option set will have, it is recommended that you avoid mixing “do” and “ignore” options, or wildcard and nonwildcard options. An example of the latter that may have unintended effects is the use of --replicate-do-db and --replicate-wild-do-table together, where --replicate-wild-do-table uses a pattern for the database name that matches the name given for --replicate-do-db. Suppose a replication slave is started with --replicate-do-db=dbx --replicate-wild-do-table=db%.t1. Then, suppose that on the master, you issue the statement CREATE DATABASE dbx. Although you might expect it, this statement is not replicated because it does not reference a table named t1.

Perhaps you need to get rid of one of those rules on the Slave and restart mysql on the Slave.