sql – MySql export schema without data

The Question :

521 people think this question is useful

I’m using a MySql database with a Java program, now I want to give the program to somebody else.

How to export the MySql database structure without the data in it, just the structure?

The Question Comments :

The Answer 1

1019 people think this answer is useful

You can do with the --no-data option with mysqldump command

mysqldump -h yourhostnameorIP -u root -p --no-data dbname > schema.sql

The Answer 2

92 people think this answer is useful

Yes, you can use mysqldump with the --no-data option:

mysqldump -u user -h localhost --no-data -p database > database.sql

The Answer 3

18 people think this answer is useful

you can also extract an individual table with the --no-data option

mysqldump -u user -h localhost --no-data -p database tablename > table.sql

The Answer 4

7 people think this answer is useful

Dumping without using output.

mysqldump --no-data <database name> --result-file=schema.sql

The Answer 5

6 people think this answer is useful

You can use the -d option with mysqldump command

mysqldump -u root -p -d databasename > database.sql

The Answer 6

4 people think this answer is useful

Beware though that –no-data option will not include the view definition. So if yo had a view like following

create view v1 
 select `a`.`id` AS `id`,
 `a`.`created_date` AS `created_date` 
from t1;

with –no-data option, view definition will get changed to following

create view v1
 select 1 AS `id`, 1 AS `created_date`

The Answer 7

3 people think this answer is useful

You Can Use MYSQL Administrator Tool its free http://dev.mysql.com/downloads/gui-tools/5.0.html

you’ll find many options to export ur MYSQL DataBase

The Answer 8

3 people think this answer is useful

In case you are using IntelliJ you can enable the Database view (View -> Tools Window -> Database)

Inside that view connect to your database. Then you can rightclick the database and select “Copy DDL”. Other IDEs may offer a similar function.

IntelliJ DDL

The Answer 9

2 people think this answer is useful

If you want to dump all tables from all databases and with no data (only database and table structures) you may use:

mysqldump -P port -h hostname_or_ip -u username -p --no-data --all-databases > db_backup.sql

This will produce a .sql file that you can load onto a mysql server to create a fresh database. Use cases for this are not many in a production environment, but I do this on a weekly basis to reset servers which are linked to demo websites, so whatever the users do during the week, on sunday nights everything rolls back to “new” 🙂

The Answer 10

1 people think this answer is useful

To get an individual table’s creation script:
– select all the table (with shift key)
– just right click on the table name and click Copy to Clipboard > Create Statement.

The Answer 11

0 people think this answer is useful

shell> mysqldump –no-data –routines –events test > dump-defs.sql

The Answer 12

0 people think this answer is useful

Add the –routines and –events options to also include stored routine and event definitions

mysqldump -u <user> -p --no-data --routines --events test > dump-defs.sql

The Answer 13

-1 people think this answer is useful

You can take using the following method

mysqldump -d <database name> > <filename.sql> // -d : without data

Hope it will helps you

Tags:,

Add a Comment