基于SSL实现MySQL的加密主从复制
2017-11-13 本文已影响0人
KingFighting
写在前面
MySQL的主从复制是明文传输的，这对一些特殊场景来说是不允许的，因为数据安全性会受到威胁。有关MySQL的主从复制以及SSL的相关概念网上相关资料比较多，在这里不予过多涉及。下面将尝试来构建基于SSL的MySQL的主从复制。
实验实现
实验环境准备
实验环境在同一个网络中
三台虚拟机 （centos7.3）
node1 ： 主mysql服务器，ip地址是192.168.1.16
node2 ： 从mysql 服务器，ip地址是192.168.1.7
node3 ： CA服务器，ip地址是192.168.1.15
数据库使用的是 mariadb-server 5.5.52
确保所有虚拟机上面的防火墙和selinux处于关闭状态
各节点时间必须同步
各节点之间通过主机名互相通信，建议使用/etc/hosts文件实现
各节点之间的root用户可以基于密钥认证的ssh服务完成互相通信
实验过程
一、在node1和node2上面
之前在node1和node2上面，已经安装了mariadb数据库以及数据库客户端工具，并且mariadb数据库处于运行状态。
systemctl stop mariadb
rm -rf /var/lib/mysql/*
二、在node3上搭建ca服务器
cd /etc/pki/CA
# 生成证书索引数据库文件
touch /etc/pki/CA/index.txt
# 指定第一个颁发证书的序列号
echo 99 > /etc/pki/CA/serial
# 创建私钥文件cakey.pem
(umask 066; openssl genrsa -out private/cakey.pem 4096)
# 查看一下生成的私钥文件
cat private/cakey.pem
# 利用私钥文件生成自签名证书
openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 3650
# 填写的信息如下
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:henan
Locality Name (eg, city) [Default City]:zhengzhou
Organization Name (eg, company) [Default Company Ltd]:magedu.com
Organizational Unit Name (eg, section) []:Opt
Common Name (eg, your name or your server's hostname) []:ca.magedu.com
Email Address []:
# 查看签名证书的内容
openssl x509 -in cacert.pem -noout -text
三、在node1上面准备私钥及证书申请文件
mkdir /var/lib/mysql/ssl
cd /var/lib/mysql/ssl
# 创建私钥文件
(umask 066; openssl genrsa -out mysql.key 1024)
# 利用私钥生成证书申请文件
openssl req -new -key mysql.key -out mysql.csr
# 填写信息如下
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:henan
Locality Name (eg, city) [Default City]:zhengzhou
Organization Name (eg, company) [Default Company Ltd]:magedu.com
Organizational Unit Name (eg, section) []:Opt
Common Name (eg, your name or your server's hostname) []:mysql.magedu.com
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
# 提交证书申请文件
scp mysql.csr 192.168.1.15:/etc/pki/CA/
四、在node2上面准备私钥及证书申请文件
mkdir /var/lib/mysql/ssl
cd /var/lib/mysql/ssl
# 创建私钥文件
(umask 066; openssl genrsa -out slave.key 1024)
# 利用私钥生成证书申请文件
openssl req -new -key slave.key -out slave.csr
# 填写信息如下
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:henan
Locality Name (eg, city) [Default City]:zhengzhou
Organization Name (eg, company) [Default Company Ltd]:magedu.com
Organizational Unit Name (eg, section) []:Opt
Common Name (eg, your name or your server's hostname) []:slavedb.magedu.com
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
# 提交证书申请文件
scp slave.csr 192.168.1.15:/etc/pki/CA/
五、在node3上面颁发证书
cd /etc/pki/CA
# 生成node1主mysql的证书
openssl ca -in mysql.csr -out certs/mysql.crt -days 300
# 生成node2从mysql的证书
openssl ca -in slave.csr -out certs/slave.crt -days 300
# 将CA自签名证书 cacert.pem 以及 mysql.crt 拷贝给node1
scp cacert.pem 192.168.1.16:/var/lib/mysql/ssl
scp certs/mysql.crt 192.168.1.16:/var/lib/mysql/ssl
# 将CA自签名证书 cacert.pem 以及 slave.crt 拷贝给node2
scp cacert.pem 192.168.1.7:/var/lib/mysql/ssl
scp slave.crt 192.168.1.7:/var/lib/mysql/ssl
六、在node1上面配置主mysql
# 确认所有证书相关文件
cd /var/lib/mysql/ssl
ls
cacert.pem mysql.crt mysql.csr mysql.key
# 改变文件的用户和组，以及修改权限
chown mysql.mysql *
chmod 600 *
# 修改数据库的配置
vim /etc/my.cnf.d/server.cnf
[server]
skip_name_resolve = ON
innodb_file_per_table = ON
max_connections = 2000
log_bin = master-log
server_id = 1
ssl_ca = /var/lib/mysql/ssl/cacert.pem
ssl_cert = /var/lib/mysql/ssl/mysql.crt
ssl_key = /var/lib/mysql/ssl/mysql.key
# 启动数据库
systemctl start mariadb
# 查看数据库ssl相关变量
mysql
MariaDB [(none)]> show variables like '%ssl%';
+---------------+-------------------------------+
| Variable_name | Value |
+---------------+-------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /var/lib/mysql/ssl/cacert.pem |
| ssl_capath | |
| ssl_cert | /var/lib/mysql/ssl/mysql.crt |
| ssl_cipher | |
| ssl_key | /var/lib/mysql/ssl/mysql.key |
+---------------+-------------------------------+
七、在node2上面配置从mysql
# 确认所有证书相关文件
cd /var/lib/mysql/ssl
ls
cacert.pem slave.crt slave.csr slave.key
# 改变文件的用户和组，以及修改权限
chown mysql.mysql *
chmod 600 *
# 修改数据库的配置
vim /etc/my.cnf.d/server.cnf
[server]
skip_name_resolve = ON
innodb_file_per_table = ON
max_connections = 2000
relay_log = relay-log
server_id = 2
read_only = ON
ssl_ca = /var/lib/mysql/ssl/cacert.pem
ssl_cert = /var/lib/mysql/ssl/slave.crt
ssl_key = /var/lib/mysql/ssl/slave.key
# 启动数据库
systemctl start mariadb
# 查看数据库ssl相关变量
mysql
MariaDB [(none)]> show variables like '%ssl%';
+---------------+-------------------------------+
| Variable_name | Value |
+---------------+-------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /var/lib/mysql/ssl/cacert.pem |
| ssl_capath | |
| ssl_cert | /var/lib/mysql/ssl/slave.crt |
| ssl_cipher | |
| ssl_key | /var/lib/mysql/ssl/slave.key |
+---------------+-------------------------------+
八、在node1上面配置主mysql
mysql
MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 245 | | |
+-------------------+----------+--------------+------------------+
# 确认没有 repluser 用户
MariaDB [(none)]> select host,user,password from mysql.user;
MariaDB [(none)]> grant replication client,replication slave on *.* to
-> 'repluser'@'192.168.1.%' identified by 'centos' require ssl;
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 | 507 | | |
+-------------------+----------+--------------+------------------+
九、在node2上面配置从mysql
mysql
MariaDB [(none)]> stop slave;
MariaDB [(none)]> change master to
-> MASTER_HOST='192.168.1.16',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='centos',
-> MASTER_LOG_FILE='master-log.000003',
-> MASTER_LOG_POS=507,
-> MASTER_SSl=1,
-> MASTER_SSl_CA='/var/lib/mysql/ssl/cacert.pem',
-> MASTER_SSl_CERT='/var/lib/mysql/ssl/slave.crt',
-> MASTER_SSl_KEY='/var/lib/mysql/ssl/slave.key';
MariaDB [(none)]> start slave IO_THREAD,SQL_THREAD;
MariaDB [(none)]> show slave status\G;
# 主要信息如下
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.16
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000003
Read_Master_Log_Pos: 507
Relay_Master_Log_File: master-log.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /var/lib/mysql/ssl/cacert.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /var/lib/mysql/ssl/slave.crt
Master_SSL_Cipher:
Master_SSL_Key: /var/lib/mysql/ssl/slave.key
Master_SSL_Verify_Server_Cert: No
Master_Server_Id: 1
在node2上面用ssl加密连接node1的mysql
mysql -urepluser -pcentos -h192.168.1.16 --ssl-cert=/var/lib/mysql/ssl/slave.crt --ssl-key=/var/lib/mysql/ssl/slave.key
用status命令查看，下面是出现的信息
MariaDB [(none)]> status;
--------------
mysql Ver 15.1 Distrib 5.5.52-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 1004
Current database:
Current user: repluser@192.168.1.7
SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 5.5.52-MariaDB MariaDB Server
Protocol version: 10
Connection: 192.168.1.16 via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 35 min 58 sec
主要是：
Current user: repluser@192.168.1.7
SSL: Cipher in use is DHE-RSA-AES256-GCM-SHA384
也可以用tcpdump等抓包工具查看是否加密。
十、在node1创建数据库和表
mysql
MariaDB [(none)]> create database hidb;
MariaDB [(none)]> use hidb;
MariaDB [(none)]> CREATE TABLE students(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name CHAR(30) NOT NULL,age TINYINT UNSIGNED,gender ENUM('F','M'),major VARCHAR(200));
在bash shell 里面执行
GENDER=('F' 'M')
for i in {1..1000};do mysql -e "INSERT INTO hidb.students(name,age,gender) VALUES ('stu$i',$[RANDOM%80+18],'${GENDER[RANDOM%2]}')";done
insert into students(name,age,gender,major) values('liuqingyun',52,'M','performer'),('wangfei',48,'F','singer');
insert into students(name,age,gender,major) values('zhouxingchi',52,'M','director'),('Aaron Sorkin',48,'M','Screenwriter');
十一、在node2上面查看数据库和表是否生成
mysql
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hidb |
| mysql |
| performance_schema |
| ssl |
| test |
+--------------------+
MariaDB [hidb]> use hidb;
# 发现表文件以及其中的记录已经同步过来
MariaDB [hidb]> select * from students;
十二、出现的问题
在从数据库服务器中插入数据，出现问题，此时在从数据库服务器一端插入的数据并不会同步到主数据库服务器一端。而且，很有可能会导致，主数据库服务器一端，对数据的修改也无法同步到从数据库一端，会导致主从数据不一致。总结来说就是，从数据库服务器的数据是绝不允许多过主数据库服务器一端，不然的话，从节点会无所适从。
如果在从数据库服务器一端向hidb数据库插入数据，导致同步出错，可以如下进行操作。
mysql
MariaDB [hidb]> stop slave;
MariaDB [hidb]> show databases;
MariaDB [hidb]> drop database hidb;
MariaDB [(none)]> change master to
-> MASTER_HOST='192.168.1.16',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='centos',
-> MASTER_LOG_FILE='master-log.000003',
-> MASTER_LOG_POS=507,
-> MASTER_SSl=1,
-> MASTER_SSl_CA='/var/lib/mysql/ssl/cacert.pem',
-> MASTER_SSl_CERT='/var/lib/mysql/ssl/slave.crt',
-> MASTER_SSl_KEY='/var/lib/mysql/ssl/slave.key';
MariaDB [(none)]> start slave IO_THREAD,SQL_THREAD;
至此，基于SSL实现MySQL的加密主从复制以及可能会出现的问题就已经基本说明清楚了，博客参考了
http://blog.csdn.net/u012974916/article/details/53316758
http://www.zuimoban.com/jiaocheng/mysql/11089.html
不足之处，请多指正。
