Thanks so much for all the suggestions. Really appreciated.
The sql_log_bin isn't a runner for the reasons Tyler explained.
We'll probably use replicate-wild-ignore-table=db_name.tbl_name as all our
temporary tables being with 'temp_' so it would be easy to implement.
Thanks again for all your help on this.
On 8 February 2010 19:00, Rick James <rjames@stripped> wrote:
> 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
> 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.
> -- 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
> 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
> > Aodhan,
> > 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
> > replicate-ignore-db=[db_to_ignore]
> > 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
> >> 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
> >> them.
> >> We get around this normally by sending the CREATE TEMPORARY TABLE
> >> to the slave, so they never go to the master (and never end up in the
> >> log then). The problem is when we only have one copy on the master, and
> >> slave is getting in sync the master's binary log gets filled up with
> >> unnecessary statements.
> >> My question is there a way I can stop all CREATE TEMPORARY TABLE
> >> from being logged to the binary log?
> >> Many thanks for your time on this.
> >> Aodhan
> > --
> > *Tyler Poland, MySQL DBA*
> > Engine Yard: Support, Scalability, Reliability
> > 866.518.9273 x248
> > USA/EST
> > IRC: tpol