SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Dump / Restore (Data Only)

    Hi Chaps,

    I have a CMD prompt script that will dump only the data from my database (dbjobs):
    Code:
    mysqldump -u 'username' -p'password' --no-create-info --complete-insert dbjobs > c:/database_data_only.sql
    But when I try to restore the data, I get multiple errors:
    e.g.
    Code:
    ERROR 1062 (23000): Duplicate entry '1' for key 1
    Is there something that I can add to the mysqldump command that will solve this issue?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    are you restoring it to the same table you dumped it from?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, but I've included '--complete-insert' to accomodate the possibility of restoring the data to the same table, with extra columns, at a later date

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if you're gettin a "duplicate key" error when you try to load the dumed data back into the same table you dumped it from, then there are only two possibilities that i can think of --

    1. you defined the key after you dumped the data

    2. you're loading the same data into the table without clearing out the table first
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    2. you're loading the same data into the table without clearing out the table first
    Sounds right to me, how do I clear the data first, then restore the data in the sql file?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, that sounds sweet . . .is there a way I can add this to my CMD prompt, so each table is TRUNCATEd automatically?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sorry, i don't do command prompts, i gave up DOS when Windows came out

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK far enough, the PHP comand and CMD prompt are the same:
    mysqldump -u 'username' -p'password' --no-create-info --complete-insert dbjobs > c:/database_data_only.sql
    Can TRUNCATE / or equivalent be added to the mysqldump options?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by kool_samule View Post
    Can TRUNCATE / or equivalent be added to the mysqldump options?
    what did you find when you looked for this in da manual?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Couldn't find anything that would suggest that TRUNCATE could be used, hence 'equivalent'. . . I've looked at: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html, but couldn;t see anything that could do the same. . .unless I've missed it?

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    hint: the answer was no

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so is there no way of dumping the data out of a table, clearing it, then importing what was dumped?

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, there is a way -- three separate steps

    1. dump
    2. truncate
    3. load
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Addict
    Join Date
    Aug 2009
    Posts
    299
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK,
    three separate steps
    ....Got it!
    Cheers


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
  •