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

Question

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 1

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.

Answer 2

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