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

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

4 Answers

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.

This is the command I use to backup all databases in MySQL:

mysqldump -u USERNAME -p --all-databases --events --ignore-table=mysql.event --extended-insert --add-drop-database --disable-keys --flush-privileges --quick --routines --triggers | gzip > "all_databases.gz"

• The '--all-databases' option tells the command to include all of the databases. If you want to specify one or more then remove that option and replace it with '--databases dbname1 dbname2 dbnameX'
• To backup all of your mysql users, passwords, permissions then include the 'mysql' database in your backup. The --all-databases option includes this database in the backup.
• The '--routines' option includes stored procedures and functions in the backup.
• The '--triggers' option includes any triggers in the backup.

To restore from a *.gz mysqldump file:

gunzip < all_databases.gz | mysql -u USERNAME -p

To display a progress bar while importing a sql.gz file, download pv and use the following:

pv mydump.sql.gz | gunzip | mysql -u root -p

If PV command is not installed on your system then try below command relatively

In CentOS/RHEL

yum install pv

In Debian/Ubuntu

apt-get install pv

In MAC

brew install pv

Output Something like that -->

pv mydump.sql.gz | gunzip | mysql -u root -p dbname

Enter password:

255MiB 0:05:49 [ 748kiB/s] [===========> ] 30%

/applications/MAMP/library/bin/mysql -u root -p < all_dbs2.sql