# Mysql 主从复制配置SSL ## 简介 [Mysql主从复制原理及搭建案例](https://chenyongjun.vip/articles/6) 介绍了Mysql主从复制原理和搭建案例。 当Master-Slave通过Internet传输时，数据进行明文传输，他人可以窃取复制的数据，下面介绍通过SSL对传输数据进行加密。 可以参考Mysql官方文档，提供了脚本。链接：[6.3.9 Creating SSL Certificates and Keys Using openssl](https://dev.mysql.com/doc/refman/5.5/en/creating-ssl-files-using-openssl.html) 本文包含以下部分： * 创建CA证书中心 * 创建Master端和Slave端证书 * 配置Mysql SSL通讯 * 问题记录 ## 创建CA证书中心 创建自签名根证书和CA中心用于管理证书，这里使用openssl创建证书，ssl默认配置在/etc/ssl/openssl.cnf，下面是配置文件主要属性说明： ``` dir = /etc/ssl/demoCA # 根目录，可自行修改，我配成了默认的 demoCA目录 certs = $dir/certs # 证书存放目录 crl_dir = $dir/crl # 吊销证书存放目录 database = $dir/index.txt # 证书索引文件 #unique_subject = no # 是否允许创建相同主题(subject)的证书[yes,no] new_certs_dir = $dir/newcerts # 新证书存放目录 certificate = $dir/cacert.pem # CA证书名称 serial = $dir/serial # 当前序列号 private_key = $dir/private/cakey.pem# CA私钥文件 ``` 下面创建CA证书 ``` cd /etc/ssl mkdir demoCA cd demoCA mkdir private certs crl newcerts #创建openssl产生证书所需要的目录，在上图截取的openssl.cnf中都有 touch index.txt #创建证书索引文件 echo 00 > serial #创建证序列号文件 #创建CA私钥和自签名证书(-x509参数用于自签名证书) openssl req -new -x509 -keyout private/cakey.pem -out cacert.pem -days 3600 #下面是交互信息(自行设置证书密码及相关属性),需填写部分已加粗 Generating a 2048 bit RSA private key .............................................................................................................................+++ .................................................................................+++ writing new private key to 'private/cakey.pem' Enter PEM pass phrase: Verifying - Enter PEM pass phrase: ----- 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) [AU]:CN State or Province Name (full name) [Some-State]:. Locality Name (eg, city) []: Organization Name (eg, company) [Internet Widgits Pty Ltd]:Mysql Organizational Unit Name (eg, section) []: Common Name (e.g. server FQDN or YOUR name) []:CA Email Address []: ``` ## 创建Master端和Slave端证书 ``` #创建master端私钥和证书申请文件 openssl req -new -keyout master.key -out master.csr -days 3600 #下面是交互信息(自行设置证书密码及相关属性，除密码和common name外其他属性与CA证书保持一致),需填写部分添加已加粗 Generating a 2048 bit RSA private key ...........+++ ............+++ writing new private key to 'master.key' Enter PEM pass phrase: Verifying - Enter PEM pass phrase: ----- 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) [AU]:CN State or Province Name (full name) [Some-State]:. Locality Name (eg, city) []: Organization Name (eg, company) [Internet Widgits Pty Ltd]:Mysql Organizational Unit Name (eg, section) []: Common Name (e.g. server FQDN or YOUR name) []:master Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []: #删除秘钥文件密码 openssl rsa -in master.key -out master.key #用CA签发master端证书(申请信息位于证书申请文件中) openssl ca -cert cacert.pem -policy policy_anything -out master.crt -infiles master.csr #创建客户端私钥和证书申请文件(除密码和comman name外其他属性保存与CA证书一致，common name可设置为slave) openssl req -new -keyout slave.key -out slave.csr -days 3600 #删除秘钥文件密码 openssl rsa -in slave.key -out slave.key #用CA签发slave端证书 openssl ca -cert cacert.pem -policy policy_anything -out slave.crt -infiles slave.csr #验证证书是否ok openssl verify -CAfile cacert.pem master.crt slave.crt 显示： master.crt: OK slave.crt: OK ``` 至此，SSL通讯所需要的证书已准备好。 ## 配置Mysql SSL通讯 Mysql默认未开启ssl通讯，下面配置master端Mysql SSL通讯。配置master mysql的 /etc/mysql/my.cnf，在[mysqld]下设置： 开启ssl，配置CA证书和master私钥文件及证书。可以将这些文件拷贝到其他位置(/etc/mysql/ssl)，这里仅做展示，不移动文件了 ``` ssl ssl-ca=/etc/ssl/demoCA/cacert.pem ssl-cert=/etc/ssl/demoCA/master.crt ssl-key=/etc/ssl/demoCA/master.key ``` 重启mysql使配置生效 ``` #查看mysql ssl状态 mysql> show variables like '%ssl%'; +---------------+----------------------------+ | Variable_name | Value | +---------------+----------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /etc/ssl/demoCA/cacert.pem | | ssl_capath | | | ssl_cert | /etc/ssl/demoCA/master.crt | | ssl_cipher | | | ssl_key | /etc/ssl/demoCA/master.key | +---------------+----------------------------+ 7 rows in set (0.00 sec) ``` 现在master端已设置好，可以设置用于主从复制的用户只能通过ssl连接 ```mysql grant all privileges on *.* to 'repluser'@'%' identified by '123456' require ssl; #如果需要取消ssl使用 require none ``` 下面配置slave端mysql 将生成的 slave.key, slave.crt和cacert.pem拷贝到slave服务器/etc/mysql/ssl 或其他任何目录，这里以/etc.mysql/ssl为例。 先测试生成的slave证书是否可用 ``` mysql -urepluser -p123456 -h120.24.111.58 --ssl-ca=cacert.pem --ssl-cert=slave.crt --ssl-key=slave.key ``` 如果可以成功连接说明证书没问题，可以成功连接上master，接着配置slave的mysql ssl通讯，配置slave mysql 的 my.cnf ``` ssl ssl_ca = /etc/mysql/ssl/cacrt.pem ssl_cert = /etc/mysql/ssl/slave.crt ssl_key = /etc/mysql/ssl/slave.key ``` 配置完后重启mysql，show variables like '%ssl%'; 查看是否成功开启ssl。 然后查看master的binlog文件和偏移量重新设置slave(可参考上一遍文章，见本文开头链接) ``` mysql> stop slave; mysql> change master to master_host='120.24.111.58', master_user='repluser', master_password='123456', master_port=3306, master_log_file='mysql-bin.000017', master_log_pos=399, master_connect_retry=10, 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'; mysql> start slave; mysql> show slave status\G; ``` 成功后如下： ``` mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 120.24.111.58 Master_User: repluser Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000017 Read_Master_Log_Pos: 399 Relay_Log_File: mysqld-relay-bin.000010 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000023 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: 387 Relay_Log_Space: 513 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_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: 1 1 row in set (0.00 sec) ``` 如果有异常可以查看mysql日志，/var/log/mysql/error.log ## 问题记录 * 无法获取证书，Mysql无法启用ssl通讯 ``` SSL error: Unable to get certificate from '/etc/ssl/demoCA/master.crt' 160619 11:46:30 [Warning] Failed to setup SSL 160619 11:46:30 [Warning] SSL error: Unable to get certificate ``` 参考mysql forums问题记录：[http://forums.mysql.com/read.php?26,393495,393636#msg-393636](http://forums.mysql.com/read.php?26,393495,393636#msg-393636) 编辑sudo vi /etc/apparmor.d/usr.sbin.mysqld 添加 ``` /etc/ssl/demoCA/*.pem r, /etc/ssl/demoCA/*.crt r, /etc/ssl/demoCA/*.key r, ``` * 无法获取.key文件，不能启用mysql ssl ``` SSL error: Unable to get private key from '/etc/ssl/demoCA/master.key' 160619 12:56:56 [Warning] Failed to setup SSL 160619 12:56:56 [Warning] SSL error: Unable to get private key ``` 检查key文件是否删除了密码