MySQL-based encrypted master-slave replication using SSL

We all know that MySQL master-slave replication is transmitted in plain text, which is not allowed for some special businesses. Next we will try to build an SSL-based master-slave Replication

Environment: RHEL5.8 SELinux disabled, iptables disabled, MySQL 5.5.28-i686 tar package initialization and installation (non-compilation) plan:

 
 

  
  
master: 172.16.1.18  master.laoguang.me 
  
  
slave:  172.16.1.19  slave.laoguang.me 
 
 

Preparations: the hostname is consistent with the plan. Configure/etc/hosts for resolution. The time must be synchronized and the process will not be described in detail. For details, refer to the details section. 1. Configure the CA server on the master and issue a certificate to the master and slave.1.1 master to establish a CA server, the process see the http://laoguang.blog.51cto.com/6013350/10356081.2 master MySQL Certificate Application

 
 

  
  
Mkdir/data/mydata/ssl
  
  
Cd/data/mydata/ssl
  
  
Openssl genrsa 1024> mysql. key
  
  
Openssl req-new-key mysql. key-out mysql. csr-days 3650
  
  
# The following input is the same as that when the CA is created. The common name is master. laoguang. me.
  
  
Openssl ca-in mysql. csr-out mysql. crt # MySQL visa
  
  
Cp/etc/pki/CA/cacert. pem. # copy the CA certificate.
  
  
Chown mysql: mysql *
  
  
Chmod 600 *
 
 

1.3 apply for a certificate on slave

 
 

  
  
Mkdir/data/mydata/ssl
  
  
Cd/data/mydata/ssl
  
  
Openssl genrsa 1024> mysql. key
  
  
Openssl req-new-key mysql. key-out mysql. csr-days 3650
  
  
# The following input is the same as that when the CA is created. The common name is slave. laoguang. me.
  
  
Scp mysql. csr master:/root
 
 

1.4 The master node issues the Server Load balancer instance

 
 

  
  
cd /root 
  
  
openssl ca -in mysql.csr -out mysql.crt 
  
  
scp mysql.crt slave:/data/mydata/ssl 
  
  
scp /etc/pki/CA/cacert.pem slave:/data/mydata/ssl 
 
 

1.5 Change permissions and owner on slave

 
 

  
  
chown mysql:mysql mysql.* 
  
  
chmod 600 mysql.* 
 
 

Ii. Compile/etc/my. cnf on the Master to enable ssl, And set Master/Slave2.1 Modify/etc/my. cnf

 
 

  
  
[Mysqld]
  
  
Log-bin = mysql-bin
  
  
Sync_binlog = 1 # binary log
  
  
Server-id = 1 # This id must be globally unique
  
  
Innodb_flush_log_at_trx_commit = 1 # immediately fl transaction logs to the disk every second
  
  
Ssl ## ssl is disabled by default. in mysql, view show variables like '% ssl % '.
  
  
Ssl_ca =/data/mydata/ssl/cacert. pem # location of the ca File
  
  
Ssl_cert =/data/mydata/ssl/mysql. crt # Certificate file location
  
  
Ssl_key =/data/mydata/ssl/mysql. key # location of the private key file
 
 

2.2 start mysql and view ssl information

 
 

  
  
service mysqld start 
  
  
mysql 
  
  
mysql> show variables like '%ssl%'; 
  
  
+---------------+-----------------------------+ 
  
  
| Variable_name | Value                       | 
  
  
+---------------+-----------------------------+ 
  
  
| have_openssl  | YES                         | 
  
  
| have_ssl      | YES                         | 
  
  
| ssl_ca        | /data/mydata/ssl/cacert.pem | 
  
  
| ssl_capath    |                             | 
  
  
| ssl_cert      | /data/mydata/ssl/mysql.crt  | 
  
  
| ssl_cipher    |                             | 
  
  
| ssl_key       | /data/mydata/ssl/mysql.key  | 
  
  
+---------------+-----------------------------+ 
 
 

2.3 create a minimum permission account for synchronization and require ssl

 
 

  
  
mysql> create user 'backup_ssl'@'172.16.1.19' identified by 'redhat'; 
  
  
mysql> revoke all privileges,grant option from 'backup_ssl'@'172.16.1.19'; 
  
  
mysql> grant replication slave,replication client on *.* to 'backup_ssl'@'172.16.1.19' require ssl; 
  
  
mysql> flush privileges; 
 
 

3. Compile/etc/my. cnf on Slave, enable ssl, and set Master/Slave3.1 editing/etc/my. cnf

 
 

  
  
