MySQL 5.7 – Disable use of SSL connection

MySQLmysql-5.7ssl

For some reason when setting up a server with MySQL 5.7, it was created to use SSL connections:

have_ssl=YES,
have_openssl =YES

There is no SSL configured in my.cnf.

How can I disable this option? It's for a development machine and we don't need this on it. We get an SSL authentication error, and for some reason this feature is enabled on the server while it was not configured and there is no SSL reference in the my.cnf.

I was using 5.7 and could not find a way to disable the SSL. I needed to downgrade to 5.5 (due to timestamp rounding bug on 5.6/7) and on 5.5 SSL is disabled. I just want to know if there is an option/command to change this option to disabled. In 5.5 its disabled by default.

Best Answer

I would block the SSL port using your machine's software firewall (iptables, etc). This avoids having to restart mysqld.

e.g. if your SSL port is 3307:

iptables -I INPUT -i eth0 -p tcp --dport 3307 -j DROP

Otherwise, simply add a hash at the beginning of each line containing 'ssl' in your /etc/my.cnf, then restart mysqld.

e.g.

# have_ssl...
# anything_ssl...

If you're running mysqld < 5.7.x, then you can use the ssl=0 configuration option here. That option is deprecated and will be removed in a future release, though.

However, your best bet is to block all other ports than your non-ssl port and allow ssl clients to fail attempting to connect to that port.

Related Solutions

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.)

Mysql – thesqlbinlog: unknown variable ssl_key or ssl_cert or ssl_ca

BOTTOM OF THE BUG REPORT YOU REFERENCED

[21 Jan 16:44] David Moss

Thanks for your feedback. This was fixed in version 5.7.3, and the following text was added to the 5.7.3 release notes: Previous versions of mysqlbinlog did not correctly accept the ssl-ca option in an option file. This fix ensures that this option can be correctly used. In earlier versions a work around is to use the loose-ssl-ca option.

[19 Feb 16:08] David Moss

Posted by developer:

This was covered in the release notes. Closing.

[27 Feb 9:02] Christoph Mitasch

will this also be fixed for the 5.6.X release?

MySQL 5.6 Documentation

The --ssl-crl and --ssl-crlpath variables were first introduced for mysqlbinlog in MySQL 5.6.3. Once MySQL 5.6 went GA, those options were not meant to be used at all. How did I know ? Look at the list of options for mysqlbinlog (Table 4.16). They appear in the list between --socket and --start-datetime=datetime BUT THEY HAVE NO HYPERLINKS !!!

mysqlbinlog

Here is something else to consider: mysqlbinlog is not a client program. It is a utility program. In mysql, client programs require logging into mysqld with a username and password.

If you look at the list of MySQL Client Programs, please note that mysqlbinlog is not there because it is a utility. You can see mysqlbinlog among the list of MySQL Administrative and Utility Programs.

If you are using an option file, mysqlbinlog can never respond to the options under the [client] group header. Why ? According to the Options File Documentation:

The [client] option group is read by all client programs (but not by mysqld). This enables you to specify options that apply to all clients. For example, [client] is the perfect group to use to specify the password that you use to connect to the server. (But make sure that the option file is readable and writable only by yourself, so that other people cannot find out your password.) Be sure not to put an option in the [client] group unless it is recognized by all client programs that you use. Programs that do not understand the option quit after displaying an error message if you try to run them.

SUGGESTION

I don't know if you will encounter the bug with my suggestion, but here it goes ...

If you are using an option file, make the header use [mysqlbinlog] instead of [client]. As a utility, I know [mysqlhotcopy] can be used as a group header. Please try out [mysqlbinlog] as a group header and see if it helps.

ADDITIONAL OBSERVATION

Since you are connecting to DB Server running MySQL 5.5, its mysqld will never understand --ssl-crl and --ssl-crlpath variables because they belong to MySQL 5.6.3. I guess this means you were connecting from a MySQL 5.6 machine using the mysqlbinlog for MySQL 5.6. You will not need to specify any SSL connection options if you are remote connecting for binlgogs