From: Rick James Date: February 8 2010 7:00pm Subject: Re: how to never log any CREATE TEMPORARY TABLE statement to the binary log List-Archive: http://lists.mysql.com/replication/1815 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit 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 > Date: Mon, 8 Feb 2010 11:25:24 -0500 > To: Aodhan Cullen > Cc: > 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