Fosiul.com

SSl Replication between 2 Active Active Mysql Server

Bellow commands will verify if mysql server is compiled with ssl supports

SHOW VARIABLES LIKE ‘have_openssl’;

output :

(Yes mean)Mysql Server is compiled with ssl

Creating Self signed certificate :
ref :http://dev.mysql.com/doc/refman/5.1/en/secure-create-certs.html

mkdir /usr/local/mysql/ssl ( I am assuming ,mysql has been compiled at /usr/local/mysql directory)

cd /usr/local/mysql/ssl

# Create CA certificate (Use different common name)

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

# Create server certificate (use different common name)

shell> openssl req -newkey rsa:2048 -days 1000 \
         -nodes -keyout server-key.pem > server-req.pem
shell> 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

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

So it will be like bellow picture

Step 2 :

Copy all these files into Server 2 .Location : /usr/local/mysql/ssl

Reason : we will make Master Master Active Active Replication. There would be ssl encryption between Server1 to server 2 and server2 to server1.
Picture :SSl Replication between 2 Active Active Mysql Server

scp * root@ns2.server2co.uk:/usr/local/mysql/ssl/
(Assume, we are in /usr/local/mysql/ssl directory of Server1)

Step 3:

For Server1 :

Edit my.cnf file add bellow lines in [ Mysqld] sections
 
ssl-key=/usr/local/mysql/ssl/server-key.pem
ssl-cert=/usr/local/mysql/ssl/server-cert.pem
ssl-ca=/usr/local/mysql/ssl/ca-cert.pem
 
[client]
ssl-ca=/usr/local/mysql/ssl/ca-cert.pem
ssl-key=/usr/local/mysql/ssl/client-key.pem
ssl-cert=/usr/local/mysql/ssl/client-cert.pem
 
 
For Server2 :
<pre lang="GNU">
Edit my.cnf file add bellow lines in [ Mysqld] sections
 
ssl-key=/usr/local/mysql/ssl/server-key.pem
ssl-cert=/usr/local/mysql/ssl/server-cert.pem
ssl-ca=/usr/local/mysql/ssl/ca-cert.pem
 
 
[client]
ssl-ca=/usr/local/mysql/ssl/ca-cert.pem
ssl-key=/usr/local/mysql/ssl/client-key.pem
ssl-cert=/usr/local/mysql/ssl/client-cert.pem

Restart the both server, using the –skip-slave-start
ref :href=”http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_skip-slave-start

/usr/local/mysql/bin/mysqld_safe --skip-slave-start --user=mysql &

Now check if both server has ssl linked to accurate directory

Execute bellow command in mysql server console in both server.

mysql> show variables like '%ssl%';

it will give output like bellow picture

Ssl Enabled and its looking to right directory

Step 4 :
Create replication user
For server 1

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave@'ip.of.your.server2' IDENTIFIED BY 'strong-password' require SSL;

For server 2

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave@'ip.of.your.server1' IDENTIFIED BY 'strong-password' require SSL;

Step 5 :
Server 1:
Open firewall rules ,and allow traffic to 3306 port from only ip of server2

Server 2:

Open Firewall rules ,and allow traffic to 3306 port from only ip of server1

Step 5 :

Test if both server accepting ssl connection from each other and its going via Secure ssl encryption
From Server 1/Server 2 :

mysql --ssl -hip-of-server1 -uSSL_CLIENT -ppassword

if everything goes ok then you should see mysql prompt. at the mysql prompt , type
\s to verify that its going through via ssl encryption.

Look at the ssl column for :
SSL:Cipher in use is DHE-RSA-AES256-SHA or similar
Same as bellow picture:

Ssl is enabled

Step 6 :
Connect Serve1 with SErver 2 and SErver 2 with Server 1

Server1 to Server2 :

CHANGE MASTER TO MASTER_HOST='ip.of.your.server2', MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=53678, MASTER_SSL=1,MASTER_SSL_CA = '/usr/local/mysql/ssl/ca-cert.pem', MASTER_SSL_CERT = '/usr/local/mysql/ssl/client-cert.pem', MASTER_SSL_KEY = '/usr/local/mysql/ssl/client-key.pem';

Server2 to Server1

CHANGE MASTER TO MASTER_HOST='ip.of.your.server1', MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=53488, MASTER_SSL=1,MASTER_SSL_CA = '/usr/local/mysql/ssl/ca-cert.pem', MASTER_SSL_CERT = '/usr/local/mysql/ssl/client-cert.pem', MASTER_SSL_KEY = '/usr/local/mysql/ssl/client-key.pem';

NOte : make sure you lock al the tables before taking log file positions and also check the log file position from both server.

Step 6 :
Now start slave server on both server.

 
slave start

Step 7:
Verify if both server looking to each other.

Server1/Server2

show slave status\G;

check if the output is similar with the bellow picture

Check if all slaves looking to each other

Look for bellow options :

Master_Host: xx.xx.xx.xx
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 128108
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /usr/local/mysql/ssl/ca-cert.pem
Master_SSL_Cert: /usr/local/mysql/ssl/client-cert.pem
Master_SSL_Key: /usr/local/mysql/ssl/client-key.pem

Please let me know if there is any problem you face while implementing this.
Thanks

This entry was posted on Wednesday, June 9th, 2010 at 9:27 am and is filed under Linux, MySQL Server. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.