基于SSL实现Mysql加密的主从复制配置 - AFF大佬 - 提供主机优惠信息深度测评和服务器运维编程技术
当前位置: 主页 » 技术干货 » 基于SSL实现Mysql加密的主从复制配置

基于SSL实现Mysql加密的主从复制配置

      2018年01月14日   阅读 349 次     0 评论   Tags: ·

MySQL主从复制都是基于明文复制,那么就存在被劫持的危险,所以MySQL加密主从复制就应运而生,适用于跨机房主从复制,和对安全性要求比较高的场景。

环境:Centos7,MySQL5.7,开放3306端口。

MySQL安装:

#因为Centos7默认是MariaDB数据库,所以需要卸载MariaDB数据库
rpm -qa | grep mariadb
yum autoremove -y mariadb-libs-5.5.56-2.el7.x86_64
yum -y install epel-release.noarch gcc gcc-c++ wget openssl openssl-devel firewalld
yum update -y

#yum 安装MySQL源,并安装MySQL
yum install -y https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
yum install -y mysql-community-server mysql-community-devel mysql-community-client

#开放3306端口
systemctl start firewalld
firewall-cmd --zone=public --add-port=22/tcp --permanent
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

#因为MySQL5.7以后启动会随机生成十二位的密码,密码在/var/log/mysqld.log文件内,使用grep password /var/log/mysqld.log命令获取12位随机密码。本例随机密码为:P6lktKTfk5!q
[root@li1856-120 mysql]# grep password /var/log/mysqld.log 
2018-01-14T12:35:07.110209Z 1 [Note] A temporary password is generated for root@localhost: P6lktKTfk5!q

#以上操作均在主从机进行操作,下面MySQL主从加密复制配置,主机代表master,从机代表slave。
主机操作:
[root@li1856-120 ~]# mysql -uroot -p
Enter password: (密码)P6lktKTfk5!q
mysql> show databases;
报错:`ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.`
解决:执行 mysql>alter user 'root'@'localhost' identified by 'P6lktKTfk5!q';命令即可。

主机公钥文件:
[root@li1856-120 mysql]# ll /var/lib/mysql
total 122964
-rw-r----- 1 mysql mysql       56 Jan 14 20:35 auto.cnf
-rw------- 1 mysql mysql     1675 Jan 14 20:35 ca-key.pem
-rw-r--r-- 1 mysql mysql     1107 Jan 14 20:35 ca.pem(主机公钥)
-rw-r--r-- 1 mysql mysql     1107 Jan 14 20:35 client-cert.pem  (从机私钥)
-rw------- 1 mysql mysql     1679 Jan 14 20:35 client-key.pem   (从机私钥)
drwxr-xr-x 2 root  root      4096 Jan 14 20:45 data
-rw-r----- 1 mysql mysql      318 Jan 14 21:02 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Jan 14 22:03 ibdata1
-rw-r----- 1 mysql mysql 50331648 Jan 14 22:03 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jan 14 20:35 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Jan 14 21:02 ibtmp1
drwxr-x--- 2 mysql mysql     4096 Jan 14 20:35 mysql
-rw-r----- 1 mysql mysql      421 Jan 14 21:02 mysql-bin.000001
-rw-r----- 1 mysql mysql     1441 Jan 14 22:03 mysql-bin.000002
-rw-r----- 1 mysql mysql       38 Jan 14 21:02 mysql-bin.index
srwxrwxrwx 1 mysql mysql        0 Jan 14 21:02 mysql.sock
-rw------- 1 mysql mysql        5 Jan 14 21:02 mysql.sock.lock
drwxr-x--- 2 mysql mysql     4096 Jan 14 20:35 performance_schema
-rw------- 1 mysql mysql     1679 Jan 14 20:35 private_key.pem
-rw-r--r-- 1 mysql mysql      451 Jan 14 20:35 public_key.pem
-rw-r--r-- 1 mysql mysql     1107 Jan 14 20:35 server-cert.pem   (主机公钥)
-rw------- 1 mysql mysql     1675 Jan 14 20:35 server-key.pem(主机公钥)
drwxr-x--- 2 mysql mysql    12288 Jan 14 20:35 sys
drwxr-x--- 2 mysql mysql     4096 Jan 14 22:03 affdalao

#主机配置文件:
[root@li1856-120 mysql]# cat /etc/my.cnf
binlog-do-db = affdalao    #从机需要同步的数据库 多个库可以写多个值用英文逗号隔开
binlog-ignore-db =mysql  #禁止从机复制的数据库 多个库可以写多个值用英文逗号隔开
port=3306           #端口号
server_id=1            #主机serverid表示符,必须和从机不一样   
log-bin=mysql-bin         #log文件
sync_binlog=1            ##二进制日志  
innodb_flush_log_at_trx_commit=1   ##每秒将事务日志立刻刷写到磁盘 


ssl   ##启用ssl默认是不开启的,mysql中show variables like '%ssl%'查看
ssl_ca =/var/lib/mysql/ca.pem  #ca.pem文件的位置
ssl_cert= /var/lib/mysql/server-cert.pem  #server-cert.pem文件的位置
ssl_key = /var/lib/mysql/server-key.pem   #server-key.pem文件的位置

datadir=/var/lib/mysql     #数据目录可改
socket=/var/lib/mysql/mysql.sock    #socket文件,可改如果没有直接touch一个,然后给权限即可。

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log   #错误日志文件
pid-file=/var/run/mysqld/mysqld.pid   #pid文件

