Leave a comment

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: