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.

Tags:

Mysql

View