数据对于大部分公司来说都是最重要的部分，而MySQL的服务器在同步数据时，默认是使用明文进行传输，所以接下来就来说说MySQL基于SSL协议进行密文传输数据的主从复制模式。

逻辑拓扑：

接下来的实验中Master节点服务器即使Master节点数据库服务器，同时也是CA。

环境准备：

一、主从服务器时间需要同步：

[root@node9~]# chronyc sources

210 Number of sources = 1

MS Name/IP addressStratum Poll Reach LastRx Last sample

===============================================================================

^* server.magelinux.com3 7 37782 +71us[ +148us] +/- 100ms

[root@node10 ~]# chronycsources

210 Number of sources = 1

MS Name/IP addressStratum Poll Reach LastRx Last sample

===============================================================================

^* server.magelinux.com3 7 37795 +116us[ +155us] +/- 100ms

二、主节点node9搭建好CA环境：

[root@node9~]# cd /etc/pki/CA

[root@node9 CA]# touch index.txt serial

[root@node9 CA]# echo 01 > serial

[root@node9 CA]# (umask 077;openssl genrsa -out cakey.pem 2048)

[root@node9 CA]# openssl req -new -x509 -key private/cakey.pem -outcacert.pem -days 3650

You are about to be asked to enter information that will beincorporated

intoyour certificate request.

Whatyou are about to enter is what is called a Distinguished Name or a DN.

Thereare quite a few fields but you can leave some blank

Forsome fields there will be a default value,

Ifyou enter '.', the field will be left blank.

-----

CountryName (2 letter code) [XX]:CN

Stateor Province Name (full name) []:BeiJing

LocalityName (eg, city) [Default City]:BeiJing

OrganizationName (eg, company) [Default Company Ltd]:hisen

OrganizationalUnit Name (eg, section) []:Ops

