Restore MySQL database when there are views on there - Do this in order to restore it
Posted: 2019-08-09 15:47
I have a mysql database with 3 tables and 2 views. Before you ask, i use these views on reports I built outside AppGini using CodeIgniter and bootstrap.
When I use the Backup/Restore tool in Admin Area to backup this database it works great. But when I try to recover the backup it will not recover my views. I was looking at the dump that was generated and I found a way to make it work.
Below is part of the dump where it creates one of my mysql views:
I just need to delete all after the word CREATE and before the word VIEW and it works. Code above will be as below:
After you get the sql file that is in /admin/backups you just run it in phpMyAdmin. You can also edit this file and use the tool in admin area to restore it (just make a backup of the original file before).
I wish there were a way to make mysqldump strip these commands when generating the dump.
When I use the Backup/Restore tool in Admin Area to backup this database it works great. But when I try to recover the backup it will not recover my views. I was looking at the dump that was generated and I found a way to make it work.
Below is part of the dump where it creates one of my mysql views:
Code: Select all
--
-- Final view structure for view `vw1itens`
--
/*!50001 DROP VIEW IF EXISTS `vw1itens`*/;
/*!50001 SET @saved_cs_client = @@character_set_client */;
/*!50001 SET @saved_cs_results = @@character_set_results */;
/*!50001 SET @saved_col_connection = @@collation_connection */;
/*!50001 SET character_set_client = utf8mb4 */;
/*!50001 SET character_set_results = utf8mb4 */;
/*!50001 SET collation_connection = utf8mb4_general_ci */;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`orc2prod`@`%%` SQL SECURITY DEFINER */
/*!50001 VIEW `vw1itens` AS select `itens`.`id` AS `item_id`,`grupos`.`id` AS `grupo_id`,`categorias`.`id` AS `categoria_id`,`itens`.`tirar` AS `tirar`,`itens`.`dataini` AS `dataini`,`itens`.`datafim` AS `datafim`,`itens`.`descricao` AS `descricao`,`itens`.`valor` AS `valor`,`itens`.`qt` AS `qt`,(`itens`.`valor` * `itens`.`qt`) AS `total1`,(case when isnull(`itens`.`tirar`) then (`itens`.`valor` * `itens`.`qt`) else 0 end) AS `total`,`itens`.`ordem` AS `ordem` from ((`itens` join `categorias` on((`categorias`.`id` = `itens`.`categoria_id`))) join `grupos` on((`grupos`.`id` = `categorias`.`grupo_id`))) order by `grupos`.`ordem`,`categorias`.`ordem`,`itens`.`ordem` */;
/*!50001 SET character_set_client = @saved_cs_client */;
/*!50001 SET character_set_results = @saved_cs_results */;
/*!50001 SET collation_connection = @saved_col_connection */;
Code: Select all
--
-- Final view structure for view `vw1itens`
--
/*!50001 DROP VIEW IF EXISTS `vw1itens`*/;
/*!50001 SET @saved_cs_client = @@character_set_client */;
/*!50001 SET @saved_cs_results = @@character_set_results */;
/*!50001 SET @saved_col_connection = @@collation_connection */;
/*!50001 SET character_set_client = utf8mb4 */;
/*!50001 SET character_set_results = utf8mb4 */;
/*!50001 SET collation_connection = utf8mb4_general_ci */;
/*!50001 CREATE VIEW `vw1itens` AS select `itens`.`id` AS `item_id`,`grupos`.`id` AS `grupo_id`,`categorias`.`id` AS `categoria_id`,`itens`.`tirar` AS `tirar`,`itens`.`dataini` AS `dataini`,`itens`.`datafim` AS `datafim`,`itens`.`descricao` AS `descricao`,`itens`.`valor` AS `valor`,`itens`.`qt` AS `qt`,(`itens`.`valor` * `itens`.`qt`) AS `total1`,(case when isnull(`itens`.`tirar`) then (`itens`.`valor` * `itens`.`qt`) else 0 end) AS `total`,`itens`.`ordem` AS `ordem` from ((`itens` join `categorias` on((`categorias`.`id` = `itens`.`categoria_id`))) join `grupos` on((`grupos`.`id` = `categorias`.`grupo_id`))) order by `grupos`.`ordem`,`categorias`.`ordem`,`itens`.`ordem` */;
/*!50001 SET character_set_client = @saved_cs_client */;
/*!50001 SET character_set_results = @saved_cs_results */;
/*!50001 SET collation_connection = @saved_col_connection */;
I wish there were a way to make mysqldump strip these commands when generating the dump.