通过Mysql-proxy实现Mariadb读写分离加密通讯
一、环境
CA服务器（learn01）：192.168.50.205 mysql-proxy（0.8.5）&& CA
master节点（learn02）：192.168.50.206 5.5.68-MariaDB MariaDB Server
slave节点（learn03）：192.168.50.207 5.5.68-MariaDB MariaDB Server
二、创建主/从加密通讯
2.1、创建本地CA证书服务器，将相关证书颁发至相应主机
1）创建CA根证书
#创建证书存放路径及文件
[root@learn01 ~]# mkdir -p /etc/certlist
#移动至证书存放目录，并创建CA证书服务器私钥（cakey.pem）
[root@learn01 ~]# cd /etc/certlist/;( umask 022;openssl genrsa 2048 > cakey.pem )
#导入CA证书服务器私钥（cakey.pem），生成CA证书服务器公钥（cacert.pem）有效期为10年，并设置相关属性
[root@learn01 certlist]# openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650 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) []:SD Locality Name (eg, city) [Default City]:QD Organization Name (eg, company) [Default Company Ltd]:LN Organizational Unit Name (eg, section) []: Common Name (eg, your name or your server's hostname) []: Email Address []:
#查看当前CA证书公钥文件（cacert.pem）相关信息
[root@learn01 certlist]# openssl x509 -in cacert.pem -noout -text
2）生成master证书文件，并发送至learn02服务器
#创建master证书私钥(master.key)，并生成master证书请求文件（master.csr）
[root@learn01 certlist]# openssl req -newkey rsa:1024 -days 365 -nodes -keyout master.key > master.csr Generating a 1024 bit RSA private key ..++++++ ..............++++++ writing new private key to 'master.key' ----- 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) []:SD Locality Name (eg, city) [Default City]:QD Organization Name (eg, company) [Default Company Ltd]:LN02 Organizational Unit Name (eg, section) []: Common Name (eg, your name or your server's hostname) []: Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []:
#通过CA根证书颁发导入master证书请求文件（master.csr），从而生成master证书文件（master.crt）
[root@learn01 certlist]# openssl x509 -req -in master.csr -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt
#在learn02服务器从CA证书服务器拷贝相关证书文件（CA根证书、master私钥文件、master公钥证书文件）
[root@learn02 ~]# mkdir /etc/certlist [root@learn02 ~]# cd /etc/certlist/ [root@learn02 certlist]# scp 192.168.50.205:/etc/certlist/{cacert.pem,master.key,master.crt} ./
3）生成slave证书文件，并发送至learn02服务器
#创建slave证书私钥（slave.key）,并生成slave证书请求文件（slave.csr）
[root@learn01 certlist]# openssl req -newkey rsa:1024 -days 365 -nodes -keyout slave.key > slave.csr Generating a 1024 bit RSA private key .................................................................................................................++++++ ....++++++ writing new private key to 'slave.key' ----- 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) []:SD Locality Name (eg, city) [Default City]:QD Organization Name (eg, company) [Default Company Ltd]:LN03 Organizational Unit Name (eg, section) []: Common Name (eg, your name or your server's hostname) []: Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []:
#通过CA根证书颁发slave证书请求文件（slave.csr），从而生成slave证书文件（slave.crt）
[root@learn01 certlist]# openssl x509 -req -in slave.csr -CA cacert.pem -CAkey cakey.pem -set_serial 02 > slave.crt
#在learn03服务器创建存放证书文件夹（certlist），CA证书远程拷贝相关文件（CA根证书、master私钥文件、master公钥证书文件）至learn03服务器
[root@learn03 ~]# mkdir /etc/certlist [root@learn03 ~]# cd /etc/certlist/ [root@learn01 certlist]# scp cacert.pem slave.key slave.crt root@192.168.50.207:/etc/certlist/
2.2、创建Mariadb数据库主/从关系
1）learn02服务器（master节点）
#修改/etc/my.cnf.d/server.cnf配置文件
[root@learn02 certlist]# vim /etc/my.cnf.d/server.cnf
:
#查看相关文件
[root@learn02 certlist]# mysql -uroot -p
MariaDB [(none)]> SHOW VARIABLES LIKE '%ssl%';
#创建一个强制使用加密通讯的账户
MariaDB [(none)]> GRANT REPLICATION slave ON *.* TO 'slave'@'172.16.1.207' IDENTIFIED BY '123456' REQUIRE ssl; MariaDB [(none)]> FLUSH PRIVILEGES;
#查看Master相关参数
MariaDB [(none)]> SHOW MASTER STATUS;
#firewall防火墙方通来自learn0服务器(172.16.1.207)的数据库房访问数据
[root@learn02 certlist]# firewall-cmd --zone=public --add-interface=eth1 --permanent [root@learn02 certlist]# firewall-cmd --permanent --zone=public --add-rich-rule="rule family="ipv4" source address="172.16.1.207/32" service name="mysql" accept"
2）learn03服务器（slave节点）
#修改/etc/my.cnf.d/server.cnf配置文件
[root@learn03 certlist]# vim /etc/my.cnf.d/server.cnf
#查看相关文件
[root@learn03 certlist]# mysql -uroot -p
MariaDB [(none)]> SHOW VARIABLES LIKE '%ssl%';
#指定配置master相关节点参数，并启动slave线程；
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.1.206' , MASTER_USER='slave' , MASTER_PASSWORD='123456' , MASTER_LOG_FILE='mariadb-bin.000006' , MASTER_LOG_POS=245 , MASTER_SSL=1; MariaDB [(none)]> slave start;
#查看slave状态
MariaDB [(none)]> SHOW SLAVE STATUS\G;
3）测试：证书是否能够正常使用
[root@learn03 certlist]# mysql --ssl-ca=cacert.pem --ssl-key=slave.key --ssl-cert=slave.crt -uslave -h172.16.1.206 -p Enter password:
三、使用mysql-proxy实现读写分离
#下载mysql-proxy，并放到指定位置
[root@learn01 ~]# wget https://downloads.mysql.com/archives/get/p/21/file/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz [root@learn01 ~]# tar -zxvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz [root@learn01 ~]# mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy
#配置mysql-proxy，创建主配置文件
[root@learn01 ~]# cd /usr/local/mysql-proxy/ [root@learn01 mysql-proxy]# mkdir lua [root@learn01 mysql-proxy]# mkdir logs [root@learn01 mysql-proxy]# cp share/doc/mysql-proxy/rw-splitting.lua ./lua/ [root@learn01 mysql-proxy]# vim /etc/mysql-proxy.cnf
[mysql-proxy]
user=root
admin-username=repl
admin-password=123456
proxy-address=192.168.50.205 #默认端口为3306，假如需指定4040端口则：192.168.50.205:4040
proxy-backend-addresses=192.168.50.206:3306
proxy-read-only-backend-addresses=192.168.50.207:3306
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log
log-level=info
daemon=true
keepalive=true
[root@learn01 mysql-proxy]# chmod 660 /etc/mysql-proxy.cnf
#修改读写分离配置文件
[root@learn01 ~]# vim /usr/local/mysql-proxy/lua/rw-splitting.lua
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1,
max_idle_connections = 1,
is_debug = false
}
end
#在learn02服务器（master节点），mysql-proxy用户，learn03服务器（从节点）会通过主/从复制同步相关数据。
MariaDB [(none)]> GRANT all ON *.* TO 'repl'@'192.168.50.205' IDENTIFIED BY '123456'; MariaDB [(none)]> FLUSH PRIVILEGES;
#在learn01服务器（mysql-proxy）、learn02服务器（master节点）与learn03服务器（slave节点）分别开放相关安全策略；
[root@learn01 ~]# firewall-cmd --zone=public --add-interface=eth0 --permanent [root@learn01 ~]# firewall-cmd --permanent --zone=public --add-rich-rule="rule family="ipv4" source address="192.168.50.0/24" port port="3306" protocol="tcp" accept"
[root@learn02 ~]# firewall-cmd --zone=public --add-interface=eth0 --permanent [root@learn02 ~]# firewall-cmd --permanent --zone=public --add-rich-rule="rule family="ipv4" source address="192.168.50.205" service name="mysql" accept"
[root@learn03 ~]# firewall-cmd --zone=public --add-interface=eth0 --permanent [root@learn03 ~]# firewall-cmd --permanent --zone=public --add-rich-rule="rule family="ipv4" source address="192.168.50.205" service name="mysql" accept"
#在learn01服务器启动mysql-proxy服务
[root@learn01 ~]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf [root@learn01 ~]# netstat -tanp | grep 4040
四、测试Mysql-Proxy读写分离
4.1、mysql-proxy是否能够正常数据库
#通过learn02服务器远程连接learn01服务器（mysql-proxy节点）
4.2、查看数据库内容，判断Mysql-proxy读写分离功能会否正常。
#关闭learn03服务器（slave节点）主从同步同能
#修改learn03服务器（slave节点）learn01数据库proxy表内数据
#查看learn01服务器（master节点），learn01数据库proxy表内数据
#访问learn01服务器（mysql-proxy节点），查看learn01数据库proxy表内数据
4.3、查看数据走向，判断mysql-proxy读写分离功能会否正常。
#访问learn02服务器（mysql-proxy节点）并查看内部流量
1）查看mysql-proxy读数据流量走向
#在learn01服务器（mysql-proxy节点）抓包确认读数据走向
2）查看mysql-proxy写数据流量走向
#在learn01服务器（mysql-proxy节点）抓包确认写数据走向
