一、前言
备份数据库是生产环境中的首要任务,重中之重,有时候不得不通过网络进行数据库的复制,这样就需要保证数据在网络传输过程中的安全性,因此使用基于SSL的复制会大加强数据的安全性
二、准备工作
1、主从服务器时间同步
|
2、mysql说明
(1)主服务器
hostname:master IP:172.16.7.202
(2)从服务器
hostname:slave IP:172.16.7.250
(3)数据目录
/mydata/data
(4)二进制日志目录
/mydata/binlogs
(5)中继日志目录
/mydata/relaylogs
三、SSL主从同步的实现
1、master(172.16.7.202)安装后配置文件
|
2、将master(172.16.7.202)做为CA服务器
|
3、为master(172.16.7.202)签发证书
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
|
[root@master CA]
# mkdir /usr/local/mysql/ssl
[root@master CA]
# cd /usr/local/mysql/ssl
[root@master ssl]
# (umask 077;openssl genrsa -out master.key 2048)
Generating RSA private key, 2048 bit long modulus
..........+++
............................................................+++
e is 65537 (0x10001)
[root@master ssl]
#
[root@master ssl]
# openssl req -new -key master.key -out master.csr -days 36500
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) []:Beijing
Locality Name (eg, city) [Default City]:Beijing
Organization Name (eg, company) [Default Company Ltd]:sina
Organizational Unit Name (eg, section) []:mysql
Common Name (eg, your name or your server's
hostname
) []:master.sina.com
Email Address []:
Please enter the following
'extra'
attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[root@master ssl]
#
[root@master ssl]
# openssl ca -in master.csr -out master.crt -days 36500
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 3 13:34:58 2014 GMT
Not After : Apr 9 13:34:58 2114 GMT
Subject:
countryName = CN
stateOrProvinceName = Beijing
organizationName = sina
organizationalUnitName = mysql
commonName = master.sina.com
X509v3 extensions:
X509v3 Basic Constraints:
CA:FALSE
Netscape Comment:
OpenSSL Generated Certificate
X509v3 Subject Key Identifier:
62:EF:37:1D:96:FF:8A:89:47:09:2D:93:74:42:14:BF:8E:AC:51:49
X509v3 Authority Key Identifier:
keyid:6B:73:D6:FE:81:13:2C:0E:EC:61:EE:F7:6F:92:91:6D:82:37:A0:11
Certificate is to be certified
until
Apr 9 13:34:58 2114 GMT (36500 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
|
4、slave生成证书申请请求
|
5、为slave(172.16.7.250)签发证书
|
6、为master及slave提供CA的证书
|
7、修改master和slave的属主、属组为"mysql"
|
8、修改mysql配置文件开启SSL加密功能
|
9、在master上验证SSL加密功能开启并创建基于密钥认证用户
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
[root@master ~]
# mysql
MariaDB [(none)]> show variables like
'%ssl%'
;
+---------------+---------------------------------+
| Variable_name | Value |
+---------------+---------------------------------+
| have_openssl | NO |
| have_ssl | YES |
| ssl_ca |
/usr/local/mysql/ssl/cacert
.pem |
| ssl_capath | |
| ssl_cert |
/usr/local/mysql/ssl/master
.crt |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key |
/usr/local/mysql/ssl/master
.key |
+---------------+---------------------------------+
MariaDB [(none)]>
MariaDB [(none)]> grant replication slave,replication client on *.* to
'repluser'
@
'172.16.%.%'
identified by
'repluser'
require ssl;
MariaDB [(none)]> flush privileges;
|
10、查看master状态信息
|
11、验证slave开启SSL加密功能
|
12、slave连接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
50
51
52
53
54
55
|
MariaDB [(none)]> change master to master_host=
'172.16.7.202'
,master_user=
'repluser'
,master_password=
'repluser'
,master_log_file=
'master-bin.000002'
,master_log_pos=652,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'
;
Query OK, 0 rows affected (0.06 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.04 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting
for
master to send event
Master_Host: 172.16.7.202
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 652
Relay_Log_File: relay.000002
Relay_Log_Pos: 536
Relay_Master_Log_File: master-bin.000002
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: 652
Relay_Log_Space: 823
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: 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_SSL_Crl:
/usr/local/mysql/ssl/cacert
.pem
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
1 row
in
set
(0.00 sec)
|
四、同步验证
1、在master上新建数据库hlbrc
|
2、在slave上验证
|
本文转自 nmshuishui 51CTO博客,原文链接:http://blog.51cto.com/nmshuishui/1405856,如需转载请自行联系原作者