How are binlogs updated for MySQL temporary tables?

Question

1. 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?

2. If I use data from the temp table to write to a non-temp table, is the
   part-of-the-statement-that-reads-from-temp-table
   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.

Answer 1

Statements like CREATE TEMPORARY TABLE will land in the binary logs. This has plaugued me for years when troubleshooting. Here is why:

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 STOP SLAVE; before the mysqldump and START SLAVE; afterwards. When you run STOP SLAVE;, it terminates both the IO thread and the SQL thread for Replication. When the SQL thread dies, all tables created with CREATE TEMPORARY TABLE are dropped immediately. When you try to run START SLAVE;, MySQL Replication breaks instantly, stating that the table it was INSERTing into or SELECTing from does not exist. How did I find the table? I would have to run mysqlbinlog against the master's binary logs to locate the position that the CREATE TEMPORARY TABLE was issued.

So, to answer both questions, all you have to do is this:

Any INSERT into a temp table should like like this:

SET SQL_LOG_BIN=0;
INSERT INTO tmptable ... ;
SET SQL_LOG_BIN=1;

Any UPDATE of a temp table should like like this:

SET SQL_LOG_BIN=0;
UPDATE tmptable SET ... ;
SET SQL_LOG_BIN=1;

Any INSERT into a real table using data from a temp table should like like this:

SET SQL_LOG_BIN=0;
INSERT INTO realtable SELECT ... FROM tmptable ... ;
SET SQL_LOG_BIN=1;

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 SET SQL_LOG_BIN=0;