Modify DEFINER on Many Views
You can use ALTER VIEW in conjunction with the information schema. You mentioned dumping it out to a text file, so perhaps something like this:
SELECT CONCAT("ALTER DEFINER=`youruser`@`host` VIEW `",table_name,"` AS ", view_definition,";")
FROM information_schema.views WHERE table_schema='databasename'
Mix this with the mysql command line (assuming *nix, not familiar with windows):
> echo "*abovequery*" | mysql -uuser -p > alterView.sql
> mysql -uuser -p databasename < alterView.sql
Sidenote: You can't alter the information_schema entries directly. Note2: This works for only one database at a time, if you leave off WHERE table_schema you need to insert USE commands between each.
Create a text file with all the view definitions:
mysql -uusername -ppassword -A --skip-column-names -e"SELECT CONCAT('SHOW CREATE VIEW ',table_schema,'.',table_name,'\\G') FROM information_schema.tables WHERE engine IS NULL" | mysql -uusername -ppassword -A --skip-column-names > AllMyViews.sql
You edit AllMyViews.sql from there. Then, Drop the Views
mysql -uusername -ppassword -A --skip-column-names -e"SELECT CONCAT('DROP VIEW ',table_schema,'.',table_name,';') FROM information_schema.tables WHERE engine IS NULL" | mysql -uusername -ppassword -A
After editing AllMyViews.sql reload them
mysql -uusername -ppassword -A < AllMyViews.sql
Give it a Try !!!
Export all the views of the database <DB>
:
mysql -BNe "SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA = '<DB>' AND TABLE_TYPE = 'VIEW'" \
information_schema | xargs mysqldump --single-transaction --no-data <DB> >views.sql
or:
mysql -BNe "SELECT TABLE_NAME FROM VIEWS WHERE TABLE_SCHEMA = '<DB>'" \
information_schema | xargs mysqldump --single-transaction --no-data <DB> >views.sql
Edit views.sql
(change definer) and recreate them:
cat views.sql | mysql <DB>
Specify -u
and -p
switches if necessary.