How to Make a mysqldump File Smaller

Sometimes the best fix for an oversized dump is to make it not oversized. A few export choices can cut a dump by 70–90% before it ever touches an import limit.

1. Compress it (the biggest single win)

mysqldump database_name | gzip > dump.sql.gz

SQL is extremely compressible — text, repeated keywords, repeated structure. gzip routinely shrinks a dump to 10–20% of its original size. Most import tools (CLI, Cloud SQL, even SQLSplit) read .gz directly.

2. Skip the data you don't need

Cache, session, and log tables are often the bulk of a database and rarely worth migrating. Exclude their data while keeping their structure:

mysqldump database_name \
  --ignore-table=database_name.wp_actionscheduler_logs \
  --ignore-table=database_name.sessions \
  > dump.sql

# Or dump structure-only for heavy tables, data for the rest:
mysqldump --no-data database_name big_log_table > schema_only.sql

3. Use extended inserts (smaller and faster)

Extended inserts pack many rows per statement, which shrinks the file and speeds up import. It's on by default, but make sure it isn't disabled:

mysqldump --extended-insert --quick database_name > dump.sql

4. Drop binary log / GTID noise

mysqldump --skip-comments --set-gtid-purged=OFF \
  --no-tablespaces database_name > dump.sql

• --skip-comments removes the verbose header/footer comments
• --set-gtid-purged=OFF avoids GTID statements that also cause import privilege errors
• --no-tablespaces avoids PROCESS-privilege warnings on managed hosts

5. Separate schema from data

Two smaller files are easier to handle than one big one — and you can import schema once, then load data in pieces:

mysqldump --no-data db > schema.sql
mysqldump --no-create-info db > data.sql