基于SSL的mysql(MariaDB)主從復制

一、前言

? ?備份數據庫是生產環境中的首要任務,重中之重,有時候不得不通過網絡進行數據庫的復制,這樣就需要保證數據在網絡傳輸過程中的安全性,因此使用基于SSL的復制會大加強數據的安全性

二、準備工作

1、主從服務器時間同步

1
2
[root@master ~]# crontab -e
*/30?* * * *?/usr/sbin/ntpdate?172.16.0.1 &>/dev/null

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)安裝后配置文件

1
2
3
4
5
6
7
8
9
10
thread_concurrency = 4
datadir =?/mydata/data????#數據目錄
log-bin=/mydata/binlogs/master-bin
relay-log=/mydata/relaylogs/relay
sync_binlog = 1????#設定每1秒鐘同步一次緩沖中的數據到日志文件中
binlog_format=mixed????#二進制日志格式為混合模式
server-id???????= 1???????#主服務器的server-id=1,從的等于2
#
#
#slave(172.16.7.250)同master

2、將master(172.16.7.202)做為CA服務器

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
[root@master ~]# cd /etc/pki/CA
[root@master CA]#
[root@master CA]# (umask 077;openssl genrsa -out private/cakey.pem 2048)
Generating RSA private key, 2048 bit long modulus
..........................................+++
..................+++
e is 65537 (0x10001)
[root@master CA]#
[root@master CA]# openssl req -new -x509 -key private/cakey.pem -out cacert.pem -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 []:
[root@master CA]# touch index.txt serial crlnumber
[root@master CA]# echo 01 > serial

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生成證書申請請求

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
[root@slave ~]# mkdir /usr/local/mysql/ssl
[root@slave ~]# cd /usr/local/mysql/ssl
[root@slave ssl]# (umask 077;openssl genrsa -out slave.key 2048)
Generating RSA private key, 2048 bit long modulus
.....................................................+++
........................................+++
e is 65537 (0x10001)
[root@slave ssl]#
[root@slave ssl]# openssl req -new -key slave.key -out slave.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) []:slave.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@slave ssl]#
[root@slave ssl]#
[root@slave ssl]# scp slave.csr root@172.16.7.202:/root

5、為slave(172.16.7.250)簽發證書

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
[root@master ~]# openssl ca -in slave.csr -out slave.crt -days 36500
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? 3 13:43:28 2014 GMT
????????????Not After : Apr? 9 13:43:28 2114 GMT
????????Subject:
????????????countryName?????????????? = CN
????????????stateOrProvinceName?????? = Beijing
????????????organizationName????????? = sina
????????????organizationalUnitName??? = mysql
????????????commonName??????????????? = slave.sina.com
????????X509v3 extensions:
????????????X509v3 Basic Constraints:
????????????????CA:FALSE
????????????Netscape Comment:
????????????????OpenSSL Generated Certificate
????????????X509v3 Subject Key Identifier:
????????????????20:CB:55:9C:D0:7A:F0:25:70:AC:84:2B:8E:F4:24:FB:1F:51:48:9D
????????????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:43:28 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
[root@master ~]#
[root@master ~]#
[root@master ~]# scp slave.crt root@172.16.7.250:/usr/local/mysql/ssl/

6、為master及slave提供CA的證書

1
2
[root@master ~]# cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl/
[root@master ~]# scp /etc/pki/CA/cacert.pem root@172.16.7.250:/usr/local/mysql/ssl/

7、修改master和slave的屬主、屬組為"mysql"

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[root@master ~]# chown -R mysql.mysql /usr/local/mysql/ssl/
[root@master ~]# ll /usr/local/mysql/ssl/
total 20
-rw-r--r-- 1 mysql mysql 1330 May? 3 21:48 cacert.pem
-rw-r--r-- 1 mysql mysql 4465 May? 3 21:35 master.crt
-rw-r--r-- 1 mysql mysql 1009 May? 3 21:33 master.csr
-rw------- 1 mysql mysql 1675 May? 3 21:32 master.key
###
###
[root@slave ssl]# chown -R mysql.mysql /usr/local/mysql/ssl/
[root@slave ssl]# ll /usr/local/mysql/ssl/
total 20
-rw-r--r-- 1 mysql mysql 1330 May? 3 21:49 cacert.pem
-rw-r--r-- 1 mysql mysql 4460 May? 3 21:44 slave.crt
-rw-r--r-- 1 mysql mysql 1005 May? 3 21:40 slave.csr
-rw------- 1 mysql mysql 1679 May? 3 21:38 slave.key

