Is it possible to have complete backup of mysql database server from command line?
Solution 1
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
Solution 2
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.
Solution 3
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
Sinan
some coding dude located in San Francisco but sometimes in DE, RU, UA, or TR.
Updated on September 17, 2022Comments
-
Sinan over 1 year
$ 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
Thanks.
-
Sinan almost 15 yearsthanks 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 almost 15 yearsbtw it says: mysql: unknown option '--all-databases'
-
Tommeh almost 15 yearsI've changed "mysql" to "mysqldump" for you, sorry about that! :)
-
Tommeh almost 15 yearsand yes, "| gzip -9f > file.gz" will pipe the SQL into a gzip'ed file.
-
Sinan almost 15 yearsok, works now... i didn't notice that you've changed it.
-
anonymous coward almost 15 yearsJust FYI, this could cause table locking or data syncronization issues with certain engines, so be sure you know when is appropriate to fire this off. = )
-
anonymous coward almost 15 yearsThis would have made an great Comment.
-
AWesley almost 15 yearsIf you want the backup to be consistent then you should be locking the whole database while running mysqldump. Also, i'd add in the force option aswell, it'll mean that if it hits an error during the dump that it'll continue on rather than just stopping.
-
Bruno Bronosky over 8 yearsDamn I was snarky back in 2009!
-
Mladen B. over 2 yearsQuicker guide: stackoverflow.com/questions/9293042/…