i have a program that writes into a mysql database, and at some time, i have to do some sort of database rotation
that's like a dump followed by a truncate (at least it's how I see it)
the problem is that while i do the dump, that program is very likely to write into the database and after the dump finishes, if i truncate the tables i might lose some lines (the ones added during the dump)
i know i can set mysqldump to lock the tables, but after the dump ends it releases them and the problem of the program inserting lines before the truncate starts appears once more (it may add even a few lines before it actually does the truncate, and then I lose them with the truncate)
practically i want to backup a database (periodically) to a file and then clear the database, without losing any intermediate data inserted into it
is there any way to solve this?