CommonName (eg, your name or your server's hostname) []:ca.hisen.com

EmailAddress []:admin.com

三、主节点node9生成证书申请，并由CA进行签署：

[root@node9~]# cd /var/lib/mysql/ssl/

[root@node9ssl]# (umask 077;openssl genrsa -out master.key 2048)

[root@node9ssl]# openssl req -new -key master.key -out master.csr -days 3650

Youare about to be asked to enter information that will be incorporated

intoyour certificate request.

Whatyou are about to enter is what is called a Distinguished Name or a DN.

Thereare quite a few fields but you can leave some blank

Forsome fields there will be a default value,

Ifyou enter '.', the field will be left blank.

-----

CountryName (2 letter code) [XX]:CN

Stateor Province Name (full name) []:BeiJing

LocalityName (eg, city) [Default City]:BeiJing

OrganizationName (eg, company) [Default Company Ltd]:hisen

OrganizationalUnit Name (eg, section) []:Ops

CommonName (eg, your name or your server's hostname) []:master.hisen.com

EmailAddress []:master.com

Pleaseenter the following 'extra' attributes

tobe sent with your certificate request

Achallenge password []:

Anoptional company name []:

[root@node9ssl]# openssl ca -in master.csr -out master.crt -days 2048

Usingconfiguration from /etc/pki/tls/openssl.cnf

Checkthat the request matches the signature

Signatureok

CertificateDetails:

Serial Number: 1 (0x1)

Validity

Not Before: Feb 22 11:21:11 2017GMT

Not After : Oct 2 11:21:11 2022 GMT

Subject:

countryName = CN

stateOrProvinceName = BeiJing

organizationName = hisen

organizationalUnitName = Ops

commonName = master.hisen.com

emailAddress = master.com

X509v3 extensions:

X509v3 Basic Constraints:

CA:FALSE

Netscape Comment:

OpenSSL Generated Certificate

X509v3 Subject Key Identifier:

2B:1D:F7:18:00:89:1B:CB:6D:09:59:4B:5E:03:78:BA:60:6A:62:BB

X509v3 Authority Key Identifier:

keyid:C4:30:C5:87:EB:80:6C:87:AE:60:71:FC:E9:79:1F:5A:31:57:5B:88

Certificateis to be certified until Oct 2 11:21:112022 GMT (2048 days)

Signthe certificate? [y/n]:y

1out of 1 certificate requests certified, commit? [y/n]y

Writeout database with 1 new entries

DataBase Updated

四、从节点node10生成证书，并由CA进行签署：

[root@node10 ~]# cd/var/lib/mysql/ssl

[root@node10ssl]# (umask 077;openssl genrsa -out slave.key 2048)

GeneratingRSA private key, 2048 bit long modulus

..+++

...........................................................+++

eis 65537 (0x10001)

[root@node10ssl]# (umask 077;openssl genrsa -out slave.key 2048)

GeneratingRSA private key, 2048 bit long modulus

..+++

...........................................................+++

eis 65537 (0x10001)

[root@node10ssl]# openssl req -new -key slave.key -out slave.csr -days 3650

Youare about to be asked to enter information that will be incorporated

intoyour certificate request.

Whatyou are about to enter is what is called a Distinguished Name or a DN.

Thereare quite a few fields but you can leave some blank

Forsome fields there will be a default value,

Ifyou enter '.', the field will be left blank.

-----

CountryName (2 letter code) [XX]:CN

Stateor Province Name (full name) []:BeiJing

LocalityName (eg, city) [Default City]:BeiJing

OrganizationName (eg, company) [Default Company Ltd]:hisen

OrganizationalUnit Name (eg, section) []:Ops

CommonName (eg, your name or your server's hostname) []:slave.hisen.com

EmailAddress []:slave.com

Pleaseenter the following 'extra' attributes

tobe sent with your certificate request

Achallenge password []:

Anoptional company name []:

[root@node10 ssl]# scp slave.csr 192.168.17.90:/root

[root@node9~]# openssl ca -in slave.csr -out slave.crt -days 3650

Usingconfiguration from /etc/pki/tls/openssl.cnf

Checkthat the request matches the signature

Signatureok

CertificateDetails:

Serial Number: 2 (0x2)

Validity

Not Before: Feb 22 11:27:17 2017GMT

Not After : Feb 20 11:27:17 2027GMT

Subject:

countryName = CN

stateOrProvinceName = BeiJing

organizationName = hisen

organizationalUnitName = Ops

commonName = slave.hisen.com

emailAddress = slave.com

X509v3 extensions:

X509v3 Basic Constraints:

CA:FALSE

Netscape Comment:

OpenSSL Generated Certificate

X509v3 Subject Key Identifier:

68:31:D7:B1:03:5A:C0:6E:A3:58:4D:67:53:AC:F7:F5:1E:2A:19:4E

X509v3 Authority Key Identifier:

keyid:C4:30:C5:87:EB:80:6C:87:AE:60:71:FC:E9:79:1F:5A:31:57:5B:88

Certificateis to be certified until Feb 20 11:27:17 2027 GMT (3650 days)

Signthe certificate? [y/n]:y

1out of 1 certificate requests certified, commit? [y/n]y

Writeout database with 1 new entries

DataBase Updated

[root@node9 ~]# scp slave.crt 192.168.17.100:/var/lib/mysql/ssl/

五、将node9的CA证书复制给Master和Slave各一份：

[root@node9 ~]# cp/etc/pki/CA/cacert.pem /var/lib/mysql/ssl/ #复制CA证书到本地

[root@node9 ~]# scp/etc/pki/CA/cacert.pem 192.168.17.100:/var/lib/mysql/ssl/#复制CA到node10

六、修改/var/lib/mysql/ssl/下文件的属主属组以及给予最小权限：

[root@node9 ~]# chown -R mysql:mysql /var/lib/mysql/ssl/ ; chmod 600/var/lib/mysql/ssl/*

[root@node10 ~]# chown-R mysql:mysql /var/lib/mysql/ssl/ ; chmod 600 /var/lib/mysql/ssl/*

MySQL文件配置：

Master：

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

symbolic-links=0

skip_name_resolve=ON

innodb_file_per_table=1

server_id=2

log-bin=master-log

ssl#开启SSL

ssl_ca=/var/lib/mysql/ssl/cacert.pem#Master节点CA证书存放位置

ssl_cert=/var/lib/mysql/ssl/master.crt#Master节点证书

ssl_key=/var/lib/mysql/ssl/master.key#Master节点key

Slave：

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

symbolic-links=0

skip_name_resolve=ON

innodb_file_per_table=ON

server_id=3

relay-log=relay-log

read-only=1

ssl#开启SSL

ssl_ca=/var/lib/mysql/ssl/cacert.pem#Slave节点CA证书存放位置

ssl_cert=/var/lib/mysql/ssl/slave.crt#Slave节点证书

ssl_key=/var/lib/mysql/ssl/slave.key#Slave节点key

MySQL服务配置：

一、启动MySQL服务，并查看MySQL中SSL信息：

[root@node9 ~]# systemctl startmariadb.service #启动node9的MySQL服务

MariaDB [(none)]> SHOW GLOBALVARIABLES LIKE '%ssl%'; #查看node9的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/master.crt |

|ssl_cipher | |

|ssl_key |/var/lib/mysql/ssl/master.key |

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

7rows in set (0.01 sec)

[root@node10 ssl]# systemctl start mariadb.service#启动node10的MySQL服务

MariaDB [(none)]> SHOW GLOBALVARIABLES LIKE '%ssl%'; #查看node10的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/slave.crt |

|ssl_cipher | |

|ssl_key |/var/lib/mysql/ssl/slave.key |

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

7rows in set (0.00 sec)

二、Master节点授权一个能用于SSL协议进行复制信息的用户，并测试用户：

MariaDB[(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO'userssl'@'192.168.17.%' IDENTIFIED BY 'passwordssl' REQUIRE SSL;

Query OK, 0 rows affected (0.00 sec)#授权一个仅能够通过SSL复制数据的用户

MariaDB [(none)]> FLUSH PRIVILEGES;#刷新权限

QueryOK, 0 rows affected (0.00 sec)

[root@node9~]# mysql -uuserssl -ppasswordssl -h292.168.17.90 \

>--ssl_ca=/var/lib/mysql/ssl/cacert.pem \

>--ssl_cert=/var/lib/mysql/ssl/master.crt \

>--ssl_key=/var/lib/mysql/ssl/master.key #测试用户能否使用SSL协议登录

Welcometo the MariaDB monitor. Commands endwith ; or \g.

YourMariaDB connection id is 4

Serverversion: 5.5.44-MariaDB-log MariaDB Server

Copyright(c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB[(none)]>

三、配置Slave节点，指向Master节点：

Master节点当前正在使用的binlog文件：master-log.000004，以及binlog位置：512

MariaDB[(none)]> SHOW MASTER STATUS;

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

|File | Position |Binlog_Do_DB | Binlog_Ignore_DB |

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

|master-log.000004 | 512 | | |

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

1row in set (0.00 sec)

Slave节点配置：

MariaDB[(none)]> CHANGE MASTER TO

-> MASTER_HOST='192.168.17.90',

-> MASTER_USER='userssl',

-> MASTER_PASSWORD='passwordssl',

-> MASTER_LOG_FILE='master-log.000004',

-> MASTER_LOG_POS=512,

-> MASTER_SSL=1,

->MASTER_SSL_CA='/var/lib/mysql/ssl/cacert.pem',

->MASTER_SSL_CERT='/var/lib/mysql/ssl/slave.crt',

->MASTER_SSL_KEY='/var/lib/mysql/ssl/slave.key';

QueryOK, 0 rows affected (0.01 sec)

MariaDB [(none)]> START SLAVE;启动SLAVE功能

QueryOK, 0 rows affected (0.01 sec)

MariaDB [(none)]> SHOW SLAVESTATUS\G; 查看SLAVE状态信息

***************************1. row ***************************

Slave_IO_State: Waiting formaster to send event

Master_Host: 192.168.17.90

Master_User: userssl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File:master-log.000004

Read_Master_Log_Pos: 512

Relay_Log_File: relay-log.000002

Relay_Log_Pos: 530

Relay_Master_Log_File:master-log.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

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: 512

Relay_Log_Space: 818

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: Yes

Master_SSL_CA_File:/var/lib/mysql/ssl/cacert.pem

Master_SSL_CA_Path:

Master_SSL_Cert:/var/lib/mysql/ssl/slave.crt

Master_SSL_Cipher:

Master_SSL_Key:/var/lib/mysql/ssl/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: 2

1row in set (0.00 sec)

四、测试主从同步数据：

Master节点：

MariaDB [(none)]> CREATE DATABASEhisendb; node9主节点创建hisendb数据库

QueryOK, 1 row affected (0.00 sec)

MariaDB[(none)]> USE hisendb;

Databasechanged

MariaDB [hisendb]> CREATE TABLE friends(id INT UNSIGNED PRIMARYKEY NOT NULL,Name VARCHAR(20) NOT NULL,Age TINYINT,Gender ENUM('F','M'));

Query OK, 0 rows affected (0.01 sec)#在hisendb数据库中创建friends表

MariaDB[hisendb]> INSERT INTO friends VALUES (1,'Xu He',25,'M'),(2,'XuMingying',27,'F'),(3,'Tian Tao',26,'M'),(4,'LiangJuntao',28,'M');

Query OK, 4 rows affected (0.00 sec)#在friends表中插入数据

Records:4 Duplicates: 0 Warnings: 0

MariaDB [hisendb]> SELECT * FROMfriends; #查看结果

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

|id | Name | Age | Gender |

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

| 1 | Xu He |25 | M |

| 2 | Xu Mingying | 27| F |

| 3 | Tian Tao |26 | M |

| 4 | Liang Juntao | 28 | M|

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

4rows in set (0.00 sec)

Slave节点：

MariaDB [(none)]> USE hisendb;

Readingtable information for completion of table and column names

Youcan turn off this feature to get a quicker startup with -A

Databasechanged

MariaDB [hisendb]> SELECT * FROMfriends; #在从节点可以查看主节点写入的数据

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

|id | Name | Age | Gender |

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

| 1 | Xu He |25 | M |

| 2 | Xu Mingying | 27| F |

| 3 | Tian Tao |26 | M |

| 4 | Liang Juntao | 28 | M|

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

4rows in set (0.00 sec)

由上可知，主从已经完成基于SSL协议的数据复制。