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

Want to support HowtoForge? Become a subscriber!
 
Submitted by falko (Contact Author) (Forums) on Tue, 2010-02-16 17:13. :: CentOS | MySQL

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

Version 1.0
Author: Falko Timme <ft [at] falkotimme [dot] com>
Follow me on Twitter
Last edited 02/08/2010

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.

I do not issue any guarantee that this will work for you!

 

1 Preliminary Note

In this tutorial I will show how to replicate the database exampledb from the server server1.example.com (master) with the IP address 192.168.0.100 to the server server2.example.com (slave) with the IP address 192.168.0.101. Both systems are running CentOS 5.4; however, the configuration should apply to almost all distributions with little or no modifications. The database exampledb with tables and data is already existing on the master, but not on the slave.

This tutorial also covers SSL encryption for connections from the master to the slave for additional security.

I'm running all the steps in this tutorial with root privileges, so make sure you're logged in as root.

 

2 Installing MySQL 5 And Enabling SSL Support

If MySQL 5 isn't already installed on server1 and server2, install it now:

server1/server2:

yum install mysql mysql-devel mysql-server

Then we create the system startup links for MySQL (so that MySQL starts automatically whenever the system boots) and start the MySQL server:

chkconfig --levels 235 mysqld on
/etc/init.d/mysqld start

Run

server1:

mysqladmin -u root password yourrootsqlpassword
mysqladmin -h server1.example.com -u root password yourrootsqlpassword

server2:

mysqladmin -u root password yourrootsqlpassword
mysqladmin -h server2.example.com -u root password yourrootsqlpassword

to set a password for the user root (otherwise anybody can access your MySQL database!).

Now we must check if both MySQL server support SSL connections. Log into MySQL...

server1/server2:

mysql -u root -p

... and run the following command on the MySQL shell:

show variables like '%ssl%';

If the output is as follows (both have_openssl and have_ssl show DISABLED)...

mysql> show variables like '%ssl%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_key       |          |
+---------------+----------+
7 rows in set (0.01 sec)

mysql>

... it means that MySQL was compiled with SSL support, but it's currently not enabled. To enable it, leave the MySQL shell first...

quit;

... and open /etc/my.cnf:

vi /etc/my.cnf

Add a line with the word ssl to the [mysqld] section:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
ssl

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Restart MySQL...

/etc/init.d/mysqld restart

... and check again if SSL is now enabled:

mysql -u root -p

show variables like '%ssl%';

Output should be as follows which means that SSL is now enabled:

mysql> show variables like '%ssl%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl  | YES   |
| have_ssl      | YES   |
| ssl_ca        |       |
| ssl_capath    |       |
| ssl_cert      |       |
| ssl_cipher    |       |
| ssl_key       |       |
+---------------+-------+
7 rows in set (0.00 sec)

mysql>

Type...

quit;

... to leave the MySQL shell.

 

3 Configuring The Master

First we create a log directory for the MySQL bin-logs:

server1:

mkdir /var/log/mysql
chown mysql:mysql /var/log/mysql

Now we create the CA, server, and client certificates that we need for the SSL connections. I create these certificates in the directory /etc/mysql/newcerts which I have to create first:

mkdir -p /etc/mysql/newcerts && cd /etc/mysql/newcerts

Create CA certificate:

openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

Create server certificate:

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem
openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

Create client certificate:

openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem
openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

The output of...

ls -l

... should now look as follows:

[root@server1 newcerts]# ls -l
total 32
-rw-r--r-- 1 root root 1375 Feb  8 17:37 ca-cert.pem
-rw-r--r-- 1 root root 1679 Feb  8 17:37 ca-key.pem
-rw-r--r-- 1 root root 1119 Feb  8 17:37 client-cert.pem
-rw-r--r-- 1 root root 1675 Feb  8 17:37 client-key.pem
-rw-r--r-- 1 root root  968 Feb  8 17:37 client-req.pem
-rw-r--r-- 1 root root 1119 Feb  8 17:37 server-cert.pem
-rw-r--r-- 1 root root 1679 Feb  8 17:37 server-key.pem
-rw-r--r-- 1 root root  968 Feb  8 17:37 server-req.pem
[root@server1 newcerts]#

