SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Addict Viral's Avatar
    Join Date
    Nov 2001
    Location
    Washington DC
    Posts
    294
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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?

    Thanks,
    --Viral
    A computer without Windows is like a chocolate cake without mustard.

  2. #2
    Are you ready for BSD? Marshall's Avatar
    Join Date
    Dec 2001
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

  4. #4
    Are you ready for BSD? Marshall's Avatar
    Join Date
    Dec 2001
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    SitePoint Addict Viral's Avatar
    Join Date
    Nov 2001
    Location
    Washington DC
    Posts
    294
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    A computer without Windows is like a chocolate cake without mustard.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •