PDA

View Full Version : Clearing out a Table After Backup?



timmynyce
January 9th, 2007, 01:21 PM
Everything I have read so far with regards to Zmanda looks good. I was wondering if there was a clean way to keep the read lock on a table (or whole database), back it up, verify the backup was successful, then clear out the table or DB and allowing new incoming data once that process is complete.

I basically have a table where all the data is critical, but will be stored in a permanent DB outside of the live mysql DB, so I wanted to set up incremental backups each day, then do one full backup at the end of the week, clear the table and start over each week. I will then archive all data in a permanent DB for reporting,etc.

Thanks in Advance for any suggestions of how Zmanda could help manage this.

paddy
January 9th, 2007, 02:00 PM
Have you considered MySQL replication? You can replicate from your critical database to the slave. Do full and incremental backups from replication slave using ZRM for MySQL.
At the end of week, you can remove all the data from the replication slave after stopping
replication.

Paddy

timmynyce
January 10th, 2007, 07:52 AM
I have thought of that, but the permanent database is inside a very secure network, and is a windows box. Frankly, I don't have plans to connect the two right now (I'm sure eventually I will). I was just wondering how to clear the table after verifying the backup was 100% successful, then release the lock on the table and start fresh.

Thanks Again,
Tim

paddy
January 10th, 2007, 11:26 AM
MySQL replication will make the solution easier.

An alternate solution could be to write a pre-backup and post-backup plugin.

pre-backup plugin will obtain a read lock on the table.

post-backup plugin will do verification of the backup, obtain a write lock (can we do lock upgrade?), delete all rows in the table, release the lock.

Please let us know the status if you try it.

Thanks,
Paddy

timmynyce
January 10th, 2007, 03:04 PM
Thats pretty much what I was planning on doing if there was not a simpler solution. I will have this done pretty soon and post my results for everyone....

Tim

timmynyce
January 18th, 2007, 11:22 AM
Just wanted to confirm some details on this before proceeding:

Using a Pre/Post Plugin to handle the locks on the tables will require creating database connections as well? (i.e. using perl:DBI module) I could not find any management of mysql connection handles in the existing zmanda files....

Furthermore,
Since we need to lock/unlock on the the same thread, the connection handle will presumably need to be shared between the Pre and Post plugins. As a non-perl programmer, it's not entirely obvious how this would be done.

Also, will locking the table in the Pre plugin interfere with the actual backup process? Or will it continue regardless of a read or write lock on the table(s)?

Just checking, as your suggestion seems like the direction I want to head in, but several items would need to be tested first. I'll post back anything I discover for everyone. Thanks!