MySQL Different command for Backup and Restore Databases

Backup Database

Making backup all databases:

mysqldump -u usuario -p –all-databases > dump.sql

Making backup only one database:

mysqldump -u usuario -p –databases db1 > dump.sql

Making backup many databases:

mysqldump -u usuario -p –databases db1 db2 db… > dump.sql

Making backup using gzip compresson

mysqldump -u usuario -p –all-databases | gzip > dump.sql.gz

Making backup using bzip2 compresson

mysqldump -u usuario -p –all-databases | bzip2 > dump.sql.bz2

Restore Database

Normal

mysql -u usuario -p < dump.sql

gzip:

gunzip < dump.sql.gz | mysql -u usuario -p

bzip2

bunzip2 < dump.sql.bz2 | mysql -u usuario -p

While dumping mysql database use –single-transaction option so that database table not get locked while dumping process

and command for dumping mysql file using gzip compression

mysqldump -uroot -p'password' databasename | gzip new_db_name.sql.gz