SSL-based master-slave MySQL Replication

In MySQL master-slave replication, the transmission process is plain text transmission, and data security cannot be guaranteed. During Mysql compilation and installation, a -- with-openssl

In MySQL master-slave replication, the transmission process is plain text transmission, and data security cannot be guaranteed. During Mysql compilation and installation, a -- with-openssl

In the master-slave replication of MySQL, the transmission process is plain text transmission, which does not guarantee data security. When compiling and installing Mysql, an option such as -- with-openssl is basically added, that is, it supports the openssl encrypted transmission protocol. Therefore, you can configure ssl-based encrypted transmission for mysql.

Planning:
Master:
IP Address: 172.16.4.111
MySQL version: 5.5.20
Operating System: RHEL5.4

Slave:
IP Address: 172.16.4.112
MySQL version: 5.5.20
Operating System: RHEL5.4

Because the ssl encrypted transmission protocol is used, it means that the master and slave servers must authenticate each other before transmission, so they must prepare their respective certificates and private keys for the two servers, and the CA certificate that issues the certificate for it. To clarify this, first prepare the private key and certificate for the two servers. You need to configure the Master as a CA server to issue certificates to the Master server.Ps: Configure the server as a CA server. For details, refer to: Openssl usage (set up a CA Server)

1. Set Master/Slave services
On server 172.16.4.111
Edit/etc/my. cnf
# Vim/etc/my. cnf
Change serier_id to 11
Server_id = 11 # modify server_id = 11
Log_bin = mysql-bin # enable binary log
Sync_binlog = 1 # the binary file written to the disk immediately after any transaction is committed.
Innodb_flush_logs_at_trx_commit = 1 # write any transaction to the log file on the disk immediately after it is submitted.
Save and exit
Start mysql
# Service mysqld start

On server 172.16.4.112
Edit/etc/my. cnf
# Vim/etc/my. cnf
Server_id = 12 # modify server_id = 12
# Log-bin # comment out log-bin. The slave server does not need binary logs, so it is disabled.
Relay-log = mysql-relay # define the relay log name and enable the slave server relay log
Relay-log-index = mysql-relay.index # define relay log index name, enable slave server relay index
Read_only = 1 # Set the slave server to only perform read operations, not write operations
Save and exit
Start mysql
# Service mysqld start


Ii. Prepare the certificate and Private Key
1. Configure Master as a CA Server
# Vim/etc/pki/tls/openssl. cnf
Change dir = ../CA
Dir =/etc/pki/CA
# (Umask 077; openssl genrsa 2048> private/cakey. pem)
# Openssl req-new-x509-key private/cakey. pem-out cacert. pem-days 3650
# Mkdir certs crl newcerts
# Touch index.txt
# Echo 01> serial

2. Prepare a private key and issue a certificate for Mysql on the Master node.
# Mkdir/usr/local/mysql/ssl
# Cd ssl/
# (Umask 077; openssl genrsa 1024> mysql. key)
# Openssl req-new-key mysql. key-out mysql. csr
# Openssl ca-in mysql. csr-out mysql. crt
# Cp/etc/pki/CA/cacert. pem/usr/local/mysql/ssl/
# Chown-R mysql. mysql ssl/

3. Prepare the private key for Mysql on Slave and apply for a certificate
# Mkdir/usr/local/mysql/ssl
# Cd ssl/
# (Umask 077; openssl genrsa 1024> mysql. key)
# Openssl req-new-key mysql. key-out mysql. csr
# Scp./mysql. csr 172.16.4.111:/root

4. issue a certificate for Slave on the Master
# Cd
# Openssl ca-in mysql. csr-out mysql. crt
# Scp./mysql. crt 172.16.4.112:/usr/local/mysql/ssl
# Cd/etc/pki/CA
# Scp./cacert. pem 172.16.4.112:/usr/local/mysql/ssl

At this point, the certificate and private key are ready. Make sure that the following files are available in the/usr/local/mysql/ssl directory of the Master and Slave, as well as the owner and group: