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

root directory for mysql databases

The above shows the structure used for the datafiles. Within innoDB_datafiles folder is the ibdata1 file which looks to be approx 12Gb.

Individual databases and associated files All the databases on the orginal server appear to have their own folder with various files for the tables.

The file that appears to contain all the stored procedures 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 mysql8 data folder

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)

    `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:

  `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!

1 Answer 1

  1. Install the previous version of MySQL you have migrated from.
  2. Import the dumpfile you have made.
  3. Dump again in the proper way.
  4. Install the newer MySQL version.
  5. Import your proper backup.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.