备案控制台
探索云世界
开发者社区 数据库 文章 正文

基于SSL的mysql(MariaDB)主从复制

2017-11-21 1111
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL Serverless，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL Serverless，价值2615元额度，1个月
简介：

一、前言

   备份数据库是生产环境中的首要任务，重中之重，有时候不得不通过网络进行数据库的复制，这样就需要保证数据在网络传输过程中的安全性，因此使用基于SSL的复制会大加强数据的安全性

二、准备工作

1、主从服务器时间同步

1
2
[root@master ~] # crontab -e
* /30  * * * *  /usr/sbin/ntpdate  172.16.0.1 &> /dev/null

2、mysql说明

（1）主服务器

   hostname:master    IP：172.16.7.202

（2）从服务器

   hostname:slave     IP：172.16.7.250

（3）数据目录

   /mydata/data

（4）二进制日志目录

   /mydata/binlogs

（5）中继日志目录

   /mydata/relaylogs

三、SSL主从同步的实现

1、master（172.16.7.202）安装后配置文件

1
2
3
4
5
6
7
8
9
10
thread_concurrency = 4
datadir =  /mydata/data     #数据目录
log-bin= /mydata/binlogs/master-bin
relay-log= /mydata/relaylogs/relay
sync_binlog = 1     #设定每1秒钟同步一次缓冲中的数据到日志文件中
binlog_format=mixed     #二进制日志格式为混合模式
server- id        = 1        #主服务器的server-id=1,从的等于2
#
#
#slave（172.16.7.250）同master

