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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
| @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
修改配置文件
在Master上：
vi /etc/my.cnf
[mysqld]
log-bin=master-bin
binlog_format=mixed
server-id=1
ssl
/etc/init.d/mysqld restart
在Slave上：
vi /etc/my.cnf
[mysqld]
log-bin=slave-bin
binlog_format=mixed
server-id=10
ssl
/etc/init.d/mysqld restart
(1)将master服务器自己做成CA服务器
[iyunv@nan86 tmp]# cd /etc/pki/CA/
[iyunv@nan86 CA]# (umask 077;openssl genrsa -out private/cakey.pem 2048)
Generating RSA private key, 2048 bit long modulus
.......+++
........+++
e is 65537 (0x10001)
[iyunv@nan86 CA]# openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 365
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) []:ShangHai
Locality Name (eg, city) [Default City]:PuDong
Organization Name (eg, company) [Default Company Ltd]:Allen
Organizational Unit Name (eg, section) []:Tech
Common Name (eg, your name or your server's hostname) []:master.allen.com
Email Address []:
[iyunv@nan86 CA]# touch index.txt
[iyunv@nan86 CA]# echo 01>serial
(2)为master创建证书申请并由CA服务器签发证书
[iyunv@nan86 CA]# mkdir /usr/local/mysql/ssl
[iyunv@nan86 CA]# cd /usr/local/mysql/ssl/
[iyunv@nan86 ssl]# (umask 077;openssl genrsa -out master.key 2048)
Generating RSA private key, 2048 bit long modulus
.................+++
................................................................................................................+++
e is 65537 (0x10001)
[iyunv@nan86 ssl]# openssl req -new -key master.key -out master.csr -days 365
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) []:ShangHai
Locality Name (eg, city) [Default City]:PuDong
Organization Name (eg, company) [Default Company Ltd]:Allen
Organizational Unit Name (eg, section) []:Tech
Common Name (eg, your name or your server's hostname) []:master.allen.com
Email Address []:master@allen.com
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[iyunv@nan86 ssl]# 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: Feb 24 10:41:53 2014 GMT
Not After : Feb 24 10:41:53 2015 GMT
Subject:
countryName = CN
stateOrProvinceName = ShangHai
organizationName = Allen
organizationalUnitName = Tech
commonName = master.allen.com
emailAddress = master@allen.com
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
AA:9B:97:2D:72:00:38:55:DB:77:86:3E:87:1C:30:C4:12:BA:69:F0
X509v3 Authority Key Identifier:
keyid:F4:DD:53:C2:28:E4:C9:1B:F0:CA:1F:5F:79:35:E5:32:4E:7A:55:39
Certificate is to be certified until Feb 24 10:41:53 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
(3)为slave服务器创建证书申请
在slave
[iyunv@nan85 ~]# mkdir /usr/local/mysql/ssl
[iyunv@nan85 ~]# cd /usr/local/mysql/ssl/
[iyunv@nan85 ssl]# (umask 077;openssl genrsa -out slave.key 2048)
Generating RSA private key, 2048 bit long modulus
.......................................+++
.........+++
e is 65537 (0x10001)
[iyunv@nan85 ssl]# openssl req -new -key slave.key -out slave.csr -days 365
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) []:ShangHai
Locality Name (eg, city) [Default City]:PuDong
Organization Name (eg, company) [Default Company Ltd]:Allen
Organizational Unit Name (eg, section) []:Tech
Common Name (eg, your name or your server's hostname) []:slave.allen.com
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
(4)为slave服务器签署证书
#####将证书申请请求拷贝到CA服务器签署
slave上
[iyunv@nan85 ssl]# scp slave.csr 10.10.54.86:/tmp/
在master上
[iyunv@nan86 tmp]# openssl ca -in /tmp/slave.csr -out /tmp/slave.crt -days 365
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: Feb 24 11:09:32 2014 GMT
Not After : Feb 24 11:09:32 2015 GMT
Subject:
countryName = CN
stateOrProvinceName = ShangHai
organizationName = Allen
organizationalUnitName = Tech
commonName = slave.allen.com
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
7C:5A:7F:49:88:29:8C:C3:33:7B:E7:46:71:C8:68:47:60:F5:69:27
X509v3 Authority Key Identifier:
keyid:F4:DD:53:C2:28:E4:C9:1B:F0:CA:1F:5F:79:35:E5:32:4E:7A:55:39
Certificate is to be certified until Feb 24 11:09:32 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
#####签署好证书申请拷贝到slave服务器
在主上：
[iyunv@nan86 tmp]# scp /tmp/slave.crt 10.10.54.85:/usr/local/mysql/ssl/
(5)将CA证书拷贝到slave服务器一份并为master拷贝一份
主机：
[iyunv@nan86 tmp]# scp /etc/pki/CA/cacert.pem 10.10.54.85:/usr/local/mysql/ssl/
[iyunv@nan86 tmp]# cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl/
(6)修改master和slave服务器证书属主、属组为"mysql"用户
主机：
[iyunv@nan86 tmp]# chown -R mysql.mysql /usr/local/mysql/ssl/
[iyunv@nan86 tmp]# ll /usr/local/mysql/ssl/
total 20
-rw-r--r-- 1 mysql mysql 1330 2月 24 19:17 cacert.pem
-rw-r--r-- 1 mysql mysql 4532 2月 24 18:42 master.crt
-rw-r--r-- 1 mysql mysql 1054 2月 24 18:39 master.csr
-rw------- 1 mysql mysql 1679 2月 24 18:36 master.key
从机：
[iyunv@nan85 ssl]# chown -R mysql.mysql /usr/local/mysql/ssl/
[iyunv@nan85 ssl]# ll /usr/local/mysql/ssl/
total 20
-rw-r--r-- 1 mysql mysql 1330 2月 24 19:16 cacert.pem
-rw-r--r-- 1 mysql mysql 4456 2月 24 19:13 slave.crt
-rw-r--r-- 1 mysql mysql 1009 2月 24 18:47 slave.csr
-rw------- 1 mysql mysql 1675 2月 24 18:45 slave.key
(7)在master与slave服务器修改主配置文件开启SSL加密功能
####修改master数据库
[iyunv@nan86 tmp]# vim /etc/my.cnf
ssl_ca=/usr/local/mysql/ssl/cacert.pem
ssl_cert=/usr/local/mysql/ssl/master.crt
ssl_key=/usr/local/mysql/ssl/master.key
[iyunv@nan86 tmp]# /etc/init.d/mysqld restart
####修改slave数据库
[iyunv@nan85 ssl]# vim /etc/my.cnf
ssl_ca=/usr/local/mysql/ssl/cacert.pem
ssl_cert=/usr/local/mysql/ssl/slave.crt
ssl_key=/usr/local/mysql/ssl/slave.key
[iyunv@nan85 ssl]# /etc/init.d/mysqld restart
(8)在master服务器查看SSL加密是否开启，然后创建授权一个基于密钥认证的用户
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/master.crt |
| ssl_cipher | |
| ssl_key | /usr/local/mysql/ssl/master.key |
+---------------+---------------------------------+
mysql> grant replication client,replication slave on *.* to 'slave'@'10.10.54.85' identified by 'slave' require ssl;
Query OK, 0 rows affected (1.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
(9)查看master服务器二进制日志文件和事件位置用于slave服务器链接从这个位置开始复制
mysql> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000021 | 806 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
(10)测试使用加密用户指定的密钥链接服务器
在slave上
[iyunv@nan85 ssl]# mysql -uroot -p123 -h 10.10.54.86 --ssl-ca=/usr/local/mysql/ssl/cacert.pem --ssl-cert=/usr/local/mysql/ssl/slave.crt --ssl-key=/usr/local/mysql/ssl/slave.key
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 33
Server version: 5.5.30-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
(11)查看slave服务器SSL是否开启并连接master服务器
#####产看是否开启SSL
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/slave.crt |
| ssl_cipher | |
| ssl_key | /usr/local/mysql/ssl/slave.key |
+---------------+---------------------------------+
#####链接master服务器
change master to
master_host='10.10.54.86',master_user='slave',master_password='slave',
master_log_file='master-bin.000021',master_log_pos=806,master_ssl=1,
master_ssl_ca='/usr/local/mysql/ssl/cacert.pem',
master_ssl_cert='/usr/local/mysql/ssl/slave.crt',
master_ssl_key='/usr/local/mysql/ssl/slave.key';
#####获取命令帮助
###@@@@@@@@@@@
mysql> help change master to
| MASTER_SSL = {0|1} #是否使用SSL功能
| MASTER_SSL_CA = 'ca_file_name' #CA证书位置
| MASTER_SSL_CERT = 'cert_file_name' #指定自己的证书文件
| MASTER_SSL_KEY = 'key_file_name' #指定自己的密钥文件
(12)查看slave服务器的状态
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 10.10.54.86
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000021
Read_Master_Log_Pos: 353
Relay_Log_File: nan85-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000021
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB: a
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: 353
Relay_Log_Space: 107
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /usr/local/mysql/ssl/cacert.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /usr/local/mysql/ssl/slave.crt
Master_SSL_Cipher:
Master_SSL_Key: /usr/local/mysql/ssl/slave.key
Seconds_Behind_Master: NULL
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: 0
1 row in set (0.00 sec)
ERROR:
No query specified
基于SSL复制的结果验证
(1)在master服务器上创建数据库
[iyunv@nan86 tmp]# mysql -uroot -p123456 -e 'create database slave;'
[iyunv@nan86 tmp]# mysql -uroot -p123456 -e 'show databases';
+--------------------+
| Database |
+--------------------+
| information_schema |
| employees |
| mysql |
| performance_schema |
| slave |
| test |
+--------------------+
(2)登录slave服务器验证slave数据库是否存在
[iyunv@nan85 ssl]# mysql -uroot -p123456 -e 'show databases';
+--------------------+
| Database |
+--------------------+
| information_schema |
| employees |
| mysql |
| performance_schema |
| slave |
| test |
+--------------------+
|
QQ群⑧：
窥视卡
雷达卡
发表于 2015-5-18 13:18:56
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