MySQL: backup & restore database dump without definer, update definer

- Remove DEFINER clause from MySQL Dumps (2 Methods)
$ mysqlpump -h localhost -u database-user -p database-name | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > dump-without-definer.sql

- Restore database dump without definer:
$ cat dump-with-definer.sql | sed 's/DEFINER=[^*]*\*/\*/' > dump-without-definer.sql
$ mysql -u database-user -p --max-allowed-packet=32M database-name < dump-without-definer.sql

- Restore database dump without definer to different schema;
$ mysql -u database-user -p --max-allowed-packet=32M database-name < dump-without-definer-schema.sql |  sed s/`database-name`/`other-database-name`/g > dump-without-definer-schema.sql

- Change database definer from user “root” to user “database-user” run below command using mysql workbench and root access
mysql> SELECT CONCAT("ALTER DEFINER=`database-user`@`%` VIEW ", table_name, " AS ", view_definition, ";") FROM information_schema.views WHERE table_schema='database-name';

- Right click with the mouse on results rows and click Copy Row (unquoted), Paste and run the results commands
- Grant user ‘database-user’ privileges and flush privileges
mysql> GRANT ALL PRIVILEGES ON *.* TO `database-user`@`%`;mysql>  GRANT CREATE, ALTER, DROP, CREATE VIEW ON *.* to 'database-user'@'%';
mysql> FLUSH PRIVILEGES;
-Verify views updates, query all result rows replace \G with ; in each sql line of results
mysql> SELECT CONCAT('SHOW CREATE VIEW ',table_schema,'.',table_name,'\\G') FROM information_schema.tables WHERE engine IS NULL;



- Another way to backup views without definer to a .sql file and drop views and recreate views without definer from previous .sql file.
$ mysql -u root -p -A --skip-column-names -e"SELECT CONCAT('SHOW CREATE VIEW ',table_schema,'.',table_name,'\\G') FROM information_schema.tables WHERE engine IS NULL" | mysql -u root -p -A --skip-column-names > AllMyViews.sql
$ mysql -u root -p -A --skip-column-names -e"SELECT CONCAT('DROP VIEW ',table_schema,'.',table_name,';') FROM information_schema.tables WHERE engine IS NULL" | mysql -u root -p -A
$ mysql -u root -p  -A < AllMyViews.sql

$ echo "SELECT CONCAT("ALTER DEFINER=`database-user`@`%` VIEW ", table_name, " AS ", view_definition, ";") FROM information_schema.views WHERE table_schema='database-name'" | mysql -u root -p > alterView.sql
$ mysql -u root -p database-name < alterView.sql