mysqldump Command Generator

mysqldump's defaults are full of quiet surprises: stored routines are not dumped unless you ask, --single-transaction is only consistent for InnoDB, and an --all-databases dump will happily try to overwrite the mysql system schema on restore. Describe the backup you want and this generator writes the mysqldump command, the matching mysql restore command, and a warning whenever options contradict each other. It runs entirely in your browser and never connects to your database.

HostPortUserScope

DatabaseOnly tablesIgnore tables

ContentsOutput filegzipRestore into

ℹ --single-transaction gives a consistent snapshot without locking tables, but only for InnoDB. MyISAM tables are still copied as they are at the moment each one is read, so mixed-engine databases can come out inconsistent.

ℹ Dumping a bare database name does not emit CREATE DATABASE / USE statements. Add the target database to the restore command (as generated below), or use the multiple-databases scope to embed it in the dump.

Backup command

# -p with no value makes mysql prompt for the password interactively.
# Never write -pMyPassword on the command line — it leaks into ps output and shell history.
# For scripts, use a login path instead:
#   mysql_config_editor set --login-path=backup --host=localhost --user=root --password
# then replace the -h/-u/-p flags with --login-path=backup.

mysqldump -h localhost -P 3306 -u root -p \
  --single-transaction --quick --routines --add-drop-table --hex-blob \
  appdb \
  | gzip > appdb.sql.gz

Restore command

# The dump has no CREATE DATABASE statement, so create the target first:
mysql -h localhost -P 3306 -u root -p -e "CREATE DATABASE IF NOT EXISTS \`appdb_restored\`"

gunzip < appdb.sql.gz | mysql -h localhost -P 3306 -u root -p appdb_restored

Verify the restore

-- After the restore, verify instead of trusting exit codes.
-- Compare table counts and row estimates between source and restored copies:
SELECT table_name, table_rows
FROM information_schema.tables
WHERE table_schema = 'appdb_restored'
ORDER BY table_rows DESC
LIMIT 20;

-- table_rows is an estimate for InnoDB; spot-check important tables exactly:
-- SELECT COUNT(*) FROM some_table;

Do more than mysqldump command generator — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Enter the connection details, then choose the scope — one database, specific tables, several databases, or the whole server.
Pick schema/data contents and toggle options like --single-transaction, --routines and gzip compression; the command updates live with warnings for conflicting choices.
Copy the mysqldump command, and keep the generated restore command and verification SQL for when you need to bring the backup back.

Frequently asked questions

Does mysqldump lock my tables during the backup?

With --single-transaction (the default in this generator), no: the dump runs inside a REPEATABLE READ transaction and reads a consistent InnoDB snapshot while writes continue normally. Without it, mysqldump uses LOCK TABLES per database and blocks writes for the duration. The transaction trick only works for InnoDB — MyISAM tables have no MVCC, so they are read as-is and can be mutually inconsistent. Avoid running DDL (ALTER/DROP) during the dump either way, as it breaks the snapshot.

Why are my stored procedures missing after restoring a mysqldump file?

Because mysqldump does not include stored procedures or functions by default — you must pass --routines, and --events for scheduled events. Triggers are the one object type included by default. This generator keeps --routines on precisely because silently losing procedures is one of the most common mysqldump mistakes. Views are included as part of the table definitions.

How do I restore a mysqldump backup into a new database?

A single-database dump (mysqldump mydb) contains no CREATE DATABASE statement, so create the target first with CREATE DATABASE and then pipe the file in: mysql -u root -p newdb < dump.sql. Dumps made with --databases or --all-databases embed CREATE DATABASE and USE statements, so you restore them without naming a database — which also means they always restore into the original database names. For compressed dumps, stream them with gunzip < dump.sql.gz | mysql ... instead of unpacking to disk first.