Best method to import/export the database?

Question

I have a copy of Q2A running on localhost while I make changes to the theme and so on. However, if I export the live site and import into localhost I always get errors with key constraints.

I think it should work fine if I delete/truncate the tables in the correct order, with either qa_posts or qa_users last. What would be the correct order, and is there a simple way to backup the database? Currently I'm using phpMyAdmin.

EDIT: also, I am unable to truncate or delete the qa_posts table at all, even after all the others are truncated. I get this message:
#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`pokemondb`.`qa_posts`, CONSTRAINT `qa_posts_ibfk_2` FOREIGN KEY (`parentid`) REFERENCES `qa_posts` (`postid`))

Answer 1

Your best solution would be to use the mysqldump command line utility which takes the output from one MySQL database and converts it into SQL that can be run to recreate the data in another database.

To prevent foreign key errors, please see the comments on the mysqldump page about switching off foreign key checks during the import phase:

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

The reason you can't truncate the qa_posts table is that it has a foreign key within itself, from parentid to postid, so your solution would be to first set the parentid to NULL for every row before truncating.

Comments

Thanks, I'll look at that now. It's not going to be the solution for everybody though since most people probably wouldn't have access to their server.

---

To update this - I found that phpMyAdmin has an option "Disable foreign key checks" you can choose when exporting SQL. So you can import all the data without this problem.

---

While moving a DB from one server to another (I am using phpmyadmin since the mysqldump threw errors) I have got "#1215 - Cannot add foreign key constraint". Thanks @Scott for the tip to "Disable foreign key checks".

When importing you should also uncheck the option: "Enable foreign key checks"

I would suggest to add this to the docs: "Moving a database to another server" because everybody using phpmyadmin will run into this issue.