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

Solution 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.

Solution 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

Solution 3

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%

Comments

[ahhmarr]

nothing is mentioned about restoring databases from an all_db.sql.gz file

[ahhmarr]

haha I know that. what I meant was that they havent specified how to restore from an --all-databse backup

[mockinterface]

Your --all-database output file should contain the necessary CREATE DATABASE foo; commands for the backed up dbs, so when you restore from it, all the databases will be restored. You can open the all.sql file and grep for these commands to make sure all the dbs are mentioned.

[ahhmarr]

yea it's there I just wanted to confirm the way in which I should restore after a fresh MySQL installation what is the command that can be used for importing DB all at once from my alldb file

[John Laniba]

It seems that I'm stuck in these problem as well. I've backup more than 50 databases using --all-databases dump. And now i need to manually identify those databases instead of 1 import command.

[Nam G VU]

Well running your command get me error 'ERROR 1049 (42000): Unknown database 'MY_DB_NAME'. Do we have to create the database first?

[Nam G VU]

And when dropping the database name, it raise 'ERROR 1046 (3D000) at line 22: No database selected'. I guess my backup script don't create the database first.

[Kanuj Bhatnagar]

If you're referring to an already posted answer, please include the link to the original. Answer copied from: serverfault.com/a/445130/275858

[Ravi Tyagi]

Okay but i have not copied anything from anywhere if you think so you can take help from there

[MestreLion]

pv is a truly incredible gem