Copy MySQL Database Structure without Data

Three easy steps to copy the structure of a MySQL database, i.e. the table definitions but no rows/records/data:

1. Create the new database, either from MySQL Query Browser (part of MySQL GUI Tools) or MySQL Command Line:

CREATE DATABASE `new_database_name`;

2. Copy the database structure using the mysqldump command from a dos command prompt. Note: If MySQL is not in your path you will have to navigate to the MySQL bin directory, on my machine this is in C:\Program Files\MySQL\MySQL Server 5.1\bin.

mysqldump -d -u root -p -v original_database_name > c:\temp\dbstruct.sql

3. Restore the copied database structure to your new database from the same dos command prompt as in step 2.

mysql.exe -uroot -p new_database_name < c:\temp\dbstruct.sql

Done!

Some notes:
– I used MySQL Server 5.1 when testing this but should work also with earlier versions. If you can or cannot get it to work with another version please add a comment.
– In this example I used root as user but any user with read and create permissions would work.

[Edit: Use mysqldump -R parameter to backup stored procedures and functions, above example would be:
mysqldump -d -u root -p -v -R original_database_name > c:\temp\dbstruct.sql
Link to mysqldump in MySQL Reference Manual ]

5 Replies to “Copy MySQL Database Structure without Data”

  1. Found an even easier way to do this but have not tested it. You will probably need to add parameters for username and password for it to work:
    shell> mysql -e ‘create database new_db’
    shell> mysqldump -d old_db | mysql new_db

  2. To include all stored procedures in copy add the –routines parameter:

    mysqldump -d -u root -p -v --routines original_database_name > c:\temp\dbstruct.sql

  3. I’m having problems with viewing your page layout in the latest version of Opera. It’s fine in Explorer 7 and Firefox however.Hope you have a wonderful day.

  4. i am importing a database of 112 tables with records. but only 40 tables imported and after that it gives the error of foreign key

Leave a Reply

Your email address will not be published. Required fields are marked *