List:Replication« Previous MessageNext Message »
From:Tyler Poland 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  
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