Until recently I have been running MySQL5.5.8 - foolishly a couple of days ago I decided to upgrade to take advantage of new features to be found in version 8. Before the upgrade I did a backup, using MySQL Administrator of most, not all, databases - some were expendable. Perhaps foolishly I also backed up the mysql table which caused some issues when trying to import from the backup but after editing the sql file I appear to have all the databases back and accessible.
The problem though is there are no stored procedures ( of which there were many ) which means my development website will no longer load. The same is true of the functions, events and triggers - it appears that none of these were backed up by MySQL Administrator
The above shows the structure used for the datafiles. Within innoDB_datafiles folder is the ibdata1 file which looks to be approx 12Gb.
All the databases on the orginal server appear to have their own folder with various files for the tables.
Within the mysql database are the proc.myd and proc.myi files. There are similar files for events ( event.myd & event.myi ) and for functions (func.myd & func.myi ) - I can not see the equivalent for triggers so assume they are referenced elsewhere but not found that yet.
With the new MySQL8 server I created a new Stored Procedure to see if the proc.myd/myi file would be generated in the equivalent mysql directory - as per below screenshot
It appears that MySQL8 does things rather differently and I don't have a clue what to do next to be honest. I have tried running the following command in a gui (heidi)
select
`r`.`specific_name` as 'name'
from `information_schema`.`routines` as r
where `r`.`routine_type` = "procedure";
This query would previously list all procedures perfectly well but on MySQL8 nothing! Similar story for functions:
select
`r`.`specific_name` as 'name'
from `information_schema`.`routines` as r
where `r`.`routine_type` = "function"; // no results
There was success when I tried for triggers - they appear to have been within the backup file.
select `trigger_name` as 'name'
from `information_schema`.`triggers`;
Again, success for a newly created event
select `event_name` as 'name'
from `information_schema`.`events`; // :-)
The question is basically how can I restore my server to a healthy state with all the procedures, functions and events based upon the details within either the respective myd or myi files or within the original ibdata1file?
Any help and advice to help rectify this would be greatly appreciated!
