I often generate a fair number of views while developing reporting SQL, and have off an on looked for a way to easily save their source code to a file. mysqldump
doesn't directly support extracting only views, but with a little command line trickery and a query against INFORMATION_SCHEMA
you can make it do the right thing:
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
The skip-column-names
and batch
options produce output with just one view name per line, which is what xargs needs as input.
Be sure to replace both occurences of username and database with appropriate values, and add -h for remote hosts and -p if the user requires a password. Here's a one-line example for user root with no password on localhost, with a database named "foo":
mysql -u root INFORMATION_SCHEMA --skip-column-names --batch -e "select table_name from tables where table_type = 'VIEW' and table_schema = 'foo'" | xargs mysqldump -u root foo > views.sql
2 comments:
way to go, putting up ths way to dump a view. What is wrong with the MySQL gods, they don't know people need to be able to dump views, and they don't bother making it non-braindead? And what about phpmyadmin, are they asleep on the job?
Good article..while restoring the dump need to re look on definer in the function creation body
Post a Comment