Error replicating database due to cross-db reference - table doesn't exist

Question

We have mysql v5.0.77 running on a server collecting some measurement data.

On the mysql server, we have the following databases:

raw_data_db
config_tables_db
processed_data_db

We ONLY want to replicate the 'processed_data_db' which is constructed using information from the 'raw_data_db' and 'config_tables_db'.

We keep getting errors on our slave server when it tries to duplicate the statements that are constructing the processed data.

Example:
[ERROR] Slave: Error 'Table 'raw_data_db.s253' doesn't exist' on query. Default database: 'data'. Query: 'CREATE TEMPORARY TABLE temp SELECT * FROM raw_data_db.s253 WHERE DateTimeVal>='2011/04/21 17:00:00' AND DateTimeVal<='2011/04/21 17:10:00'', Error_code: 1146

What I am assuming is happening is that the cross-db selects can't find the raw database because we aren't replicating it, and the data do not exist on the slave...or something along those lines?

So I tried using ignores, but we're still getting the errors
replicate-wild-ignore-table = raw_data_db.*
replicate-wild-ignore-table = data.temp*

Other configuration information:
replicate-rewrite-db = processed_data_db->data
replicate-do-db = data

Is it possible to replicate just the one database if all the tables are created from references to other databases? Any ideas on how to get around this error?

I looked in to row-based replication which seemed like it might do the trick, but it's only available in v5.1 or greater....is there anything similar in earlier versions?

I fixed the ignore table statements to "data.%temp%", and it seems to be ignoring just fine, but I still can't replicate the tables I want because the insert statement is now referencing a table that doesn't exist.

ex.
Error 'Table 'data.temp' doesn't exist' on query. Default database: 'data'. Query: 'INSERT INTO abc SELECT FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(DateTimeVal))), ROUND(AVG(Difference),3), ROUND(STDDEV(Difference),3), ROUND(AVG(Frequency),0), ROUND(AVG(SignalPower),1) FROM temp WHERE ABS(Difference)<'10000.0' AND Difference!='0''

The processing is creating temporary tables from the raw database and then averaging all the values in the temporary table and inserting the result in to the processed_data_db, but since I'm ignoring the create statements, it doesn't have access to those tables, but the reason I'm ignoring them in the first place is because they reference tables outside of what I want to replicate...so I'm not sure how I should approach this....any suggestions would be greatly appreciated.

Answer 1

Temporary tables and replication options. By default, all temporary tables are replicated; this happens whether or not there are any matching --replicate-do-db, --replicate-do-table, or --replicate-wild-do-table options in effect. However, the --replicate-ignore-table and --replicate-wild-ignore-table options are honored for temporary tables.

http://dev.mysql.com/doc/refman/5.0/en/replication-features-temptables.html

edit:

1. replicate raw_data_db and config_tables_db tables which using in you insert query
2. use drbd protocol http://www.mysql.com/why-mysql/drbd/