Restaurar mysqldump completo con problemas relacionados con VIEW y sus permisos

Mysqldump, restore, vistas y seguridad

Algunas veces, sobre todo cuando estamos trabajando con backups de otros servidores mysql, con problemas para restaurar un backup de una base de datos individual, que se hizo de forma completa.

Esta incluye las vistas, y por ende, los usuarios con permisos para las vistas, conocido en MySQL como SQL SECURITY DEFINER .

Si en nuestro sistema no existen esos usuarios, a los que hace referencia el fichero sql, obtendremos un fallo, en el volcado o restauración de la copia de seguridad, incluso si lo ejecutamos como root

Por ejemplo

ERROR 1356 (HY000) at line 1693: View 'database.view_condition_xx_table' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Podemos intentar algunos tips, que hay por ahí, incluso hacer un restore forzado pero nos quedaríamos con la duda de si está todo bien.

Solución propuesta: desactivar al usuario afectado en su SQL SECURITY DEFINER

Primero para ver el tema y si este es el problema realmente (copiar y pegar artículos de internet no es buena idea si no se sabe lo que se hace) vamos a ver si los tiros van por ahí.

❯ cat my-database-dump.sql | grep -i DEFINER
/*!50013 DEFINER=`root`@`127.0.0.1` SQL SECURITY DEFINER */
/*!50013 DEFINER=`root`@`127.0.0.1` SQL SECURITY DEFINER */
/*!50013 DEFINER=`otrouser`@`%` SQL SECURITY DEFINER */

Vemos que existe un usuario que no existe en nuestro servidor, y ese el portanto el que produce el problema en el volcado.

La mejor opción en mi opinión, no es tratar de saltarse o modificar nada en el servidor, sino de obviar ese usuario, salvo que sea de interés crearlo en nuestro sistema, por otras causas.

Desactivación del SQL SECURITY DEFINER problemático

Usando sed podremos cambiar el definer que nos falla a root y volver a intentar la restauración de la copia de seguridad de mysql que nos falló a causa definer/invoker of view lack rights to use them

sed -i.bak 's/DEFINER\=\`otrouser/DEFINER\=\`root/g' my-database-dump.sql 

Aviso

Esta documentación y su contenido, no implica que funcione en tu caso o determinados casos. También implica que tienes conocimientos sobre lo que trata, y que en cualquier caso tienes copias de seguridad. El contenido el contenido se entrega, tal y como está, sin que ello implique ningún obligación ni responsabilidad por parte de Castris

Si necesitas soporte profesional puedes contratar con Castris soporte profesional.