Setting up MySQL SSL and secure connections

Setting up MySQL SSL and secure connections

PREVIOUS POST
NEXT POST

There are different articles on how to setup MySQL with SSL but it’s sometimes difficult to end up with a good simple one. Usually, setting up MySQL SSL is not really a smooth process due to such factors like “it’s not your day”, something is broken apparently or the documentation lies 🙂 I am going to provide the brief instructions on how to setup MySQL with SSL, SSL replication and how to establish secure connections from the console and scripts showing the working examples.

SSLQuick links:

 

Setup SSL on MySQL

1. Generate SSL certificates according to the example 1. Use the different Common Name for server and client certificates.

2. For the reference, I store the generated files under /etc/mysql-ssl/

3. Add the following lines to /etc/my.cnf under [mysqld] section:

# SSL
ssl-ca=/etc/mysql-ssl/ca-cert.pem
ssl-cert=/etc/mysql-ssl/server-cert.pem
ssl-key=/etc/mysql-ssl/server-key.pem

4. Restart MySQL.

5. Create an user to permit only SSL-encrypted connection:

GRANT ALL PRIVILEGES ON *.* TO ‘ssluser’@’%’ IDENTIFIED BY ‘pass’ REQUIRE SSL;

Establish secure connection from console

1. If the client is on a different node, copy /etc/mysql-ssl/ from the server to that node.

2. Add the following lines to /etc/my.cnf under [client]:

# SSL
ssl-cert=/etc/mysql-ssl/client-cert.pem
ssl-key=/etc/mysql-ssl/client-key.pem

3. Test a secure connection:

[root@centos6 ~]# mysql -u ssluser -p -sss -e ‘\s’ | grep SSL
SSL: Cipher in use is DHE-RSA-AES256-SHA

Setup SSL replication

1. Establish a secure connection from the console on slave like described above, to make sure SSL works fine.

2. On Master add “REQUIRE SSL” to the replication user:

GRANT REPLICATION SLAVE ON *.* to ‘repl’@’%’ REQUIRE SSL;

3. Change master options and restart slave:

STOP SLAVE;
CHANGE MASTER MASTER_SSL=1,
MASTER_SSL_CA=’/etc/mysql-ssl/ca-cert.pem’,
MASTER_SSL_CERT=’/etc/mysql-ssl/client-cert.pem’,
MASTER_SSL_KEY=’/etc/mysql-ssl/client-key.pem’;

SHOW SLAVE STATUSG
START SLAVE;
SHOW SLAVE STATUSG

Establish secure connection from PHP

1. Install php and php-mysql packages. I use the version >=5.3.3, otherwise, it may not work.

2. Create the script:

[root@centos6 ~]# cat mysqli-ssl.php
$conn=mysqli_init();
mysqli_ssl_set($conn, ‘/etc/mysql-ssl/client-key.pem’, ‘/etc/mysql-ssl/client-cert.pem’, NULL, NULL, NULL);
if (!mysqli_real_connect($conn, ‘127.0.0.1’, ‘ssluser’, ‘pass’)) { die(); }
$res = mysqli_query($conn, ‘SHOW STATUS like “Ssl_cipher”‘);
print_r(mysqli_fetch_row($res));
mysqli_close($conn);

3. Test it:

[root@centos6 ~]# php mysqli-ssl.php
Array
(
[0] => Ssl_cipher
[1] => DHE-RSA-AES256-SHA
)

Establish secure connection from Python

1. Install MySQL-python package.

2. Create the script:

[root@centos6 ~]# cat mysql-ssl.py
#!/usr/bin/env python
import MySQLdb
ssl = {‘cert’: ‘/etc/mysql-ssl/client-cert.pem’, ‘key’: ‘/etc/mysql-ssl/client-key.pem’}
conn = MySQLdb.connect(host=’127.0.0.1′, user=’ssluser’, passwd=’pass’, ssl=ssl)
cursor = conn.cursor()
cursor.execute(‘SHOW STATUS like “Ssl_cipher”‘)
print cursor.fetchone()

3. Test it:

[root@centos6 ~]# python mysql-ssl.py
(‘Ssl_cipher’, ‘DHE-RSA-AES256-SHA’)

Notes

Alternative local SSL connection setup
If you connect locally to the server enabled for SSL you can also establish a secure connection this way:
1. Create ca.pem:

cd /etc/mysql-ssl/
cat server-cert.pem client-cert.pem > ca.pem

2. Have only the following ssl- lines in /etc/my.cnf under [client]:

# SSL
ssl-ca=/etc/mysql-ssl/ca.pem

Error with “ssl-ca” on local connections
If you left the line “ssl-ca=/etc/mysql-ssl/ca-cert.pem” under [client] section in /etc/my.cnf on the server enabled for SSL and try to establish local SSL connection, you will get “ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)”.

Discrepancy in documentation
http://dev.mysql.com/doc/refman/5.5/en/using-ssl-connections.html says “A client can connect securely like this: shell> mysql –ssl-ca=ca-cert.pem” which does not work with “REQUIRE SSL”. You still have to supply the client cert and key for any or a combined client+server cert for a local secure connection.

PREVIOUS POST
NEXT POST

Share this post

