Restoring a partial mysql backup

[Viral]

Hey guys,

Our lab is going to lose power for maintenance this weekend and we collect data into a mysql database. In order to solve this problem, we have decided to build a second (replica) database in another location and point our collection scripts to it. So basically, this second database will collect 24 hours worth of data which we then need to append back to our master server on monday.

Finally, my question. Does anyone know how to take a .sql file (made with mysqldump) and append it to an existing database?

Thanks,
--Viral

[Marshall]

The functionality that you're describing is called transaction tracking and MySQL does not support it. Your best bet would be to make the new database an exact replica of the old one, then completely replace the old version with the new current version after your power difficulties pass. See http://www.sitepointforums.com/showt...threadid=43210 for multiple descriptions of how this transfer can be accomplished.

- Marshall

[DR_LaRRY_PEpPeR]

Originally posted by Viral
Finally, my question. Does anyone know how to take a .sql file (made with mysqldump) and append it to an existing database?

yeah, sure. you just don't want the CREATE TABLE statements. if you already had the dump, you could just delete them from the file. or, when you do the dump, use the -t or --no-create-info options, and it'll only put the INSERT statements. so,

mysqldump --opt -t database > /path/to/dump/file.sql

then to import, of course

mysql database < /path/to/dump/file.sql

BUT, do you have an AUTO_INCREMENT column on the tables? well, if your "24 hour" table starts at 1, it's going to cause problems appending it to the "real" one. to fix that, before you do the dump of the "24 hour" tables (the AUTO_INCREMENT ones only), remove the AUTO_INCREMENT and NOT NULL parts with ALTER TABLE:

ALTER TABLE table MODIFY your_ai_col INT

then update all the rows to NULL

UPDATE table SET your_ai_column=NULL

dump it, and its rows will then correctly generate new AUTO_INCREMENT numbers when INSERTed into the "real" tables.

i THINK all this will work.

[Marshall]

If you're modifying records as well as adding new ones to the database then you're still going to have problems. A solution to that possible problem would be time stamping each transaction. Add a new column of type DATETIME to your table that would be set to NOW() with each update/insert query. Then, when transferring the data back to the original database, you could simply retrieve all records that were modified after a certain date. Remove the records from the first database with the same UniqueID values (as described by larry), and replace them with the new records.

- Marshall

[Viral]

This is a stats collection server that has been running for several years. Basically, it has nearly 30 gigs of data and our backup sun server doesn't have that kind of HD space available. So there's no way we can dump the data to the replica server, only the table structures.

I think Dr Pepper has the best solution for our needs. We'll give that a shot. We do have an auto-increment field, but I'm sure we can work through that small problem. Thanks for all your help guys!

--Viral