Setting up MySQL replication with SSL

There are many posts out there on how to setup both MySQL replication and self signed SSL certificates but not really any that combine the 2 very well. This post is partly for my own reference and partly for anyone else who is interested in the subject.

To enable SSL replication both the client and server require signed certificates, these could be a purchased certificate from a common CA but really as they are only going to be used for private communication there is no need and a self signed certificate will do the job.

Please note, that for the section of this post which discusses the MySQL replication, I am assuming that you already know how to setup replication, including ensuring data is consistent on master/slave. The focus of his article is to document the additional configuration required to setup replication with SSL.

For the purpose of this example I will be configuring the following hosts:

master.example.com - The MySQL replication master
slave.example.com - The MySQL replication slave

Creating the certificates:

The first thing to do is to create a certificate authority in order to sign the certificates, this is based on the Ubuntu Documentation with a few changes.

First, create some directories to organise things and change into the new CA directory:

mkdir -p CA/private
mkdir -p CA/certs
cd CA

Create a certificate database:

echo '01' > serial  && touch index.txt

Create a configuration for the CA:

nano ca.conf
# Example CA config file
#
# Default configuration to use when one is not provided on the command line.
#
[ ca ]
default_ca      = local_ca
#
#
# Default location of directories and files needed to generate certificates.
#
[ local_ca ]
dir             = /home/<strong style="color:red">user</strong>/CA
certificate     = $dir/ca.pem
database        = $dir/index.txt
new_certs_dir   = $dir/certs
private_key     = $dir/private/cakey.pem
serial          = $dir/serial
#      
#
# Default expiration and encryption policies for certificates.
#
default_crl_days        = 365
default_days            = 1825
default_md              = md5
#      
policy          = local_ca_policy
x509_extensions = local_ca_extensions
#      
#
# Default policy to use when generating server certificates.  The following
# fields must be defined in the server certificate.
#
[ local_ca_policy ]
commonName              = supplied
stateOrProvinceName     = supplied
countryName             = supplied
emailAddress            = supplied
organizationName        = supplied
organizationalUnitName  = supplied
#      
#
# x509 extensions to use when generating server certificates.
#
[ local_ca_extensions ]
subjectAltName          = DNS:example.com
basicConstraints        = CA:false
nsCertType              = server
#      
#
# The default root certificate generation policy.
#
[ req ]
default_bits    = 2048
default_keyfile = /home/<strong style="color:red">user</strong>/CA/private/cakey.pem
default_md      = md5
#      
prompt                  = no
distinguished_name      = root_ca_distinguished_name
x509_extensions         = root_ca_extensions
#
#
# Root Certificate Authority distinguished name.  Change these fields to match
# your local environment!
#
[ root_ca_distinguished_name ]
commonName              = <strong style="color:green">My  Certificate Authority</strong>
stateOrProvinceName     = <strong style="color:green">London</strong>
countryName             = <strong style="color:green">GB</strong>
emailAddress            = <strong style="color:green">ca@example.com</strong>
organizationName        = <strong style="color:green">My Company Name</strong>
organizationalUnitName  = <strong style="color:green">IT Department</strong>
#      
[ root_ca_extensions ]
basicConstraints        = CA:true

You will need to change the user in red with your Linux username and the CA info in green.

Now generate the CA certificate with the following:

openssl req -x509 -newkey rsa:2048 -out ca.pem -outform PEM -days 1825 -config ca.conf

You will be prompted for a passphrase:

Generating a 2048 bit RSA private key
...............................................+++
...................................................................................+++
writing new private key to '/home/phil/CA/private/cakey.pem'
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
-----

We now have a certificate and key which we can use to sign the certificates. The next stage is to create certificates for each of our 2 hosts (or more, if more than 2 hosts are being configured in the replication setup).

Create a config file for each host:

nano master.conf
#
# Config file for master.example.com
#

[ req ]
prompt                  = no
distinguished_name      = server_distinguished_name

[ server_distinguished_name ]
commonName              = <strong style="color:green">master.example.com</strong>
stateOrProvinceName     = <strong style="color:green">London</strong>
countryName             = <strong style="color:green">GB</strong>
emailAddress            = <strong style="color:green">ca@example.com</strong>
organizationName        = <strong style="color:green">My Company Name</strong>
organizationalUnitName  = <strong style="color:green">IT Department</strong>
nano slave.conf
#
# Config file for slave.example.com
#

[ req ]
prompt                  = no
distinguished_name      = server_distinguished_name

[ server_distinguished_name ]
commonName              = <strong style="color:green">slave.example.com</strong>
stateOrProvinceName     = <strong style="color:green">London</strong>
countryName             = <strong style="color:green">GB</strong>
emailAddress            = <strong style="color:green">ca@example.com</strong>
organizationName        = <strong style="color:green">My Company Name</strong>
organizationalUnitName  = <strong style="color:green">IT Department</strong>

Change the bits in green to your company name, the commonName must be the host name of the machine, this is important or the certificate will be invalid.

The next step is to create a certificate signing request (CSR) and private key for each of our hosts:

openssl req -newkey rsa:1024 -keyout master.key.pem -keyform PEM -out master.csr.pem -outform PEM -config master.conf

You will be prompted for a passphrase again:

Generating a 1024 bit RSA private key
..++++++
.++++++
writing new private key to 'master.key.pem'
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
-----

Repeat for slave (and additional hosts):

