mysql master-slave replication encryption (ssl)

After reading several blogs, I also refer to other people's documents and row many holes. It's done

Summarize the steps:

1. Download openssl
2. Generate CA organization and certificate.
3. Generate the corresponding certificate and key for the master and slave of mysql
4. Modify mysql configuration file
5. Check the ssl login of the server
6. Master-slave replication

1, Generate CA organization and self signed certificate

#yum -y install openssl / / install OpenSSL

#vim /etc/pki/tls/openssl.cnf      
    certificate     = $dir/ca.crt     //Generated self signed certificate file name
    private_key     = $dir/private/ca.key //Private key file name of the generated ca

#cd /etc/pki/CA  
#(umask 077; openssl genrsa -out private/ca.key 2048) / / generate private key
//genrsa encryption 2048 encryption string length private/ca.key generated file name
#openssl req -new -x509 -key private/ca.key -out ca.crt -days 3650 [see the tail for details]
//Generate self signed certificate x509 is the certificate template format ca.crt is the self signed certificate generated by CA 3650 is the validity period (10 years)
#Touch index.txt / / Ca required files
#Echo 01 > serial / / Ca required files

2, Generate the certificate of mysql server

master
#CD / etc / my.cnf. D / / / enter the directory at any location. Specify later
#(umask 077; openssl genrsa -out master.key 2048) / / generate the master server's key 
#openssl req -new -key master.key -out master.csr / / generate certificate request
[stay ca Server execution] will master.csrSpread to ca The server
#openssl ca -in master.csr -out master.crt -days 365 / / send certificate request to CA server to generate certificate
[take maste.crtTo main server]
[take ca.crtTo main server]
ls /etc/my.cnf.d/
ca.crt  master.crt  master.key     Last directory exists master Certificate, key, and CA Server self signed certificate
chown -R mysql.mysql /etc/my.cnf.d/  Empowerment
chmod 600 /etc/my.cnf.d/  //Fixed permissions, do not make other changes, otherwise there will be errors
slave server
#CD / etc / my.cnf. D / / / enter the directory at any location. Specify later
#(umask 077; openssl genrsa -out slave.key 2048) / / generate the master server's key 
#openssl req -new -key slave.key -out slave.csr / / generate certificate request
[stay ca Server execution] will slave.csrSpread to ca The server
#openssl ca -in slave.csr -out slave.crt -days 365 / / send the certificate request to the CA server to generate the certificate
[take maste.crtTo slave server]
[take ca.crtTo slave server]
ls /etc/my.cnf.d/
ca.crt  slave.crt  slave.key     Last directory exists master Certificate, key, and CA Server self signed certificate
chown -R mysql.mysql /etc/my.cnf.d/  Empowerment
chmod 600 /etc/my.cnf.d/  //Fixed permissions, do not make other changes, otherwise there will be errors

3, Modify mysql configuration file

master
#vim /etc/my.cnf

//Add to
    ssl_ca = /etc/my.cnf.d/ca.crt   #Specify the location of the CA certificate
    ssl_cert = /etc/my.cnf.d/master.crt   #Specify the location of the master certificate
    ssl_key = /etc/my.cnf.d/master.key    #Specify the location of the master key

Restart service

#systemctl restart mysqld
slave
#vim /etc/my.cnf
//Add to
    ssl_ca = /etc/my.cnf.d/ca.crt   #Specify the location of the CA certificate
    ssl_cert = /etc/my.cnf.d/slave.crt   #Specify the location of the master certificate
    ssl_key = /etc/my.cnf.d/slave.key    #Specify the location of the master key

Restart service

#systemctl restart mysqld

4, Check if the configuration is successful

mysql> show variables  like '%ssl%';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| have_openssl  | YES                      |    by yes Is available for use
| have_ssl      | YES                      |    by yes Is available for use
| ssl_ca        | /etc/my.cnf.d/ca.crt     |
| ssl_capath    |                          |
| ssl_cert      | /etc/my.cnf.d/master.crt |
| ssl_cipher    |                          |
| ssl_crl       |                          |
| ssl_crlpath   |                          |
| ssl_key       | /etc/my.cnf.d/master.key |
+---------------+--------------------------+
9 rows in set (0.00 sec)
Login verification [master/slave]
mysql --ssl-ca=/etc/my.cnf.d/ca.crt --ssl-cert=/etc/my.cnf.d/master.crt  --ssl-key=/etc/my.cnf.d/master.key  -uroot  -p'Wenliang@123'

