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?

Stelu

Answer 1

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

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

Mcginnis

IMHO, mysqldump is the best answer. MySQL Administrator is abandoned and MySQL Workbench is still quite buggy. – Buttress

Also consider using the --single-transaction option if you don't want or can't do table locks. – Verruca

-d is --no-data for short. – Inconclusive

Is it also possible to export the database without data except a few tables? Or is it easier to do this in multiple exports? – Apologize

Also consider adding --routines if you're database has stored procedures/functions – Islas

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 – Yoko

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... – Sot

you may want to use --single-transaction to avoid issues with LOCK Tables while executing the dump. – Kalif

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 – Allege

Answer 2

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

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

Singleaction

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. – Florilegium

@zypA13510, apparently ten seconds can be a difference of 749 upvotes. – Pesade

Answer 3

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

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

Gillis

Answer 4

You can use the -d option with mysqldump command

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

Trademark

Answer 5

Dumping without using output.

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

Pattipattie

Answer 6

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`

Morel

Answer 7

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.

Carillo

I gave it a try, it doesnt contain any triggers (and god knows what else not) – Weasner

I used datagrip to generate DDL, and it somewhat didn't copy the default null and my database schema is inconsistent now :3 – Diffidence

Answer 8

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

Internode

Answer 9

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

Gegenschein

Answer 10

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

Superficies

Answer 11

For example, you can export only the schema of the tables of apple database to backup.sql with -d(--no-data) as shown below. *My answer explains how to export both the schema and data of the tables of a database:

mysqldump -u john -p -d apple > backup.sql

Or:

mysqldump -u john -p --no-data apple > backup.sql

Loquat

Answer 12

You can take using the following method

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

Melson

This answer adds nothing that other answer haven't already said. – Buettner

Answer 13

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

Fragmental

Welcome to StackOverflow. You could make a great answer out of this if you give a little more information and constraints. – Counterproof

Answer 14

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

Kutaisi

Answer 15

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.

Inaccessible

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... ;-) – Rung

Answer 16

From phpmyadmin you can do the following:

1. Export
2. Export method: Custom - display all possible options
3. Format-specific options: structure NOT structure and data

To export the entire database:

Cogen