Here’s how you back up a database from the command line. The first line is the backup, the second is the restore.
These commands work on both Windows and Linux. On Windows you might want to add the MySQL bin directory to your environment PATH — the easy way is Win + Break → Advanced System Settings → Environment → PATH. Then open cmd (the command prompt). If you already had a command prompt open before adding the PATH, restart it for the change to take effect.
MySQL doesn’t export stored procedures, functions, or triggers by default. If you need those, add –routines –triggers to your command. Even if you don’t currently use stored procedures, functions, or triggers, it’s safer to back up with those options enabled — they’re cheap, and you’ll thank yourself later if someone adds one.
1 2 | mysqldump -uusername -ppassword --routines --triggers --lock-tables=false database_name > YOURSQLFILE.sql mysql -uusername -ppassword database_name < YOURSQLFILE.sql |
If you need to back up all databases, the user we typically use is root. This will back up all triggers and functions. You’ll need to pre-create any other users with the appropriate privileges on stored procedures or functions for the restored copy to work end-to-end.
1 2 | mysqldump -uusername -ppassword --opt --events --all-databases > alldb.sql mysql -uusername -ppassword < alldb.sql |
A few useful additions.
The password-on-the-command-line problem. The -ppassword form (with no space — that’s important) is convenient but has two real issues: anyone running ps aux on the same machine can see your password while the command is running, and it gets stored in your shell history. Two cleaner options:
1 2 3 4 5 6 7 8 9 10 11 12 | # Option 1: prompt for the password interactively mysqldump -uusername -p database_name > out.sql # (you'll be asked: Enter password:) # Option 2: use an option file with restricted permissions cat > ~/.my.cnf << 'EOF' [client] user=username password=secret EOF chmod 600 ~/.my.cnf mysqldump --defaults-file=~/.my.cnf database_name > out.sql |
The ~/.my.cnf approach also helps for scheduled backups, where there’s no human around to type the password. Just make sure the file mode is 600 — MySQL clients refuse to load it otherwise on Linux.
For InnoDB tables: –single-transaction. If your tables are InnoDB (the default since MySQL 5.5), this flag wraps the dump in a transaction and gives you a consistent snapshot without locking the tables. Concurrent writes can keep happening while you back up:
1 2 | mysqldump --single-transaction --routines --triggers \ -uusername -p database_name > out.sql |
Don’t combine it with –lock-tables — they fight each other. –single-transaction only works for InnoDB; if you have MyISAM tables, use –lock-tables (the default for that case) and accept that writes block.
The MySQL 8 “Access denied PROCESS privilege” trap. Starting around MySQL 5.7+/8.0, mysqldump tries to query information_schema.tablespaces and fails for non-superusers with a confusing PROCESS-privilege error:
1 | mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces |
The fix is one flag — –no-tablespaces. Most application backups don’t actually need tablespace info anyway:
1 2 | mysqldump --no-tablespaces --single-transaction --routines --triggers \ -uusername -p database_name > out.sql |
Compress as you go. SQL dumps compress beautifully — typical 5-10x. For anything larger than a few hundred megabytes, pipe through gzip:
1 2 3 4 5 6 7 | # backup mysqldump --single-transaction --routines --triggers -uusername -p database_name | gzip > out.sql.gz # restore gunzip < out.sql.gz | mysql -uusername -p database_name # or in one shot zcat out.sql.gz | mysql -uusername -p database_name |
Restoring a single database from an –all-databases dump. Common scenario: you have a full-instance dump but only want to restore one schema. mysql has a –one-database flag that ignores statements not for the named database:
1 | mysql -uusername -p --one-database wantedschema < alldb.sql |
Caveat: –one-database filters by the most recent USE statement, not by table name, so it works cleanly when the dump uses USE schema; blocks (which mysqldump –all-databases emits by default). For surgical restores from arbitrary dumps, sed/awk through the file is sometimes cleaner.
For really big databases: mysql shell. If you’re operating on databases in the hundreds-of-GB range, the modern mysqlsh utility (ships with MySQL 8.0+) does parallel chunk-based dumps and is dramatically faster than mysqldump:
1 2 | mysqlsh -uusername -h hostname -- util dumpInstance /path/to/backup/dir --threads=4 mysqlsh -uusername -h hostname -- util loadDump /path/to/backup/dir --threads=4 |
It also handles compression natively, supports resumable loads, and writes a manifest you can inspect. For everyday small-to-medium dumps, mysqldump is still simpler and the recipes above are all you need; for anything where the backup window matters, it’s worth knowing mysqlsh exists.