Mysql---基于SSL的主从复制
浏览数：34 /
时间：2015年06月12日
由于mysql的主从复制是明文传送的，但如果在生产环境中跨网络我们使用的主从还是明文传送的话，就保证不了数据的安全性，为了解决这一问题，我们需要加密进行传送，也就是基于SSL的加密方法进行传输数据。@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ 修改配置文件 在Master上： vi /etc/my.cnf [mysqld] log-bin=master-bin binlog_format=mixed server-id=1 ssl /etc/init.d/mysqld restart 在Slave上： vi /etc/my.cnf [mysqld] log-bin=slave-bin binlog_format=mixed server-id=10 ssl /etc/init.d/mysqld restart (1)将master服务器自己做成CA服务器 [root@nan86 tmp]# cd /etc/pki/CA/ [root@nan86 CA]# (umask 077;openssl genrsa -out private/cakey.pem 2048) Generating RSA private key, 2048 bit long modulus .......+++ ........+++ e is 65537 (0x10001) [root@nan86 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) []:ShangHai Locality Name (eg, city) [Default City]:PuDong Organization Name (eg, company) [Default Company Ltd]:Allen Organizational Unit Name (eg, section) []:Tech Common Name (eg, your name or your server‘s hostname) []:master.allen.com Email Address []: [root@nan86 CA]# touch index.txt [root@nan86 CA]# echo 01>serial (2)为master创建证书申请并由CA服务器签发证书 [root@nan86 CA]# mkdir /usr/local/mysql/ssl [root@nan86 CA]# cd /usr/local/mysql/ssl/ [root@nan86 ssl]# (umask 077;openssl genrsa -out master.key 2048) Generating RSA private key, 2048 bit long modulus .................+++ ................................................................................................................+++ e is 65537 (0x10001) [root@nan86 ssl]# 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) []:ShangHai Locality Name (eg, city) [Default City]:PuDong Organization Name (eg, company) [Default Company Ltd]:Allen Organizational Unit Name (eg, section) []:Tech Common Name (eg, your name or your server‘s hostname) []:master.allen.com Email Address []:[email protected] Please enter the following ‘extra‘ attributes to be sent with your certificate request A challenge password []: An optional company name []: [root@nan86 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: Feb 24 10:41:53 2014 GMT Not After : Feb 24 10:41:53 2015 GMT Subject: countryName = CN stateOrProvinceName = ShangHai organizationName = Allen organizationalUnitName = Tech commonName = master.allen.com emailAddress = [email protected] X509v3 extensions: X509v3 Basic Constraints: CA:FALSE Netscape Comment: OpenSSL Generated Certificate X509v3 Subject Key Identifier: AA:9B:97:2D:72:00:38:55:DB:77:86:3E:87:1C:30:C4:12:BA:69:F0 X509v3 Authority Key Identifier: keyid:F4:DD:53:C2:28:E4:C9:1B:F0:CA:1F:5F:79:35:E5:32:4E:7A:55:39 Certificate is to be certified until Feb 24 10:41:53 2015 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 (3)为slave服务器创建证书申请 在slave [root@nan85 ~]# mkdir /usr/local/mysql/ssl [root@nan85 ~]# cd /usr/local/mysql/ssl/ [root@nan85 ssl]# (umask 077;openssl genrsa -out slave.key 2048) Generating RSA private key, 2048 bit long modulus .......................................+++ .........+++ e is 65537 (0x10001) [root@nan85 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) []:ShangHai Locality Name (eg, city) [Default City]:PuDong Organization Name (eg, company) [Default Company Ltd]:Allen Organizational Unit Name (eg, section) []:Tech Common Name (eg, your name or your server‘s hostname) []:slave.allen.com Email Address []: Please enter the following ‘extra‘ attributes to be sent with your certificate request A challenge password []: An optional company name []: (4)为slave服务器签署证书 #####将证书申请请求拷贝到CA服务器签署 slave上 [root@nan85 ssl]# scp slave.csr 10.10.54.86:/tmp/ 在master上 [root@nan86 tmp]# openssl ca -in /tmp/slave.csr -out /tmp/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: Feb 24 11:09:32 2014 GMT Not After : Feb 24 11:09:32 2015 GMT Subject: countryName = CN stateOrProvinceName = ShangHai organizationName = Allen organizationalUnitName = Tech commonName = slave.allen.com X509v3 extensions: X509v3 Basic Constraints: CA:FALSE Netscape Comment: OpenSSL Generated Certificate X509v3 Subject Key Identifier: 7C:5A:7F:49:88:29:8C:C3:33:7B:E7:46:71:C8:68:47:60:F5:69:27 X509v3 Authority Key Identifier: keyid:F4:DD:53:C2:28:E4:C9:1B:F0:CA:1F:5F:79:35:E5:32:4E:7A:55:39 Certificate is to be certified until Feb 24 11:09:32 2015 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 #####签署好证书申请拷贝到slave服务器 在主上： [root@nan86 tmp]# scp /tmp/slave.crt 10.10.54.85:/usr/local/mysql/ssl/ (5)将CA证书拷贝到slave服务器一份并为master拷贝一份 主机： [root@nan86 tmp]# scp /etc/pki/CA/cacert.pem 10.10.54.85:/usr/local/mysql/ssl/ [root@nan86 tmp]# cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl/ (6)修改master和slave服务器证书属主、属组为"mysql"用户 主机： [root@nan86 tmp]# chown -R mysql.mysql /usr/local/mysql/ssl/ [root@nan86 tmp]# ll /usr/local/mysql/ssl/ total 20 -rw-r--r-- 1 mysql mysql 1330 2月 24 19:17 cacert.pem -rw-r--r-- 1 mysql mysql 4532 2月 24 18:42 master.crt -rw-r--r-- 1 mysql mysql 1054 2月 24 18:39 master.csr -rw------- 1 mysql mysql 1679 2月 24 18:36 master.key 从机： [root@nan85 ssl]# chown -R mysql.mysql /usr/local/mysql/ssl/ [root@nan85 ssl]# ll /usr/local/mysql/ssl/ total 20 -rw-r--r-- 1 mysql mysql 1330 2月 24 19:16 cacert.pem -rw-r--r-- 1 mysql mysql 4456 2月 24 19:13 slave.crt -rw-r--r-- 1 mysql mysql 1009 2月 24 18:47 slave.csr -rw------- 1 mysql mysql 1675 2月 24 18:45 slave.key (7)在master与slave服务器修改主配置文件开启SSL加密功能 ####修改master数据库 [root@nan86 tmp]# vim /etc/my.cnf ssl_ca=/usr/local/mysql/ssl/cacert.pem ssl_cert=/usr/local/mysql/ssl/master.crt ssl_key=/usr/local/mysql/ssl/master.key [root@nan86 tmp]# /etc/init.d/mysqld restart ####修改slave数据库 [root@nan85 ssl]# vim /etc/my.cnf ssl_ca=/usr/local/mysql/ssl/cacert.pem ssl_cert=/usr/local/mysql/ssl/slave.crt ssl_key=/usr/local/mysql/ssl/slave.key [root@nan85 ssl]# /etc/init.d/mysqld restart (8)在master服务器查看SSL加密是否开启，然后创建授权一个基于密钥认证的用户 mysql> show variables like ‘%ssl%‘; +---------------+---------------------------------+ | Variable_name | Value | +---------------+---------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /usr/local/mysql/ssl/cacert.pem | | ssl_capath | | | ssl_cert | /usr/local/mysql/ssl/master.crt | | ssl_cipher | | | ssl_key | /usr/local/mysql/ssl/master.key | +---------------+---------------------------------+ mysql> grant replication client,replication slave on *.* to ‘slave‘@‘10.10.54.85‘ identified by ‘slave‘ require ssl; Query OK, 0 rows affected (1.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) (9)查看master服务器二进制日志文件和事件位置用于slave服务器链接从这个位置开始复制 mysql> show master status; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-bin.000021 | 806 | | | +-------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) (10)测试使用加密用户指定的密钥链接服务器 在slave上 [root@nan85 ssl]# mysql -uroot -p123 -h 10.10.54.86 --ssl-ca=/usr/local/mysql/ssl/cacert.pem --ssl-cert=/usr/local/mysql/ssl/slave.crt --ssl-key=/usr/local/mysql/ssl/slave.key Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 33 Server version: 5.5.30-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> (11)查看slave服务器SSL是否开启并连接master服务器 #####产看是否开启SSL mysql> show variables like ‘%ssl%‘; +---------------+---------------------------------+ | Variable_name | Value | +---------------+---------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /usr/local/mysql/ssl/cacert.pem | | ssl_capath | | | ssl_cert | /usr/local/mysql/ssl/slave.crt | | ssl_cipher | | | ssl_key | /usr/local/mysql/ssl/slave.key | +---------------+---------------------------------+ #####链接master服务器 change master to master_host=‘10.10.54.86‘,master_user=‘slave‘,master_password=‘slave‘, master_log_file=‘master-bin.000021‘,master_log_pos=806,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‘; #####获取命令帮助 ###@@@@@@@@@@@ mysql> help change master to | MASTER_SSL = {0|1} #是否使用SSL功能 | MASTER_SSL_CA = ‘ca_file_name‘ #CA证书位置 | MASTER_SSL_CERT = ‘cert_file_name‘ #指定自己的证书文件 | MASTER_SSL_KEY = ‘key_file_name‘ #指定自己的密钥文件 (12)查看slave服务器的状态 mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 10.10.54.86 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000021 Read_Master_Log_Pos: 353 Relay_Log_File: nan85-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: master-bin.000021 Slave_IO_Running: Connecting Slave_SQL_Running: Yes Replicate_Do_DB: a 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: 353 Relay_Log_Space: 107 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: NULL 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: 0 1 row in set (0.00 sec) ERROR: No query specified 基于SSL复制的结果验证 (1)在master服务器上创建数据库 [root@nan86 tmp]# mysql -uroot -p123456 -e ‘create database slave;‘ [root@nan86 tmp]# mysql -uroot -p123456 -e ‘show databases‘; +--------------------+ | Database | +--------------------+ | information_schema | | employees | | mysql | | performance_schema | | slave | | test | +--------------------+ (2)登录slave服务器验证slave数据库是否存在 [root@nan85 ssl]# mysql -uroot -p123456 -e ‘show databases‘; +--------------------+ | Database | +--------------------+ | information_schema | | employees | | mysql | | performance_schema | | slave | | test | +--------------------+
参考资料：
http://my.oschina.net/u/1454868/blog/202770
本文出自 “笔记” 博客，请务必保留此出处http://sunflower2.blog.51cto.com/8837503/1651701
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
