How recover a deleted MySQL function

I have functions saved under proc.

I am using MySQL Workbench.  I just tried to alter a function which resulted in an error (something about it being unsafe and binary logging); however, when I clicked "Apply", it dropped my function... and then as I was searching for the relevant option setting, Workbench crashed!

What's the easiest way to recover this function from a file-based backup?

W7 64
MySQL 5.6.12
WorkBench 6.0.6.11184
SAbboushiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RobOwner (Aidellio)Commented:
Have you got a backup of your database? The function will be in there and you can use work bench to retrieve it. What type of backup were you running?
0
Ray PaseurCommented:
I may not be able to help you find this one, but going forward, this article may be helpful.
http://www.experts-exchange.com/Software/Development/Management_Debug/Version_Control_CVS/A_12235-Git-101.html
0
RobOwner (Aidellio)Commented:
@Ray are you suggesting version control for this?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Ray PaseurCommented:
I'm suggesting version control for everything that gets written in code, to the greatest extent possible, every time.  If you've ever lost a script, or tried something and screwed up a script, it's really nice to be able to get it back.
0
RobOwner (Aidellio)Commented:
Agree with code versioning, but I'm not sure how it would work in a database environment?  

Unless I'm missing something, it's slightly different in this case as the function would be stored in the database and not in a code file as such that would be easily checked in/out of a CVS.

It would mean every change to the database would need to be dumped and checked in to the version control?
0
Ray PaseurCommented:
My thinking would be that the code to be stored in the data base would get there through a predictable path, such as running a script that stored the function.  The script can be versioned and stored in a CVS.

Unfortunately that's not helpful to the instant question, it's just something to think about for the future.
0
RobOwner (Aidellio)Commented:
Yeah I see what you're saying now.. all your SQL would be stored in one or more files that would be run against the database as required to update it.  Consequently these files would be perfect for a CVS.
Hadn't come across that before so thanks Ray
0
Ray PaseurCommented:
10-4.  We call it a "build."  It's used to create the test environment and it usually starts with dropping all of the tables, creating the tables, loading them with the test data set. etc.  Then we can run unit and integrated tests and if (when) the data model gets messed up, we fix the scripts and just rerun the "build" to move on to the next round of testing.
0
SAbboushiAuthor Commented:
Thanks for the interesting posts.

tagit:
Have you got a backup of your database? The function will be in there and you can use work bench to retrieve it. What type of backup were you running?

File-based backup (i.e. a backup of the file system i.e. the windows data folder where the mysql data files are stored)
0
RobOwner (Aidellio)Commented:
Ok restore the data files to another directory. Shutdown the mysql service.
Add or Edit the data location (datadir directive) in your my.ini / mycnf file to point to this new directory. Reference: http://dev.mysql.com/doc/refman/5.6/en/server-options.html
Restart the server.
Extract the function you need (include a dump file! )
Shutdown the server.
Edit the data location back (or remove it for default location)
Start the server.
You can now add the function back in.
0
RobOwner (Aidellio)Commented:
Did you manage to restore your function?
0
SAbboushiAuthor Commented:
tagit - Thanks for your post.  This is the direction I was looking for.

My concern is that I have binary logging and mixed ISAM and InnoDB schemas...

Might this not cause me problems if the databases are now temporarily swapped out with binary logging in place?
0
RobOwner (Aidellio)Commented:
In your case, it would pose an issue if you didn't restore the binary log files as well.  As for Innodb, you'll need the log files but it should be ok.  Remember you're only trying to recover this function, not set up a fully working database.

How I would go about this in your case is set up a machine you don't care what happens to it, like a virtual machine if you have it or even a spare laptop lying around the office.  Install the same version of mysql on it, stop it running and copy your backed up files across (and the logs and your my.cnf/ini file).  Start it up and see what happens.  You only need it to be stable enough for you to get your function ;)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SAbboushiAuthor Commented:
Thanks!
0
RobOwner (Aidellio)Commented:
No problem! Now please do yourself a favour and set up a backup and test it right away as well as monthly :-)
0
SAbboushiAuthor Commented:
Thanks again -
0
SAbboushiAuthor Commented:
btw - any suggestions on the backup?  I assume you mean one that backs up MySQL in a form that I can restore tables without needing to swap out the MySQL files at the file-system level?
0
RobOwner (Aidellio)Commented:
That's right. I'm sure it's been answered here somewhere so to save you opening a new question, do a search. If I find one I'll post it too.
Essentially you're wanting MySQL to do the backup/dump. Gets tricky with InnoDB tables and binary log files as you may have found out.
There are other issues such as table relationships etc but certainly worth a read
On backing up from the mysql website.
My apps have been ok with just a call to mysqldump so you've got to find something that works for you and your apps and environment
0
SAbboushiAuthor Commented:
Thanks again for your help
0
RobOwner (Aidellio)Commented:
No problem.  I'll keep track of you so when you post more questions i'll be around to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.