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:[EMAIL PROTECTED] Sent: Thursday, March 09, 2006 1:35 PM To: Goldblatt, Eric Cc: mysql@lists.mysql.com 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 > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
