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.

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

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

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.

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.

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.

THanks a lot

I also using InnoDB Engine mainly for my small MRP Database.
And I usually do this to backup:

And to restore, just use these commands:

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