当mysql跨越互联网进行复制时别人可以窃取到mysql的复制信息，这些信息是明文的，因此存在不安全性，这里通过ssl对复制的信息进行加密。
创新互联专业为企业提供
石河子网站建设、石河子做网站、石河子网站设计、石河子网站制作等企业网站建设、网页设计与制作、石河子企业网站模板建站服务，十余年
石河子做网站经验，不只是建网站，更提供有价值的思路和整体网络服务。
准备环境:
1.系统环境：Centos6.5
2.数据库版本：5.5.36-MariaDB-log MariaDB Server
3.Host:
Master主机: master.samlee.com 172.16.100.7
Slave主机: slave.samlee.com 172.16.100.8
Public主机: public.samlee.com 172.16.100.9
----------------------------------------------------------------------------------------------------------
实现过程如下:
Public主机上操作：
1.创建证书中心:
--在public主机上创建证书中心
# cd /etc/pki/CA/
--生成私钥
# (umask 077;openssl genrsa -out /etc/pki/CA/private/cakey.pem 2048)
生成自签证书,由于需要输入大量用户信息，因此编辑证书的配置文件，在私有的CA上创建证书要注意所有的用户信息要和CA中的一致，从国家到部门都要相同，否则会造成证书无法使用。
--根据实验要求修改配置文件如下:
# vim /etc/pki/tls/openssl.cnf
[ req_distinguished_name ]
countryName_default = CN
stateOrProvinceName_default = GUANGDONG
localityName_default = GUANGZHOU
0.organizationName_default = JUST.LTD
organizationalUnitName_default = IT
生成自签证书--以CAserver的身份自建CA证书
# openssl req -new -x509 -key /etc/pki/CA/private/cakey.pem -out /etc/pki/CA/cacert.pem -days 3650
Country Name (2 letter code) [CN]:
State or Province Name (full name) [GUANGDONG]:
Locality Name (eg, city) [GUANGZHOU]:
Organization Name (eg, company) [JUST.LTD]:
Organizational Unit Name (eg, section) [IT]:
Common Name (eg, your name or your server's hostname) []:public.samlee.com
创建证书编号
# touch {index.txt,serial}
# echo 01 > serial
给Master服务器生成key、证书请求、证书:
--创建私钥
# cd /etc/pki/CA/
# (umask 077;openssl genrsa -out master.key 2048)
--生成证书申请
# openssl req -new -key master.key -out master.csr
Country Name (2 letter code) [CN]:
State or Province Name (full name) [GUANGDONG]:
Locality Name (eg, city) [GUANGZHOU]:
Organization Name (eg, company) [JUST.LTD]:
Organizational Unit Name (eg, section) [IT]:
Common Name (eg, your name or your server's hostname) []:master.samlee.com
--在证书服务器上对master的证书进行签发
# openssl ca -in master.csr -out master.crt -days 3650
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: Jun 23 00:09:31 2016 GMT
Not After : Jun 21 00:09:31 2026 GMT
Subject:
countryName = CN
stateOrProvinceName = GUANGDONG
organizationName = JUST.LTD
organizationalUnitName = IT
commonName = master.samlee.com
Data Base Updated --看到此项出现说明证书签发成功了
给Slave服务器生成key、证书请求、证书:
--创建私钥
# cd /etc/pki/CA/
# (umask 077;openssl genrsa -out slave.key 2048)
--生成证书申请
# openssl req -new -key slave.key -out slave.csr
Country Name (2 letter code) [CN]:
State or Province Name (full name) [GUANGDONG]:
Locality Name (eg, city) [GUANGZHOU]:
Organization Name (eg, company) [JUST.LTD]:
Organizational Unit Name (eg, section) [IT]:
Common Name (eg, your name or your server's hostname) []:slave.samlee.com
--在证书服务器上对master的证书进行签发
# openssl ca -in slave.csr -out slave.crt -days 3650
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: Jun 23 00:18:21 2016 GMT
Not After : Jun 21 00:18:21 2026 GMT
Subject:
countryName = CN
stateOrProvinceName = GUANGDONG
organizationName = JUST.LTD
organizationalUnitName = IT
commonName = slave.samlee.com
Data Base Updated --看到此项出现说明证书签发成功了
Master与Slavr主机上操作：
2.在Master主机和Slave主机上安装MariaDB
使用软件安装包为mariadb-5.5.36-linux-x86_64.tar.gz，上传至root目录下，安装配置Mairadb，并设置mydata目录为数据和日志存放目录；以下步骤在node1和node2上一致
# cd /root
# tar -xzf mariadb-5.5.36-linux-x86_64.tar.gz -C /usr/local/
# ln -sv mariadb-5.5.36-linux-x86_64 mysql
# groupadd=mysql
# useradd=mysql -g mysql
# mkdir /mydata/{data,binlog,relaylog} -pv
# chown -R mysql.mysql /mydata/*
# mkdir /etc/mysql
# cp /usr/local/mysql/support-files/my-large.cnf /etc/mysql/my.cnf
修改/etc/mysql/my.cnf
# vim /etc/mysql/my.cnf 最后一行增加以下语句
datadir=/mydata
# chown -R mysql.mysql /etc/mysql/*
# echo "export PATH=$PATH:/usr/local/mysql/bin" > /etc/profile.d/mysql.sh
# source /etc/profile.d/mysql.sh
# mkdir /var/lib/mysql/
# chown -R mysql.mysql /var/lib/mysql/
# cd /usr/local/mysql
# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
# chmod +x /etc/init.d/mysqld
# scripts/mysql_install_db --user=mysql --datadir=/mydata/data
# service mysqld restart
Master主机上操作：
3.配置Master主机为主节点(主服务器)
--创建二进制日志存储目录及目录权限授予
# mkdir -pv /mydata/binglogs
# chown -R mysql.mysql /mydata/binglogs
--配置my.cnf设置Master为主节点
server-id = 1
log-bin=/mydata/binglogs/master-binlog
Slave主机上操作：
4.配置Slave主机为从节点(从服务器)
--创建中继日志存储目录及目录权限授予
# mkdir -pv /mydata/relaylogs
# chown -R mysql.mysql /mydata/relaylogs
--配置my.cnf设置Master为主节点
server-id = 11
relay-log=/mydata/relaylogs/relay-bin
Master主机上操作：
创建拥有复制权限的用户
--在Master创建拥有复制权限的用户,支持SSL认证，新建一个用户repluser@'172.16.100.%'，密码replpass，并授权所有访问权限
MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'172.16.%.%' IDENTIFIED BY 'replpass' REQUIRE ssl;
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> SHOW MASTER STATUS;
+----------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| master-binlog.000001 | 684 | | |
+----------------------+----------+--------------+------------------+
5.配置Master主机与Slave主机支持SSL认证
Master主机上操作：
配置Master主机支持SSL认证:
--创建文件夹用于存放ca证书和Master自己的key+证书
# mkdir /etc/mysql/ssl
--从Public主机获取CA证书及自己的证书
# scp root@172.16.100.9:/etc/pki/CA/private/cakey.pem /etc/mysql/ssl/
# scp root@172.16.100.9:/etc/pki/CA/cacert.pem /etc/mysql/ssl/
# scp root@172.16.100.9:/etc/pki/CA/master.key /etc/mysql/ssl/
# scp root@172.16.100.9:/etc/pki/CA/master.crt /etc/mysql/ssl/
# chown -R mysql.mysql /etc/mysql/ssl
--修改mysql的配置文件，使mysql支持ssl模式
# vim /etc/mysql/my.cnf
ssl 开启SSL功能
ssl-ca = /etc/mysql/ssl/cacert.pem 指定CA文件位置
ssl-cert = /etc/mysql/ssl/master.crt 指定证书文件位置
ssl-key = /etc/mysql/ssl/master.key 指定密钥所在位置
--重启mysql服务
# service mysqld restart
--查询SSL相关变量是否启动
mysql > SHOW GLOBAL VARIABLES LIKE '%ssl%';
+---------------+---------------------------+
| Variable_name | Value |
+---------------+---------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /etc/mysql/ssl/cakey.pem |
| ssl_capath | |
| ssl_cert | /etc/mysql/ssl/master.crt |
| ssl_cipher | |
| ssl_key | /etc/mysql/ssl/master.key |
+---------------+---------------------------+
Slave主机上操作：
配置Slave主机支持SSL认证:
--创建文件夹用于存放ca证书和Master自己的key+证书
# mkdir /etc/mysql/ssl
--从Public主机获取CA证书及自己的证书
# scp root@172.16.100.9:/etc/pki/CA/private/cakey.pem /etc/mysql/ssl/
# scp root@172.16.100.9:/etc/pki/CA/cacert.pem /etc/mysql/ssl/
# scp root@172.16.100.9:/etc/pki/CA/slave.key /etc/mysql/ssl/
# scp root@172.16.100.9:/etc/pki/CA/slave.crt /etc/mysql/ssl/
# chown -R mysql.mysql /etc/mysql/ssl
--修改mysql的配置文件，使mysql支持ssl模式
# vim /etc/mysql/my.cnf
ssl 开启SSL功能
ssl-ca = /etc/mysql/ssl/cacert.pem 指定CA文件位置
ssl-cert = /etc/mysql/ssl/slave.crt 指定证书文件位置
ssl-key = /etc/mysql/ssl/slave.key 指定密钥所在位置
--重启mysql服务
# service mysqld restart
--查询SSL相关变量是否启动
mysql > SHOW VARIABLES LIKE '%ssl%';
+---------------+--------------------------+
| Variable_name | Value |
+---------------+--------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /etc/mysql/ssl/cakey.pem |
| ssl_capath | |
| ssl_cert | /etc/mysql/ssl/slave.crt |
| ssl_cipher | |
| ssl_key | /etc/mysql/ssl/slave.key |
+---------------+--------------------------+
6.Slave通过SSL远程连接访问Master主服务器
# mysql -urepluser -preplpass -h272.16.100.7 --ssl-ca=/etc/mysql/ssl/cacert.pem --ssl-cert=/etc/mysql/ssl/slave.crt --ssl-key=/etc/mysql/ssl/slave.key
7.Slave以ssl的方式连接数据库，并设置支持主从复制默认
mysql > CHANGE MASTER TO MASTER_HOST='172.16.100.7',MASTER_USER='repluser',MASTER_PASSWORD='replpass',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',MASTER_LOG_FILE='master-binlog.000005',MASTER_LOG_POS=245;
> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-binlog.000005
Read_Master_Log_Pos: 245
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 533
Relay_Master_Log_File: master-binlog.000005
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: 245
Relay_Log_Space: 821
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: 1
8.Windows下通过“Navicat for MySQL”以ssl的方式连接访问数据库Master主节点服务器
将node1/etc/mysql/ssl下的几个文件存放到windows上，此处我放到我的H盘下ssl目录
注意，在ssl里选择证书和key的时候，需要先点击“使用验证”，等选择完毕后再勾除“使用验证”
配置如下所示:
另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
网站栏目：关系型数据库之MySQL基于SSL主从复制及SSL远程访问-创新互联
本文来源：
http://cdkjz.cn/article/dedshg.html
