- MySQL Database v5.0.45
- MySQL Workbench v6.0.8.11354 build 833
What?
This is a quick article on how to get around the problem of backing up your MySQL database when attempting to "Data Export" using MySQL Workbench. This is not regarding the connection issue as I can connect to my database using MySQL Workbench (I have enabled the old authentication protocol). The error ONLY appears when I try to "data export" the database.
Why?
The quick solution for everyone else is to change/reset the password of the connecting database user, but herein lies the problem. When you read my workaround, you'll say that I haven't solved anything; but this is a production database I want to export for offline backup. I have to raise and log a formal change request and follow a workflow process in order to make a change on a live system to a service user account... Especially one used by the scripts to access the database-driven website.
How?
I'm first going to highlight 2 methods which I found on the web and can be applied if you work somewhere that doesn't care about testing, about changing a production environment and doesn't need a hierarchical authorization workflow process (in other words, you don't have a boss):
Method #1 (cowboy fix - not recommended):
I would not recommend the following but I found this on the MySQL Forums. Someone solved their issue by resetting (or re-issuing) their password which implies there was an issue with pre 4.1.1 passwords and the hashing algorithm:
copyraw
SET session old_passwords = 0;
SET PASSWORD for 'youruser' = PASSWORD('yourpassword');
- SET session old_passwords = 0;
- SET PASSWORD for 'youruser' = PASSWORD('yourpassword');
Method #2 (useless fix - unlikely to change anything):
Another solution has been advised that you have to tell MySQL WorkBench 6.0 to "use the old authentication protocol":
• For versions before 6.0.7
• For versions after 6.0.7
Method #3 (Recommended):
Do not use MySQL WorkBench!!! Seriously, your pride will slow you down or get you sacked! Instructions:
- Go to Google Search
- Search for "Free MySQL database manager" (preferably add the keyword "portable")
- Avoid links to MySQL Workbench and download an alternative.
Job done without making any changes to the LIVE environment!
Category: MySQL :: Article: 572
