Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Posts
    57

    Lightbulb Unanswered: Correct way to backup mysql database with foreign Key

    Hi all,

    I am using MySql version 5.0 (Innodb). I would like to backup my database (include data) and restore to another server.
    Every time i backup using mysql administrator, it will backup to a db.sql file and sort by tablename. I facing problem on restore my database as my table have foreign key.
    example: i have table
    1. member
    2. state
    3. city
    4. country

    and in the db.sql file, the backup sequence will be city, country, member and state.
    But in my database, city have foreign key which link to state and country, so when i try to restore the database from the sql file, it faild during create table for city, cause state and country table not exist.

    any better to do this?

    very appreciate on your help.

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Interesting, it sounds like MySQL administrator has a quirk in it. Have you tried using mysqldump directly to see if it does it in alpha order?

    Ideally what you need to do is turn off foreign key checks.

    So instead of reordering all your tables in the .sql file you append that code to the top and bottom

    Code:
    SET AUTOCOMMIT = 0;
    SET FOREIGN_KEY_CHECKS=0;
    
    .. your dump file ..
    
    SET FOREIGN_KEY_CHECKS = 1;
    COMMIT;
    SET AUTOCOMMIT = 1;

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    nah
    sounds more like you are not reloading the tables in precendence order
    you will need to make sure that your tables go in the correct sequence to maintain the key integrity.

    I would have thought your restore sequence should be
    Country
    State
    City
    Member

    optionally I think you can switch key constraints on or off as you perform this type of process.. but Im not sure Id reccomend it.. although it can make a significant amount of difference to backup/restore times
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jul 2007
    Location
    Africa
    Posts
    7
    You need to export your stuff, with this you can easliy import and it won't affect any thing of such. like me when designing a database i just run everything on my local server and import to remote server, with this i don't loose anything.

  5. #5
    Join Date
    Jun 2004
    Posts
    57
    actually i want to restore directly from the backup file, without re-ordering the query. I have a lot of table inside my database, the sample given is just few table.

    i do try backup in phpmyadmin which provide by my webhosting company and i am able to restore directly from the sql file.

    However, i have no idea how to install phpmyadmin in my local window pc. so still facing problem on restore database to my web server from my develop machine.

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Edit your backup file and put in the bits I told you to at the top and bottom of your file and then use PHPMyAdmin to import it. Or if you want to use MySQL Administrator, use the MySQL Query Browser, open up a script tab, and copy and paste in your sql backup file into the script tab, and then append my parts to the top and bottom of the script and then run it.

  7. #7
    Join Date
    Jun 2004
    Posts
    57
    THanks a lot

  8. #8
    Join Date
    Jul 2007
    Posts
    4
    I also using InnoDB Engine mainly for my small MRP Database.
    And I usually do this to backup:

    Code:
    mysqldump database_name -u root -p rootpass > backup.sql
    And to restore, just use these commands:

    Code:
    $mysql -u root -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 132
    Server version: 5.0.32-Debian_7etch1-log Debian etch distribution
    
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    
    mysql>source backup.sql;
    I did the backup almost everyday, and the restoration is prefect..

    I hope this will help

    ------Edited----------
    Ah, sorry! I think you have found your solution... But it's no shame isn't it?


    Regards

Posting Permissions

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