I am developing a very huge system in Java using Hibernate and MySQL. Now, I want to automate the daily, weekly, and/or monthly replication (backing up) of the data that are stored from the database using the system. I am very aware that this could be done easily using the mysqldump command, which could be easily incorporated in Java. But since the data that are handled and stored are very enormous, it came to my mind lately the possibility of backing up only those records that are newly added or modified from the last accomplished dump. Now I'm soliciting for your ideas or procedures on how could this be done. Thanks.
1 Answer
We have a similar process in place, where we run a script to
TRUNCATE TABLE backupTable;
INSERT INTO backupTable SELECT * FROM mainTable WHERE datefield >= xxxx
WHERE x is the time the backup ran the day before. Then we run MySQLDump against backupTable.
Alternatively you could do something similar and set a 'backedup' flag in the main table, and then just
INSERT INTO backupTable SELECT * FROM mainTable WHERE 'backedUp' = 0;
and then update the backedUp flag in the main table.
