Mysql detailed Operation tutorial of master-slave copying based on SSL protocol _mysql

When MySQL is replicating across the internet, others can steal information about MySQL's replication, which is plaintext, so there is no security, where the replicated information is encrypted via SSL. When the client does not have a fixed IP to access the server, MySQL to allow access to arbitrary addresses, server and client through certificate validation can prevent violent cracking.

Before we begin, let's review the installation process for SSL protocol client OpenSSL:
Install OpenSSL

Mkdir/test/setup
cd/test/setup
tar zxvf openssl-0.9.8b.tar.gz
cd openssl-0.9.8b
./config
Make && make Install

Open the SSL function in MySQL
Login to MySQL View

Mysql> Show variables like '%ssl% '; 

+---------------+----------+ 
| variable_name | Value  | 
+---------------+----------+ 
| have_openssl | DISABLED | 
| Have_ssl   | DISABLED | 
|  Ssl_ca | | | | ssl_capath | | | | | | 
Ssl_key    |     | 
+---------------+----------+

If the MySQL output is as described above, then continue the operation to turn on SSL; if not, recompile install MySQL, note that when generating makefile the correct parameters are filled in.
Exit MySQL, edit/etc/my.cnf
Between [mysqld] and [mysqldump], add the following configuration information:

Ssl

Restart MySQL after saving and login to MySQL again

Mysql-uroot-p
mysql> Show variables like '%ssl% '; 

+---------------+-------+ 
| variable_name | Value | 
+---------------+-------+ 
| have_openssl | YES  | 
| Have_ssl   | YES | | ssl_ca | | | ssl_capath | | | 
ssl_ cipher | | | | 
ssl_key 
| | +---------------+-------+

Okay, let's get down to business:
MySQL based on SSL replication
1. Create Certificate Center
Create a certificate center on the primary server

Cd/etc/pki/ca

Generate private key

(Umask 077;openssl GENRSA-OUT/ETC/PKI/CA/PRIVATE/CAKEY.PEM 2048)

Generated from the visa book, because of the need to enter a large number of user information, so edit the certificate's profile, on a private CA to create a certificate to note that all user information to the CA and the same, from the country to the department will be the same, otherwise the certificate can not be used

Vim/etc/pki/tls/openssh.cnf

 [Req_distinguished_name]
 CountryName     = Country Name (2 letter code)
 Countryname_default = CN
 countryname_min   = 2
 Countryname_max   = 2
 stateorprovincename = State or province name (full name)
 Stateorpovincename_default = FJ
 Localityname    = locality Name (eg,city)
 localityname    = FZ o.organizationname
 = Organization Name (eg,company)
 o.organizationname_default = Zdz
 organizationalunitname   = Organizational unit Name (eg,section)
 Organizationalunitname_default = Zdz

Generate self-signed certificate

OpenSSL req-new-x509-key/etc/pki/ca/private/cakey.pem-out/etc/pki/ca/cacert.pem-days 3650

-x509 is a parameter that is required to create a visa book and cannot be added when creating another certificate

Since it's a visa, you have to modify the certificate path

Vim/etc/pki/tls/openssl.cnf

 [Ca_defalut]
 dir =/etc/pki/ca
 certs = $dir/certs   #存放生成证书的目录
 crl_dir = $dir/crl   #存放吊销证书的目录
 database = $dir/ Index.txt  #证书的索引文件
 new_certs_dir = $dir _newcerts  #新签的证书目录
 serial = $dir/serial  #序列号
 CRL = $dir/crl.pem
 private_key = $dir/private/cakey.pem  #证书中心私钥文件

Create a certificate number

mkdir certs CRL Newcerts Touch
 index.txt
 echo > serial

2. Create a certificate for the main server
the name of the server must be fixed and the server name should be entered when the certificate is requested, and the certificate and server name correspond

Create private key

Mkdir/usr/local/mysql/ssl
 cd/usr/local/mysql/ssl
 (umask 077;openssl genrsa-out/usr/local/mysql/ssl/ Master.key 2048)

