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

How To Set Up MySQL Database Replication With SSL Encryption On CentOS 5.4 This tutorial describes how to set up database replication in MySQL using an SSL connection for encryption (to make it impossible for hackers to sniff out passwords and data transferred between the master and slave). MySQL replication allows you to have an exact copy of a database from a master server on another server (slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync. This is not a backup policy because an accidentally issued DELETE command will also be carried out on the slave; but replication can help protect against hardware failures though.

8 Comment(s)

Add comment

Please register in our forum first to comment.

Comments

By: Log Buffer
Reply  

[...]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

By: IvanV
Reply  

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?

By:
Reply  

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

By: Underlost
Reply  

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.

By: Pierre Dumuid
Reply  

Whilst these steps worked in the past on Ubuntu, recently they failed, because of apparmor not letting mysqld open the certificate files. The following steps fix this:
echo " /path/to/the/certificates/*.pem r," | sudo tee -a /etc/apparmor.d/local/usr.sbin.mysqld
service apparmor restart

By: Anonymous
Reply  

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


By: zaslayer
Reply  

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

By: Ramesh
Reply  

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