Export only the schema (structure) from an SQL dump

Three situations keep coming up:

• Stand up a schema for staging or CI — you want the empty table structure so tests can run against real columns, indexes and constraints, but you absolutely do not want to ship gigabytes of production rows into a throwaway environment.
• Seed data only — the schema already exists in the target database, and you just need the INSERT rows to populate it, without CREATE statements that would collide with what's there.
• Get rid of noise — the dump is bloated by logs, sessions, audit_trail or cache tables with tens of millions of rows you never want to import.

Why mysqldump --no-data isn't enough

The classic advice is mysqldump --no-data (structure only) or mysqldump --no-create-info (data only). Both are export flags: they change what a fresh dump from a running database contains. They do nothing for a dump you already have sitting on disk as a .sql file.

In practice the file often arrives from someone else, or from a backup job, or from a server you no longer have live access to. You can't re-run mysqldump against a database that isn't reachable — the credentials are gone, the server is decommissioned, or the file is simply all you were handed. At that point the flags are useless, and you're left editing the file itself.

The manual way

The obvious move is to strip lines with grep or sed. To keep structure and drop the data:

# keep everything except INSERT rows
grep -v '^INSERT INTO' dump.sql > schema.sql

# or keep only the INSERT rows for a data-only file
grep '^INSERT INTO' dump.sql > data.sql

This looks fine on a small, tidy dump — and then breaks on a real one:

• Multi-line INSERTs. A single INSERT INTO … VALUES can span thousands of lines. grep matches the first line and leaves the rest of the values orphaned, producing a broken file.
• Extended inserts. mysqldump batches many rows into one giant statement. Line-based filters can't tell where such a statement ends.
• Comments and blocks. Conditional /*! … */ blocks, LOCK TABLES, and -- comments interleave with the data, so a naive filter either keeps junk or cuts something the import needs.
• Data-only is worse. A pure grep '^INSERT' throws away the SET and LOCK statements the rows depend on, and any INSERT that doesn't start exactly at column zero silently disappears.

You end up debugging your own filter instead of getting your schema.

Structure-only or data-only with DumpCleaner

DumpCleaner understands SQL statements instead of matching lines, so it splits structure from data reliably — on files of any size:

1. Drag your .sql dump into DumpCleaner (MySQL, MariaDB or PostgreSQL). It parses the file by streaming, so a multi-gigabyte dump loads with constant memory.
2. Toggle the statement types you want: CREATE TABLE, CREATE INDEX, INSERT INTO, ALTER, DROP, LOCK, SET. Uncheck INSERT INTO and you get structure only; uncheck CREATE TABLE / CREATE INDEX and you get data only.
3. Or pick a preset — Structure only or Data only — to set every toggle in one step, then refine per table if you like.
4. Export. You get a lean schema file for staging or CI, or a clean data-only dump for seeding.

Because the filter is statement-aware, a multi-line extended INSERT is kept or dropped as a whole — never cut in the middle.

The real power is that filtering works per statement type and per table, and the two combine. You can keep the structure of every table but include INSERT data only for the three tables you actually need — leaving the 89-million-row logs table as an empty shell. That's something no --no-data flag can express.

Frequently asked questions