Back Up and Restore a MySQL Database using mysqldump

mysqldump is used to dump a database or a collection of databases for backup or transfer to other SQL server.

The dump contains SQL statements to create, insert and drop table.

using mysqldump we can generate files in CSV, other delimited text, or XML format.

 

Syntax for backup and restore MySQL database using mysqldump:

Backup database:
mysqldump -u [user_name] -p[password] [db_name] > dumpfilename.sql

Restore database:
mysql -u [user_name] -p[password] [db_name] < dumpfilename.sql

Backup a specific table:
mysql -u [user_name] -p[password] [db_name] [table_name] > dumpfilename.sql

[user_name] database username
[password] database password
[db_name] database name
[dumpfilename.sql] file name of database backup
[table_name] table name of a database

The dumpfilename.sql will contain drop, create and insert table command for all the tables in the database.

 

Backup a single database:

This example takes a backup of company database and dumps the output to company.sql

# mysqldump -u root -p company > company.sql

 

Backup multiple databases:

This example takes a backup of company and sector database and dumps the output to company_sector.sql

# mysqldump -u root -p company sector > company_sector.sql

 

Backup all the databases:

This example takes a backup of all the database and dumps the output to all_database.sql

# mysqldump -u root -p -all-databases > all-database.sql

 

Backup a specific table in a database.

This example takes a backup of employee table of company database and dumps the output to company_employee_table.sql

# mysqldump -u root -p company employee > company_employee_table.sql

 

Restore a database:

This example restore company database and be sure to create the company database before you can perform the restore.

# mysqldump -u root -p company < company.sql

2 thoughts on “Back Up and Restore a MySQL Database using mysqldump

  1. I am regular visitor, how are you everybody? This article posted at this website is actually fastidious.

  2. Every once in a whilst we pick out blogs that we read. Listed beneath are the newest websites that we pick out.

Comments are closed.