List:General Discussion« Previous MessageNext Message »
From:Eric Goldblatt Date:March 9 2006 7:44pm
Subject:RE: replication after editing bin logs
View as plain text  
Atle,

No, unfortunately we have no control over the DELETE queries. The master
mysql database is the back end for an application which we cannot touch.


Eric 


-----Original Message-----
From: Atle Veka [mailto:atlev@stripped] 
Sent: Thursday, March 09, 2006 1:35 PM
To: Goldblatt, Eric
Cc: mysql@stripped
Subject: Re: replication after editing bin logs

Do you have control over the DELETE queries? If so I would look into the
option of using 'SQL_LOG_BIN':

http://dev.mysql.com/doc/refman/5.0/en/set-option.html
	SQL_LOG_BIN = {0 | 1}
	If set to 0, no logging is done to the binary log for the
client.
The client must have the SUPER privilege to set this option.

This means that you can run the DELETE queries on the master only,
leaving the slave untouched.


Atle
-
Flying Crocodile Inc, Unix Systems Administrator

On Wed, 8 Mar 2006, Goldblatt, Eric wrote:

> Hello,
>
>
> Another division in my organization is maintaining a mysql database. 
> For various reasons, data is deleted from this database after it has 
> aged more than a month. My division has a need for long term storage 
> of the same data, so I am trying the following strategy:
>
> 1. Create a snapshot ("slave") of the master database.
> 2. Enable binary logging on the master database.
> 3. Periodically, run the binary logs through the mysqlbinlog utility 
> to produce SQL text.
> 4. Parse the SQL text to remove DELETE's of aged data.
> 5. Apply the SQL text to the snapshot (slave).
>
> Now data older than one month is being saved in the slave.
>
>
> When I came to step 5, I submitted the SQL text file as a batch job:
>
> mysql -u root -p -e "source binlogs_045_052_parsed"
>
> After many hours I received the following error message:
>
> ERROR 1036 (HY000) at line 1387471 in file: 'binlogs_045_052_parsed':
> Table 'AB4539p2' is read only
>
> At this point, the mysql batch job terminated.
>
>
> Has anyone come across a similar error under similar circumstances?
>
>
> Some background:
>
> I am running mysql 5.0 on a Windows 2003 server. The SQL text file, 
> binlogs_045_052_parsed, contains only three kinds of statements:
> millions of LOAD DATA LOCAL INFILEs, a handful of DROP TABLEs, and a 
> few CREATE TABLEs. The LOAD DATA LOCAL INFILEs are doing inserts into 
> about 5000 tables in the database. Before I received the error, many 
> inserts into table 'AB4539p2' succeeded without any error.
>
>
> I have a few more basic questions:
>
> 1. Will all the LOAD DATA LOCAL INFILES submitted before the error 
> have been committed, or will all the statements from the batch job 
> have rolled back once the error occurred? (I want to know if I need to

> resubmit all of the file binlogs_045_052_parsed to mysql, or just the 
> portion from the error.)
>
> 2. I notice that mysqlbinlog puts ROLLBACK statements at the beginning

> and end of the SQL text file. I don't understand the purpose of these 
> ROLLBACKs.
>
>
>
> Thank you,
>
> Eric
>
>
>
>
>
Thread
replication after editing bin logsEric Goldblatt8 Mar
  • Re: replication after editing bin logsScott Tanner9 Mar
  • Re: replication after editing bin logsAtle Veka9 Mar
RE: replication after editing bin logsEric Goldblatt9 Mar
RE: replication after editing bin logsEric Goldblatt9 Mar
RE: replication after editing bin logsDan Burke9 Mar