To protect data while it is transferred throughout the Internet can be done using SSL. For example if you need to replicate mysql data from one datacenter to another.
This is the instruction how to setup MySQL master-slave replication with ssl.
1. Create the root key:
|
1 2 3 |
mkdir -p /home/mysql_msrepl cd /home/mysql_msrepl openssl genrsa 2048 > ca-key.pem |
2. Create the root certificate:
|
1 |
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem |
Running the command above we need to answer several questions. e.g.:
|
1 2 3 4 5 6 7 |
Country Name (2 letter code) [AU]:AU State or Province Name (full name) [Some-State]: Locality Name (eg, city) []: Organization Name (eg, company) [Internet Widgits Pty Ltd]:SomeCompany Organizational Unit Name (eg, section) []: Common Name (e.g. server FQDN or YOUR name) []:mysqladmin Email Address []: |
3. Create a certificate for MASTER server and sign it with the root certificate generated above(steps 1-2):
|
1 2 3 |
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout db1-key.pem -out db1-req.pem openssl rsa -in db1-key.pem -out db1-key.pem openssl x509 -req -in db1-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out db1-cert.pem |
4. Create a certificate for SLAVE and sign it with the root certificate generated above(steps 1-2):
|
1 2 3 |
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout db2-key.pem -out db2-req.pem openssl rsa -in db2-key.pem -out db2-key.pem openssl x509 -req -in db2-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out db2-cert.pem |
5. Upload to the MASTER server files ca-cert.pem, db1-cert.pem, db1-key.pem to the /etc/mysql/openssl:
6. At the MASTER server edit my.cnf. Append mysqld section with:
|
1 2 3 4 5 |
[mysqld] ssl-ca = /etc/mysql/openssl/ca-cert.pem ssl-cert = /etc/mysql/openssl/db1-cert.pem ssl-key = /etc/mysql/openssl/db1-key.pem |
7. On the MASTER server create a user for replication:
|
1 2 |
CREATE USER 'repl'@'real.ip.slave.server' IDENTIFIED BY 'VerySecRetPassWord'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'real.ip.slave.server' REQUIRE SSL; |
8. Upload to the SLAVE server files ca-cert.pem, db2-cert.pem, db2-key.pem to the /etc/mysql/openssl:
9. On the SLAVE server edit my.cnf. Define the database to replicate. e.g. database1:
|
1 |
replicate-do-db = database1 |
10. On the SLAVE server execute CHANGE MASTER TO:
|
1 |
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='VerySecRetPassWord', MASTER_HOST='real.ip.slave.server', MASTER_SSL=1, MASTER_SSL_CA='/etc/mysql/openssl/ca-cert.pem', MASTER_SSL_CERT='/etc/mysql/openssl/db2-cert.pem', MASTER_SSL_KEY='/etc/mysql/openssl/db2-key.pem'; |
11. Start Replication(on the SLAVE server)
|
1 |
mysql> START SLAVE; |
12. Check replication status(on the SLAVE server):
|
1 |
mysql> show slave status \G |
That’s it!
P.S. This article doesn’t cover whole replication setup. It describes the part where ssl is used.