MySQL Views

• MySQL
• mysqldump

SELECT table_schema, table_name FROM information_schema.views;

SELECT view_definition FROM information_schema.views WHERE table_name = <view>;

When mysqldump dumps a database containing views, it will also export the user that created the view.

When restoring the database dump, the views will be created successfully, but they also can only be executed by the same user.

Trying to access the view using an SQL statement, or trying to dump the database will throw an error:

ERROR 1449 (HY000): The user specified as a definer ('username'@'hostname') does not exist

There are a few ways to avoid this error:

1. Delete the lines containing DEFINER=`username`@`hostname` SQL SECURITY DEFINER from the dump file. This will create the views as the same user that is executing the restore.
2. Create the user on the target database
3. Modify the user to be something else

If you want to recreate the user, here's how to get the statement to insert into the mysql database:

mysqldump --no-create-info --compact mysql --tables user -w "User='username'"