//If you enter msyql, it will be regarded as success

5, Start configuration of master-slave replication

master
mysql >grant replication slave on *.* to 'repl'@'10.18.44.%' identified by 'Wenliang@123' require ssl;
mysql> flush privileges;
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 2247
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)
slave
mysql> stop slave;
mysql> change master to
        master_host = '10.18.44.189', 
        master_user = 'repl',
        master_password = 'Wenliang@123',
        master_log_file = 'mysql-bin.000003',
        master_log_pos = 2247,
        master_ssl=1,
        master_ssl_ca='/etc/my.cnf.d/ca.crt',
        master_ssl_cert='/etc/my.cnf.d/slave.crt',
        master_ssl_key='/etc/my.cnf.d/slave.key';

mysql > start slave;
Check success
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.18.44.189
                  Master_User: wenliang
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 2247
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 2133
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes     //yes here is success
            Slave_SQL_Running: Yes     //yes here is success
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2247
              Relay_Log_Space: 2334
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/my.cnf.d/ca.crt
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /etc/my.cnf.d/slave.crt
            Master_SSL_Cipher: 
               Master_SSL_Key: /etc/my.cnf.d/slave.key
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 4383e2bb-8f53-11e8-8eb0-000c29affc23
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

Attachment: the master and slave of MySQL are not synchronized

There are three main reasons:

1. Network failure
2. The password is wrong when specifying the master
3. The pos node location of the specified master is incorrect

Problems to avoid

When preparing for MySQL active replication, try not to restart the master server, which is easy to change nodes and difficult to troubleshoot.

Attachment: CA certification

[root@mysql3 CA]# openssl req -new -x509 -key private/ca.key -out ca.crt -days 3650
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:BJ
Locality Name (eg, city) [Default City]:B  
Organization Name (eg, company) [Default Company Ltd]:^C
[root@mysql3 CA]# openssl req -new -x509 -key private/ca.key -out ca.crt -days 3650
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:BJ
Locality Name (eg, city) [Default City]:BJ
Organization Name (eg, company) [Default Company Ltd]:WL
Organizational Unit Name (eg, section) []:wenliang
Common Name (eg, your name or your server's hostname) []:www.ca.com
Email Address []:lwl@163.com

mysql certificate request

master.key is transferred from master server to CA server

[root@mysql3 mysql]# openssl req -new -key master.key -out master.csr
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN    //Must be consistent with CA
State or Province Name (full name) []:BJ     //Must be consistent with CA
Locality Name (eg, city) [Default City]:BJ     //Must be consistent with CA
Organization Name (eg, company) [Default Company Ltd]:WL    //Must be consistent with CA
Organizational Unit Name (eg, section) []:wenliang     // //Must be consistent with CA
Common Name (eg, your name or your server's hostname) []:www.mysql.com    
Email Address []:lllzzz@163.com

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

mysql certificate

[root@mysql3 mysql]# openssl ca -in master.csr -out master.crt -days 365
Using configuration from /etc/pki/tls/openssl.cnf
Check that the request matches the signature
Signature ok
Certificate Details:
        Serial Number: 1 (0x1)
        Validity
            Not Before: Jul 24 21:11:12 2018 GMT
            Not After : Jul 24 21:11:12 2019 GMT
        Subject:
            countryName               = CN
            stateOrProvinceName       = BJ
            organizationName          = WL
            organizationalUnitName    = wenliang
            commonName                = www.mysql.com
            emailAddress              = lllzzz@163.com
        X509v3 extensions:
            X509v3 Basic Constraints: 
                CA:FALSE
            Netscape Comment: 
                OpenSSL Generated Certificate
            X509v3 Subject Key Identifier: 
                AC:E2:CE:E1:DD:C2:EF:F1:3A:F0:29:9C:70:F6:0F:1D:ED:D9:82:C9
            X509v3 Authority Key Identifier: 
                keyid:1B:5D:34:54:18:67:5F:1D:40:88:12:D3:4E:55:B3:2A:DD:F3:7A:1A

Certificate is to be certified until Jul 24 21:11:12 2019 GMT (365 days)
Sign the certificate? [y/n]:y


1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated
[root@mysql3 mysql]# ls
master.crt  master.csr  master.key

Tags: MySQL OpenSSL SSL vim

Posted on Fri, 31 Jan 2020 10:29:47 -0500 by mirana