MariaDB数据库基于SSL实现远程访问和主从复制

实验环境

系统环境：Centos6.5

数据库版本：5.5.36-MariaDB-log MariaDB Server

虚机数量：2

方案实施：

  1. 配置CA；并为node1和node2生成key和证书
  2. 在node1和node2上安装Mariadb
  3. 配置节点1为MariaDB主节点
  4. 配置节点2为MariaDB从节点
  5. 配置节点1和节点2主从复制模式
  6. 配置节点1和节点2支持ssl
  7. 在node1上新建ssl模式的用户
  8. node2已ssl的方式访问数据库node1
  9. node2以ssl的方式连接数据库，并设置支持主从复制默认
  10. Windows下通过“Navicat for MySQL”以ssl的方式连接访问node1

1. 配置CA；并为node1和node2生成key和证书

确认系统已安装openssl组件，如果未安装，通过以下方式安装并查看

 1yum install openssl -y
 2rpm -ql openssl
 3/etc/pki/CA
 4/etc/pki/CA/certs
 5/etc/pki/CA/crl
 6/etc/pki/CA/newcerts
 7/etc/pki/CA/private
 8/etc/pki/tls
 9/etc/pki/tls/certs
10/etc/pki/tls/certs/Makefile
11/etc/pki/tls/certs/make-dummy-cert
12/etc/pki/tls/certs/renew-dummy-cert
13/etc/pki/tls/misc
14/etc/pki/tls/misc/CA
15/etc/pki/tls/misc/c_hash
16/etc/pki/tls/misc/c_info
17/etc/pki/tls/misc/c_issuer
18/etc/pki/tls/misc/c_name
19/etc/pki/tls/openssl.cnf
20/etc/pki/tls/private
21/usr/bin/openssl
22......

修改openssl.cnf的配置文件，修改几个地方

1vim /etc/pki/tls/openssl.cnf
2countryName_default = CN       # 国家
3stateOrProvinceName_default = GuangDong      # 城市
4localityName_default= ShenZhen     # 区县
50.organizationName_default  = TEST     # 公司
6organizationalUnitName_default  = IT      #  部门

node1以CAserver的身份自建CA证书

1cd /etc/pki/CA
2(umask 077;openssl genrsa -out private/cakey.pem 2048)
3openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 3650
4touch {index.txt,serial}
5echo 01 > serial

给node1生成key，证书请求和证书

1cd /etc/pki/CA
2(umask 077;openssl genrsa -out master.key 2048)
3# 一路回车即可
4openssl req -new -key master.key -out master.csr
5# 需要点两次y确认
6openssl ca -in master.csr -out master.crt -days 3650

给node2生成key，证书请求和证书

1cd /etc/pki/CA
2(umask 077;openssl genrsa -out slave.key 2048)
3# 一路回车即可
4openssl req -new -key slave.key -out slave.csr
5# 需要点两次y确认
6openssl ca -in slave.csr -out slave.crt -days 3650

2. 在node1和node2上安装Mariadb

使用软件安装包为mariadb-5.5.36-linux-x86_64.tar.gz，上传至root目录下，安装配置Mairadb，并设置mydata目录为数据和日志存放目录；以下步骤在node1和node2上一致

