How to create linked or mirrored tables in MySQL

I have two applications usin开发者_StackOverflow中文版g two nearly identical MySQL databases within the same cluster. Some tables must contain separate data, but others should hold identical contents (i.e. all writes and rows in db1.tbl should be accessible in db2.tbl and vice versa).

What's the proper way to go about this? Note that the applications use hardcoded table (but not database) names, so simply telling application 2 to access db1.tbl is not an option.

What you need to do is set up replication for the tables that you need. See http://dev.mysql.com/doc/refman/5.0/en/replication.html for the documentation on setting up replication in MySQL.

For databases on different mysqld processes

You should check the official manual for replicating individual tables: http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_replicate-do-table

You can setup an Master-Master relation between the two mysql processes just keep in mind to be carefull and have uniqueness on your Primary Key.

For databases residing on the same server & mysqld service

IMHO design wise you should consider the idea of moving all your shared tables under a different DB. This way you will avoid all the overkill of triggers for updating them.