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

4 Configuring The Slave

Now we must configure the slave. Open /etc/my.cnf and make sure you have the following settings (server-id, master-connect-retry, replicate-do-db) in the [mysqld] section:

server2:

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
server-id=2
master-connect-retry=60
replicate-do-db=exampledb

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

The value of server-id must be unique and thus different from the one on the master!

Restart MySQL afterwards:

/etc/init.d/mysqld restart

Before we start setting up the replication, we create an empty database exampledb on server2:

mysql -u root -p

CREATE DATABASE exampledb;
quit;

On server2, we can now import the SQL dump snapshot.sql like this:

/usr/bin/mysqladmin --user=root --password=yourrootsqlpassword stop-slave
cd /tmp
mysql -u root -pyourrootsqlpassword exampledb < snapshot.sql

Now connect to MySQL again...

mysql -u root -p

... and run the following command to make server2 a slave of server1 (it is important that you replace the values in the following command with the values you got from the SHOW MASTER STATUS; command that we ran on server1!):

CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=3096416, MASTER_SSL=1, MASTER_SSL_CA = '/etc/mysql/newcerts/ca-cert.pem', MASTER_SSL_CERT = '/etc/mysql/newcerts/client-cert.pem', MASTER_SSL_KEY = '/etc/mysql/newcerts/client-key.pem';

  • MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.100).
  • MASTER_USER is the user we granted replication privileges on the master.
  • MASTER_PASSWORD is the password of MASTER_USER on the master.
  • MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master.
  • MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.
  • MASTER_SSL makes the slave use an SSL connection to the master.
  • MASTER_SSL_CA is the path to the ca-cert.pem file on the slave.
  • MASTER_SSL_CERT is the path to the client-cert.pem file on the slave.
  • MASTER_SSL_KEY is the path to the client-key.pem file on the slave.

Finally start the slave:

START SLAVE;

Then check the slave status:

SHOW SLAVE STATUS \G

It is important that both Slave_IO_Running and Slave_SQL_Running have the value Yes in the output (otherwise something went wrong, and you should check your setup again and take a look at /var/log/syslog to find out about any errors); as you're using an SSL connection now, you should also find values in the fields Master_SSL_Allowed, Master_SSL_CA_File, Master_SSL_Cert, and Master_SSL_Key:

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.0.100
                Master_User: slave_user
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 3096416
             Relay_Log_File: mysqld-relay-bin.000002
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: exampledb
        Replicate_Ignore_DB:
         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: 3096416
            Relay_Log_Space: 235
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: Yes
         Master_SSL_CA_File: /etc/mysql/newcerts/ca-cert.pem
         Master_SSL_CA_Path:
            Master_SSL_Cert: /etc/mysql/newcerts/client-cert.pem
          Master_SSL_Cipher:
             Master_SSL_Key: /etc/mysql/newcerts/client-key.pem
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

mysql>

Afterwards, you can leave the MySQL shell on server2:

quit;

That's it! Now whenever exampledb is updated on the master, all changes will be replicated to exampledb on the slave. Test it!

 

5 Links

Share this page:

11 Comment(s)

Add comment

Comments

From: Log Buffer at: 2010-02-19 19:41:33

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

From: IvanV at: 2010-05-15 00:22:50

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?

From: at: 2010-06-28 17:03:07

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

From: Pierre Dumuid at: 2012-06-26 02:01:59

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

From: Anonymous at: 2012-11-20 17:21:18

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


From: Underlost at: 2013-07-23 14:49:28

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.

From: fabio at: 2011-05-16 14:38:22

Thanks for the useful article, my question is: How (and if is it possible) set up a replica "single master" against "multi slave" over ssl?

From: Dhaval at: 2011-07-12 20:32:10

I am getting below error in mysql.log after following mentioned steps:


 110713  1:42:40 [ERROR] Slave I/O thread: error connecting to master 'slave_user@192.168.1.2:3306': Error: 'SSL connection error'  errno: 2026  retry-time: 60  retries: 86400


 My "SHOW SLAVE STATUS \G" is as below:


*************************** 1. row ***************************
             Slave_IO_State: Connecting to master
                Master_Host: 192.168.1.2
                Master_User: slave_user
                Master_Port: 3306
                Connect_Retry: 60
                Master_Log_File: mysql-bin.000001
                Read_Master_Log_Pos: 98
                Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 98
                Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No
             Slave_SQL_Running: Yes
               Replicate_Do_DB: mysql
               Replicate_Ignore_DB:
               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: 98
               Relay_Log_Space: 98
               Until_Condition: None
               Until_Log_File:
               Until_Log_Pos: 0
               Master_SSL_Allowed: Yes
               Master_SSL_CA_File: /etc/mysql/newcerts/ca-cert.pem
               Master_SSL_CA_Path:
               Master_SSL_Cert: /etc/mysql/newcerts/client-cert.pem
               Master_SSL_Cipher:
               Master_SSL_Key: /etc/mysql/newcerts/client-key.pem
               Seconds_Behind_Master: NULL
1 row in set (0.00 sec)


 How to solve this problem to get master-slave server ready ?


From: ctc at: 2011-10-21 13:16:19

common name of server cert and client cert _MUST_ be different.

 

From: Stu at: 2012-06-17 10:56:55

Thanks for the easy to follow guide. Worked well, with the last piece in the puzzle re requiring different common names for the certs.

From: Johan at: 2012-12-12 21:25:12

The procedure to set up and encrypt replication worked great for me.  Is there a way to check that the replication data are indeed encrypted?  Perhaps using tcpdump?

 Thanks