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 ]

Published
Categorized as MySQL

5 comments

  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 comment

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