[Mysqld]
  
  
Server-id = 2 # This id must be globally unique
# Log-bin = mysql-bin # comment out, slave server does not need binary logs
  
  
Relay-log = mysql-ralay # relay logs
  
  
Relay-log-index = mysql-ralay.index # relay directory
  
  
Read-only = 1 # slave server read-only
  
  
Ssl ## ssl is disabled by default. in mysql, view show variables like '% ssl % '.
  
  
Ssl_ca =/data/mydata/ssl/cacert. pem # location of the ca File
  
  
Ssl_cert =/data/mydata/ssl/mysql. crt # Certificate file location
  
  
Ssl_key =/data/mydata/ssl/mysql. key # location of the private key file
 
 

3.2 enable mysqld and view ssl information

 
 

  
  
servie mysqld start 
  
  
mysql> show variables like '%ssl%'; 
  
  
+---------------+-----------------------------+ 
  
  
| Variable_name | Value                       | 
  
  
+---------------+-----------------------------+ 
  
  
| have_openssl  | YES                         | 
  
  
| have_ssl      | YES                         | 
  
  
| ssl_ca        | /data/mydata/ssl/cacert.pem | 
  
  
| ssl_capath    |                             | 
  
  
| ssl_cert      | /data/mydata/ssl/mysql.crt  | 
  
  
| ssl_cipher    |                             | 
  
  
| ssl_key       | /data/mydata/ssl/mysql.key  | 
  
  
+---------------+-----------------------------+ 
 
 

3.3 start the slave synchronization process and connect to the master server

 
 

  
  
Mysql> change master
  
  
-> Master_host = '2017. 16.1.18 ',
  
  
-> Master_user = 'backup _ ssl ',
  
  
-> Master_password = 'redhat ',
  
  
-> Master_log_file = 'mysql-bin.000001 ',
  
  
-> Master_ssl = 1,
  
  
-> Master_ssl_ca = '/data/mydata/ssl/cacert. pem ',
  
  
-> Master_ssl_cert = '/data/mydata/ssl/mysql. crt ',
  
  
-> Master_ssl_key = '/data/mydata/ssl/mysql. key ';
  
  
Mysql> start slave
  
  
Mysql> show slave status \ G; # view slave status
 
 

Follow these parameters:

 
 

  
  
Slave_IO_Running: Yes # Whether IOthread is running. If No is set, the slave is not running properly.
  
  
Slave_ SQL _Running: Yes ## whether SQLthread is running. If No is set, slave is not running properly.
  
  
Master_SSL_CA_File:/data/mydata/ssl/cacert. pem # whether ssl is enabled
  
  
Master_SSL_Cert:/data/mydata/ssl/mysql. crt
  
  
Master_SSL_Key:/data/mydata/ssl/mysql. key
  
  
Master_Log_File: mysql-bin.000023 # binary of the last received master server
  
  
Exec_Master_Log_Pos: 1087 # the last execution location. Check whether the location is in the master.
  
  
Last_IO_Errno: 0 # Is there an error in the last IOthread?
 
 

If it seems like tired, slave is basically normal, the following test Iv. Test4.1 create a database on the master server

 
 

  
  
mysql> create database testssl; 
 
 

4.2 check whether synchronization has been performed on the server

 
 

  
  
mysql> show databases; 
 
 

If the synchronization is successful, no error occurs. 4.3 The slave server mysql connects to the master server based on ssl and checks whether the connection status is encrypted.

 
 

  
  
mysql -ubackup_ssl -predhat -h172.16.1.18 --ssl-cert=/data/mydata/ssl/mysql.crt \
  
  
--ssl-key=/data/mydata/ssl/mysql.key 
 
 

View connection status

 
 

  
  
mysql> status; 
  
  
Current user:       backup_ssl@slave.laoguang.me 
  
  
SSL:            Cipher in use is DHE-RSA-AES256-SHA 
 
 

We can see that the connection is encrypted. You can use tcpdump to capture packets and test that the SSL-based mysql master-slave synchronization has been built. If your slave server is newly added, first, recover the last full backup of the master server to the slave server, and synchronize the binary logs after the full backup, that is, add master_log_op = n when changing the master, n represents the binary location after the complete backup, and the others are basically the same. Note: Today, I tried to only issue a certificate to slave. The master has a CA certificate. Theoretically, it should be successful, but it cannot be connected, so I will give up temporarily, then try to name the master certificate as master. crt, slave certificate is slave. the crt results cannot be connected. Later, google named the master and slave certificates and private keys mysql. crt, mysql. the key is successfully completed. If you know why the document cannot be used, the two certificates cannot be used for different names. Thank you!

This article from the "Free Linux, Share Linux" blog, please be sure to keep this source http://laoguang.blog.51cto.com/6013350/1079787