MySQL master-slave replication based on SSL encryption

Master MySQL Setup I don't have a lot of accounts.

As the company needs to be based on the public network of MySQL master replication, the requirements of data privacy protection is very strict, through the local area network or WAN to replicate data need to be encrypted, generally based on the public network to do, required to SSL tunnel. No more nonsense.

Environment: Centos6.5

master1:192.168.1.10

master2:192.168.1.30

See if SSL is turned on

Show variables like '%ssl% ';

Turn on SSL

Vim/etc/my.cnf

[Mysqld]

Qs.

Configuring the CA Server

Vim/etc/pki/tls/openssl.cnf

Dir=/etc/pki/ca

mkdir certs Newcerts CRL

Touch Index.txt

echo > Serial

1, generate the key: The CA private key storage location is/etc/pki/ca/private under the general Store name Cakey.pem the name of the permission only the owner has permission (because the file and the configuration file remains)

(umask 077;openssl genrsa-out PRIVATE/CAKEY.PEM 1024)

Command explanation:

Umask 077: Set permissions for the generated files

GENRSA: Generate private key

-out: Private Key storage Path

2048:2048-byte calculation (default = 1024)

OpenSSL req-x509-new-key private/cakey.pem-out cacert.pem-days 365

Command explanation:

Req: Generate certificate Signing request

-new: New Request

-key/path/to/keyfile: Specify the private key file location

-out/path/to/somefile: Specifies that the certificate file is stored in a location

-x509: Generate self-signed certificate

-days N: Specify the number of days to expire

State--province--region--company name--Company department name--CA Server hostname-Admin mailbox

[Email protected]

------------------------------------------------

2. Prepare the private key and issue the certificate for the primary server RS1

Mkdir/var/lib/mysql/ssl

cd/var/lib/mysql/ssl/

Generate key

(Umask 077;openssl genrsa > Master1.key)

Generate a Certificate signing request

OpenSSL Req-new-key master1.key-out MASTER1.CSR

A Challenge Password []:-----------Certificate request key, the CA needs to enter a password when reading the certificate

An optional companies name[]:-----------company name, you need to enter a name when the CA reads the certificate

OpenSSL ca-in master1.csr-out master1.crt-days 365

cp/etc/pki/ca/cacert.pem/var/lib/mysql/ssl/

Chown-r Mysql:mysql/var/lib/mysql/ssl

-----------------------------------------------

3. Prepare the private key and application certificate for MySQL on slave

Create a location to hold the certificate

Mkdir/var/lib/mysql/ssl

Cd/var/lib/mysql/ssl

Create the required certificates

(Umask 077;openssl genrsa > Master2.key)

OpenSSL Req-new-key master2.key-out MASTER2.CSR

SCP./MASTER2.CSR 192.168.1.10:/root/

Issue a certificate for Master2 on Master1

OpenSSL ca-in master2.csr-out master2.crt

SCP Master2.crt/etc/pki/ca/cacert.pem 192.168.1.30:/var/lib/mysql/ssl

Chown-r Mysql.mysql SSL

-------------------------------------------

4. Modify the configuration file

Vim/etc/my.cnf

Join

Ssl-ca=/var/lib/mysql/ssl/cacert.pem

Ssl-cert=/var/lib/mysql/ssl/master1.crt

Ssl-key=/var/lib/mysql/ssl/master1.key

Show variables like '%ssl% ';

+---------------+--------------------------------+

| variable_name | Value |

+---------------+--------------------------------+

| Have_openssl | YES |

| Have_ssl | YES |

| Ssl_ca | /var/lib/mysql/ssl/cacert.pem |

|                                Ssl_capath | |

| Ssl_cert | /VAR/LIB/MYSQL/SSL/MASTER1.CRT |

|                                Ssl_cipher | |

| Ssl_key | /var/lib/mysql/ssl/master1.key |

+---------------+--------------------------------+

Grant replication Slave,replication Client on * * to [e-mail protected] ' 192.168.1.% ' identified by ' 123456 ' require SSL;

Flush privileges;

Show master status;

Change Master to master_host= ' 192.168.1.10 ',

Master_user= ' Repluser ',

Master_password= ' 123456 ',

Master_log_file= ' mysql-bin.000008 ',

master_log_pos=308,

Master_ssl=1,

Master_ssl_ca= '/var/lib/mysql/ssl/cacert.pem ',

Master_ssl_cert= '/var/lib/mysql/ssl/master2.crt ',

master_ssl_key= '/var/lib/mysql/ssl/master2.key ';

(Own)

Start slave;

Show Slave Status\g

This article is from the "Good Big Knife" blog, please make sure to keep this source http://53cto.blog.51cto.com/9899631/1695488

MySQL master-slave replication based on SSL encryption