MySQL: Backup/Export and Restore/Import Database & Table

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

Table of Contents

1) Backup/Export single database

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

2) Backup/Export multiple databases

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

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

3) Backup/Export all databases

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

4) Backup/Export only specific tables from database

mysqldump -h hostname -u username -p --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/Export database as gzip compressed file (Linux)

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

6) Backup/Export database as bzip2 compressed file (Linux)

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

1) Restore/Import database

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

2) Restore/Import database as gzip compressed file (Linux)

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

3) Restore/Import database as bzip2 compressed file (Linux)

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

Hope it helps. Thanks.