#查看ssl支持情况show variables like '%ssl%';  支持ssl
mysql> show variables like '%ssl%';
+---------------+--------------------------------+
| Variable_name | Value                          |
+---------------+--------------------------------+
| have_openssl  | YES                            |
| have_ssl      | YES                            |
| ssl_ca        | /var/lib/mysql/ca.pem          |
| ssl_capath    |                                |
| ssl_cert      | /var/lib/mysql/server-cert.pem |
| ssl_cipher    |                                |
| ssl_crl       |                                |
| ssl_crlpath   |                                |
| ssl_key       | /var/lib/mysql/server-key.pem  |
+---------------+--------------------------------+
9 rows in set (0.00 sec)


#登陆主机MySQL,进行授权操作:affdalao用户名,%主机名%代表任意主机,REQUIRE SSL支持ssl。
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO affdalao@'%' IDENTIFIED BY 'password' REQUIRE SSL;

#报错,密码位数不够,建议20位以上
mysql> update mysql.user set authentication_string=password('sXPl@EF$LFCBLyjB') where user='root' and Host = 'localhost';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

#查看主机状态记录file和Position值,从机会用到
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     1441 | affdalao       | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

#复制从机私钥到从机:scp ca.pem client-key.pem client-cert.pem root@slave IP:/var/lib/mysql即可。

#主机配置完毕,下面是从机配置
从机配置文件:
[root@affdalao mysql]# cat /etc/my.cnf
port=3306
server_id=10
relay-log = mysql-ralay    ##中继日志
relay-log-index = mysql-ralay.index   ##中继目录
read-only = 1   ##从服务器只读

ssl    ##启用ssl默认是不开启的,mysql中
ssl_ca =/var/lib/mysql/ca.pem   #ca.pem文件的位置
ssl_cert= /var/lib/mysql/client-cert.pem   #client-cert.pem文件的位置
ssl_key = /var/lib/mysql/client-key.pem    #client-key.pem文件的位置

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#从机使用主机设置的affdalao用户进行远程登陆测试,若登陆正常已经成功了一半。
[root@affdalao ~]# mysql -uaffdalao -h masterIP -P3306 -p
Enter password: 

#从机给ca.pem client-key.pem client-cert.pem权限,因为是在/var/log/mysql
所以直接给/var/log/mysql增加读权限,和mysql所有者所属组权限。
chown -R mysql.mysql /var/log/mysql
chmod +r /var/log/mysql

#从机查看ssl支持情况
mysql> show variables like '%ssl%';
+---------------+--------------------------------+
| Variable_name | Value                          |
+---------------+--------------------------------+
| have_openssl  | DISABLED                       |
| have_ssl      | DISABLED                       |
| ssl_ca        | /var/lib/mysql/ca-key.pem      |
| ssl_capath    |                                |
| ssl_cert      | /var/lib/mysql/client-cert.pem |
| ssl_cipher    |                                |
| ssl_crl       |                                |
| ssl_crlpath   |                                |
| ssl_key       | /var/lib/mysql/client-key.pem  |
+---------------+--------------------------------+
9 rows in set (0.00 sec)


#登陆从机MySQL,执行主从加密同步准备工作
mysql> change master to   
    -> master_host='masterIP',  
    -> master_user='affdalao',  
    -> master_password='Password',
    -> master_port = 3306,  
    -> master_log_file='mysql-bin.000002',  #master机的file必须一致
    -> master_log_pos=1441,   #master机的position 必须一致
    -> master_ssl=1,  
    -> master_ssl_ca='/var/lib/mysql/ca.pem',  
    -> master_ssl_cert='/var/lib/mysql/client-cert.pem',  
    -> master_ssl_key='/var/lib/mysql/client-key.pem'; 
Query OK, 0 rows affected, 2 warnings (0.30 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.105.202.120
                  Master_User: affdalao
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1441
               Relay_Log_File: mysql-ralay.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes  (出现yes)  必须是2个yes才说明主从加密复制成功。
            Slave_SQL_Running: Yes  (出现yes)

#从机配置完毕。

#测试:master机创建affdalao数据库,并在affdalao创建数据表abc
mysql> create database affdalao;
Query OK, 0 rows affected (0.01 sec)
mysql> use affdalao;
Database changed
mysql> create table abc (a int);
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+------------------+
| Tables_in_affdalao |
+------------------+
| abc              |
+------------------+
1 row in set (0.01 sec)


#查看从机是否复制成功
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| affdalao             |
+--------------------+
5 rows in set (0.00 sec)

mysql> use affdalao;
Database changed
mysql> show tables;
+------------------+
| Tables_in_affdalao |
+------------------+
| abc              |
+------------------+
1 row in set (0.01 sec)

#如果主从一致都有affdalao和abc数据表,则说明主从加密复制成功。
  • 版权声明:本文版权归AFF大佬和原作者所有,未经许可不得转载。文章部分来源于网络仅代表作者看法,如有不同观点,欢迎进行交流。除非注明,文章均由 AFF大佬 整理发布,欢迎转载,转载请带版权。

  • 来源:AFF大佬 ( https://www.affdalao.com/ ),提供主机优惠信息深度测评和服务器运维编程技术。
  • 链接:https://www.affdalao.com/182.html
  • 评论(0

    1. 还没有任何评论,你来说两句吧

    发表评论

    电子邮件地址不会被公开。 必填项已用*标注