基于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有详细。
wKiom1TEr7TSjiR2AADUiAc8pNs993.jpg
######双节点编译安装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)
wKiom1TEsf2h6IWhAAF3o5qAEGc130.jpg
########################配置从节点使用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 ]
wKioL1TEs2CyJZEeAAFNYhxDj4Q261.jpg
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
wKiom1TEs2ajBe5uAAB6TlyIgq0443.jpg
####从节点正常复制过来了
wKioL1TEtHCRAoc7AAC64QUrCDY790.jpg
至此，基于SSL的主从复制配置完毕！！！！！
©著作权归作者所有：来自51CTO博客作者观千剑的原创作品，如需转载，请注明出处，否则将追究法律责任
mysqlSSL 主从复制数据库
随时随地看视频
