MySQL: Backup and Restore Database using mysqldump

In this post, I am sharing a script for performing Backup and restoring of MySQL Database using Mysqldump.

Mysqldump is an important tool for MySQL Server, and it creates a *.sql file with database structure and data. It also includes all possible table lock and objects existence script.

I am sharing different syntax and scripts for performing the Backup and Restore of a single database or multiple databases.

Syntax and Command to take the backup of Single Database:

1 2 3 4 5

-- Syntax mysqldump -u root -p[root_password] [database_name] > dumpMyFileName.sql   -- Example mysqldump -u root -pTestPass MyDatabase > MyDBBackup.sql

Syntax and Command for restoring of Single Database:
The new blank database requires being created before any restore operation.

1 2 3 4 5

-- Syntax mysqldump -u root -p[root_password] [database_name] < dumpMyFileName.sql   -- Example mysqldump -u root -pTestPass NewDatabase < MyDBBackup.sql

Command to take backup of multiple databases:

1	mysqldump -u root -pTestPass --databases MyDB1 MyDB2 > MultipleDB.sql

Command to take backup of all databases:

1	mysqldump -u root -pTestPass --all-databases > All.sql