SSL ENCRYPTED MYSQL REPLICATION

Set up the master server

Setting up the master consists of the following steps:

1. Generate SSL certificates.
2. Edit MySQL configuration my.cnf.
3. Restart MySQL server process.
4. Set up database replication privileges (and force SSL).
5. Perform initial database backup to start replication from.
6. Finish up.

Set up the slave server

The steps for the slave server are:

1. Setup the SSL certificates.
2. Edit MySQL server configuration my.cnf.
3. Restart MySQL server.
4. Configure and start slave server.
5. Finish up.

Detailed steps:

we generate the CA certificate:
$ mkdir ~/mysql-tutorial/ && cd ~/mysql-tutorial/
$ openssl genrsa 2048 > ca-key.pem
$ openssl req -new -x509 -nodes -days 1000 \  -key ca-key.pem -out ca-cert.pem

Create the server certificate, remove its passphrase and sign it:
$ openssl req -newkey rsa:2048 -days 1000 \  -nodes -keyout server-key.pem -out server-req.pem

$ openssl rsa -in server-key.pem -out server-key.pem

$ openssl x509 -req -in server-req.pem -days 1000 \  -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem


Create the client certificate, remove its passphrase and sign it

$ openssl req -newkey rsa:2048 -days 1000 \  -nodes -keyout client-key.pem -out client-req.pem

$ openssl rsa -in client-key.pem -out client-key.pem

$ openssl x509 -req -in client-req.pem -days 1000 \  -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

Install the certificates into to /etc/mysql/ directory:
$ sudo cp *.pem /etc/mysql/

Skip the bind address
assign the id to mysql server and enable the bin log And enable SSL by editing and add the following lines to my.cnf :
ssl-ca=/etc/mysql/ca-cert.pem
ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem

Restart  the mysql server:

sudo service mysql restart

mysql -u root -p -h <host-name>

GRANT REPLICATION SLAVE ON *.*
TO '<slave_user>'@’%'
IDENTIFIED BY '<slave_password>'
REQUIRE SSL;
FLUSH PRIVILEGES;
USE <database name>;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

note the log position and log file name
example. binlog  filename mysql-bin.000003  position :1122
Take the mysql dump  
mysqldump -h localhost -u root -p --opt <database> > ~/<database>.sql

UNLOCK TABLES;
quit;

scp ~/<database>.sql  <username>@<ipaddress>:/path/to/paste/

On The Slave

Edit the slave server

Add the id and skip the networking and bind address of the server.
Id of both the server should not be same

STOP SLAVE;
CREATE DATABASE IF NOT EXISTS <database>;
USE <database>;
SOURCE /path/to/<database>.sql;

CHANGE MASTER TO
MASTER_HOST='<master’s ip>',
MASTER_USER='slave_user',
MASTER_PASSWORD='<password>',
MASTER_CONNECT_RETRY=60,
MASTER_LOG_FILE='<binlog file name>',
MASTER_LOG_POS=<position>,
MASTER_SSL=1,
MASTER_SSL_CA='/etc/mysql/ca-cert.pem',
MASTER_SSL_CERT='/etc/mysql/client-cert.pem',
MASTER_SSL_KEY='/etc/mysql/client-key.pem';
START SLAVE;
SHOW SLAVE STATUS \G;