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 > /path/to/backup/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 ' /path/to/backup/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:
   
   mysql> CREATE DATABASE appdb_empty;
   Query OK, 1 row affected (0.01 sec)
   
   mysql> exit
   Bye

4. Import the schema dump into the target database.

   $ mysql -u root -p appdb_empty < /path/to/backup/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 |
   +--------------------+
   | migrations         |
   | orders             |
   | users              |
   +--------------------+

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

   $ mysql -u root -p -D appdb_empty -e "SHOW FULL TABLES WHERE Table_type = 'VIEW';"
   Enter password:
   +-----------------------+------------+
   | Tables_in_appdb_empty | Table_type |
   +-----------------------+------------+
   | v_active_users        | VIEW       |
   +-----------------------+------------+

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

   $ mysql -u root -p -D appdb_empty -e "SELECT ROUTINE_NAME, ROUTINE_TYPE FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = 'appdb_empty' ORDER BY ROUTINE_TYPE, ROUTINE_NAME LIMIT 10;"
   Enter password:
   +---------------------+--------------+
   | ROUTINE_NAME        | ROUTINE_TYPE |
   +---------------------+--------------+
   | fn_normalize_email  | FUNCTION     |
   | sp_recalculate_totals | PROCEDURE  |
   +---------------------+--------------+

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

   $ mysql -u root -p -D appdb_empty -e "SELECT EVENT_NAME, STATUS, INTERVAL_VALUE, INTERVAL_FIELD FROM information_schema.EVENTS WHERE EVENT_SCHEMA = 'appdb_empty';"
   Enter password:
   +-------------------+---------+----------------+----------------+
   | EVENT_NAME        | STATUS  | INTERVAL_VALUE | INTERVAL_FIELD |
   +-------------------+---------+----------------+----------------+
   | ev_cleanup_tokens | ENABLED | 1              | DAY            |
   +-------------------+---------+----------------+----------------+