How to export database schema only in MySQL or MariaDB

Exporting database schema only (structure without rows) keeps migrations, reviews, and environment rebuilds fast while avoiding production data exposure. A schema-only dump is also useful for sharing DDL changes across environments without copying sensitive records.

In MySQL and MariaDB, mysqldump produces a plain SQL file containing object definitions such as CREATE TABLE and CREATE VIEW. Using --no-data omits table contents, and adding --routines plus --events extends the dump to stored procedures, functions, and scheduled events.

Schema-only exports still depend on privileges and object metadata that can vary by environment. Views, routines, and events often contain a DEFINER clause, which can fail on import when the referenced account does not exist, and a mismatch between client and server versions can trigger option-specific errors. Avoid placing passwords directly in shell history when automation is involved.

1. Export schema only for a database to an SQL file.

   $ mysqldump -u root -p --no-data --routines --events appdb > /root/sg-work/appdb-schema.sql
   Enter password:

   Use --defaults-extra-file for scheduled exports to avoid exposing credentials in shell history.

   Add -h and -P when connecting to a remote server, and add --column-statistics=0 when a MySQL 8 client dumps a MariaDB or older MySQL server.

2. Confirm the dump contains no row data statements.

   $ grep -cE '^(INSERT|REPLACE) INTO ' /root/sg-work/appdb-schema.sql
   0

   A non-zero count indicates table data is present in the file.

3. Create an empty database for the schema import target.

   $ mysql -u root -p
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 101
   Server version: 8.0.44 MySQL Community Server - GPL
   
   Copyright (c) 2000, 2025, Oracle and/or its affiliates.
   
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
   
   mysql> CREATE DATABASE appdb_empty;
   Query OK, 1 row affected (0.00 sec)
   
   mysql> exit
   Bye

4. Import the schema dump into the target database.

   $ mysql -u root -p appdb_empty < /root/sg-work/appdb-schema.sql
   Enter password:

   Importing into the wrong database name can create or replace objects in an unintended schema, and DEFINER clauses can fail with ERROR 1449 (HY000) when the referenced account does not exist.

5. Verify tables exist in the imported database.

   $ mysql -u root -p -D appdb_empty -e "SHOW TABLES;"
   Enter password:
   +-----------------------+
   | Tables_in_appdb_empty |
   +-----------------------+
   | audit_log             |
   | customers             |
   | order_items           |
   | orders                |
   | users                 |
   +-----------------------+

6. Verify views exist when the source database uses views.

   $ mysql -u root -p -D appdb_empty
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 108
   Server version: 8.0.44 MySQL Community Server - GPL
   ##### snipped #####
   mysql> SHOW FULL TABLES WHERE Table_type = 'VIEW';
   Empty set (0.00 sec)
   
   mysql> exit
   Bye

7. Verify stored routines exist when --routines was used.

   $ mysql -u root -p -D appdb_empty
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 109
   Server version: 8.0.44 MySQL Community Server - GPL
   ##### snipped #####
   mysql> SELECT ROUTINE_NAME, ROUTINE_TYPE
       ->   FROM information_schema.ROUTINES
       ->   WHERE ROUTINE_SCHEMA = 'appdb_empty'
       ->   ORDER BY ROUTINE_TYPE, ROUTINE_NAME
       ->   LIMIT 10;
   Empty set (0.00 sec)
   
   mysql> exit
   Bye

8. Verify scheduled events exist when --events was used.

   $ mysql -u root -p -D appdb_empty
   Enter password:
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 110
   Server version: 8.0.44 MySQL Community Server - GPL
   ##### snipped #####
   mysql> SELECT EVENT_NAME, STATUS, INTERVAL_VALUE, INTERVAL_FIELD
       ->   FROM information_schema.EVENTS
       ->   WHERE EVENT_SCHEMA = 'appdb_empty';
   Empty set (0.00 sec)
   
   mysql> exit
   Bye