mysqldump: Back Up a MySQL Database
mysqldump --single-transaction appdb > dump.sql exports a database to SQL without locking tables on InnoDB.
mysqldump produces a logical SQL backup. On InnoDB, --single-transaction gives a consistent snapshot without blocking writers, which is why it is the default choice in CI over a plain dump that locks tables.
What it does
mysqldump connects to a server and writes CREATE and INSERT statements that recreate the schema and data. It restores by replaying the output through the mysql client. --single-transaction wraps the read in one transaction for a consistent InnoDB snapshot, avoiding the table locks a plain dump takes.
Common usage
# consistent InnoDB backup (no locking)
MYSQL_PWD=secret mysqldump -h db -u app --single-transaction appdb > dump.sql
# schema only, or all databases
mysqldump -h db -u app --no-data appdb > schema.sql
mysqldump -h db -u root --all-databases > all.sqlOptions
| Flag | What it does |
|---|---|
| --single-transaction | Consistent snapshot on InnoDB without locking |
| --no-data / -d | Dump structure only, no rows |
| --databases <db>... | Dump named databases with CREATE DATABASE |
| --all-databases | Dump every database on the server |
| --routines | Include stored procedures and functions |
| --single-transaction --quick | Stream large tables row by row |
In CI
Use --single-transaction for InnoDB so the dump does not block the application while it runs. Dump as a user with SELECT on every table (and PROCESS if you use --single-transaction across many tables), or the dump aborts partway. Redirect stdout to a file; mysqldump writes SQL to stdout, not a -f flag.
Common errors in CI
"mysqldump: Got error: 1044: Access denied for user \"app\"@\"...\" to database \"appdb\" when using LOCK TABLES" means the user lacks LOCK TABLES; add --single-transaction (which avoids it) or grant the privilege. "Access denied; you need (at least one of) the PROCESS privilege(s)" appears on newer MySQL with --single-transaction; grant PROCESS or add --no-tablespaces. "Unknown database" means a typo in the database name.