1cd /root
2tar -xzf mariadb-5.5.36-linux-x86_64.tar.gz -C /usr/local/
3ln -sv mariadb-5.5.36-linux-x86_64 mysql
4groupadd=mysql
5useradd=mysql -g mysql
6mkdir /mydata/{data,binlog,relaylog} -pv
7chown -R mysql.mysql /mydata/*
8mkdir /etc/mysql
9cp /usr/local/mysql/support-files/my-large.cnf /etc/mysql/my.cnf

修改/etc/mysql/my.cnf

 1vim /etc/mysql/my.cnf 
 2# 最后一行增加以下语句
 3datadir=/mydata
 4chown -R mysql.mysql /etc/mysql/*
 5echo 'export PATH=$PATH:/usr/local/mysql/bin' > /etc/profile.d/mysql.sh
 6source /etc/profile.d/mysql.sh
 7mkdir /var/lib/mysql/
 8chown -R mysql.mysql /var/lib/mysql/
 9cd /usr/local/mysql
10cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
11chmod +x /etc/init.d/mysqld
12scripts/mysql_install_db --user=mysql --datadir=/mydata/data
13service mysqld restart

3. 配置节点1为MariaDB主节点

在node1部署为MariaDB的master节点，修改node1的配置文件，增加以下内容

1vim /etc/mysql/my.cnf
2server-id=1   #  设置server-id号为1
3log-bin=/mydata/binlog/master-binlog      #  是此服务器支持binlog

4. 配置节点2为MariaDB从节点

1vim /etc/mysql/my.cnf
2server-id=1   #  设置server-id号为2
3relay-log=/mydata/relaylog/relay-bin    #    是此服务器支持relaylog

5. 配置节点1和节点2主从复制模式

在node1节点上，新建一个用户nossl@'%.%.%.%'，密码nossl，并授权所有访问权限

 1mysql
 2MariaDB [(none)]> grant all on *.* to nossl@'%.%.%.%&' identified by 'nossl';
 3Query OK, 0 rows affected (0.06 sec)
 4MariaDB [(none)]> show master status;
 5+------------------+----------+--------------+------------------+
 6| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 7+------------------+----------+--------------+------------------+
 8| mysql-bin.000001 | 2682 |  |  |
 9+------------------+----------+--------------+------------------+
101 row in set (0.02 sec)

在node2节点上，使用此账户登录，并设置同node1实现主从复制

 1mysql -unossl -hnode1 -pnossl
 2mysql> CHANGE MASTER TO MASTER_HOST=&'nod1&',MASTER_USER='nossl',MASTER_PASSWORD='nossl',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=2682;
 3Query OK, 0 rows affected (0.25 sec)
 4mysql> start slave;
 5Query OK, 0 rows affected (0.01 sec)
 6mysql> slave status\G
 7*************************** 1. row ***************************
 8  Slave_IO_State:
 9  Master_Host: node1
10  Master_User: nossl
11  Master_Port: 3306
12  Connect_Retry: 60
13  Master_Log_File: mysql-bin.000001
14  Read_Master_Log_Pos: 2682
15  Relay_Log_File: zabbix-relay-bin.000001
16  Relay_Log_Pos: 4
17  Relay_Master_Log_File: mysql-bin.000001
18  Slave_IO_Running: Yes
19  Slave_SQL_Running: Yes
20  .....
21  Master_Server_Id: 1
22  1 row in set (0.00 sec)

6. 配置节点1和节点2支持ssl

在node1上，新建文件夹用于存放ca证书和node1自己的key+证书

 1mkdir /etc/mysql/ssl
 2cp /etc/pki/CA/private/cakey.pem /etc/mysql/ssl
 3cp /etc/pki/CA/master.key /etc/mysql/ssl
 4cp /etc/pki/CA/master.crt /etc/mysql/ssl
 5chown -R mysql.mysql /etc/mysql*
 6# 修改mysql的配置文件，使mysql支持ssl模式
 7vim /etc/mysql/my.cnf
 8ssl  开启SSL功能
 9ssl-ca = /etc/mysql/ssl/cacert.pem      指定CA文件位置
10ssl-cert = /etc/mysql/ssl/master.crt    指定证书文件位置
11ssl-key = /etc/mysql/ssl/master.key   指定密钥所在位置
12
13# 在node2上，新建文件夹用于存放ca证书和node2自己的key+证书
14mkdir /etc/mysql/ssl
15scp node1:/etc/pki/CA/private/cakey.pem /etc/mysql/ssl
16scp node1:/etc/pki/CA/slave.key /etc/mysql/ssl
17scp node1:/etc/pki/CA/slave.crt /etc/mysql/ssl
18chown -R mysql.mysql /etc/mysql*
19vim /etc/mysql/my.cnf
20ssl  开启SSL功能
21ssl-ca = /etc/mysql/ssl/cacert.pem   指定CA文件位置
22ssl-cert = /etc/mysql/ssl/slave.crt    指定证书文件位置
23ssl-key = /etc/mysql/ssl/slave.key   指定密钥所在位置

在node1和node2上经过以上配置后，需要重启mysqld服务，并通过以下方式查看

 1service mysqld restart
 2mysql
 3mysql> show variables like '%ssl%';
 4+---------------+---------------------------+
 5| Variable_name | Value |
 6+---------------+---------------------------+
 7| have_openssl  | YES   |
 8| have_ssl  | YES   |
 9| ssl_ca| /etc/mysql/ssl/cacert.pem |
10| ssl_capath|   |
11| ssl_cert  | /etc/mysql/ssl/mysql.crt  |
12| ssl_cipher|   |
13| ssl_key   | /etc/mysql/ssl/mysql.key  |
14+---------------+---------------------------+
157 rows in set (0.00 sec)

7. 在node1上新建ssl模式的用户

1MariaDB [(none)]> grant all on *.* to 'ssluser&'@'%.%.%.%&' identified by 'sslpass' require ssl;

8. 在node2以ssl的方式访问数据库node1

1mysql -ussluser -psslpass -hnode1 --ssl-ca=/etc/mysql/ssl/cacert.pem --ssl-cert=/etc/mysql/ssl/slave.crt --ssl-key=/etc/mysql/ssl/slave.key

9. node2以ssl的方式连接数据库，并设置支持主从复制默认

在正式环境里，为了安全期间，可以删除nossl用户，仅保留ssluser用户用来远程连接mysql；

 1mysql -ussluser -psslpass -hnode1 --ssl-ca=/etc/mysql/ssl/cacert.pem --ssl-cert=/etc/mysql/ssl/slave.crt --ssl-key=/etc/mysql/ssl/slave.key
 2mysql> CHANGE MASTER TO MASTER_HOST='node1',MASTER_USER='ssluser',MASTER_PASSWORD='sslpass',master_ssl=1,master_ssl_ca='/etc/mysql/ssl/cacert.pem',master_ssl_cert='/etc/mysql/ssl/mysqls.crt',master_ssl_key='/etc/mysql/ssl/mysqls.key',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=26822;
 3Query OK, 0 rows affected (0.23 sec)
 4mysql> start slave;
 5Query OK, 0 rows affected (0.00 sec)
 6mysql> show slave status\G
 7*************************** 1. row ***************************
 8   Slave_IO_State: Connecting to master
 9  Master_Host: 192.168.97.47
10  Master_User: ssluser
11  Master_Port: 3306
12Connect_Retry: 60
13  Master_Log_File: mysql-bin.000001
14  Read_Master_Log_Pos: 44904037
15   Relay_Log_File: zabbix-relay-bin.00000
16Relay_Log_Pos: 4
17Relay_Master_Log_File: mysql-bin.00000
18 Slave_IO_Running: Connecting
19Slave_SQL_Running: Yes
20  Replicate_Do_DB:
21  Replicate_Ignore_DB:
22   Replicate_Do_Table:
23   Replicate_Ignore_Table:
24  Replicate_Wild_Do_Table:
25  Replicate_Wild_Ignore_Table:
26   Last_Errno: 0
27   Last_Error:
28 Skip_Counter: 0
29  Exec_Master_Log_Pos: 44904037
30  Relay_Log_Space: 245
31  Until_Condition: None
32   Until_Log_File:
33Until_Log_Pos: 0
34   Master_SSL_Allowed: Yes
35   Master_SSL_CA_File: /etc/mysql/ssl/cacert.pem
36   Master_SSL_CA_Path:
37  Master_SSL_Cert: /etc/mysql/ssl/mysqls.crt
38Master_SSL_Cipher:
39   Master_SSL_Key: /etc/mysql/ssl/mysqls.key
40  Replicate_Ignore_Server_Ids:
41 Master_Server_Id: 1
421 row in set (0.00 sec)
43通过状态查看，slave已经以ssl的工作模式正常执行同步

10. Windows下通过“Navicat for MySQL”以ssl的方式连接访问数据库node1

将node1/etc/mysql/ssl下的几个文件存放到windows上，此处我放到我的D盘下ssl目录

注意，在ssl里选择证书和key的时候，需要先点击“使用验证”，等选择完毕后再勾除“使用验证”