The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Restoring a partial mysql backup

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?

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.

Re: Restoring a partial mysql backup

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,

Code:

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

then to import, of course

Code:

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.

- Matt ** Ignore old signature for now... **
Dr.BB - Highly optimized to be 2-3x faster than the "Big 3." "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

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.

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!