Comments on How To Set Up MySQL Database Replication With SSL Encryption On CentOS 5.4

[...]Speaking of distros, here is Falko’s How To Set Up MySQL Database Replication With SSL Encryption On CentOS 5.4 on HowtoForge.[...]

Log Buffer #179

This tutorial fails with selfsigned certs.

 [root@localhost newcerts]# openssl verify -CAfile ca-cert.pem mysqlreplclient-cert.pem
mysqlreplclient-cert.pem: /C=GB/ST=Berkshire/L=Newbury/O=My Company Ltd
error 18 at 0 depth lookup:self signed certificate

100515 01:54:52  mysqld started
100515  1:54:52 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
100515  1:54:52 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
100515  1:54:52 [Warning] The syntax for replication startup options is deprecated and will be removed in MySQL 5.2. Please use 'CHANGE MASTER' instead.
100515  1:54:52  InnoDB: Started; log sequence number 0 43665
100515  1:54:52 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysqld-relay-bin' to avoid this problem.
100515  1:54:52 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000001' at position 3096416, relay log './mysqld-relay-bin.000001' position: 98
100515  1:54:52 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.77'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
100515  1:54:52 [ERROR] Slave I/O thread: error connecting to master '[email protected]:3306': Error: 'SSL connection error'  errno: 2026  retry-time: 60  retries: 86400
 

Any idea?

When you are configuring the master in Step 3, use unique common names and the rest will work fine.  I found the answer here:

http://bugs.centos.org/view.php?id=4230

In my case the issue was an incompatibility between MySQL 5.0.X and OpenSSL 0.9.8.

Updating to MySQL 5.1.X fixed the problem.

There are similar problems for other combinations of MySQL and OpenSSL versions.

echo " /path/to/the/certificates/*.pem r," | sudo tee -a /etc/apparmor.d/local/usr.sbin.mysqld
service apparmor restart

Watch out for apparmor in Ubuntu server. If you follow this tutorial and put your .pem files in /etc/mysql/newcerts sub-directory, you must modify the rules for mysql in apparmor.

see this: http://blog.cboyer.net/2011/04/mysql-ssl-problem-on-ubuntu-server.html

apparmor by default allows mysql to read /etc/mysql/*.pem but not the files in /etc/mysql/newcerts so alternative solution is just moving all your .pem files to /etc/mysql

in my case running on centos 6.8 i had to chown my /etc/mysql dir in order for mysql to read the certs files.. without this, mysql remained adamant that ssl was disabled..

Very nice writeup, it helped us setting up the SSL replication. 

we just faced one issue.

SSL was unable to start with the following error:

SSL error: Unable to get private key from '/etc/mysql/newcerts/server-key.pem'

2017-02-09  5:51:07 140006523795296 [Warning] Failed to setup SSL

2017-02-09  5:51:07 140006523795296 [Warning] SSL error: Unable to get private key

--

we had to change the key with RSA by running the command:

openssl rsa -in /etc/mysql/newcerts/server-key.pem -out /etc/mysql/newcerts/server-key.pem

the same on slave as well for the client-key.pem

openssl rsa -in /etc/mysql/newcerts/client-key.pem -out /etc/mysql/newcerts/client-key.pem