MySQL Workbench is adding –enable-cleartext-plugin option on thesqldump command

MySQLmysql-workbenchmysqldump

For some reason my copy of MySQL Workbench on one of my servers has started adding this –enable-cleartext-plugin option on every mysqldump command, which results in this error every time.

20:13:02 Dumping timesavr_dev (certifications) Running: mysqldump.exe
–defaults-file="c:\users\admini~1\appdata\local\temp\3\tmpn1an6u.cnf" –set-gtid-purged=OFF –enable-cleartext-plugin –user=root –host=localhost –protocol=tcp –port=3306 –default-character-set=utf8 –skip-triggers "timesavr_dev" mysqldump: unknown option '–enable-cleartext-plugin'

Operation failed with exitcode 2 20:13:02 Export of
C:\inetpub\wwwroot\TimeSavr\DB_Stuff\MySQLBackups\Dump20180608.sql has
finished with 1 errors

I just noticed this today but I suspect this started happening a few days ago when some other weird things also happened on this server. After a reboot all the bindings on my website had disappeared, as had the user security privileges on the website folder, and for some reason Shared Config was enabled in my redirection.config file. It all seems very random, and now this.

These are the options I have enabled, and I'm pretty sure these are default because when I click Restore Defaults nothing changes.

How can I tell MySQL Workbench to stop using the –enable-cleartext-plugin option?

Best Answer

"Enable Cleartext Authentication Plugin" is a connection setting, not a setting of "Data Export".

You can find it under "Database", "Manage Connections", here select your connection; inside the "Connection"-tab is the "Advanced"-tab with a checkbox to (un-)select that option.

Related Solutions

Thesqldump: flush-privileges option

The documentation is misleading. I read it exactly the same way you do, which isn't what the utility does.

Adding --flush-privileges causes mysqldump to include the following in the backup file, after dumping the mysql schema...

--
-- Flush Grant Tables
--

/*! FLUSH PRIVILEGES */;

...which of course causes the server where the dump is being restored to re-read the potentially-changed grant tables.

And, that's all it does.

Confirmation of this can be found in the source code for mysqldump "10.13" (the version bundled with MySQL Server 5.5.30):

if (flush_privileges && using_mysql_db == 0)
{
  fprintf(md_result_file,"\n--\n-- Flush Grant Tables \n--\n");
  fprintf(md_result_file,"\n/*! FLUSH PRIVILEGES */;\n");
}

The using_mysql_db == 0 comparison was confusing at first, but it turns out to be the return value from a string comparison function where "0" means "identical."

Mysql – Connecting to MYSQL on an EC2 instance

Since Zapier is coming in from outside your instance, a possible cause of your problem is the security group for your instance. Most ports on EC2 instances (except the SSH port) are normally blocked by default unless you explicitly open them.

So you would need to open the mysql port 3306 on your instance up to the ip address Zapier is coming in from. (preferably you would not open it up to everyone in the world.)

See Using Amazon RDS/EC2 on the Zapier site, starting from the section where it says Can't Connect to My Database. They continue on to provide specific instructions for the security group settings.

You will also probably need to grant permission on the database using the mysql GRANT command to the user coming in from the Zapier ip address.

Something like this should work :

GRANT ALL PRIVILEGES
ON mydb.*
TO 'user'@'54.86.9.50'
IDENTIFIED BY 'newpassword';

Or you can allow that user to come in from any ip adress like this:

GRANT ALL PRIVILEGES
ON mydb.*
TO 'user'@'%'
IDENTIFIED BY 'newpassword';

(Incoming requests would still be limited to Zapier ip addresses due to the security group settings.)

Related Question