mysqldump flags that save you

The mysqldump flags that usually save you are --single-transaction, --quick, and piping the output through gzip. Together they give you a consistent InnoDB dump without locking the storefront, stream rows instead of loading whole tables into memory, and keep the file size manageable. Before you restore anything to production, open the dump command that created the file and check whether it includes routines, a DROP DATABASE statement, or a half-consistent snapshot.

A dump command that does not lock checkout

mysqldump –single-transaction –quick -u user -p dbname | gzip > ~/db.sql.gz

--single-transaction starts one consistent read and dumps against that snapshot, so InnoDB tables stay writable while the dump runs. MyISAM tables still lock for the duration, which is one more reason to avoid MyISAM when you can. --quick pulls rows one at a time instead of buffering each full table in the client. Gzip on the pipe reduces disk use without a second pass over the file.

Add --routines when you need stored procedures or functions; the default dump skips them, and that gap often shows up a week later. Prefer a night dump if you cannot use a snapshot and the database takes heavy writes during the day. Dumping a busy InnoDB database without --single-transaction is a common way to lock checkout at noon.

Flags that can cause real damage

--add-drop-database writes a DROP DATABASE into the dump file. Restoring that file against the wrong schema name can wipe the wrong database. Skip it unless you truly intend to drop and recreate the database on restore. --complete-insert is more verbose and can be safer across mixed schemas; it is optional, not required.

A GUI export in phpMyAdmin is the same kind of dump until it times out around large files. You can end up with a truncated file that appears to restore cleanly while tables are missing. Use SSH for anything large so the dump can finish without a browser timeout.

Character set, locks, and what to test

Add --default-character-set=utf8mb4 when the schema uses utf8mb4 so the client does not recode data on the way out. Skip --lock-all-tables on InnoDB; that flag works against the point of --single-transaction. Test the restore onto a spare schema the same day you take the first dump, not the day you need the backup.

mysqldump is still a portable backup format you can move between hosts. Server snapshots and panel backup tools are often faster, but they are not always a plain file you can take elsewhere. If the next question is which query is slow, see Performance Schema, used lightly.