PHP/MYSQL - Backing up mysql data that changes by the second.

Question

I have a mysql table (A) that's taking in hundreds to thousands of inserts per second. Every 15 minutes I'd like to move all that data to table (B) and delete all the data from (A) without interrupting/missing the new rows going in.

Suggestions? Tips?

Answer 1

You could copy the rows, and then delete only those rows that were copied:

insert into B (keycol, col1, col2)
select keycol, col1, col2 from A

Then delete rows in A that are already in B:

delete A
from A
inner join B on A.key = B.key

Alternative syntax:

delete from A
where exists (
    select * from B where A.key = B.key
)