We must now transfer ca-cert.pem, client-cert.pem, and client-key.pem to the slave (server2); before we do this, we create the directory /etc/mysql/newcerts on server2:

server2:

mkdir -p /etc/mysql/newcerts

Back on server1, we can transfer the three files to server2 as follows:

server1:

scp /etc/mysql/newcerts/ca-cert.pem root@192.168.0.101:/etc/mysql/newcerts

scp /etc/mysql/newcerts/client-cert.pem root@192.168.0.101:/etc/mysql/newcerts

scp /etc/mysql/newcerts/client-key.pem root@192.168.0.101:/etc/mysql/newcerts

Next, open /etc/my.cnf...

vi /etc/my.cnf

... and add the following ssl-ca, ssl-cert, and ssl-key lines:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
ssl
ssl-ca=/etc/mysql/newcerts/ca-cert.pem
ssl-cert=/etc/mysql/newcerts/server-cert.pem
ssl-key=/etc/mysql/newcerts/server-key.pem

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Restart MySQL:

/etc/init.d/mysqld restart

Now we set up a replication user slave_user that can be used by server2 to access the MySQL database on server1:

mysql -u root -p

On the MySQL shell, run the following commands:

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password' REQUIRE SSL;

The REQUIRE SSL string is optional; if you leave it out, slave_user will be allowed to connect through encrypted and also unencrypted connections. If you use REQUIRE SSL, then only encrypted connections are allowed.

(If you've already set up a replication user, and now want to modify it so that it can only connect through SSL, you can modify the user as follows:

GRANT USAGE ON *.* TO 'slave_user'@'%' REQUIRE SSL;

)

FLUSH PRIVILEGES;
quit;

Furthermore we have to tell MySQL for which database it should write logs (these logs are used by the slave to see what has changed on the master), which log file it should use, and we have to specify that this MySQL server is the master. We want to replicate the database exampledb, so we add the lines server-id, log_bin, expire_logs_days, max_binlog_size, and binlog_do_db in /etc/my.cnf (in the [mysqld] section):

vi /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
ssl
ssl-ca=/etc/mysql/newcerts/ca-cert.pem
ssl-cert=/etc/mysql/newcerts/server-cert.pem
ssl-key=/etc/mysql/newcerts/server-key.pem
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db            = exampledb

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Then restart MySQL:

/etc/init.d/mysqld restart

Next we lock the exampledb database on server1, find out about the master status of server1, create an SQL dump of exampledb (that we will import into exampledb on server2 so that both databases contain the same data), and unlock the database so that it can be used again:

mysql -u root -p

On the MySQL shell, run the following commands:

USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

The last command should show something like this (please write it down, we'll need it later on):

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |  3096416 | exampledb    |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

Now don't leave the MySQL shell, because if you leave it, the database lock will be removed, and this is not what we want right now because we must create a database dump now. While the MySQL shell is still open, we open a second command line window where we create the SQL dump snapshot.sql and transfer it to server2 (using scp; again, make sure that the root account is enabled on server2):

server1:

cd /tmp
mysqldump -u root -pyourrootsqlpassword --opt exampledb > snapshot.sql
scp snapshot.sql root@192.168.0.101:/tmp

Afterwards, you can close the second command line window. On the first command line window, we can now unlock the database and leave the MySQL shell:

server1:

UNLOCK TABLES;
quit;


Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.
Submitted by Anonymous (not registered) on Tue, 2012-11-20 18:21.

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


Submitted by Pierre Dumuid (not registered) on Tue, 2012-06-26 03:01.
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
Submitted by Log Buffer (not registered) on Fri, 2010-02-19 20:41.

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

Submitted by IvanV (not registered) on Sat, 2010-05-15 01:22.

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 'slave_user@192.168.1.22:3306': Error: 'SSL connection error'  errno: 2026  retry-time: 60  retries: 86400
 

 

 

Any idea?

Submitted by Underlost (not registered) on Tue, 2013-07-23 15:49.

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.

Submitted by Tek12 (registered user) on Mon, 2010-06-28 18:03.

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