Scenario : Setup mysql master slave replication over ssl
Master Server : 192.168.1.1
Slave Server : 192.168.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 [email protected]:/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@'192.168.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=192.168.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='192.168.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: 192.168.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.
Follow
Recent Comments