9

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.

9

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 !!!

  • 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
0

The best answer for this is probably this one by olliiiver which I have slightly modified. It specifically exports a drop table query and a create or replace view query for every view in the system, without needing to mess around with any tables. The drop table query is often important, as mysqldump apparently likes to recreate views as tables unless you include every view in its own --ignore-table parameter, which would be annoying. It uses INFORMATION_SCHEMA.

mysql --skip-column-names --batch \
  -e "SELECT CONCAT('DROP TABLE IF EXISTS ', TABLE_SCHEMA, '.', TABLE_NAME, \
  '; CREATE OR REPLACE VIEW ', TABLE_SCHEMA, '.', TABLE_NAME, ' AS ', \
  VIEW_DEFINITION, '; ') AS q FROM INFORMATION_SCHEMA.VIEWS"

If you want to turn something like this into a shell script, I also recommend using the --defaults-extra-file=CREDSFILENAME parameter, so that you do not need to specify user/pass in the script.

The credentials file looks like this:

[client]
username=YourUsernameHere
password=YourPasswordHere

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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