Backup Using mysqldump and Restore MySQL Database

MySQL mysqldump command is a very effective tool to backup MySQL database. It create a *.sql text file containing DROP table, CREATE table and INSERT INTO sql statements. And then this sql text file can be used to restore the database on different location or different server.

With mysqldump, you can backup a local database and restore it on a remote database using a single command. Here are the quick syntax how to backup and restore MySQL database using mysqldump command:

To backup syntax:
mysqldump -u[user] -p[password] [database_name] > [backup_file]

To restore syntax:
mysql -u[user] -p[password] [database_name] < [backup_file]

How to Backup MySQL Database Using mysqldump

Here are the examples to backup a single database, a specific table from a database, multiple databases and all databases into an sql text file, i.e. *.sql file. Note that, this *.sql file is actually a text file that can be opened and read using Notepad or Notepad++ code friendly editor.

Backup a single database example:
mysqldump -udbuser1 -pdbpass1 online_shopping_db > online_shopping_db.sql

mysqldump -udbuser1 -p online_shopping_db > online_shopping_db.sql
Enter password: ******

Backup a specific table from a database example:
mysqldump -udbuser1 -prootpass online_shopping_db customer_table > customer.sql

Backup multiple databases example:
mysqldump -udbuser1 -prootpass --databases sugarcrm_db wordpress_db > some_db.sql

Backup all databases within a MySQL server instance example:
mysqldump -uroot -prootpass --all-databases > all_db.sql

Restoring MySQL Database From An SQL Text File

Supposely, we have sql text file for example online_shopping_db.sql and then we want to load it back into a database, we can use mysql command to restore the database. Below are the examples:

mysql -udbuser1 -pdbpass1 online_shopping_db < online_shopping_db.sql

mysql -udbuser1 -p online_shopping_db < online_shopping_db.sql
Enter password: ******

If you are unable to run above commands from the shell, then you need to change directory to mysql bin folder, because these commands are located in the bin folder. In Windows you can run the following command to get there:
C:\>cd C:\Program Files\mysql\bin