2、将master（172.16.7.202）做为CA服务器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
[root@master ~] # cd /etc/pki/CA
[root@master CA] #
[root@master CA] # (umask 077;openssl genrsa -out private/cakey.pem 2048)
Generating RSA private key, 2048 bit long modulus
..........................................+++
..................+++
e is 65537 (0x10001)
[root@master CA] #
[root@master CA] # openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 36500
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) []:Beijing
Locality Name (eg, city) [Default City]:Beijing
Organization Name (eg, company) [Default Company Ltd]:sina
Organizational Unit Name (eg, section) []:mysql
Common Name (eg, your name or your server's  hostname ) []:master.sina.com
Email Address []:
[root@master CA] # touch index.txt serial crlnumber
[root@master CA] # echo 01 > serial

3、为master（172.16.7.202）签发证书

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
[root@master CA] # mkdir /usr/local/mysql/ssl
[root@master CA] # cd /usr/local/mysql/ssl
[root@master ssl] # (umask 077;openssl genrsa -out master.key 2048)
Generating RSA private key, 2048 bit long modulus
..........+++
............................................................+++
e is 65537 (0x10001)
[root@master ssl] #
[root@master ssl] # openssl req -new -key master.key -out master.csr -days 36500
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) []:Beijing
Locality Name (eg, city) [Default City]:Beijing
Organization Name (eg, company) [Default Company Ltd]:sina
Organizational Unit Name (eg, section) []:mysql
Common Name (eg, your name or your server's  hostname ) []:master.sina.com
Email Address []:
Please enter the following  'extra'  attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[root@master ssl] #
[root@master ssl] # openssl ca -in master.csr -out master.crt -days 36500
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: May  3 13:34:58 2014 GMT
             Not After : Apr  9 13:34:58 2114 GMT
         Subject:
             countryName               = CN
             stateOrProvinceName       = Beijing
             organizationName          = sina
             organizationalUnitName    = mysql
             commonName                = master.sina.com
         X509v3 extensions:
             X509v3 Basic Constraints:
                 CA:FALSE
             Netscape Comment:
                 OpenSSL Generated Certificate
             X509v3 Subject Key Identifier:
                 62:EF:37:1D:96:FF:8A:89:47:09:2D:93:74:42:14:BF:8E:AC:51:49
             X509v3 Authority Key Identifier:
                 keyid:6B:73:D6:FE:81:13:2C:0E:EC:61:EE:F7:6F:92:91:6D:82:37:A0:11
Certificate is to be certified  until  Apr  9 13:34:58 2114 GMT (36500 days)
Sign the certificate? [y /n ]:y
1 out of 1 certificate requests certified, commit? [y /n ]y
Write out database with 1 new entries
Data Base Updated

4、slave生成证书申请请求

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
[root@slave ~] # mkdir /usr/local/mysql/ssl
[root@slave ~] # cd /usr/local/mysql/ssl
[root@slave ssl] # (umask 077;openssl genrsa -out slave.key 2048)
Generating RSA private key, 2048 bit long modulus
.....................................................+++
........................................+++
e is 65537 (0x10001)
[root@slave ssl] #
[root@slave ssl] # openssl req -new -key slave.key -out slave.csr -days 36500
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) []:Beijing
Locality Name (eg, city) [Default City]:Beijing
Organization Name (eg, company) [Default Company Ltd]:sina
Organizational Unit Name (eg, section) []:mysql
Common Name (eg, your name or your server's  hostname ) []:slave.sina.com
Email Address []:
Please enter the following  'extra'  attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[root@slave ssl] #
[root@slave ssl] #
[root@slave ssl] # scp slave.csr root@172.16.7.202:/root

5、为slave（172.16.7.250）签发证书

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
[root@master ~] # openssl ca -in slave.csr -out slave.crt -days 36500
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: May  3 13:43:28 2014 GMT
             Not After : Apr  9 13:43:28 2114 GMT
         Subject:
             countryName               = CN
             stateOrProvinceName       = Beijing
             organizationName          = sina
             organizationalUnitName    = mysql
             commonName                = slave.sina.com
         X509v3 extensions:
             X509v3 Basic Constraints:
                 CA:FALSE
             Netscape Comment:
                 OpenSSL Generated Certificate
             X509v3 Subject Key Identifier:
                 20:CB:55:9C:D0:7A:F0:25:70:AC:84:2B:8E:F4:24:FB:1F:51:48:9D
             X509v3 Authority Key Identifier:
                 keyid:6B:73:D6:FE:81:13:2C:0E:EC:61:EE:F7:6F:92:91:6D:82:37:A0:11
Certificate is to be certified  until  Apr  9 13:43:28 2114 GMT (36500 days)
Sign the certificate? [y /n ]:y
1 out of 1 certificate requests certified, commit? [y /n ]y
Write out database with 1 new entries
Data Base Updated
[root@master ~] #
[root@master ~] #
[root@master ~] # scp slave.crt root@172.16.7.250:/usr/local/mysql/ssl/

6、为master及slave提供CA的证书

1
2
[root@master ~] # cp /etc/pki/CA/cacert.pem /usr/local/mysql/ssl/
[root@master ~] # scp /etc/pki/CA/cacert.pem root@172.16.7.250:/usr/local/mysql/ssl/

7、修改master和slave的属主、属组为"mysql"

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[root@master ~] # chown -R mysql.mysql /usr/local/mysql/ssl/
[root@master ~] # ll /usr/local/mysql/ssl/
total 20
-rw-r--r-- 1 mysql mysql 1330 May  3 21:48 cacert.pem
-rw-r--r-- 1 mysql mysql 4465 May  3 21:35 master.crt
-rw-r--r-- 1 mysql mysql 1009 May  3 21:33 master.csr
-rw------- 1 mysql mysql 1675 May  3 21:32 master.key
###
###
[root@slave ssl] # chown -R mysql.mysql /usr/local/mysql/ssl/
[root@slave ssl] # ll /usr/local/mysql/ssl/
total 20
-rw-r--r-- 1 mysql mysql 1330 May  3 21:49 cacert.pem
-rw-r--r-- 1 mysql mysql 4460 May  3 21:44 slave.crt
-rw-r--r-- 1 mysql mysql 1005 May  3 21:40 slave.csr
-rw------- 1 mysql mysql 1679 May  3 21:38 slave.key

8、修改mysql配置文件开启SSL加密功能

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[root@master ~] # vim /etc/my.cnf
[mysqld]
ssl
ssl_ca =  /usr/local/mysql/ssl/cacert .pem
ssl_key =  /usr/local/mysql/ssl/master .key
ssl_cert =  /usr/local/mysql/ssl/master .crt
[root@master ~] # service mysqld restart
#
#
[root@slave ~] # vim /etc/my.cnf
[mysqld]
ssl
ssl_ca =  /usr/local/mysql/ssl/cacert .pem
ssl_key =  /usr/local/mysql/ssl/slave .key
ssl_cert =  /usr/local/mysql/ssl/slave .crt
[root@slave ~] # service mysqld restart

9、在master上验证SSL加密功能开启并创建基于密钥认证用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
[root@master ~] # mysql
MariaDB [(none)]> show variables like  '%ssl%' ;
+---------------+---------------------------------+
| Variable_name | Value                           |
+---------------+---------------------------------+
| have_openssl  | NO                              |
| have_ssl      | YES                             |
| ssl_ca        |  /usr/local/mysql/ssl/cacert .pem |
| ssl_capath    |                                 |
| ssl_cert      |  /usr/local/mysql/ssl/master .crt |
| ssl_cipher    |                                 |
| ssl_crl       |                                 |
| ssl_crlpath   |                                 |
| ssl_key       |  /usr/local/mysql/ssl/master .key |
+---------------+---------------------------------+
MariaDB [(none)]>
MariaDB [(none)]> grant replication slave,replication client on *.* to  'repluser' @ '172.16.%.%'  identified by  'repluser'  require ssl;
MariaDB [(none)]> flush privileges;

10、查看master状态信息

1
2
3
4
5
6
MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000002 |      652 |              |                  |
+-------------------+----------+--------------+------------------+

11、验证slave开启SSL加密功能

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[root@slave ~] # mysql
MariaDB [(none)]>
MariaDB [(none)]> show variables like  '%ssl%' ;
+---------------+---------------------------------+
| Variable_name | Value                           |
+---------------+---------------------------------+
| have_openssl  | NO                              |
| have_ssl      | YES                             |
| ssl_ca        |  /usr/local/mysql/ssl/cacert .pem |
| ssl_capath    |                                 |
| ssl_cert      |  /usr/local/mysql/ssl/slave .crt  |
| ssl_cipher    |                                 |
| ssl_crl       |                                 |
| ssl_crlpath   |                                 |
| ssl_key       |  /usr/local/mysql/ssl/slave .key  |
+---------------+---------------------------------+

12、slave连接master

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
MariaDB [(none)]> change master to master_host= '172.16.7.202' ,master_user= 'repluser' ,master_password= 'repluser' ,master_log_file= 'master-bin.000002' ,master_log_pos=652,master_ssl=1,master_ssl_ca= '/usr/local/mysql/ssl/cacert.pem' ,master_ssl_cert= '/usr/local/mysql/ssl/slave.crt' ,master_ssl_key= '/usr/local/mysql/ssl/slave.key' ;
Query OK, 0 rows affected (0.06 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.04 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting  for  master to send event
                   Master_Host: 172.16.7.202
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: master-bin.000002
           Read_Master_Log_Pos: 652
                Relay_Log_File: relay.000002
                 Relay_Log_Pos: 536
         Relay_Master_Log_File: master-bin.000002
              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: 652
               Relay_Log_Space: 823
               Until_Condition: None
                Until_Log_File:
                 Until_Log_Pos: 0
            Master_SSL_Allowed: Yes
            Master_SSL_CA_File:  /usr/local/mysql/ssl/cacert .pem
            Master_SSL_CA_Path:
               Master_SSL_Cert:  /usr/local/mysql/ssl/slave .crt
             Master_SSL_Cipher:
                Master_SSL_Key:  /usr/local/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
                Master_SSL_Crl:  /usr/local/mysql/ssl/cacert .pem
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
1 row  in  set  (0.00 sec)

四、同步验证

1、在master上新建数据库hlbrc

1
2
3
4
5
6
7
8
9
10
11
12
MariaDB [(none)]> create database hlbrc;
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hlbrc              |
| information_schema |
| mysql              |
| performance_schema |
|  test                |
+--------------------+

2、在slave上验证

1
2
3
4
5
6
7
8
9
10
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hlbrc              |
| information_schema |
| mysql              |
| performance_schema |
|  test                |
+--------------------+









本文转自 nmshuishui 51CTO博客，原文链接：http://blog.51cto.com/nmshuishui/1405856，如需转载请自行联系原作者
文章标签：
云数据库 RDS MySQL 版
日志服务
关系型数据库
MySQL
网络安全
数据安全/隐私保护
数据库
关键词：
云数据库 RDS MySQL 版主从复制
云数据库MariaDB版主从复制
云数据库 RDS MySQL 版mariadb
mysql云数据库MariaDB版
云数据库MariaDB版mysql
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境，其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
余二五
目录
相关文章
java知路
|
2月前
|
SQL 存储 关系型数据库
MySQL的主从复制&主从同步
MySQL的主从复制&主从同步
java知路
33 0
磊哥聊Java
|
5月前
|
SQL 关系型数据库 MySQL
面试官：说一下MySQL主从复制的原理？
面试官：说一下MySQL主从复制的原理？
磊哥聊Java
98 0
面试官：说一下MySQL主从复制的原理？
游客3bfkcht77micg
|
5月前
|
SQL 关系型数据库 MySQL
MySQL主从复制
MySQL主从复制
游客3bfkcht77micg
42 0
极客李华
|
22天前
|
负载均衡 容灾 关系型数据库
mysql主从复制
mysql主从复制
极客李华
37 1
憧憬blog
|
2月前
|
SQL 存储 运维
MySQL高可用性：主从复制和集群
MySQL高可用性：主从复制和集群
憧憬blog
41 0
流烟默
|
5月前
|
关系型数据库 MySQL Linux
Linux下搭建MySQL主从复制之一主一从架构
Linux下搭建MySQL主从复制之一主一从架构
流烟默
64 0
程序三两行
|
21天前
|
SQL 关系型数据库 MySQL
mysql主从复制
mysql主从复制
程序三两行
26 0
思考的陈
|
2月前
|
SQL 网络协议 关系型数据库
【怒怼大厂面试官】听说你精通MySQL？来说说MySQL主从复制
面试官：MySQL主从复制了解吧?嗯嗯了解的。主要是利用了MySQL的Binary Log二进制文件。那我把二进制文件丢给从库，从库复制整个文件吗。噢噢不是的。
思考的陈
48 1
【怒怼大厂面试官】听说你精通MySQL？来说说MySQL主从复制
达芬奇要当程序员
|
2月前
|
监控 负载均衡 关系型数据库
MySQL主从复制详解
MySQL主从复制详解
达芬奇要当程序员
54 0
Aries_Ro
|
4月前
|
SQL 负载均衡 关系型数据库
MySQL（六）主从复制
MySQL（六）主从复制
Aries_Ro
27 0

热门文章

最新文章

  • 1
    MySQL全局库表查询准确定位字段
  • 2
    轻松入门MySQL：深入探究MySQL的ER模型，数据库设计的利器与挑战（22）
  • 3
    基于 java + Springboot + vue +mysql 大学生实习管理系统（含源码）
  • 4
    mysql性能优化-慢查询分析、优化索引和配置
  • 5
    CentOS 7 下使用yum安装MySQL5.7.20 最简单 图文详解
  • 6
    MySQL 8教程：详解如何更改和重置用户密码
  • 7
    centos7安装mysql-带网盘安装包
  • 8
    MySQL学习手册（第一部分）
  • 9
    高性能 MySQL 第四版（GPT 重译）（三）(1)
  • 10
    mysql用户权限设置
  • 1
    如何实现在公网下使用navicat图形化工具远程连接本地内网的MariaDB数据库
    34
  • 2
    安卓手机termux上安装MariaDB数据库并实现公网环境下的远程连接
    52
  • 3
    通过内网穿透本地MariaDB数据库，实现在公网环境下使用navicat图形化工具
    44
  • 4
    如何搭建MariaDB并实现无公网ip环境远程连接本地数据库
    80
  • 5
    MariaDB忘记密码
    23
  • 6
    如何使用navicat图形化工具远程连接MariaDB数据库【cpolar内网穿透】
    83
  • 7
    centos编译安装mariadb
    140
  • 8
    Linux（centos 7.5）服务器安装MariaDB
    205
  • 9
    阿里云关系型数据库详细介绍MySQL/MariaDB/SQL Server/PolarDB/PostgreSQL等
    118
  • 10
    阿里云RDS数据库_MySQL_SQL Server_MariaDB_PolarDB_PostgreSQL
    111

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • 测试场景（RDS无优惠）
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用Python完成RDS数据开发及分析
  • Excel文件转存到RDS数据库
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • mongodb
    • 下一篇
    2024年阿里云免费云服务器及学生云服务器申请教程参考