MySQL Dump / Restore (Data Only)

Discussion in 'Databases' started by koolsamule, Feb 18, 2010.

  1. #1
    Hi Chaps,

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

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    In this case no. You are trying to insert a duplicate value for a key. You need to figure out why there are multiple rows with the value of '1' in this key column. If you have a 0 value in the first row it's possible that Mysql is giving that row a value of 1, which then breaks any subsequent inserts. Mysql doesn't like 0 as a value.

    If you run into foreign key violations when restoring, you can run: SET foreign_key_checks = 0; before the restore and then SET foreign_key_checks = 1; when you're done.
     
    jestep, Feb 18, 2010 IP
  3. koolsamule

    koolsamule Peon

    Messages:
    101
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Hi, I found TRUNCATE, which should delete the contents of the table, but don't know if it can be added to the mysqldump options????
     
    koolsamule, Feb 18, 2010 IP
  4. koko5

    koko5 Active Member

    Messages:
    394
    Likes Received:
    14
    Best Answers:
    1
    Trophy Points:
    70
    #4
    Hi, try adding a --replace=TRUE option without truncate.
     
    koko5, Feb 18, 2010 IP
  5. koolsamule

    koolsamule Peon

    Messages:
    101
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    OK, cool, I'll give that a go too. Cheers
     
    koolsamule, Feb 18, 2010 IP