mysql打开ssl

文章目录 [+]

MySQL 开启 SSL 连接的技术教程

在本文中，我们将学习如何在 MySQL 中开启 SSL 连接，SSL(Secure Sockets Layer)是一种安全协议，用于在不安全的网络环境中保护数据传输的安全，通过使用 SSL,我们可以确保在客户端和服务器之间的通信是加密的，从而防止数据被窃取或篡改。

1. 安装 SSL 证书

要启用 SSL 连接，首先需要为 MySQL 服务器安装 SSL 证书，证书通常由权威机构颁发，例如 Let's Encrypt、DigiCert 等，购买证书后，将其安装到服务器上，具体安装步骤因服务器操作系统而异，以下是在 Ubuntu 系统上安装证书的示例：

sudo apt-get update
sudo apt-get install certbot python3-certbot-nginx
sudo certbot --nginx -d example.com -d www.example.com

2. 配置 MySQL 服务器

安装证书后，需要配置 MySQL 服务器以使用 SSL，编辑 MySQL 配置文件 `/etc/mysql/mysql.conf.d/mysqld.cnf`,在 `[mysqld]` 部分添加以下内容：

[mysqld]
ssl_ca = /etc/mysql/ca-cert.pem
ssl_cert = /etc/mysql/server-cert.pem
ssl_key = /etc/mysql/server-key.pem

`ssl_ca` 指定了 CA 证书的路径，`ssl_cert` 和 `ssl_key` 分别指定了服务器证书和私钥的路径，请根据实际情况修改这些路径。

3. 重启 MySQL 服务

保存配置文件后，重启 MySQL 服务以使更改生效：

sudo systemctl restart mysql

4. 创建信任证书存储区

为了让客户端能够验证服务器的证书，我们需要创建一个信任证书存储区，生成一个新的受信任的根证书：

openssl req -x509 -newkey rsa:4096 -nodes -sha256 -days 3650 
    -keyout ca-key.pem -out ca-cert.pem 
    -subj "/CN=localhost"

接下来，创建一个新的用户并授予其访问数据库的权限：

CREATE USER 'mysql'@'%' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON *.* TO 'mysql'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

创建一个新的受信任的证书存储区：

mkdir -p ~/trusted
openssl x509 -in ca-cert.pem -outform der 
    -out ~/trusted/ca-cert.der 
    -noout 
    -signkey ca-key.pem 
    -passin pass:your_password

5. 在客户端上启用 SSL 连接

你可以在客户端上启用 SSL 连接了，以下是一个使用 Python `pymysql` 库连接到 MySQL 服务器的示例：

```python

import pymysql

import socket

from cryptography import x509

from cryptography.hazmat.backends import default_backend

from cryptography.hazmat.primitives import hashes, hmac, padding, serialization, selectors

from cryptography.hazmat.primitives.asymmetric import padding as asym_padding

from cryptography.x509 import CertificateBuilder, NameOID, load_pem_x509_certificate

import os

import time

import hashlib

import base64

import re

import urllib.parse as urlparse

from contextlib import closing, redirect_stdout, redirect_stderr

from io import BytesIO, IOBase, UnsupportedOperationException

from queue import Full, Queue, LifoQueue, _queue is not None and _queue or LifoQueue

from threading import Lock, ThreadError, currentThread, Condition as _Condition, _allocate_lock as allocate_lock, gettrace, settrace, activeCount, enumerate as enumerate_threads, localtime as _localtime, split as _split, frozenset as frozenset, hexlify as _hexlify, join as _join, split as _splithost, splitport as _splitport, stack_context as _stack_context, _endwithlinesep as _endwithlinesep, _getdefaulttimeout as _getdefaulttimeout, setdefaulttimeout as _setdefaulttimeout, timeout as _timeout, setblocking as _setblocking, setDaemonic as _setDaemonic, getDefaultTimeout as _getDefaultTimeout, getblocking as _getblocking, getDaemonic as _getDaemonic;socketserver as _socketserver;threading as _threading;select as _select;selectors;heapq;collections;itertools;functools;signal;traceback;sys;gc;atexit;runpy;codecs;tempfile;resource;collections.abc as _collections_abc;collections;weakref;itertools;errno as errno_module;errno;decimal;fractions;gzip;base64;quopri;types;re;warnings as warnings_module;warnings;logging;logging.handlers as logging_handlers;logging.config as logging_config;logging.LoggerAdapter as loggeradapter;logging.Filterer as filterer;logging.NullHandler as nullhandler;logging.StreamHandler as streamhandler;logging.FileHandler as filehandler;logging.Manager as manager;logging.Formatter as formatter;logging.UninitializedWarning as uninitializedwarning;logging.root as rootlogger;logging.getLogger as getlogger;logging.DEBUG as debuglevel;logging.INFO as infolevel;logging.WARNING as warninglevel;logging.ERROR as errorlevel;logging.CRITICAL as criticallevel;logging.NOTSET as notsetlevel;logging.Filterer as filterer;logging.NullHandler as nullhandler;logging.StreamHandler as streamhandler);urllib3 as urllib3

赞（）