欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

基于SSL的mysql主从复制

程序员文章站 2022-06-14 09:13:12
...

基于SSL的mysql主从复制 【背景】 MySQL的协议是明文的,当复制一些重要数据时。有时需要用到SSL功能,以保证数据的安全性。 【准备 】 准备前期准备 一 . 主从时间一致性 [root@node3support-files]#crontab-e####主节点*/3****/usr/sbin/ntpdate172.16.0.1

基于SSL的mysql主从复制

【背景】

MySQL的协议是明文的,当复制一些重要数据时。有时需要用到SSL功能,以保证数据的安全性。

【准备

准备前期准备

.主从时间一致性

[root@node3 support-files]# crontab -e                  ####主节点
*/3 * * * *  /usr/sbin/ntpdate 172.16.0.1 &> /dev/null
[root@node1 CA ]# crontab -e                                  ####从节
 */3 * * * * /usr/sbin/ntpdate 172.16.0.1 &> /dev/null

.主从复制使用最小小权限

三.CA是放在主节点

四.如果想要使用SSL功能,需要自己编译定制。这里不在演示:corosync+pacemaker+mysql有详细。


基于SSL的mysql主从复制

######双节点编译安装MySQL。

【配置各节点证书】

###############################CA生成私钥###################################

[root@node1 CA ]#(umask 077;openssl genrsa -out private/cakey.pem 1024)
Generating RSA private key, 1024 bit long modulus
...................++++++
................++++++
e is 65537 (0x10001)

###############################CA生成自签证书################################

