Backup and Restore MySQL Database using Command Prompt

In this article i have covered two modules Backup with mysqldump command and Restore with mysql command. Database concepts are considered as dry subject. I promised to make backup and recovery subject as interesting as possible it is very useful whenever developer or any user accidently drop database table and don’t have any backup, they want to audit database which existed quite a while ago. Sometime they have a backup and they don’t know how to restore it and something bad happens like disaster and they do not have data or any database backup. In addition to that human errors are going to be there always. So that am going to tell how to take efficient backup and recovery your database by restoring it to its original state.

Backup with mysqldump command.

 

Introduction to mysqldump: Mysqldump is very powerfull command and very easy to use. By using this we can perform backup operations.

Backup Single Database: Now im going to take a backup of single database for that open the MySQL command line client and check the database names by using typing the command show databases.

Open Run-> cmd click ok.
Cd C:\Program Files\MySQL\MySQL Server 5.5\bin mysqldump –h 127.0.0.1 –u [uname] –p patientDB> c:\backup\singleDbBackup.sql
After typing the command press enter it asks for Enter Password type the mysql password.
-h means host name of the system
-u username of the MySQL
-p password of the MySQL

Backup multiple Databases: Now let us see how we can take backup of multiple database.
C:\Program Files\MySQL\MySQL Server 5.5\bin mysqldump –h 127.0.0.1 –u [uname] –p --database patientDB hospitalDB> c:\backup\twoDbBackup.sql
Here we used –database option which is very popular in industry.It will be deprecated eventually. So many users are suggested to use –database option in future version.
Note: Before taking backup of multiple database specify the two database names after --database keyword. If you forget to specify --database keyword it treats first name as database name and second name as table name so be carefull.

Backup All Databases: Now we can learn how to take backup of all the databases.
C:\Program Files\MySQL\MySQL Server 5.5\bin mysqldump –h 127.0.0.1 –u [uname] –p --all-database> c:\backup\allDbBackup.sql
By using –all-database command we can directly backup all the databases

Backup Single Table: If you want to backup single table from particular database use the below command
C:\Program Files\MySQL\MySQL Server 5.5\bin mysqldump –h 127.0.0.1 –u [uname] –p --database patientDB –tables appointment> c:\backup\singleTable.sql
However if you want to take backup of multiple table we can specify other names of the table right after rental.

Backup Stored Routines:
C:\Program Files\MySQL\MySQL Server 5.5\bin mysqldump –h 127.0.0.1 –u [uname] –p --database  --routines --no-create-info --no-data --no-create-db --skip-opt patientDB> c:\backup\sr.sql

--no-create-info: will not create any table definition
--no-data: will not create any table content
--no-create-db: will not create database statement
--skip-opt: will turn of option set by any other options

Restore with mysql command

 

In earlier module we learn how to take backup with the help of mysqldump command and now in this module we will restore the same backup with the help of mysql command.

Restore Single Databases
Open Run-> cmd press enter.
Cd C:\Program Files\MySQL\MySQL Server 5.5\bin mysql –h localhost –u [uname] –p patientDB<c:\backup\singleDbBackup.sql

Restore multiple Databases
C:\Program Files\MySQL\MySQL Server 5.5\bin mysql –h localhost –u [uname] –p < c:\backup\twoDbBackup.sql