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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
日志服务 SLS,月写入数据量 50GB 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,如需转载请自行联系原作者
相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
SQL 关系型数据库 MySQL
mysql主从复制概述和配置
【10月更文挑战第22天】MySQL 主从复制是一种将主服务器的数据复制到一个或多个从服务器的技术,实现读写分离,提高系统性能和可用性。主服务器记录变更日志,从服务器通过 I/O 和 SQL 线程读取并应用这些变更。适用于读写分离、数据备份和恢复、数据分析等场景。配置步骤包括修改配置文件、创建复制用户、配置从服务器连接主服务器并启动复制进程。
|
2月前
|
监控 关系型数据库 MySQL
深入了解MySQL主从复制:构建高效稳定的数据同步架构
深入了解MySQL主从复制:构建高效稳定的数据同步架构
130 1
|
2月前
|
存储 关系型数据库 MySQL
MySQL主从复制原理和使用
本文介绍了MySQL主从复制的基本概念、原理及其实现方法,详细讲解了一主两从的架构设计,以及三种常见的复制模式(全同步、异步、半同步)的特点与适用场景。此外,文章还提供了Spring Boot环境下配置主从复制的具体代码示例,包括数据源配置、上下文切换、路由实现及切面编程等内容,帮助读者理解如何在实际项目中实现数据库的读写分离。
MySQL主从复制原理和使用
|
2月前
|
SQL 关系型数据库 MySQL
Mysql中搭建主从复制原理和配置
主从复制在数据库管理中广泛应用,主要优点包括提高性能、实现高可用性、数据备份及灾难恢复。通过读写分离、从服务器接管、实时备份和地理分布等机制,有效增强系统的稳定性和数据安全性。主从复制涉及I/O线程和SQL线程,前者负责日志传输,后者负责日志应用,确保数据同步。配置过程中需开启二进制日志、设置唯一服务器ID,并创建复制用户,通过CHANGE MASTER TO命令配置从服务器连接主服务器,实现数据同步。实验部分展示了如何在两台CentOS 7服务器上配置MySQL 5.7主从复制,包括关闭防火墙、配置静态IP、设置域名解析、配置主从服务器、启动复制及验证同步效果。
Mysql中搭建主从复制原理和配置
|
3月前
|
存储 关系型数据库 MySQL
分析MySQL主从复制中AUTO_INCREMENT值不一致的问题
通过对 `AUTO_INCREMENT`不一致问题的深入分析和合理应对措施的实施,可以有效地维护MySQL主从复制环境中数据的一致性和完整性,确保数据库系统的稳定性和可靠性。
118 6
|
3月前
|
存储 关系型数据库 MySQL
分析MySQL主从复制中AUTO_INCREMENT值不一致的问题
通过对 `AUTO_INCREMENT`不一致问题的深入分析和合理应对措施的实施,可以有效地维护MySQL主从复制环境中数据的一致性和完整性,确保数据库系统的稳定性和可靠性。
67 1
|
3月前
|
关系型数据库 MySQL Java
MySQL主从复制实现读写分离
MySQL主从复制(二进制日志)、 Sharding-JDBC实现读写分离
MySQL主从复制实现读写分离
|
4月前
|
安全 Java 关系型数据库
Java连接Mysql SSL初始化失败
Java连接Mysql SSL初始化失败
|
4月前
|
SQL 关系型数据库 MySQL
说一下MySQL主从复制的原理?
【8月更文挑战第24天】说一下MySQL主从复制的原理?
65 0
|
4月前
|
存储 关系型数据库 MySQL
实战!MySQL主从复制一键搭建脚本分享
实战!MySQL主从复制一键搭建脚本分享
80 2