Master MySQL Backups with mysqldump: Commands, Options, and Best Practices

MySQL Backup with mysqldump

Is mysqldump a text or binary backup?

It creates a text backup; opening the file shows all SQL statements used to recreate tables and objects, including INSERT statements for data.

What is the basic mysqldump syntax?

mysqldump -u [username] -p[password] --databases [dbname] [dbname2] > backupfile.sql

How to back up all databases?

mysqldump -u root -p --all-databases > backupfile.sql

How to back up specific databases?

mysqldump -u root -p --databases school hospital > backupfile.sql

How to back up specific tables?

mysqldump --user=root --password=mypassword -h localhost databasename table_name_to_dump table_name_to_dump_2 > dump_only_two_tables_file.sql

How to dump only the DDL (no data)?

mysqldump -u root -p --all-databases --no-data > backupfile.sql

How long does a mysqldump backup take?

It depends on database size; a 100 GB database may take two hours or more.

How to back up a remote database on another server?

mysqldump -h 172.16.25.126 -u root -ppass dbname > dbname.sql

What does the –routines option do?

It includes CREATE PROCEDURE and CREATE FUNCTION statements so stored procedures and functions are recreated.

How to list all mysqldump options? mysqldump --help Commonly used mysqldump options?

--all-databases
--databases
--routines
--single-transaction (does not lock tables, used with InnoDB)
--master-data (for replication, often omitted)
--no-data (dumps only schema)

Are triggers backed up by default? Yes. What does the –single-transaction option mean?

It avoids locking InnoDB tables during backup, allowing a consistent snapshot without table locks.

Typical command for a production mysqldump backup?

nohup mysqldump --socket=mysql.sock --user=user1 --password=pass \
  --single-transaction --flush-logs --master-data=2 \
  --all-databases --extended-insert --quick --routines \
  > market_dump.sql 2> market_dump.err &

How to compress a mysqldump backup? mysqldump [options] | gzip > backup.sql.gz Is mysqldump suitable for very large databases?

It depends on hardware (memory, disk speed). It works well for databases up to ~20 GB; backing up 200 GB is possible but slow because mysqldump is single‑threaded.

How to restore a mysqldump backup? mysql -u root -p < backup.sql How to log errors and measure restore time?

time mysql -u root -p < backup.sql > backup.out 2>&1

How to check if a restore is in progress?

Show the full process list (e.g., using SHOW PROCESSLIST).

What to do when the database is huge? Run the restore with nohup in the background. Can I back up on Windows and restore on Linux? Yes. How to transfer backup files to the target server?

scp

sftp

winscp

What happens if I restore a massive backup file?

The restore may take a long time; using nohup or screen to run it in the background is recommended.

Does mysqldump include DROP DATABASE by default? You need to add the --add-drop-database option. How to extract a single database from a multi‑database dump (e.g., database named test)?

sed -n '/^-- Current Database: `test`/,/^-- Current Database: `/p' fulldump.sql > test.sql