MySQL Server Replication with ssl

via Bhushan

TYPES OF MYSQL REPLICATION

• Statement-based Replication

• Row-based Replication

• Mixed

  To change the type of Replication modify my.cnf configuration file and change

  binlog_format=mixed | row | statement

mysql> SHOW VARIABLES LIKE ‘binlog_format’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| binlog_format | MIXED

+—————+——-+

Processes/Threads inside MySQL that are responsible for replication

• MASTER - Binlog Dump Thread

• SLAVE - I/O Thread

  • • • • SQL Thread

Statements useful to check the status of these threads as replication goes:

mysql> SHOW PROCESSLIST\G

mysql> SHOW MASTER STATUS\G

mysql> SHOW SLAVE STATUS\G

Directories and File Locations

• Datadir – /var/lib/mysql

• General Log dir. - /var/log

• Bin Log dir. - /var/log/mysql

• Configuration file - /etc/mysql/my.cnf

• SSL Certificates - /etc/mysql/ssl

• Relay Log file - /var/lib/mysql/slavehost-relay-bin.NNNNNN

• Status Files - master.info, relay-log.info

Note:

- All modification/updates to data should be done on Master only, and not on any Slave. Slave should be used for queries.

setup replication

MySQL MASTER = 192.168.1.100:3306

MySQL SLAVE = 192.168.1.111:3306

MASTER host

root@sage:~# mkdir /etc/mysql/ssl

root@sage:~# cd /etc/mysql/ssl/

root@sage:~# rm -rf *

Create CA certificate

root@sage:~# openssl genrsa 2048 > ca-key.pem

root@sage:~# openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem

Create server certificate

root@sage:~# openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem

root@sage:~# openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

Copy ca-cert file to MySQL clients & slaves

root@sage:~# scp ca-cert.pem root@slave-host-IP:/etc/mysql/ssl/

Modify configuration file

root@sage:~# vi /etc/mysql/my.cnf

Enable Binary logging in Mixed format. And specify a Unique Server ID of Master

[mysqld]

log-bin = /var/log/mysql/mysql-bin

binlog_format = mixed

server-id = 1

ssl-key = /etc/mysql/ssl/server-key.pem

ssl-cert = /etc/mysql/ssl/server-cert.pem

ssl-ca = /etc/mysql/ssl/ca-cert.pem

Test SSL connectivity using MySQL-Client

root@sage:~# /etc/init.d/mysql restart

root@sage:~# mysql --ssl-ca=/etc/mysql/ssl/ca-cert.pem -u root -p

mysql> SHOW VARIABLES LIKE ‘%ssl%’;

+—————+——————————–+

| Variable_name | Value |

+—————+——————————–+

| have_openssl | YES |

| have_ssl | YES |

| ssl_ca | /etc/mysql/ssl/ca-cert.pem |

| ssl_capath | |

| ssl_cert | /etc/mysql/ssl/server-cert.pem |

| ssl_cipher | |

| ssl_key | /etc/mysql/ssl/server-key.pem |

+—————+——————————–+

mysql> SHOW STATUS LIKE ‘Ssl_cipher’;

+—————+——————–+

| Variable_name | Value |

+—————+——————–+

| Ssl_cipher | DHE-RSA-AES256-SHA |

+—————+——————–+

confirms that SSL is supported & enabled on MASTER

Create mysql user on master that has the privileges to do replication.

mysql -u root -p

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO replssl@’%’ IDENTIFIED BY ‘replipass’ REQUIRE SSL;

If user already exists

mysql> GRANT USAGE ON *.* TO 'repl'@'%' REQUIRE SSL;

mysql> FLUSH PRIVILEGES;

mysql> SHOW GRANTS FOR repl;

Find the location where Master is writing now

mysql> show master status;

+—————-+——–+————-+—————+

| File | Position |Binlog_Do_DB |Binlog_Ignore_DB

+—————-+——–+————-+——————

|mysql-bin.000004| 7705 | |

+—————-+——–+————-+—————–

They are: mysql-bin.000004, 7705

Take snapshot of Mysql data on Master and then scp it to slave.

mysql> FLUSH TABLES WITH READ LOCK;

root@sage:~# tar czvf ~/mysql-snapshot.tar.gz /var/lib/mysql

mysql> UNLOCK TABLES;

Copy snapshot to the slave

root@sage:~# scp mysql-snapshot.tar/gz user@slave-IP:~

SLAVE side

To configure this host as a replication slave, you can choose between

two methods :

- Use the CHANGE MASTER TO command

CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,

MASTER_USER=<user>, MASTER_PASSWORD=<password> ……

OR

- Set the variables in /etc/mysql/my.cnf.

Create client certificate

root@sage:~# openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem

root@sage:~# openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

root@sage:~# vi /etc/mysql/my.cnf

[mysqld]

server-id = 2

master-host = 192.168.1.100

master-user = repl

master-password = replipass

master-port = 3306

log-bin = /var/log/mysql/mysql-bin

binlog_format = mixed

tmpdir = /tmp/

[client]

ssl-ca=/etc/mysql/ssl/ca-cert.pem

#ssl-key=/etc/mysql/ssl/client-key.pem

#ssl-cert=/etc/mysql/ssl/client-cert.pem

“If the account has no special SSL requirements or was created using a GRANT statement that includes the REQUIRE SSL option, a client can connect securely by using just the --ssl-ca option:

shell> mysql --ssl-ca=cacert.pem

To require that a client certificate also be specified, create the account using the REQUIRE X509 option. Then the client must also specify the proper client key and certificate files or the server will reject the connection:

shell> mysql --ssl-ca=cacert.pem \
       --ssl-cert=client-cert.pem \
       --ssl-key=client-key.pem

In other words, the options are similar to those used for the server. Note that the Certificate Authority certificate has to be the same. “

Ref: http://dev.mysql.com/doc/refman/5.0/en/secure-using-ssl.html

root@sage:~# /etc/init.d/mysql restart

Test connectivity to Master from Slave

root@sage:~# mysql –ssl-ca=/etc/mysql/ssl/ca-cert.pem -u root -p -h 192.168.1.100

root@sage:~# mysql -u root -p

mysql> SLAVE STOP;

mysql> mysql> CHANGE MASTER TO MASTER_HOST=’192.168.1.100′, MASTER_PORT=3306, MASTER_USER=’replssl’, MASTER_PASSWORD=’1′,MASTER_LOG_FILE=’mysql-bin.000004′, MASTER_LOG_POS=7705, MASTER_SSL=1, MASTER_SSL_CA=’/etc/mysql/ssl/ca-cert.pem’;

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS\G

mysql> SHOW PROCESSLIST\G

Note : If we have given only GRANT … REQUIRE SSL to replication user then MASTER_SSL=1, MASTER_SSL_CA are to be specidfied. ITo require that a client certificate also be specified, create the account using the REQUIRE X509 option.

Related Posts:

• No Related Posts