restore all mysql database from a --all-database sql.gz file

mysql backup mysqldump database-restore
ahhmarr picture ahhmarr · Apr 20, 2014 · Viewed 71.9k times · Source

I've backed all my mysql databases with he following command

mysqldump -u root -ppasswod --all-databases | gzip > all.sql.gz

just wanted to know will I be able to restore all of the database with following command

gunzip < alldb.sql.gz | mysql -u root -ppassword -h localhost

can you also tell me how to back up all of mysql users too?

I cant test it because I'm not sure and I don't want to break any db on my current system

Answer

mockinterface picture mockinterface · Apr 20, 2014

Yes. Generally, to restore compressed backup files you can do the following:

gunzip < alldb.sql.gz | mysql -u [uname] -p[pass] [dbname]

Please consult How to Back Up and Restore a MySQL Database

Note that the --all-databases option is applicable to backup only. The backup file itself will contain all the relevant CREATE DATABASE quux; commands for the restore.

Related questions

How to take complete backup of mysql database using mysqldump command line utility

How can I make a complete backup of mysql database using mysqldump? When I am making a backup, my tables from specified database are only getting backed up. The procedures and functions are not. Here's the backup command I am …

Read More
mysqldump with create database line

I'm in the process of moving my files onto another computer, and one thing I would like to do is to transfer the data in my MySQL database. I want to dump the databases into .sql files, but also have …

Read More
How to skip row when importing bad MySQL dump

Given bad mysqldump that causes error on import: namtar backups # mysql -p < 2010-12-01.sql Enter password: ERROR 1062 (23000) at line 8020: Duplicate entry 'l�he?' for key 'wrd_txt' Is there an easy way to tell import to just …

Read More