mysqldump: Usage, Options & Common CI Errors
mysqldump writes a MySQL/MariaDB database to a re-runnable SQL script.
mysqldump produces the SQL backups CI restores into a fresh database. The consistency trap is omitting --single-transaction, and the permissions trap is missing PROCESS/LOCK privileges.
What it does
mysqldump connects to a server and emits SQL statements (CREATE TABLE + INSERT) that recreate one or more databases. With --single-transaction it produces a consistent snapshot of InnoDB tables without locking writers.
Common usage
MYSQL_PWD=secret mysqldump -h 127.0.0.1 -u root app > app.sql
mysqldump -h 127.0.0.1 -u root -psecret --single-transaction app > app.sql
mysqldump -h db -u root -psecret --databases app1 app2 > multi.sql
mysqldump -h 127.0.0.1 -u root -psecret --no-data app > schema.sqlOptions
| Flag | What it does |
|---|---|
| --single-transaction | Consistent InnoDB dump without table locks |
| --databases <db...> | Dump named databases (adds USE/CREATE) |
| --all-databases | Dump every database |
| --no-data / -d | Schema only, no rows |
| --routines / --triggers | Include stored routines / triggers |
| --add-drop-table | Emit DROP TABLE before each CREATE |
Common errors in CI
mysqldump: Got error: 1045: Access denied for user - same -p-must-be-attached / MYSQL_PWD fix as mysql. "Access denied; you need (at least one of) the PROCESS privilege(s)" appears on MySQL 8 because dumping requires PROCESS; grant it or add --no-tablespaces. "Couldn't execute 'FLUSH TABLES WITH READ LOCK': Access denied" - use --single-transaction (InnoDB) so it does not need the global lock. Restore the result with mysql, not mysqldump.