8、修改mysql配置文件開啟SSL加密功能

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[root@master ~]# vim /etc/my.cnf
[mysqld]
ssl
ssl_ca =?/usr/local/mysql/ssl/cacert.pem
ssl_key =?/usr/local/mysql/ssl/master.key
ssl_cert =?/usr/local/mysql/ssl/master.crt
[root@master ~]# service mysqld restart
#
#
[root@slave ~]# vim /etc/my.cnf
[mysqld]
ssl
ssl_ca =?/usr/local/mysql/ssl/cacert.pem
ssl_key =?/usr/local/mysql/ssl/slave.key
ssl_cert =?/usr/local/mysql/ssl/slave.crt
[root@slave ~]# service mysqld restart

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狀態信息

1
2
3
4
5
6
MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File????????????? | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000002 |????? 652 |????????????? |????????????????? |
+-------------------+----------+--------------+------------------+

11、驗證slave開啟SSL加密功能

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[root@slave ~]# mysql
MariaDB [(none)]>
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/slave.crt? |
| ssl_cipher??? |???????????????????????????????? |
| ssl_crl?????? |???????????????????????????????? |
| ssl_crlpath?? |???????????????????????????????? |
| ssl_key?????? |?/usr/local/mysql/ssl/slave.key? |
+---------------+---------------------------------+

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

1
2
3
4
5
6
7
8
9
10
11
12
MariaDB [(none)]> create database hlbrc;
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database?????????? |
+--------------------+
| hlbrc????????????? |
| information_schema |
| mysql????????????? |
| performance_schema |
|?test???????????????|
+--------------------+

2、在slave上驗證

1
2
3
4
5
6
7
8
9
10
MariaDB [(none)]> show databases;
+--------------------+
| Database?????????? |
+--------------------+
| hlbrc????????????? |
| information_schema |
| mysql????????????? |
| performance_schema |
|?test???????????????|
+--------------------+









本文轉自 nmshuishui 51CTO博客,原文鏈接:http://blog.51cto.com/nmshuishui/1405856,如需轉載請自行聯系原作者

本文來自互聯網用戶投稿,該文觀點僅代表作者本人,不代表本站立場。本站僅提供信息存儲空間服務,不擁有所有權,不承擔相關法律責任。
如若轉載,請注明出處:http://www.pswp.cn/news/256469.shtml
繁體地址,請注明出處:http://hk.pswp.cn/news/256469.shtml
英文地址,請注明出處:http://en.pswp.cn/news/256469.shtml

如若內容造成侵權/違法違規/事實不符,請聯系多彩編程網進行投訴反饋email:809451989@qq.com,一經查實,立即刪除!

相關文章

ANSYS——分析實例,平面對稱問題

目錄 一、問題描述 二、問題分析 三、單元類型設置、材料屬性設置 四、建模

Ubuntu16.04安裝nginx

//ubuntu //安裝nginxcurl -LJO http://nginx.org/download/nginx-1.10.1.tar.gz tar zxvf nginx-1.10.1.tar.gz cd nginx-1.10.1.tar.gz sudo apt install gcc autoconf automake build-essential sudo apt-get install libpcre3 libpcre3-dev sudo apt-get install openssl s…

【數據結構作業—02】雙鏈表

