List:Replication« Previous MessageNext Message »
From:Suresh Kuna Date:February 8 2010 4:25pm
Subject:Re: how to never log any CREATE TEMPORARY TABLE statement to the
binary log
View as plain text  
As Johan said, Use  set sql_log_bin=1 just before you creating the temporary
table and switch it off after creation and follow the same when you drop it

-- 
Thanks
Suresh Kuna
MySQL DBA

On Mon, Feb 8, 2010 at 9:51 PM, Johan De Meersman <vegivamp@stripped>wrote:

> set sql_log_bin=1;
> *statements you don't want logged*
> set sql_log_bin=0;
>
> http://dev.mysql.com/doc/refman/5.0/en/set-sql-log-bin.html
>
>
>
> On Mon, Feb 8, 2010 at 4:40 PM, Aodhan Cullen <aodhan@stripped> 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
> >
>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>

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