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!

 

Share this page:

5 Comment(s)

Add comment

Comments

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 '[email protected]: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