Does replicate-rewrite-db have any real world appl

Question

I've been experimenting with master-master replication between two MySQL servers but with different database names and I've come accross a bit of a stumbling block. It appears that although UPDATEs work correctly as expected, INSERTs do not.

While I would say that this is a bug or at least a problem with my setup, it appears that MySQL says that this is a feature (here and here).

If then, as MySQL say, it only works on the default database, then how can this setting have a practical real world purpose?

Am I missing something ?

---

For reference the relevant parts in my.cnf. I've been testing this by doing inserts and updates from within phpMyAdmin if that makes any difference.

master

log-bin=mysql-bin
binlog_do_db=db1

slave

replicate-rewrite-db=db1->db2
replicate-wild-do-table=db2.table1

Answer 1

I said it as a fundamental glitch and they put it as a featured request for the same issue.. which happened in binary logging ... http://bugs.mysql.com/bug.php?id=65837

Answer 2

I believe what they are referring there in the documentation, is that it is not going to translate specific database names for you, only databases set by USE. What they mean by that is, if you were to execute something such as:

USE db1;
INSERT INTO table_name (x,y,z) VALUES (1,2,3);

It should do as you're expecting.

However, if you explicitly name a database in your query, it will not automatically translate that for you. e.g.

INSERT INTO db1.table_name (x,y,z) VALUES (1,2,3); 

It is quite possible that phpMyAdmin is translating your query to specify the database name, along with the table name.

EDIT: While this does have practical purpose if you are in complete control of your query authoring across your entire system, you need to be extremely careful that you know what your queries are before they are run. Some types of ORM systems or intermediaries will rewrite your queries for you to be explicit. I would not suggest using this in production, especially when several teams are writing code that interfaces to your MySQL server.

Answer 3

Replication-rewrite-db usage is same as other replication options. Not only Replication-rewrite-db works on the default database, but these also: replicate-do-db, replicate-ignore-db, binlog-do-db and binlog-ignore-db. Refer this and this.

There are real world purpose, else MySQL wouldn't have implemented this option. And it works only on default database because-

The main reason for this “check just the default database” behavior is that it is difficult from the statement alone to know whether it should be replicated (for example, if you are using multiple-table DELETE statements or multiple-table UPDATE statements that act across multiple databases). It is also faster to check only the default database rather than all databases if there is no need.

You should also know the replication rules. From here.

I tested phpmyadmin by issuing INSERT, DELETE and UPDATE and noticed(By enabling general_query_log) that it issues INIT DB 'db_name'('Init DB' is logged for the mysql_select_db() API call).

For example:

Init DB sakila
1 Query INSERT INTO `sakila`.`actor_info` (`actor_id`, `first_name`, `last_name`, `film_info`) VALUES ('1235', 'abc', 'efg', NULL)

So ultimately the replication shouldn't break as phpmyadmin does it correctly by executing USE db before every query.

Answer 4

I have a set up with a limited number of MySQL servers, two. One is production (PROD) and only used for production, the other is development and doubles as Disaster Recovery (DR) and for running longing queries.

To enable disaster recovery, the PROD server replicates to the DR using replication but without renaming this would end up with two databases on the same server with the same name. So what I have is on production:

db_application

and on development I have two

db_application (which is used for development)
slave_application (which is used for DR)

Instead of using USE in every statement, I use the connection string to specify which database is the default and none of the statements include the database name. (i.e. SELECT * FROM TABLE rather than SELECT * FROM DB.TABLE)

This allows replication to work and to have a database on the development server with the same name as the production server. Which suits my needs but I appreciate others may not find this set up practical. This better suits having full control of the SQL but limited control of connection strings.