Mysqldump Export Syntax Notes (MySQL / MariaDB 5.5)

This article summarizes practical usages and command syntax for mysqldump in MySQL / MariaDB 5.5 environments.
It covers common export scenarios, important options, and best practices — ideal as a quick reference for backup and restoration tasks.

1. What Is `mysqldump`?

mysqldump is a built-in MySQL/MariaDB utility used to export:

Table structures (CREATE TABLE)
Table data (INSERT INTO)
Stored procedures & functions
Triggers
Events

The exported result is a plain SQL file that can be imported back using mysql or mariadb commands.

2. Basic Export Syntax

Export a Single Database

mysqldump -u user -p --host=127.0.0.1 --port=3306 \
  --databases dbname > dbname_$(date +%F).sql

Export All Databases

mysqldump -u user -p --all-databases > all_$(date +%F).sql

Export Specific Tables

mysqldump -u user -p dbname table1 table2 > tables_$(date +%F).sql

3. Transaction Consistency and Locking Strategies

Option	Engine	Description
`--single-transaction`	InnoDB	Takes a consistent snapshot without locking tables
`--lock-tables`	MyISAM	Locks tables to ensure data consistency

InnoDB Example

mysqldump -u user -p dbname --single-transaction --quick > dump.sql

MyISAM Example

mysqldump -u user -p dbname --lock-tables > dump.sql

4. Recommended Common Options

mysqldump -u user -p dbname \
  --single-transaction \
  --quick \
  --routines \
  --events \
  --triggers \
  --default-character-set=utf8 \
  --hex-blob \
  | gzip > dbname_$(date +%F).sql.gz

Option	Description
`--routines`	Export stored procedures and functions
`--events`	Export scheduled events
`--triggers`	Export triggers (enabled by default, but safe to include)
`--hex-blob`	Dump BLOB/TEXT fields in hex to avoid encoding issues
`--quick`	Stream results row-by-row to save memory
`--default-character-set=utf8`	Specify output encoding
`gzip`	Compress output on the fly

5. Export Only Schema or Only Data

Schema Only (No Data)

mysqldump -u user -p dbname --no-data > schema.sql

Data Only (No Schema)

mysqldump -u user -p dbname --no-create-info > data.sql

6. Conditional Exports (Using WHERE Clause)

Export only part of the data:

mysqldump -u user -p dbname orders \
  --where="order_date >= '2025-01-01'" > orders_2025.sql

7. Exporting Users and Privileges

Since MySQL 5.5 / MariaDB 5.5 doesn’t include mysqlpump,
you can dump the mysql system database directly:

mysqldump -u root -p --databases mysql > mysql_priv_$(date +%F).sql

⚠️ Note: System table structures may differ between versions.
Alternatively, you can export user privileges safely using:

mysql -u root -p -e "SHOW GRANTS FOR 'user'@'host';"

8. Export with Compression (gzip)

To save disk space, compress while exporting:

mysqldump -u user -p dbname | gzip > dbname_$(date +%F).sql.gz

To restore:

gunzip -c dbname_2025-09-16.sql.gz | mysql -u user -p dbname

9. Common Issues and Recommendations

Issue	Cause / Solution
Slow export	Use `--quick` to stream rows
Garbled characters	Add `--default-character-set=utf8`
Table locked during dump	Use `--single-transaction` (InnoDB) or `--lock-tables` (MyISAM)
BLOB data corrupted	Add `--hex-blob`
High CPU usage	Use `--quick` and avoid large parallel queries

10. Restore Command Reference

Action	Command
Import `.sql`	`mysql -u user -p dbname < dump.sql`
Import `.sql.gz`	`gunzip -c dump.sql.gz

11. Example for Windows

mysqldump.exe -u user -p dbname --single-transaction --quick ^
  --routines --events --triggers --default-character-set=utf8 ^
  > C:\backup\dbname_%DATE:~0,10%.sql

12. Practical Suggestions

Automate daily backups using cron (Linux) or Task Scheduler (Windows).
Backup multiple databases with --databases option.
Ensure version compatibility when restoring across different MySQL/MariaDB versions.
Regularly test restoration in a staging environment to validate your backups.

13. Complete Example (Compressed + Includes Routines and Events)

mysqldump -u backupuser -p'abcd123' \
  --single-transaction --quick \
  --routines --events --triggers \
  --default-character-set=utf8 --hex-blob \
  --databases nuface \
  | gzip > /backup/nuface_$(date +%F).sql.gz

14. Conclusion

mysqldump remains a simple yet powerful backup tool —
especially for small to medium-sized databases where a full logical dump is sufficient.

📌 Recommended base command:

mysqldump --single-transaction --quick --routines --events --triggers --default-character-set=utf8

This single line covers most reliable and production-safe backup needs.

📎 Related Reading

1. What Is mysqldump?