List:Replication« Previous MessageNext Message »
From:Aodhan Cullen Date:February 8 2010 9:11pm
Subject:Re: how to never log any CREATE TEMPORARY TABLE statement to the
binary log
View as plain text  
Hi Guys,

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.

Aodhan

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
> main
> master and all slaves.  So was the DROP.  It was a big waste, and a big
> burden.
>
> 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
> incomplete.
>
> Setup:
> * 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
> slave)
>
> 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
> >
> > 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
> 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
> >> them.
> >>
> >> 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.
> >>
> >> Aodhan
> >>
> >>
> >
> >
> > --
> > *Tyler Poland, MySQL DBA*
> > Engine Yard: Support, Scalability, Reliability
> > 866.518.9273 x248
> > USA/EST
> > IRC: tpol
>
>

Thread
how to never log any CREATE TEMPORARY TABLE statement to the binary logAodhan Cullen8 Feb
  • Re: how to never log any CREATE TEMPORARY TABLE statement to the binary logJohan De Meersman8 Feb
    • Re: how to never log any CREATE TEMPORARY TABLE statement to the binary logSuresh Kuna8 Feb
  • Re: how to never log any CREATE TEMPORARY TABLE statement to thebinary logTyler Poland8 Feb
    • Re: how to never log any CREATE TEMPORARY TABLE statement to thebinary logRick James8 Feb
      • Re: how to never log any CREATE TEMPORARY TABLE statement to the binary logAodhan Cullen8 Feb
      • Re: how to never log any CREATE TEMPORARY TABLE statement to the binary logJohan De Meersman8 Feb
        • Re: how to never log any CREATE TEMPORARY TABLE statement to the binary logRick James8 Feb
          • Re: how to never log any CREATE TEMPORARY TABLE statement to thebinary logGuilhem Bichot9 Feb