MySql export schema without data

Question

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?

score 1250 · Accepted Answer · edited Nov 03 '20 at 11:10

1250

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

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

edited Nov 03 '20 at 11:10

Kasun Siyambalapitiya

3,956
8
38
58

answered May 30 '11 at 11:09

Daric

16,229
11
41
58

14

IMHO, `mysqldump` is the best answer. MySQL Administrator is abandoned and MySQL Workbench is still quite buggy. – Álvaro González May 30 '11 at 11:35
62

Also consider using the `--single-transaction` option if you don't want or can't do table locks. – Jim Jan 22 '13 at 21:55
57

-d is --no-data for short. – marstone Oct 08 '14 at 17:15
1

Is it also possible to export the database without data except a few tables? Or is it easier to do this in multiple exports? – Peter Nov 20 '14 at 13:49
45

Also consider adding --routines if you're database has stored procedures/functions – crafterm Dec 02 '14 at 00:01
28

By default, this does not include the `CREATE DATABASE` command. To include, replace `dbname` with `--databases dbname` (shorthand: `-B dbname`). Then to import on another server, use `mysql -u root -p < schema.sql` – Sean Oct 28 '16 at 01:00
To avoid having the character set of your shell interfere with encoding, -r schema.sql is preferred over > schema.sql. Also a good idea to specify the character set explicitly with --default-character-set=utf8 (or whatever). You'll still want to check the set names at the top of the dump file. I've been caught in MySQL charset encoding hell before... – Craig Jacobs Feb 14 '17 at 23:47
you may want to use `--single-transaction` to avoid issues with LOCK Tables while executing the dump. – gk. Feb 27 '19 at 03:50
3

Summing all the options in the comments, the complete command is: `mysqldump -u root -p --single-transaction -d -R -B -r schema_structure_backup.sql schema_name` – Metafaniel Oct 02 '20 at 23:12

score 127 · Answer 2 · answered May 30 '11 at 11:09

127

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

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

answered May 30 '11 at 11:09

onteria_

68,181
7
71
64

5

I'm surprised this is not the accepted answer despite being posted ten seconds earlier and being more complete if not identical to the accepted answer. – zypA13510 Sep 05 '18 at 08:30
2

@zypA13510, apparently ten seconds can be a difference of 749 upvotes. – emallove Mar 05 '19 at 18:41

score 25 · Answer 3 · answered Mar 11 '16 at 13:19

25

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

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

answered Mar 11 '16 at 13:19

PodTech.io

4,874
41
24

score 9 · Answer 4 · answered Apr 01 '15 at 11:17

9

You can use the -d option with mysqldump command

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

answered Apr 01 '15 at 11:17

Rahul Chipad

2,373
17
20

score 8 · Answer 5 · answered May 24 '17 at 09:36

8

Dumping without using output.

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

answered May 24 '17 at 09:36

Anders B

3,343
1
26
17

score 7 · Answer 6 · edited Aug 30 '20 at 11:07

7

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`

edited Aug 30 '20 at 11:07

Majid Fouladpour

29,356
21
76
127

answered May 21 '18 at 17:50

Mamta Satoor

71
1
2

score 6 · Answer 7 · answered Nov 12 '16 at 17:17

6

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

answered Nov 12 '16 at 17:17

Ant1Zykl0n

372
4
11

2

I gave it a try, it doesnt contain any triggers (and god knows what else not) – phil294 Feb 21 '17 at 04:24
I used datagrip to generate DDL, and it somewhat didn't copy the default null and my database schema is inconsistent now :3 – ssi-anik Mar 24 '22 at 18:16

score 4 · Answer 8 · answered Nov 07 '18 at 12:36

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" :)

score 4 · Answer 9 · answered May 30 '11 at 11:10

4

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

answered May 30 '11 at 11:10

Khaleel Hmoz

987
2
13
24

score 2 · Answer 10 · answered May 14 '20 at 20:31

2

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

answered May 14 '20 at 20:31

Ranga

1,191
12
19

score 1 · Answer 11 · edited Jun 15 '23 at 10:55

1

You can take using the following method

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

edited Jun 15 '23 at 10:55

halfer

19,824
17
99
186

answered Jan 09 '17 at 07:04

Phoenix

1,470
17
23

7

This answer adds nothing that other answer haven't already said. – Daniel Jan 11 '17 at 23:40

score 1 · Answer 12 · answered Mar 05 '20 at 13:16

1

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

answered Mar 05 '20 at 13:16

user13012757

11
1

5

Welcome to StackOverflow. You could make a great answer out of this if you give a little more information and constraints. – Janhoo Mar 05 '20 at 13:32

TanvirChowdhury · Answer 13 · 2021-11-16T11:53:57.673

Using mysql-backup4j its quite easy to backup any database with few lines of code.this will generate the sql dump for database that can later be use to restore database easily.

maven dependency required for this is :

 <dependency>
        <groupId>com.smattme</groupId>
        <artifactId>mysql-backup4j</artifactId>
        <version>1.0.1</version>
    </dependency>

And here goes the implementation in java..you can play with the cool parameter sets

/**
 * Creator : Tanvir Chowdhury
 * Date    : 2021-11-15
 */
public class BackupDatabase {
    public static void main(String[] args) throws Exception {

        Properties properties = new Properties();
        properties.setProperty(MysqlExportService.DB_NAME, "hcs_test_db");
        properties.setProperty(MysqlExportService.DB_USERNAME, "root");
        properties.setProperty(MysqlExportService.DB_PASSWORD, "root");
        properties.setProperty(MysqlExportService.DELETE_EXISTING_DATA, "true");
        properties.setProperty(MysqlExportService.DROP_TABLES, "true");
        properties.setProperty(MysqlExportService.ADD_IF_NOT_EXISTS, "true");
        properties.setProperty(MysqlExportService.JDBC_DRIVER_NAME, "com.mysql.cj.jdbc.Driver");
        properties.setProperty(MysqlExportService.JDBC_CONNECTION_STRING, "jdbc:mysql://localhost:3306/hcs_test_db");

        properties.setProperty(MysqlExportService.TEMP_DIR, new File("/Users/tanvir/Desktop/backups/backup.sql").toString());
        properties.setProperty(MysqlExportService.PRESERVE_GENERATED_ZIP, "true");

        MysqlExportService mysqlExportService = new MysqlExportService(properties);
        mysqlExportService.export();

    }
}

if required you can also use the mail sending options quite easily to send the backup file to any email address.

Or You can do with the --no-data option with mysqldump command.If you want to do this from java then pass this command to runtime exec() method as param.

mysqldump -u root -h localhost --no-data -proot hcs_db_one?useSSL=false > db_one_dump.sql

score 0 · Answer 14 · answered Oct 26 '16 at 10:01

0

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.

answered Oct 26 '16 at 10:01

mias

17
1

you might want to update your answer with as to what program you are doing that in, for example MS SQL Server Mgmt. Studio or Oracle SQL Developer... ;-) – JGlass Apr 24 '21 at 02:47

Austyn Wilson · Answer 15 · 2022-06-24T03:41:40.603

0

From phpmyadmin you can do the following:

Export
Export method: Custom - display all possible options
Format-specific options: structure NOT structure and data

To export the entire database:

edited Jun 24 '22 at 03:41

answered Jun 15 '22 at 08:35

Austyn Wilson

75
5

MySql export schema without data

15 Answers15

Linked

Related