Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
$ mysqldump -h localhost -u username -p database_name > backup_db.sql

I can use the line above but it is just for one db in the server, can i have a complete backup of the all databases into one backup file?

Or is there some command which serializes this process?

something like below (i added -all which is most probably wrong):

$ mysqldump -u username -h localhost -p -all | gzip -9 > backup_db.sql.gz


share|improve this question
up vote 10 down vote accepted

Use the --all-databases option, it causes all tables in all databases to be dumped:

mysqldump -u username -h localhost -p --all-databases > all_databases.sql
share|improve this answer
thanks for the answer. mysql -u username -h localhost -p --all-databases | gzip -9 > all_databases.sql.gz would zip the output file then right? – Sinan Jun 29 '09 at 13:20
btw it says: mysql: unknown option '--all-databases' – Sinan Jun 29 '09 at 13:24
I've changed "mysql" to "mysqldump" for you, sorry about that! :) – Tommeh Jun 29 '09 at 13:26
and yes, "| gzip -9f > file.gz" will pipe the SQL into a gzip'ed file. – Tommeh Jun 29 '09 at 13:27
ok, works now... i didn't notice that you've changed it. – Sinan Jun 29 '09 at 13:29

In addition, I would suggest setting up a my.cnf file so that your password is not visible in the process list. Check this out. It will help you and prevent you from having your SQL DB hacked by anyone else that might have access to your system.

share|improve this answer
This would have made an great Comment. – anonymous coward Jun 29 '09 at 14:31

I would suggest LVM snapshots for backups of "all databases" as the lock time is likely to be high. However, the displayed lack of investigating the usage string or "man mysql<^M>/all" makes me feel it may be irresponsible to make such a suggestion.

~$ mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help
share|improve this answer
Damn I was snarky back in 2009! – Bruno Bronosky Sep 24 '15 at 3:15

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.