Reputation: 9745

Export schema without data

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?

Upvotes: 682

Answers (16)

Super Kai - Kazuya Ito

Reputation: 1

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

Upvotes: 2

Phoenix

Reputation: 1528

You can take using the following method

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

Upvotes: 1

Austyn Wilson

Reputation: 362

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:

Upvotes: 0

TanvirChowdhury

Reputation: 2445

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

Upvotes: 1

Daric

Reputation: 16789

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

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

Upvotes: 1298

Mamta Satoor

Reputation: 91

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`

Upvotes: 9

Ranga

Reputation: 1401

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

Upvotes: 3

user13012757

Reputation: 11

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

Upvotes: 1

Javier Larroulet

Reputation: 3237

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

Upvotes: 5

Anders B

Reputation: 3465

Dumping without using output.

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

Upvotes: 8

Ant1Zykl0n

Reputation: 392

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

Upvotes: 7

mias

Reputation: 17

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.

Upvotes: 0

PodTech.io

Reputation: 5264

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

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

Upvotes: 26

Rahul Chipad

Reputation: 2411

You can use the -d option with mysqldump command

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

Upvotes: 10

Khaleel Hmoz

Reputation: 995

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

Upvotes: 4

onteria_

Reputation: 70587

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

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

Upvotes: 138

Export schema without data

Answers (16)

Related Questions