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