基于SSL的mysql主从复制

基于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有详细。

######双节点编译安装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###################################

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

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  ]

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的主从复制配置完毕！！！！！