2.實現下述要求的Locate運算的函數 問題描述 設有一個帶表頭結點的雙向鏈表L,每個結點有4個數據成員:指向前驅結點的指針prior、指向后繼結點的指針next、存放數據的成員data和訪問頻度freq。所有結點的freq初始時都為0。每當在鏈表上進行一次Locate (L,…

ANSYS——對稱模型對稱邊界的確定以及對稱邊界的約束施加問題

目錄 一、什么是對稱模型(對稱模型的特性)? 二、利用模型的對稱特性的目的?

徹底明白Java語言中的IO系統

ava的核心庫java.io提供了全面的IO接口,包括:文件讀寫,標準設備輸出等等。Java中IO是以流為基礎進行輸入輸出的,所有數據被串行化寫入輸出流,或者從輸入流讀入。在具體使用中很多初學者對Java.io包的使用非常含糊&…

第9章 接口

1、抽象類: 包含抽象方法的類叫抽象類,如果一個類包含一個或多個抽象方法(abstract void f();),該類必須被限定為抽象的,否則編譯出錯。 1、抽象類不能被實例化,實例化的工作應該交由它的子類來完成,它只需…

用node-webkit(NW.js)創建桌面程序

以往寫windows桌面程序需要用MFC、C#之類的技術,那么如果你只會web開發技術呢?或者說你有一個網站,但是你想把你的網站打包成一個桌面應用程序,該如何做呢? 答案就是用node-webkit這個開源框架,他封裝了web…

一頭扎進Node系列 - 目錄

前言 本系列是屬于初級教程。博主我也還只是一個node的新兵蛋子,想通過學習官網的API文檔,慢慢的打好Node基礎。當然后期這系列文檔會慢慢完善,并且會添加一些項目實戰中遇到的一些問題以及解決方案!如果你也是初學者,…

ANSYS——“There is at least 1 small equation solver pivot term”問題的解決辦法

目錄 問題出現的原因 問題解決辦法 1、根據提示對節點進行約束的添加

JAVA中幾個常用的方法

類Object是類層次結構的根類&#xff0c;每一個類都使用Object作為超類&#xff0c;所有對象&#xff08;包括數組&#xff09;都實現這個類的方法。jdk1.5中&#xff0c;描述了該類中的11個方法 1.getClass public final Class<? extends Object> getClass() 返回一個對…

ANSYS——載荷的方向

目錄 一、壓力的方向(FORCE) 1、為正的情況 2、為負的情況 二、壓強的方向(PRESSURE)

kindeditor用法簡單介紹(轉)

1&#xff0c;首先去官網下載http://www.kindsoft.net/ 2&#xff0c;解壓之后如圖所示&#xff1a; 由于本人做的是用的是JSP&#xff0c;所以ASP,PHP什么的就用不上了&#xff0c;直接把那些去掉然后將整個文件夾扔進Myeclipse&#xff0c;如圖&#xff1a; 里面有個報錯&am…

hadoop 分片與分塊,map task和reduce task的理解

分塊&#xff1a;Block HDFS存儲系統中&#xff0c;引入了文件系統的分塊概念&#xff08;block&#xff09;&#xff0c;塊是存儲的最小單位&#xff0c;HDFS定義其大小為64MB。與單磁盤文件系統相似&#xff0c;存儲在 HDFS上的文件均存儲為多個塊&#xff0c;不同的是&#…

SOLIDWORKS——參數化建模

https://www.sohu.com/a/259742200_100042821 知識點&#xff1a;投影曲線、曲面填充、掃描、外觀設置 建模步驟 1.先在工具——方程式里輸入一個直徑的變量A120 。 2.在前視基準面上草繪圓&#xff0c;畫一條直徑。直徑等于變量A。 3.旋轉&#xff0c;選擇粉色區域。 4.上視…

Arch 常用工具

一、網絡瀏覽pacman -S firefox firefox-i18n注&#xff1a;該命令中的前者為 Firefox 主程序,后者為語言包。pacman -S opera二、圖像編輯pacman -S gimp #圖像編輯軟件首選 GIMPpacman -S inkscape #矢量圖形編輯軟件Inkscapepacman -S scrot #…

Androd安全——反編譯技術完全解析

0&#xff0e;前言單純從技術角度上來講&#xff0c;掌握反編譯功能確實是一項非常有用的技能。另外既然別人可以反編譯程序&#xff0c;我們當然有理由應該對程序進行一定的保護&#xff0c;因此代碼混淆也是我們必須要掌握的一項技術。看完此篇如果對代碼混淆也感興趣&#x…

python——shape 與reshape

轉載自:https://blog.csdn.net/u010916338/article/details/84066369 shape()和reshape()都是數組array中的方法 numpy中reshape函數的三種常見相關用法 numpy.arange(n).reshape(a, b) 依次生成n個自然數&#xff0c;并且以a行b列的數組形式顯示np.arange(16).reshape(2,…

誤刪了microsoft visual c++后如何正常運行matlab

誤刪了microsoft visual c后如何正常運行matlab 本人在卸載visual studio2013的時候&#xff0c;因為這個軟件卸載的過程中出現一些問題&#xff0c;誤將visual c當成VS的組件一同刪除了。但是在打開matlab 時發現出錯&#xff0c;matlab打開后會出現下面的界面。 出現這個問題…

iScreenLocker 3.1.8 安卓鎖屏通知--蘋果一樣的體驗

*軟件介紹:蘋果鎖屏通知(iScreenLocker)是一款android上ios風格的鎖屏軟件。它顛覆安智通知設計&#xff0c;將原來狀態欄的通知搬到鎖屏界面上來&#xff0c;能夠在桌面輕松收發短信,微博,微信等消息。它獨有的消息喚醒功能。能使手機從待機界面喚醒而消耗非常少的電量。手指輕…

JSP慕課網階段用戶登錄小例子(不用數據庫)

getAttribute和setAttribute一起使用&#xff0c;而getParameter用于取得如request傳來的參數。 Web是請求/響應架構的使用&#xff0c;而request和response就是在服務器端生成的相應的兩個對象&#xff0c;request能夠獲取客戶端傳遞的參數及相關的一些信息&#xff0c;而resp…