Setup mysql master slave replication over ssl

Master Server : 10.10.1.1

Slave Server : 10.10.1.2

Confirm your mysql server is compiled/enabled to support ssl connections using the following command

# mysql -u root -p

mysql > show variables like ‘%ssl%’ ;

If you are getting an output some thing like as follows then you can confirm mysql is compiled to support ssl connections

mysql> show variables like ‘%ssl%’;

| have_openssl | DISABLED |

| have_ssl | DISABLED |

The above shows that mysql is compiled with ssl support but it not enabled in the configuration .

Create Certificates

# cd /var/lib/mysql

# mkdir ssl

>>> 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 Sertificate .

# 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

Copy the keys to Slave server

# scp ca-cert.pem client-cert.pem client-key.pem root@10.10.1.2:/var/lib/mysql/ssl

Master Side configuration

# vi /etc/my.cnf

#bind-address = 127.0.0.1

ssl

ssl-ca=/var/opt/mysql/ssl/ca-cert.pem

ssl-cert=/var/opt/mysql/ssl/server-cert.pem

ssl-key=/var/opt/mysql/ssl/server-key.pem

binlog-do-db=mydatabase

server-id = 1

log_bin = /var/lib/mysql/mysql-bin.log

Note that server id should be unique here for master its 1.

Restart mysql and confirm now ssl values are showing properly in ” mysql > show variables like ‘%ssl%’ ; ”

# mysql –u root –p

GRANT all privileges ON *.* TO replication@'10.10.1.2' IDENTIFIED BY 'password' REQUIRE SSL;

Slave Side Configuration

# vi /etc/my.cnf

bind-address = 0.0.0.0

server-id=2

master-host=10.10.1.1

master-connect-retry=60

replicate-do-db=mydatabase

replicate_ignore_db=dataold

replicate_ignore_db=data_duplicate

slave-skip-errors=all

relay-log=mysql-relay-bin.log

check master status on the master node

mysql > show master status ;

| File | Position | Binlog_do_db | Binlog_ignore_db | +

| mysql-bin.002 | 80600 | mydatabase | | +

 

Update the log location and Position on Slave

Msql > slave stop;

Mysql > CHANGE MASTER TO MASTER_HOST='10.10.1.1', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE=' mysql-bin.002', MASTER_LOG_POS=80600, MASTER_SSL=1, MASTER_SSL_CA = '/var/opt/mysql/ssl/ca-cert.pem', MASTER_SSL_CERT = '/var/opt/mysql/ssl/client-cert.pem', MASTER_SSL_KEY = '/var/opt/mysql/ssl/client-key.pem';

Mysql > slave start ;

Mysql > show slave status \G ;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.10.1.1

Master_User: replication

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 12345100

Relay_Log_File: mysql-relay-bin.000002

Relay_Log_Pos: 11381900

Relay_Master_Log_File: mysql-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: mydatabase

Replicate_Ignore_DB:

The above lines which are marked in green shows that replication is working fine from master to slave.

This entry was posted in Linux, MySQL. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *