Backing Up Views with Mysql Dump

Question

I want to back up only the Views with mysqldump.

Is this possible?

If so, how?

Answer 1

NOTE: This answer from Ken moved from suggested edit to own answer.

here's a full command line example using a variant of the above

 mysql -u username INFORMATION_SCHEMA   --skip-column-names --batch   -e "select table_name from tables where table_type = 'VIEW'       and table_schema = 'database'"   | xargs mysqldump -u username database   > views.sql 

This extracts all of the view names via a query to the INFORMATION_SCHEMA database, then pipes them to xargs to formulate a mysqldump command. --skip-column-names and --batch are needed to make the output xargs friendly. This command line might get too long if you have a lot of views, in which case you'd want to add some sort of additional filter to the select (e.g. look for all views starting with a given character).

Answer 2

Backing up views over multiple databases can be done by just using 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, "; ") table_name from information_schema.views'