John Magnolia John Magnolia - 1 month ago 13
MySQL Question

Phpmyadmin export VIEW without DATABASE_NAME or ALGORITHM

When exporting a sql dump with phpmyadmin it creates the VIEW table like this:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER
VIEW `database`.`table` etc..


Each time I have to manually edit the sql dump to remove the
root
user and
database
name.

Answer

Maybe I'm not understanding... but this has always worked for me. There are no references to the database name and all definers get wiped out so it's super-easy to restore from the file it generates:

mysqldump -uUSERNAME -pPASSWORD database |  sed -e 's/DEFINER=[^*]*\*/\*/' > backup.sql

To restore:

mysql -uUSERNAME -pPASSWORD database < backup.sql

As long as the database you're restoring to exists (empty or not), works like a charm.