I, too, got burned big time by a create temp table. We had a dual-master
setup, with slaves hanging off both masters (only one master was in write
rotation). The standby master was in read rotation, so it received the
CREATE TEMPORARY TABLE requests, which were dutifully replicated to the main
master and all slaves. So was the DROP. It was a big waste, and a big
No -- set sql_log_bin=0; does not work -- it requires (in some versions)
SUPER. Having web servers connect as SUPER is totally unacceptable. (Johan
-- your 0 and 1 were reversed.)
Our quick solution was to take the one machine out of read rotation.
The diff db solution is probably the best. But Tyler's comments are
* CREATE DATABASE tmp;
* binlog-ignore-db=tmp (or equivalent -- combinations of binlog statements
can act strangely) (Less bandwidth used if you stop on master instead of
During the query:
1. USE tmp; -- this is critical; without it, foo will be replicated!
2. CREATE TEMPORARY TABLE foo ...; -- The actual name won't be 'foo'.
3. ... foo JOIN main_db.main_table -- note reaching from tmp to the other db
4. DROP TEMPORARY TABLE foo;
> From: Tyler Poland <tpoland@stripped>
> Date: Mon, 8 Feb 2010 11:25:24 -0500
> To: Aodhan Cullen <aodhan@stripped>
> Cc: <replication@stripped>
> Subject: Re: how to never log any CREATE TEMPORARY TABLE statement to the
> binary log
> You could create all temporary tables in a different database "create
> temporary table [db_to_ignore].tablename..." and then ignore that
> database in the replication stream by setting the following option in
> your config
> you could also use replicate-ignore-table to ignore each individual
> table name you use.
> On 2/8/10 10:40 AM, Aodhan Cullen wrote:
>> Hi Folks,
>> For our application we use a lot of CREATE TEMPORARY TABLE statements to
>> generate temporary reports. We never use a temporary table to insert or
>> update data in another table.
>> However all our CREATE TEMPORARY TABLE statements get recorded to the binary
>> log, which gives our slaves a problem with catching up. They execute all
>> these statements and there is no need to do so. They could simply ignore
>> We get around this normally by sending the CREATE TEMPORARY TABLE statements
>> to the slave, so they never go to the master (and never end up in the binary
>> log then). The problem is when we only have one copy on the master, and the
>> slave is getting in sync the master's binary log gets filled up with these
>> unnecessary statements.
>> My question is there a way I can stop all CREATE TEMPORARY TABLE statements
>> from being logged to the binary log?
>> Many thanks for your time on this.
> *Tyler Poland, MySQL DBA*
> Engine Yard: Support, Scalability, Reliability
> 866.518.9273 x248
> IRC: tpol