How to Backup/Restore/Crate/Drop the MYSQL Data Base through the Command Prompt?

There are no of IDEs (SQLyog) are to handle the MySQL Databases, but it’s always better to use command prompt instead of IDE. It will reduce the processing time as well as burden on the server. we discussed about how to optimize the Stored Procedure? and how to avoid the Cursors inside the Stored procedure?.

To backup the Database:

mysqldump –u[Username] –p[Password] --database [DatabaseName] > D:\DBBackup.sql

To take backup with all the Databases :

--all-databases :- use to take backup all DataBases

mysqldump –u[Username] –p[Password] --all-databases > D:\DBBackup.sql

To take backup with Stored procedures, functions, triggers, views :

--routines :- Use to take backup with all Stored procedures, functions, triggers, views.

mysqldump –u[Username] –p[Password] --routines --database [DatabaseName] > D:\DBBackup.sql

To take backup from the different system:

–h[IP Address]:- use to take backup from different machine.

mysqldump –u[Username] –p[Password] –h[IP Address] --database [DatabaseName] > D:\DBBackup.sql

To take backup with only tables structure:

--no-data :-Use to take backup with out tables data.

mysqldump –u[Username] –p[Password] --no-data --database [DatabaseName] > D:\DBBackup.sql

To take backup with only tables data (without table structures):

--no-create-info :- use to take backup with tables data only.

mysqldump –u[Username] –p[Password] --no-create-info --database [DatabaseName] > D:\DBBackup.sql

To drop the Database:

-f :- use to drop Database forcibly in case any sql error happen in middle.

mysqldump –u[Username] –p[Password] –h[IP Address] -f drop [DataBaseName]

To create the DataBase:

mysqldump –u[Username] –p[Password] –h[IP Address] create [DataBaseName]

To restore the DataBase:

mysql –u[Username] –p[Password] –h[IP Address] -f [DataBaseName] < D:\DBBackup.sql

Here also we can use ‘–f’ to restore the Database forcibly in case if any sql error occur.