How To Set Up MySQL Database Replication With SSL Encryption On Debian Lenny

Version 1.0
Author: Falko Timme
Follow me on Twitter

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 (master) with the IP address to the server (slave) with the IP address Both systems are running Debian Lenny; 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.

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:


aptitude install mysql-server mysql-client

You will be asked to provide a password for the MySQL root user - this password is valid for the user [email protected] as well as [email protected] / [email protected], so we don't have to specify a MySQL root password manually later on:

New password for the MySQL "root" user: <-- yourrootsqlpassword
Repeat password for the MySQL "root" user: <-- yourrootsqlpassword

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

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


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


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

vi /etc/mysql/my.cnf

Scroll down to the * Security Features section (within the [mysqld] section) and add a line with the word ssl to it:

# * Security Features
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

Restart MySQL...

/etc/init.d/mysql 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)




... to leave the MySQL shell.


3 Configuring The Master

To make sure that the replication can work, we must make MySQL listen on all interfaces on the master (server1), therefore we comment out the line bind-address = in /etc/mysql/my.cnf:


vi /etc/mysql/my.cnf
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           =

Restart MySQL afterwards:

/etc/init.d/mysql restart

Then check with

netstat -tap | grep mysql

that MySQL is really listening on all interfaces on the master:

server1:~# netstat -tap | grep mysql
tcp        0      0 *:mysql                 *:*                     LISTEN      3771/mysqld

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 /etc/mysql/newcerts && cd /etc/mysql/newcerts

Make sure that openssl is installed:

aptitude install openssl

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:

server1:/etc/mysql/newcerts# ls -l
total 32
-rw-r--r-- 1 root root 1346 2010-08-18 20:13 ca-cert.pem
-rw-r--r-- 1 root root 1675 2010-08-18 20:13 ca-key.pem
-rw-r--r-- 1 root root 1099 2010-08-18 20:14 client-cert.pem
-rw-r--r-- 1 root root 1675 2010-08-18 20:14 client-key.pem
-rw-r--r-- 1 root root 956 2010-08-18 20:14 client-req.pem
-rw-r--r-- 1 root root 1099 2010-08-18 20:14 server-cert.pem
-rw-r--r-- 1 root root 1679 2010-08-18 20:14 server-key.pem
-rw-r--r-- 1 root root 956 2010-08-18 20:14 server-req.pem

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:


mkdir /etc/mysql/newcerts

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


scp /etc/mysql/newcerts/ca-cert.pem [email protected]:/etc/mysql/newcerts
  scp /etc/mysql/newcerts/client-cert.pem [email protected]:/etc/mysql/newcerts
  scp /etc/mysql/newcerts/client-key.pem [email protected]:/etc/mysql/newcerts

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

vi /etc/mysql/my.cnf

... and modify the * Security Features section; uncomment the ssl-ca, ssl-cert, and ssl-key lines and fill in the correct values:

# * Security Features
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".

Restart MySQL:

/etc/init.d/mysql 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;



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/enable the following lines in /etc/mysql/my.cnf (in the [mysqld]section):

vi /etc/mysql/my.cnf
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db            = exampledb

Then restart MySQL:

/etc/init.d/mysql 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;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| mysql-bin.000001 |       98 | exampledb    |                  |
1 row in set (0.00 sec)


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


cd /tmp
mysqldump -u root -pyourrootsqlpassword --opt exampledb > snapshot.sql
scp snapshot.sql [email protected]:/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:



Share this page:

4 Comment(s)

Add comment

Please register in our forum first to comment.


By: TripHunter

Thanks for this. Extremely useful post and just what i needed for a travel customer of mine


Falko, as usual, this is an excellent How To!  Thank you.

I discovered that there are some tweaks that are necessary for Drupal.  I am running Drupal 6 multisite on ISPConfig2 on Debian Lenny (I used your ISPConfig/Drupal5 How To to setup the original environment in Drupal5 and have since upgraded to D6 ).

The tweaks mainly involve ignoring some temporary tables that will render your Drupal replication out of sync very quickly.

A list of the key tweaks (some required) are here:

Thanks again for the awesome instructions and hopefully this additional info helps someone; I was pulling my hair out wondering what was wrong!

By: Andy Beverley

Are the permissions really correct in the directory listing above? Surely the private keys should be only readable by mysql?

By: razvan

Thank you for your tutorial.

It was great and useful, but for Ubuntu 12.10 has two problems that did not allow me to activate  the SSL for master.

1. The generated keys for server and client are missing RSA and from header. This generates a  "SSL error: Unable to get private key from '/etc/mysql/server-key.pem' " in "/var/log/mysql/error.log".

So instead of

shoud be

 and instead of

  -----END PRIVATE KEY-----
should be

See:      MySQL :: Re: MySQL 5.5.8 SSL error: Unable to get private key from 'server-key.pem'

 2. The generated files should be placed in /etc/mysql/
At least this was the solution that worked for me.

All the best.