mysqldump: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) when trying to connect

Applies to:
  • 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');
  1.  SET session old_passwords = 0
  2.  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
Use Legacy Auth setting by parameter

• For versions after 6.0.7
Use Legacy Auth setting by parameter

Method #3 (Recommended):
Do not use MySQL WorkBench!!! Seriously, your pride will slow you down or get you sacked! Instructions:
  1. Go to Google Search
  2. Search for "Free MySQL database manager" (preferably add the keyword "portable")
  3. Avoid links to MySQL Workbench and download an alternative.
MySQL.com may in the time that I have written this article, release a newer version that corrects this. I personally downloaded HeidiSQL, setup a connection and exported the database.

Job done without making any changes to the LIVE environment!

Category: MySQL :: Article: 572

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.