Rebuilding a test database into a production one

I've been testing a database in MySQL for several months using manual deletes, updates, and other queries. It's fair to say the DB has 开发者_StackOverflow社区been abused.

I now want to upload it to my production server, but I want to start with a fresh copy. Is there a way to do this?

I don't want to retain any data. I just want a nice, clean database to start with that hasn't been abused with months of testing.

If you're using mysqldump, supply the --no-data parameter:

mysqldump --host=host --user=user --password=password --no-data db_name > structure.sql

That will create a new file structure.sql containing the CREATE TABLE statements necessary.

If you have some other administration tool that allows exporting, e.g. phpMyAdmin, you should have an option to leave out the data and only export the structure.

If you could not find the option in your DB Manager software, you can always use TRUNCATE TABLE <table_name>; for each table. This cleans the table, and returns the AUTO_INCREMENT counter to 0 (if you are using it). If you are using foreign keys, you can wrap all your truncate query like this:

SET FOREIGN_KEY_CHECKS=0;

TRUNCATE TABLE <table_name1>;
TRUNCATE TABLE <table_name2>;

SET FOREIGN_KEY_CHECKS=1;

to avoid foreign checks.