MySQL backup and restore from command line

6
votes

I am using Command line to backup and restore MYSQL Database. Let use I m having a Database Data1 having Views and Procedures in it. When in Cmd line, I use mysqldump i.e 

..>bin> mysqldump -u root -proot Data1 > Datafile.mysql

When I use above Cmd, It creates a Backup file on bin Folder of Mysql with Dtafile.mysql Name.

but the thing is it creates Back Up of Only Tables, Not Procedures.

And when I m restoring it in a Blank Database "Data2"

..bin> mysql - u root -proot Data2 < Dataafile.mysql

What it does is, It creates all the Tables and Convert Views into Tables and No Procedures has been restored.

Means I am not able to Restore my full database backup with All tables, views and Procedures.

Can any of you guys help it.. I would be extremely Thankfull.

2
mysqlbackupmysqldumprestore

2 Answers

11
votes

Include the "--routines" parameter for mysqldump and it will dump the procedures.

EDIT #1 : Your mysqldump command should now look like, 

mysqldump -u root -proot --routines Data1 > Datafile.mysql

EDIT #2:
You need to add the --add-drop-table switch as well if you want to preserve your Views. The reason is Views are created in two steps (first as dummy tables, then as real views). Also please take a note that Tables and Views share the same name space, so don't get misled by the output given by "Show Tables" command in the mysqlclient. Use "Show Create View vw_test" instead if that command shows you the create view query then bingo, also to make sure that the views have been restored correctly insert some data into the relevant tables in your other database and then run a select query on the view, if the data ties up than you hit a bulls eye, pat yourself on the back :) else you can always return to this wonderful community at Stackoverflow.

So (to maintain views) your mysqldump command should now look like this

mysqldump -u root -proot --routines --add-drop-table Data1 > Datafile.mysql

HTH

-1
votes

there is three best ways to backup database use command line,phpmyadmin or mysql administrator mysql database backup and restore in easy way, use mysql database backup

mysql administrator

phpmyadmin