MySql 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?

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

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

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

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

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

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

You can use the -d option with mysqldump command

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

Dumping without using output.

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

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.

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`

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

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

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

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

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.

You can take using the following method

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

Hope it will helps you

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