openssl req -newkey rsa:1024 -keyout slave.key.pem -keyform PEM -out slave.csr.pem -outform PEM -config slave.conf
Generating a 1024 bit RSA private key
....................++++++
....++++++
writing new private key to 'slave.key.pem'
Enter PEM pass phrase:
Verifying - Enter PEM pass phrase:
-----

Now in their current form the keys we have just generated will require the passphrase every time we start the server, this is far from ideal as MySQL will have to be manually started every time the server is rebooted (intentionally or not).

We can create an unencrypted version of the keys so that the passphrase is not required for server starts:

openssl rsa < master.key.pem > master.dckey.pem
Enter pass phrase:
writing RSA key
openssl rsa < slave.key.pem > slave.dckey.pem
Enter pass phrase:
writing RSA key

Finally, we can sign the certificate requests for each host with our CA:

openssl ca -in master.csr.pem -out master.crt.pem -config ca.conf
Using configuration from ca.conf
Enter pass phrase for /home/phil/CA/private/cakey.pem:
Check that the request matches the signature
Signature ok
The Subject's Distinguished Name is as follows
commonName            :PRINTABLE:'master.example.com'
stateOrProvinceName   :PRINTABLE:'London'
countryName           :PRINTABLE:'GB'
emailAddress          :IA5STRING:'ca@example.com'
organizationName      :PRINTABLE:'My Company Name'
organizationalUnitName:PRINTABLE:'IT Department'
Certificate is to be certified until Aug 26 08:56:20 2015 GMT (1825 days)
Sign the certificate? [y/n]:y

1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated
openssl ca -in slave.csr.pem -out slave.crt.pem -config ca.conf
Using configuration from ca.conf
Enter pass phrase for /home/phil/CA/private/cakey.pem:
Check that the request matches the signature
Signature ok
The Subject's Distinguished Name is as follows
commonName            :PRINTABLE:'slave.example.com'
stateOrProvinceName   :PRINTABLE:'London'
countryName           :PRINTABLE:'GB'
emailAddress          :IA5STRING:'ca@example.com'
organizationName      :PRINTABLE:'My Company Name'
organizationalUnitName:PRINTABLE:'IT Department'
Certificate is to be certified until Aug 26 08:57:52 2015 GMT (1825 days)
Sign the certificate? [y/n]:y

1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated

Configuring the servers:

We will need to copy 3 files to each machine:

ca.pem – The CA certificate

On the master:

master.crt.pem – The master certificate
master.key.pem or master.dckey.pem – Either the encrypted or decrypted version of the master private key

On the slave:

slave.crt.pem – The slave certificate
slave.key.pem or slave.dckey.pem – Either the encrypted or decrypted version of the slave private key

These files should be copied into /etc/mysql on the respective hosts.

On the master, create a user for replication:

CREATE USER slave@slave.example.com IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO slave@slave.example.com REQURE SSL;

Note that the addition of the ‘REQUIRE SSL’ at the end of the grant statement, this means insecure connections from the slave will not be possible.

Create the configuration on the master for replication:

server-id = 1
report_host = master
# These should be used for multiple masters
#auto_increment_increment = 2
#auto_increment_offset   = 1
log_bin = /var/log/mysql/binlog
log_bin_index = /var/log/mysql/binlog.index
sync_binlog = 1
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = mydb
#binlog_do_db = myotherdb
binlog_ignore_db = mysql

Also we need to add the SSL configuration:

ssl-ca = /etc/mysql/ca.pem
ssl-cert = /etc/mysql/master.crt.pem
ssl-key = /etc/mysql/master.dckey.pem

Restart MySQL to apply the configuration:

sudo /etc/init.d/mysql restart

or (in newer versions with upstart):

restart mysql

Then configure the slave for replication:

server-id = 2
report_host = slave
relay_log = /var/log/mysql/relay-bin
relay_log_index = /var/log/mysql/relay-bin.index
relay_log_info_file = /var/log/mysql/relay-bin.info
master_host = master.example.com
master_user = slave
master_password = password
master_port = 3306
master_ssl = 1
master_ssl_ca = /etc/mysql/ca.pem
master_ssl_cert = /etc/mysql/slave.crt.pem
master_ssl_key = /etc/mysql/slave.dckey.pem
replicate_do_db = mydb
#replicate_do_db = myotherdb
replicate_ignore_db = mysql
read_only

Restart MySQL, and everything should be up and running.

From the master we should see something like this:

show master status\G
*************************** 1. row ***************************
            File: binlog.000001
        Position: 106
    Binlog_Do_DB: mydb
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
show slave hosts\G
*************************** 1. row ***************************
        Server_id: 2
             Host: slave
             Port: 3306
Rpl_recovery_rank: 0
        Master_id: 1
1 row in set (0.00 sec)

And on the slave:

show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master.example.com
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 106
               Relay_Log_File: relay-bin.000001
                Relay_Log_Pos: 253
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: mydb
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 106
              Relay_Log_Space: 549
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/mysql/ca.pem
           Master_SSL_CA_Path:
              Master_SSL_Cert: /etc/mysql/slave.crt.pem
            Master_SSL_Cipher:
               Master_SSL_Key: /etc/mysql/slave.dckey.pem
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.02 sec)

Note: The status info above I copy/pasted from a live replication setup and replaced with values that you can expect to see, values will likely differ (especially log file positions).

Share