How to export database schema only in MySQL or MariaDB

A schema-only SQL dump is useful when a database definition needs to be reviewed, versioned, or recreated without copying live rows. It keeps migrations and rebuilds smaller while avoiding accidental exposure of application data.

In MySQL and MariaDB, a logical schema export comes from mysqldump or mariadb-dump. The --no-data option writes table and view definitions without row inserts, triggers stay with their table definitions, and --routines plus --events extend the file to stored procedures, functions, and scheduled events.

The dump still preserves metadata such as DEFINER clauses, character-set settings, and other object attributes that can matter during import. Current MariaDB releases prefer mariadb-dump, and MariaDB documents that its dump files can start with a sandbox-mode command that older mariadb or MySQL mysql clients do not understand, so use a current matching client when possible and keep concurrent schema changes out of the source database while the export runs.

1. Create a directory to hold the schema dump.

   $ mkdir -p /root/sg-work/backups/mysql

2. Export the database structure to an SQL file.

   $ mysqldump --user=root --password --no-data --routines --events --triggers appdb > /root/sg-work/backups/mysql/appdb-schema.sql
   Enter password:

   On current MariaDB hosts, use mariadb-dump when mysqldump is not installed; MariaDB documents that the mysqldump symlink is deprecated and removed from the MariaDB Docker Official Image from MariaDB 11.0.

   Avoid --password=secret or -psecret so credentials do not leak into shell history or process listings.

3. Confirm the dump contains no row inserts.

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

   A non-zero count means the file is not structure-only.

4. Preview the object definitions captured in the dump.

   $ grep -nE '^(CREATE TABLE|CREATE DEFINER=|/\*!50001 CREATE|/\*!50003 CREATE\*/|/\*!50106 CREATE\*/)' /root/sg-work/backups/mysql/appdb-schema.sql | head -n 6
   26:/*!50001 CREATE VIEW `active_customers` AS SELECT
   38:CREATE TABLE `audit_log` (
   54:CREATE TABLE `customers` (
   71:/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `customers_ai` AFTER INSERT ON `customers` FOR EACH ROW BEGIN
   96:/*!50106 CREATE*/ /*!50117 DEFINER=`root`@`localhost`*/ /*!50106 EVENT `purge_audit_log` ON SCHEDULE EVERY 1 DAY STARTS '2026-04-09 15:33:32' ON COMPLETION NOT PRESERVE ENABLE DO DELETE FROM audit_log WHERE created_at < NOW() - INTERVAL 30 DAY */ ;;
   118:CREATE DEFINER=`root`@`localhost` PROCEDURE `sync_customer_stats`()

   Line formats vary slightly between MySQL and MariaDB, but table, view, trigger, routine, and event definitions should all be visible when they exist in the source schema.

5. Create an empty database for a validation import.

   $ mysql --user=root --password -e "CREATE DATABASE appdb_schema;"
   Enter password:

   If the source database uses a non-default character set or collation, create the target with matching values before the import.

6. Import the schema dump into the empty database.

   $ mysql --user=root --password appdb_schema < /root/sg-work/backups/mysql/appdb-schema.sql
   Enter password:

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

   If the file was created with current mariadb-dump, load it with the matching mariadb client because the sandbox-mode header can fail in older MariaDB or MySQL mysql clients.

7. Verify tables and views exist in the imported schema.

   $ mysql --user=root --password -D appdb_schema -e "SHOW FULL TABLES;"
   Enter password:
   +------------------------+------------+
   | Tables_in_appdb_schema | Table_type |
   +------------------------+------------+
   | active_customers       | VIEW       |
   | audit_log              | BASE TABLE |
   | customers              | BASE TABLE |
   +------------------------+------------+

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

   $ mysql --user=root --password -D appdb_schema -e "SELECT ROUTINE_NAME, ROUTINE_TYPE FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA='appdb_schema';"
   Enter password:
   +---------------------+--------------+
   | ROUTINE_NAME        | ROUTINE_TYPE |
   +---------------------+--------------+
   | sync_customer_stats | PROCEDURE    |
   +---------------------+--------------+

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

   $ mysql --user=root --password -D appdb_schema -e "SELECT EVENT_NAME, STATUS FROM information_schema.EVENTS WHERE EVENT_SCHEMA='appdb_schema';"
   Enter password:
   +-----------------+---------+
   | EVENT_NAME      | STATUS  |
   +-----------------+---------+
   | purge_audit_log | ENABLED |
   +-----------------+---------+

10. Verify triggers were recreated with the table definitions.

    $ mysql --user=root --password -D appdb_schema -e "SHOW TRIGGERS\\G"
    Enter password:
    *************************** 1. row ***************************
                 Trigger: customers_ai
                   Event: INSERT
                   Table: customers
                  Timing: AFTER
                 Definer: root@localhost

    mysqldump enables triggers by default, but keeping --triggers in the export command makes the schema-only intent explicit and keeps the command aligned with mariadb-dump usage.