Generate Certificate Request

OpenSSL Req-new-key master.key-out MASTER.CSR

Signing Master's certificate on the Certificate Server

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

3. Create a certificate from a server

(Umask 077;openssl genrsa-out/usr/local/mysql/ssl/slave.key 2048)
 OpenSSL Req-new-key slave.key-out SLAVE.CSR

The certificate request file from the server is copied to the Certificate Server for signing

Opessl ca-in slave.csr-out slave.crt-days 356

4, modify the certificate permissions and MySQL configuration file
Copy the certificate's public key CACERT.PEM to the master-slave server directory

Cd/usr/local/mysql/ssl
 Cp/etc/pki/ca/cacert.pem./
 chown-r mysql:mysql master.crt master.key Cacert.pem
 chmod master.crt master.key cacert.pem
 vim/usr/local/mysql/my.cnf
 SSL Ssl_ca         =/usr/local/ Mysql/ssl/cacrt.pem
 Ssl_cert        =/usr/local/mysql/ssl/master.crt
 ssl_key         =/usr/local/mysql/ssl/ Master.key

Modify from server configuration

Cd/usr/local/mysql/ssl
 Cp/etc/pki/ca/cacert.pem./
 chown-r mysql:mysql slave.crt slave.key Cacert.pem
 chmod slave.crt slave.key cacert.pem
 vim/usr/local/mysql/my.cnf
 SSL Ssl_ca         =/usr/ Local/mysql/ssl/cacrt.pem
 Ssl_cert        =/usr/local/mysql/ssl/slave.crt
 ssl_key         =/usr/local/ Mysql/ssl/slave.key

5. Create a replication user on the primary server

Grant replication Slave on *.* to slave@ ' 192.168.216.133 ' identified by ' slave ' requere SSL;
 Flush privileges;

View the current binary location of the primary server

Mysql> Show master status;

 +-------------------------+------------+---------------------+--------------------------+---------------------- ----+
 | File              | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
 +-------------------------+------------+---------------------+--------------------------+---------------------- ----+
 | mysql-bin.000007 |   1015 | | | |
 +-------------------------+------------+---------------------+--------------------------+---------------------- -----+
 1 row in Set (0.00 sec)

6, start copying from the server

Change Master to
 master_host= ' 192.168.216.132 ',
 master_user= ' slave ',
 master_password= ' slave
 ', Master_log_file= ' mysql-bin.000007 ',
 master_log_pos=1015,
 master_ssl=1,
 master_ssl_ca= '/usr/local/ Mysql/ssl/cacrt.pem ',
 master_ssl_cert= '/usr/local/mysql/ssl/slave.crt ',
 master_ssl_key= '/usr/local/ Mysql/ssl/slave.key ';
 Start slave;

View status

Error 1:

If you want to make sure that there are no problems with the certificate, you can connect to SSL by establishing a test user to open a user with a large permission on the primary server for SSL logon tests

Grant all privileges on *.* to root@ ' 192.168.216.133′identified by ' root ' require SSL;

[Root@slave ssl]# Mysql-uroot-proot-h192.168.216.133–ssl-ca=cacrt.pem–ssl-cert=slave.crt–ssl-key=slave.key

Warning:using a password on the command line interface can is insecure.

ERROR 2026 (HY000): SSL connection Error:ASN:before date in the future

This is because the virtual time is incorrect and causes
If you do not use SSL to connect then you will report an error

[Root@slave ssl]# mysql-uroot-proot-h192.168.216.133;

Warning:using a password on the command line interface can is insecure.

ERROR 1045 (28000): Access denied for user ' root ' @ ' 192.168.216.132′ (using Password:yes)

Error 2:

Perform show variables like '%ssl% ' when you add a certificate configuration to a configuration file

This is because the owner of the certificate is not changed to MySQL, you can tell from the log that there is no permission to obtain the private key