[root@node1 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) []:HA
Locality Name (eg, city) [Default City]:ZZ
Organization Name (eg, company) [Default Company Ltd]:magedu
Organizational Unit Name (eg, section) []:14qi
Common Name (eg,your name or your server's hostname) []:cacert
Email Address []:admin.stu11.com
[root@node1 CA ]# touch index.txt          
[root@node1 CA ]# echo 01 > serial

#################################master生成私钥###################################

[root@node1 CA ]# cd /etc/mysql/ssl/
[root@node1 ssl ]# (umask 077;openssl genrsa -out master.key 1024)
Generating RSA
private key, 1024 bit long modulus
...................................++++++
.............................++++++
e is 65537 (0x10001)

###############################master生成证书签署请求##############################

[root@node1ssl ]# 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) []:HA
Locality Name (eg, city) [Default City]:ZZ
Organization Name (eg, company) [Default Company Ltd]:magedu
Organizational Unit Name (eg, section) []:14qi
Common Name (eg, your name or your server's hostname) []:master.crt
Email Address[]:admin@stu11.com
Please enter thefollowing 'extra' attributes
to be sent with your certificate request
A challenge password[]:
An optional company name []:

###############################master签署证书######################################

[root@node1 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: Jan 25 07:12:12 2015
GMT
            Not After : Jan 25 07:12:12 2016
GMT
        Subject:
            countryName               = CN
            stateOrProvinceName       = HA
            organizationName          = magedu
            organizationalUnitName    = 14qi
            commonName                = master.crt
            emailAddress              = admin@stu11.com
        X509v3 extensions:
            X509v3 Basic Constraints:
                CA:FALSE
            Netscape Comment:
                OpenSSL Generated Certificate
            X509v3 Subject Key Identifier:
               
93:50:74:97:39:91:86:5A:1F:C6:2F:6A:87:FB:77:04:7B:70:33:5C
            X509v3 Authority Key Identifier:            
keyid:C0:69:22:4E:9A:E5:BD:13:2B:BD:93:7B:0F:99:E6:0F:3A:FA:40:7E
Certificate is to be
certified until Jan 25 07:12:12 2016 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
[root@node1 ssl ]#
ls
master.crt  master.csr 
master.key
[root@node1 ssl ]#
chown -R mysql:mysql *
[root@node1 ssl ]#ll
total 16 
-rw-r--r-- 1 mysql mysql 1013 Jan 25 15:12 cacert.pem
-rw-r--r-- 1 mysql mysql 3161 Jan 25 15:12 master.crt
-rw-r--r-- 1 mysql mysql  680 Jan 25 15:11 master.csr
-rw------- 1 mysql mysql  887 Jan 25 15:09 master.key

#################################slave生成私钥###################################

[root@node3 ssl]# (umask 077;openssl genrsa -out slave.key 1024)
Generating RSA private key, 1024 bit long modulus
..........................++++++
.........................++++++
e is 65537 (0x10001)

###############################slave生成签署请求################################

[root@node3 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) []:HA
Locality Name (eg, city) [Default City]:ZZ
Organization Name (eg, company) [Default Company Ltd]:magedu
Organizational Unit Name (eg, section) []:14qi
Common Name (eg, your name or your server's hostname) []:slave.cert
Email Address []:admin@stu11.com 
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

###############################slave签署与收回###################################

[root@node3 ssl]# scp slave.csr 172.16.249.141:/etc/pki/CA/   
[root@node1 CA ]# openssl ca -in slave.csr -out 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: Jan 25 07:21:11 2015
GMT
            Not After : Jan 25 07:21:11 2016
GMT
        Subject:
            countryName               = CN
            stateOrProvinceName       = HA
            organizationName          = magedu
            organizationalUnitName    = 14qi
            commonName                = slave.cert
            emailAddress              = admin@stu11.com
        X509v3 extensions:
            X509v3 Basic Constraints:
                CA:FALSE
            Netscape Comment:
                OpenSSL Generated Certificate
            X509v3 Subject Key Identifier:
               
F8:06:AD:F0:1D:8A:78:62:ED:A7:FF:BB:7A:F6:79:14:D4:FB:26:39
            X509v3 Authority Key Identifier:           
keyid:C0:69:22:4E:9A:E5:BD:13:2B:BD:93:7B:0F:99:E6:0F:3A:FA:40:7E
Certificate is to be certified until Jan 25 07:21:11 2016 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
[root@node1 CA ]# scp slave.crt 172.16.11.3:/etc/mysql/ssl/
[root@node1 CA ]# scp cacert.pem 172.16.11.3:/etc/mysql/ssl/
[root@node3 ssl]# chown -R mysql:mysql *
[root@node3 ssl]# ll
total 16
-rw-r--r-- 1 mysql mysql 1013 Jan 25 15:22 cacert.pem
-rw-r--r-- 1 mysql mysql 3161 Jan 25 15:21 slave.crt
-rw-r--r-- 1 mysql mysql  680 Jan 25 15:19 slave.csr
-rw------- 1 mysql mysql  887 Jan 25 15:14 slave.key

########################配置主节点使用SSL###################################

################主节点配置文件

 41 thread_concurrency = 8
 42 datadir = /mydata                 ####数据目录
 43 innodb_file_per_table =on         ####每表一个innodb
 44 skip_name_resolve =on             ####跳过名称解析
 45 ssl                               ####开启ssl功能
 46 ssl_ca =/etc/mysql/ssl/cacert.pem         ####指定ca位置
 47 ssl_key =  /etc/mysql/ssl/master.key      ####主节点密钥
 48 ssl_cert =  /etc/mysql/ssl/master.crt     ####主节点证书
 63log-bin=/bin/log/master-bin                ####二进制文件开启
 66 binlog_format=mixed                       ####二进制文件格式
 71 server-id   = 10                          ####唯一的server-id
[root@node1 CA ]# service mysqld start              ####启动主节点
Starting MySQL                                            [  OK  ]
##############授权一个可以让从节点复制的用户,并请强制要求使用##############
mysql> grant replication slave,replication client on *.* to 'cpuser'@'%' identified by
'magedu' require ssl;
Query OK, 0 rows affected (0.00 sec) 
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

基于SSL的mysql主从复制

########################配置从节点使用SSL###################################

################从节点配置文件

41 thread_concurrency= 8
42 datadir = /mydata          ####数据目录
43 innodb_file_per_table =on  ####每表一个innodb
44 skip_name_resolve =on      ####跳过名称解析
45 ssl                                       ###开启ssl功能
46 ssl_ca =/etc/mysql/ssl/cacert.pem         ####指定ca位置
47 ssl_key =  /etc/mysql/ssl/slave.key       ####主节点密钥
48 ssl_cert =  /etc/mysql/ssl/slave.crt      ####主节点证书
66 binlog_format=mixed                       ####二进制文件格式
71 server-id   = 10                          ####唯一的server-id
72relay-log=relay-bin                        ####开启中继日志
73read-only = on                             ####从节点只读
[root@node3 CA ]# service mysqld start       ####启动主节点
Starting MySQL                                            [  OK  ]

基于SSL的mysql主从复制

mysql> show
master status;
+-------------------+----------+--------------+------------------------------------+
|File               | Position |Binlog_Do_DB  | Binlog_Ignore_DB                   |
+-------------------+----------+--------------+------------------------------------+
|master-bin.000006  |     669  |              |                                    |
+-------------------+----------+--------------+------------------------------------+

############################配置从节点使用ssl连接主节点##################################

mysql> change master to
master_host='172.16.249.141',master_user='cpuser',master_password='magedu',master_log_file='master-bin.000006',master_log_pos=669,master_ssl=1,master_ssl_ca='/etc/mysql/ssl/cacert.pem',master_ssl_cert='/etc/mysql/ssl/slave.crt',master_ssl_key='/etc/mysql/ssl/slave.key';
Query OK, 0 rows affected (0.14 sec)
mysql> start slave;                                                     
#####启动从节点线程
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;                                   
#####查看状态
*******************************************1. row *************************************
  Slave_IO_State: Waiting for master to send event
  Master_Host: 172.16.249.141
  Master_User: cpuser
  Master_Port: 3306
  Connect_Retry: 60
  Master_Log_File: master-bin.000006
  Read_Master_Log_Pos: 669
  Relay_Log_File: relay-bin.000002
  Relay_Log_Pos: 536
  Relay_Master_Log_File: master-bin.000006
  Slave_IO_Running: Yes                             ####IO线程准备就绪
  Slave_SQL_Running: Yes                            ####SQL线程准备就绪
  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: 669
  Relay_Log_Space: 827
  Until_Condition: None
  Until_Log_File: 
  Until_Log_Pos: 0
  Master_SSL_Allowed: Yes
  Master_SSL_CA_File: /etc/mysql/ssl/cacert.pem
  Master_SSL_CA_Path:
  Master_SSL_Cert: /etc/mysql/ssl/slave.crt
  Master_SSL_Cipher:
  Master_SSL_Key: /etc/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: 10
  Master_SSL_Crl: /etc/mysql/ssl/cacert.pem
  Master_SSL_Crlpath:
  Using_Gtid: No
  Gtid_IO_Pos:

###############################测试结果############################################

#####主节点创建库tb1

基于SSL的mysql主从复制

####从节点正常复制过来了

基于SSL的mysql主从复制


至此,基于SSL的主从复制配置完毕!!!!!