ansaurus

Question

MySQL backup and restore from command line.

Answer 1

+1 A:

Hi,

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

Anand 2009-05-05 08:27:03

Hey how do I include --routines while taking dump?? CAn you plz explain a bit

Ashok Gupta 2009-05-05 08:40:49

Hi, I have edited my answer above to show the command to use.

Anand 2009-05-05 09:09:15

Hi Thanks..It Works.. One More Problem, I am facing Is that in "Data1" I am having Views that refers other databases. when I am restoring it, It convert all Views into tables. i.e I m having View vw_test in Data1 but when I restore it, It becomes a table with Name vw_test.What would be the problem. Help me.Thanks a Lot.

Ashok Gupta 2009-05-05 09:28:35

Hi, I have edited my answer again. Please check.

Anand 2009-05-05 10:12:12

Hi Thanks for your reply, but .. It doesn't work.Still the script convert Views(Having Reference to other database) into Tables.

Ashok Gupta 2009-05-05 10:25:09

what is the output of "show create view vw_test"?

Anand 2009-05-05 10:34:24

Show Create Table shows the Table structure of vw_test.Create Table vw_test(id int...) in this manner.

Ashok Gupta 2009-05-05 10:48:53

You probably didn't read my comment properly, I asked you to run "Show Create View vw_test" not "Show Create Table". Also what version of mysql are you running, everything at my end is running fine. Did you insert some data in the other database and then run a select query on your view what was the result?

Anand 2009-05-05 11:37:05

Hi Anand, I am trying with writing "Show create view" but it is showing, vw_test is not a view.

Ashok Gupta 2009-05-08 07:38:43

Is mysqldump giving you any errors when restoring the database. Also please paste the "Create View" query that you are running to create the view in the database. Also have you considered using MySQL Administrator GUI for back/restore operations.

Anand 2009-05-08 08:30:02

Hey Anand.. I am really thankful for your precious time, Can I have your Id Plz, I can Attach a file and send to ur ID.

Ashok Gupta 2009-05-09 06:57:52

"Create Database Test"---------------------"Create Database Test1"Use Test1Create Table Test1(id int, fname varchar(20))-------------------Create Database Test2Create Table Test2(id int, lname varchar(20))delimiter $$create view vw_Test2asselect * from Test2$$delimiter ;delimiter $$Create View vw_Test1asselect * from Test1.Test1$$delimiter ;----------------------------For Backup: >mysqldump -u root -proot --routines --add-drop-tables Test2>test.mysql----------------For Restore >mysql -u roout -proot test<test.mysql----------------

Ashok Gupta 2009-05-09 07:05:37

I guess you are missing a "Use Database Test2" somewhere in your script pasted above, using the script given above all the tables and views get created in Database Test1, your Database Test2 is blank...so on restoring nothing gets restored as Test2 is blank. If you still have problems paste your email id and I will reply you on that email id. Regards, Anand

Anand 2009-05-09 07:24:51

[email protected]

Ashok Gupta 2009-05-09 07:29:25

Check your email.

Anand 2009-05-09 07:56:09

Hi Anand, I have Drpo you a mail, with Proper Explanation.

Ashok Gupta 2009-05-09 08:44:29

Answer 2

A:

I too having problem with mysqldump where when restored, views and procedures becomes tables not views and procedures anymore... still no resolutions. anyone with any insight, please help

nanao 2010-01-05 17:13:58

Do one thing... One you create the Backup, Open the file and Check where Views are bieng created.. you could see, that there wrong..old reference has been passed, Delete the old reference and then restore.. this would solve ur problem.

Ashok Gupta 2010-01-07 09:49:32

Answer 3

A:

i too having problem with back up from data base i use mysql Administrator and forward in workbench both have this problem and convert view to table and then create view with select this tables mysql kill my time for solving this problem.

Jeus 2010-07-17 01:02:41

ansaurus

tags:

views:

answers:

MySQL backup and restore from command line.

related questions