Comments (23)

  • Daniël van Eeden Reply

    Did you file a bug about the documentation discrepancy?

    June 23, 2013 at 5:18 am
  • Roman Vynar Reply

    Daniel, no I didn’t. May be under some unknown circumstances it can work somehow but it’s not the purpose of this article.

    June 23, 2013 at 5:23 am
  • Chris Reply

    So how do you resolve “ERROR 2026”? Removing ssl-ca isn’t an option…

    October 29, 2013 at 4:13 pm
  • Roman Vynar Reply

    Chris, what’s your MySQL distribution/version if it’s not an option?

    October 29, 2013 at 4:44 pm
  • Reinard Dolleschel Reply

    Thanks for the post. One note though: I think you should discourage people from using the -p[pass] option on the command line. It shows up in the history and in the process list with the password exposed in plain text and is a horrible idea from a security standpoint. Especially when you’re just testing, just use -p and wait for it to prompt you for the password.

    January 28, 2014 at 5:50 pm
  • Miklos Szel Reply

    Hi Roman,

    First of all, good post, thanks!

    I know that this is not the point of this whole article but maybe it would be better to change the:
    GRANT USAGE ON *.* to ‘repl’@’%’ REQUIRE SSL;
    to
    GRANT REPLICATION SLAVE ON *.* to ‘repl’@’%’ REQUIRE SSL;
    as we speak about a replication and this could be a bit confusing.

    February 18, 2014 at 11:00 am
  • Roman Vynar Reply

    Reinard, Miklos, thanks, I have made your notes.

    February 19, 2014 at 9:35 am
  • QS Reply

    Looks like your escape character got lost in translation:

    [root@centos6 ~]# mysql -u ssluser -p -sss -e ‘s’ | grep SSL

    should be

    [root@centos6 ~]# mysql -u ssluser -p -sss -e ‘\s’ | grep SSL

    July 3, 2014 at 1:26 pm
  • Roman Vynar Reply

    Thanks QS. I had to put html backslash code \ instead.

    July 4, 2014 at 5:32 am
  • CHRIS Reply

    I believe in step #3, “CHANGE MASTER MASTER_SSL=1” should read “CHANGE MASTER TO MASTER_SSL=1”

    September 10, 2014 at 6:55 pm
  • Fabrizio Bartolomucci Reply

    In the process of moving a web server to another domain, I would like to grant the access to the files in the old location to the mysql DB on the new site so to minimize differences between them. Yet the latter app presently only allows access by ssl and I would need to both configure QuincyKit and the normal php scripts to accept that. Easier would be to temporarily also allow the regular sql access. How do I do it?

    November 25, 2014 at 4:18 pm
  • Ajay Singh Reply

    i need to complete the project and topic is : ” How to secure a MySQL or MS SQL connected website ”
    So can anybody help me like what should i need to write into this ?

    March 24, 2015 at 11:23 pm
  • Emilio Macias Reply

    I would like to use it for federated tables. How i can do it.

    July 9, 2015 at 11:00 am
  • Roman Vynar Reply

    Looks like it’s not possible. https://bugs.mysql.com/bug.php?id=23206

    July 9, 2015 at 4:36 pm
  • Shaun McPeck Reply

    I lost a lot of time during my setup because of a crucial setting missing from the my.cnf file. The final winning move for me was:

    ssl-cipher=AES256-SHA:DHE-DSS-AES256-SHA:DHE-RSA-AES256-SHA

    October 4, 2015 at 10:37 am
  • fred mcintyre Reply

    Thanks for the good info. One problem I had that took a while to figure out: In Example 1 (http://dev.mysql.com/doc/refman/5.5/en/creating-ssl-files-using-openssl.html) it uses ca.pem and your instructions use ca-cert.pem. I happily copied/pasted without noticing.

    October 4, 2015 at 8:36 pm
  • sweetfa Reply

    If your server key has a passphrase, how do you configure that, particularly when running mysql as a service

    May 10, 2016 at 4:03 am
    • Shane Bishop Reply

      A bit of an old post, but you would remove the passphrase like so: openssl rsa -in server-key.pem -out server-key.pem
      There is no way to have mysql enter a passphrase for a key (that I’m aware of), when running mysql as a service (and who runs it any other way?).

      September 10, 2016 at 3:25 pm
  • Bob McRae Reply

    I am able to connect via SSL using mysql and the custom perconi.ini (Windows). However, I keep getting access denied using the same host, user, password, and .ini file. Any other things to check?

    July 27, 2016 at 11:12 am
  • Anatoli Reply

    If you tried everything, but SSL is not working, and at the same time you’re running mysqld in chroot, then the cause for the errors like:

    ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)

    or

    ERROR 2026 (HY000): SSL connection error: protocol version mismatch

    could be that you forgot to create dev/random and dev/urandom devices in the chroot environment (and openssl lib can’t obtain entropy – it opens these devices *after* chroot). You can do that this way (replace /srv/mysqld with your chroot dir and mysqld with the user mysqld is running under):

    sudo install -d -o mysqld -g mysqld -m 500 /srv/mysqld/dev
    sudo mknod -m 444 /srv/mysqld/dev/random c 1 8
    sudo mknod -m 444 /srv/mysqld/dev/urandom c 1 9

    December 11, 2016 at 10:18 pm
  • Matthew Schumacher Reply

    on the [root@centos6 ~]# mysql -u ssluser -p -sss -e ‘\s’ | grep SSL
    SSL: Cipher in use is DHE-RSA-AES256-SHA

    is the -sss supposed to be -ssl ?

    January 27, 2017 at 11:59 am
  • tv Reply

    It is possible to have differents certificates for each users (and also prevent a user to use someone else cert) ?
    Thanks

    March 29, 2017 at 7:01 pm
  • Dominic Reply

    This is just pure gold. No bullshit, everything you need.

    September 24, 2017 at 12:05 pm

Leave a Reply