Print

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