Wednesday, 2 May 2012

MYSQL:How to create MySql Dumps using batch file (WINDOWS)


Too much ,, really in MYSQL finding complete solution like oracle & sql server is too difficult. That I have faced so many times with my development team. One of my colleague asked about the dumps creation using batch file. Really I searches many sites on internet with unexpected   results. Taking dumps using command line I have already post on my blog , but in this case I need a batch file for execution. And finally when I prepare it for serve , I was really happy and now I am sharing this happiness with all my visitors .  Below the code inside the batch file :-

cd "c:\Program Files\MySQL\MySQL Server 5.0\bin\"
mysqldump -hyourIPaddress -uyour_user_name -pyour_password yourdbname > d:\output_backup.sql
exit


Now create a batch file and paste the code above with host,username, password,database name and output file name for store data.

Example 1:-
cd "c:\Program Files\MySQL\MySQL Server 5.0\bin\"
mysqldump -hlocalhost -uroot -proot manojdb > d:\mybackupdumps.sql
exit

If you want to take all database backup/dumps you can also do by following code :-

cd "c:\Program Files\MySQL\MySQL Server 5.0\bin\"
mysqldump -hlocalhost -umyuname -pmypassword --all-databases > d:\myalldatabasedumps.sql
exit


MYSQL Reference manual provides so many options for using with mysqldump . Some of the most useful are below :-


--add-locks  is used for    Surround each table dump with LOCK TABLES and UNLOCK TABLES statements.

 --all-databases  is used for Dump all tables in all databases.
  
 --comments  is used for Add comments to the dump file.

--compact is used for produce more compact out put .

--ignore-table=db_name.tbl_name is used for Do not dump the given table



You can also follow mysql reference manual for dumps creation  by Click Here. 



Hope it will help you.
Ping me always.