一、前言
? ?備份數據庫是生產環境中的首要任務,重中之重,有時候不得不通過網絡進行數據庫的復制,這樣就需要保證數據在網絡傳輸過程中的安全性,因此使用基于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上驗證
|