Home › Database › MySQL › MySQL Master-Slave Replication (Async), Semi-Synchronous Replication, and SSL-Based Replication (Part 2)

MySQL Master-Slave Replication (Async), Semi-Synchronous Replication, and SSL-Based Replication (Part 2)

Categories: MySQL 2014-06-12 root Comments (0)

SSL Replication

    Both the master and slave servers require their own certificate and private key. By default, the SSL capability is not enabled on either the master or slave and must be enabled first.

1
2
3
4
5
6
7
8
9
10
11
12
    mysql> show variables like '%ssl%';
    +---------------+----------+
    | Variable_name | Value    |
    +---------------+----------+
    | have_openssl  | DISABLED |
    | have_ssl      | DISABLED |
    | ssl_ca        |          |
    | ssl_capath    |          |
    | ssl_cert      |          |
    | ssl_cipher    |          |
    | ssl_key       |          |
    +---------------+----------+

1. Configure Master as a CA Certificate Server

1
2
3
4
5
6
7
8
9
10
11
12
13
# vim /etc/pki/tls/openssl.cnf
# cd /etc/pki/CA/
# (umask 077; openssl genrsa 1024 >private/cakey.pem)
# openssl req -new -x509 -key private/cakey.pem -out cacert.pem
    Country Name (2 letter code) [GB]:CN 
    State or Province Name (full name) [Berkshire]:GD
    Locality Name (eg, city) [Newbury]:ZS
    Organization Name (eg, company) [My Company Ltd]:NEO
    Organizational Unit Name (eg, section) []:tech
    Common Name (eg, your name or your server's hostname) []:station01.neo.com
# mkdir newcerts certs crl
# touch index.txt
# echo 01 >serial

2. Prepare Private Key and Issue Certificate for MySQL on Master

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
# mkdir /usr/local/mysql/ssl
# cd /usr/local/mysql/ssl/
#(umask 077; openssl genrsa 1024 > mysql.key)
# openssl req -new -key mysql.key -out mysql.csr -days 3650
    Country Name (2 letter code) [GB]:CN
    State or Province Name (full name) [Berkshire]:GD
    Locality Name (eg, city) [Newbury]:ZS
    Organization Name (eg, company) [My Company Ltd]:NEO
    Organizational Unit Name (eg, section) []:tech
    Common Name (eg, your name or your server's hostname) []:station01.neo.com
    Email Address []:
    
    Please enter the following 'extra' attributes
    to be sent with your certificate request
    A challenge password []:
    An optional company name []:
# openssl ca -in mysql.csr -out mysql.crt
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: May 28 02:26:17 2014 GMT
            Not After : May 28 02:26:17 2015 GMT
        Subject:
            countryName               = CN
            stateOrProvinceName       = GD
            organizationName          = NEO
            organizationalUnitName    = tech
            commonName                = station01.neo.com
        X509v3 extensions:
            X509v3 Basic Constraints: 
                CA:FALSE
            Netscape Comment: 
                OpenSSL Generated Certificate
            X509v3 Subject Key Identifier: 
                A4:B7:A6:98:9F:60:08:BE:86:87:65:5F:B6:13:BC:4A:5B:D4:44:3A
            X509v3 Authority Key Identifier: 
                keyid:4F:D8:57:42:D9:39:17:7D:39:44:91:01:A4:01:DE:32:92:D6:F9:DF
 
Certificate is to be certified until May 28 02:26:17 2015 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
# chown mysql.mysql *

3. Apply for a Certificate on the Slave

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# mkdir /usr/local/mysql/ssl
# (umask 077; openssl genrsa 1024 >mysql.key)
# openssl req -new -key mysql.key -out mysql.csr -days 3650
    Country Name (2 letter code) [GB]:CN
    State or Province Name (full name) [Berkshire]:GD
    Locality Name (eg, city) [Newbury]:ZS
    Organization Name (eg, company) [My Company Ltd]:NEO
    Organizational Unit Name (eg, section) []:tech
    Common Name (eg, your name or your server's hostname) []:station02.neo.com
    Email Address []:
    
    Please enter the following 'extra' attributes
    to be sent with your certificate request
    A challenge password []:
    An optional company name []:
# scp mysql.csr 192.168.100.11:/root/

4. Issue Certificate for Slave on the Master

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
# openssl ca -in mysql.csr -out mysql.crt
Using configuration from /etc/pki/tls/openssl.cnf
Check that the request matches the signature
Signature ok
Certificate Details:
        Serial Number: 2 (0x2)
        Validity
            Not Before: May 28 02:36:24 2014 GMT
            Not After : May 28 02:36:24 2015 GMT
        Subject:
            countryName               = CN
            stateOrProvinceName       = GD
            organizationName          = NEO
            organizationalUnitName    = tech
            commonName                = station02.neo.com
        X509v3 extensions:
            X509v3 Basic Constraints: 
                CA:FALSE
            Netscape Comment: 
                OpenSSL Generated Certificate
            X509v3 Subject Key Identifier: 
                81:9F:5B:E7:06:D0:64:B7:E6:81:3F:98:95:71:D4:DF:C6:B8:CE:3D
            X509v3 Authority Key Identifier: 
                keyid:4F:D8:57:42:D9:39:17:7D:39:44:91:01:A4:01:DE:32:92:D6:F9:DF
 
Certificate is to be certified until May 28 02:36:24 2015 GMT (365 days)
Sign the certificate? [y/n]:yes
 
 
1 out of 1 certificate requests certified, commit? [y/n]yes
Write out database with 1 new entries
Data Base Updated
# scp mysql.crt 192.168.100.12:/usr/local/mysql/ssl/
# scp /etc/pki/CA/cacert.pem 192.168.100.12:/usr/local/mysql/ssl/

 

5. Edit /etc/my.cnf on Master to Enable SSL and Set Up Master-Slave Replication

 

1
2
3
4
5
6
7
8
9
10
# vim /etc/my.cnf
[mysqld] 
log-bin=mysql-bin 
sync_binlog = 1                          ##Binary log 
server-id = 1                            ##This server-id must be globally unique 
innodb_flush_log_at_trx_commit=1         ##Flush transaction log to disk every second 
ssl                                      ##Enable SSL; default is off, check with: show variables like '%ssl%' 
ssl_ca =/usr/local/mysql/ssl/cacert.pem      ##Path to the CA file 
ssl_cert= /usr/local/mysql/ssl/mysql.crt     ##Path to the certificate file 
ssl_key = /usr/local/mysql/ssl/mysql.key     ## Location of the private key file

 

 

 

 

 

 

6. Start MySQL and Check SSL Information

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# service mysqld start 
# mysql 
mysql> show variables like '%ssl%'; 
+---------------+---------------------------------+ 
| Variable_name | Value                           | 
+---------------+---------------------------------+ 
| have_openssl  | YES                             | 
| have_ssl      | YES                             | 
| ssl_ca        | /usr/local/mysql/ssl/cacert.pem | 
| ssl_capath    |                                 | 
| ssl_cert      | /usr/local/mysql/ssl/mysql.crt  | 
| ssl_cipher    |                                 | 
| ssl_key       | /usr/local/mysql/ssl/mysql.key  | 
+---------------+---------------------------------+

 

 

7. Create a Minimum-Privilege Account for Replication and Require SSL

1
2
3
4
5
mysql> create user 'backup_ssl'@'192.168.100.12' identified by 'redhat'; 
mysql> revoke all privileges,grant option from 'backup_ssl'@'192.168.100.12'; 
mysql> grant replication slave,replication client on *.* to 'backup_ssl'@'192.168.100.12' require ssl; 
mysql> flush privileges;
mysql> flush logs;

 

8. Edit /etc/my.cnf on the Slave, Enable SSL, and Configure Master-Slave Replication

1
2
3
4
5
6
7
8
9
10
11
# vim /etc/my.cnf
[mysqld] 
server-id = 2                        ## This ID must be globally unique 
##log-bin = mysql-bin                ##Comment out, slave servers do not need binary logs
relay-log = mysql-relay              ##Relay log 
relay-log-index = mysql-ralay.index  ##Relay index 
read-only = 1                        ##Slave server read only
ssl                                  ##Enable SSL. It is off by default. Check with: show variables like '%ssl%' 
ssl_ca =/usr/local/mysql/ssl/cacert.pem  ##Location of the CA file 
ssl_cert= /usr/local/mysql/ssl/mysql.crt ##Location of the certificate file 
ssl_key = /usr/local/mysql/ssl/mysql.key ##Location of the private key file

 


   

 

9. Start mysqld and Check SSL Information

1
2
3
4
5
6
7
8
9
10
11
12
13
# service mysqld start 
mysql> show variables like '%ssl%'; 
+---------------+---------------------------------+ 
| Variable_name | Value                           | 
+---------------+---------------------------------+ 
| have_openssl  | YES                             | 
| have_ssl      | YES                             | 
| ssl_ca        | /usr/local/mysql/ssl/cacert.pem | 
| ssl_capath    |                                 | 
| ssl_cert      | /usr/local/mysql/ssl/mysql.crt  | 
| ssl_cipher    |                                 | 
| ssl_key       | /usr/local/mysql/ssl/mysql.key  | 
+---------------+---------------------------------+

10. Start the Slave Sync Process and Connect to the Master Server

1
2
3
4
5
6
7
8
9
10
11
mysql> change master to  
    -> master_host='192.168.100.11', 
    -> master_user='backup_ssl', 
    -> master_password='redhat', 
    -> master_log_file='mysql-bin.000004', 
    -> master_ssl=1, 
    -> master_ssl_ca='/usr/local/mysql/ssl/cacert.pem', 
    -> master_ssl_cert='/usr/local/mysql/ssl/mysql.crt', 
    -> master_ssl_key='/usr/local/mysql/ssl/mysql.key'; 
mysql> start slave 
mysql> show slave status/G; ##Check slave status

 


   

 

11. Pay attention to the following parameters:

1
2
3
4
5
6
7
8
Slave_IO_Running: Yes                                  ## Whether the IO thread is running. If No, the slave is not functioning properly 
Slave_SQL_Running: Yes                                 ## Whether the SQL thread is running. If No, the slave is not functioning properly 
Master_SSL_CA_File: /usr/local/mysql/ssl/cacert.pem    ## Whether SSL is enabled 
Master_SSL_Cert: /usr/local/mysql/ssl/mysql.crt 
Master_SSL_Key: /usr/local/mysql/ssl/mysql.key 
Master_Log_File: mysql-bin.00005                       ## The last received master binary log 
Exec_Master_Log_Pos: 338                               ## The last executed position; verify this position exists on the master 
Last_IO_Errno: 0                                       ##Check if the last IO thread reported any errors

 

 

This article is from the “jun.wang” blog. Please be sure to retain this source link http://junwang.blog.51cto.com/5050337/1424711

Related Posts

Leave a Comment

Your email address will not be published.