Tuesday, January 29, 2013

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

1 comments 1/29/2013
Sign up to receive our site updates!
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.
Your Ad Here


devtools korzh on October 2, 2013 at 10:07 PM said... [Reply]

Good tutorial!

Respects for your's Questions & Opinions

  • Friends

    Recent Posts

    Copyright 2008 All Rights Reserved Tech Tasks Template by Rajesh Kumar Chekuri