Home » Database, MySQL10 October 2007

MySQL: Backup and Restore database & table

This article shows how to backup and restore single & multiple databases and tables in MySQL.

1) Backup single database

mysqldump -h hostname –u username –p password database_name > /path/backup.sql

2) Backup multiple databases

mysqldump -h hostname –u username –p password –-databases db1 db2 db3 > /path/threedb.sql

Here: db1, db2, db3 are three different database name.

3) Backup all databases

mysqldump -h hostname –u username –p password –-all-databases > /path/alldb.sql

4) Backup only specific tables from database

mysqldump -h hostname –u username –p password –databases database_name –table table_name1 table_name2 > /path/backup.sql

Here: table_name1 and table_name2 are dumped from database database_name.

5) Backup database as gzip compressed file (Linux)

mysqldump -h hostname –u username –p password database_name | gzip > /path/backup.sql.gz

6) Backup database as bzip2 compressed file (Linux)

mysqldump -h hostname –u username –p password database_name | bzip2 > /path/backup.sql.bz2

7) Restore database

mysql -h hostname –u username –p password db_name < /path/backup.sql

8) Restore database as gzip compressed file (Linux)

gunzip < /path/backup.sql.gz | mysql -h localhost -u username -p database_name

9) Restore database as bzip2 compressed file (Linux)

bunzip2 < /path/backup.sql.bz2 | mysql -h localhost -u username -p database_name

Hope it helps.
Thanks.

MySQL

Get New Post by Email

Find me on

FacebookTwitterGoogle+LinkedInRSS Feed