If I use
CREATE TEMPORARY TABLE, can I write and update this MySQL table and know that none of it will be stored to the binlogs?
If I use data from the temp table to write to a non-temp table, is the
simply substituted with plain values, or is there something else that happens in the binlog to indicate the data source?
I ask this because normally when reading from another table, the binlog shows the whole statement, meaning it normally does not substitute the plain values.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
In MySQL Replication, if you create a temp table, it will remain in existence as long as the SQL thread is in session. I have had dozens of instances where someone would schedule a nightly backup which included
So, to answer both questions, all you have to do is this:
Any INSERT into a temp table should like like this:
Any UPDATE of a temp table should like like this:
Any INSERT into a real table using data from a temp table should like like this:
UPDATE 2012-03-19 17:06 EDT
I read your comment
Question 1 from your comments : What happens if two threads both create temp tables of the same name? Do the statements fall into the binlogs and conflict?
Answer : Each temp table of identical names (even identical schemas) are unique to the the DB Connection that opened it. Therefore, no conflict whatsoever. In fact, since replication is single-threaded along the SQL thread, all SQL statements with temp tables of identical names are processed in a serialized manner, i.e., like a FIFO queue.
Question 2 from your comments : Wouldn't this cause the changes to realtable to not be reflected in the binlogs?
Answer : You are correct. Therefore, since you want the changes to the realtable replicated to the Slave, you will not need