Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to backup my entire database along with the views. I am going to use mysqldump command. I was wondering if that will copy all the views or I will have to use separate command for it.

Please suggest me an answer. Any help is greatly appreciated.

share|improve this question
add comment

1 Answer 1

up vote 6 down vote accepted

There is no specific option for dumping views.

You can try the following:

mysqldump -h... -u... -p... --all-databases --routines --triggers --no-data > /root/MySQLDBSchema.sql
grep "CREATE ALGORITHM" /root/MySQLDBSchema.sql

You should be able to see the views. This indicates that when you dump databases, the view comes with it.

Another stunt you can try, just to get the views only, is this:

mysql -uroot -prootpass AN -e"select concat('SHOW CREATE VIEW ',table_schema,'.',table_name,';') from information_schema.views" | sed 's/;/\\G/g' | mysql --uroot -prootpass > /root/MySQLDBViews.sql

Give it a Try !!!

share|improve this answer
    
Thanks a lot for such an explanatory answer. The problem is, my databases are very big so i am planning to copy one at a time. In all this mess I also need to copy 'views' so I cant use --all-databases command. Please suggest a way to conly copy views from Mysql DB Server. –  vinny Jan 20 '12 at 3:54
    
So, instead of using --all-databases, specify the database you want. –  Derek Downey Feb 15 '12 at 16:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.