知识分享

分享、变通、创造、创新

树莓派5 raspberry pi 5 MariaDB ssl证书配置 c++ 局域网、本地连接

由 · 2026年5月24日

树莓派5 raspberry pi 5 MariaDB ssl证书配置 c++ 局域网、本地连接

#一、
sudo nano /etc/my.cnf.d/mariadb-server.cnf


[client]
ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/client-cert.pem
ssl-key=/etc/mysql/certs/client-key.pem

# 1、必须验证客户端登录所有用户 验证ssl证书
#ssl-verify-server-cert=on


#二、
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf 

# this is only for the mysqld standalone daemon
[mysqld]
# SSL 配置
ssl = ON

ssl-ca = /etc/mysql/certs/ca-cert.pem
ssl-cert = /etc/mysql/certs/server-cert.pem
ssl-key = /etc/mysql/certs/server-key.pem
require-secure-transport = on

#tls_version = TLSv1.3
# 配置兼容性更好的加密套件
ssl_cipher = TLS_AES_128_GCM_SHA256:TLS_AES_256_GCM_SHA384:AES128-GCM-SHA256:AES256-GCM-SHA384
# 允许 TLS 1.2 和 1.3（增加兼容性）
tls_version = TLSv1.2,TLSv1.3

三、
方案 3：统一重建用户（最干净）

```sql
-- 删除所有 steven_roc_ssl 用户
DROP USER IF EXISTS 'steven_roc_ssl'@'localhost';
DROP USER IF EXISTS 'steven_roc_ssl'@'192.168.10.%';
DROP USER IF EXISTS 'steven_roc_ssl'@'192.168.11.%';
DROP USER IF EXISTS 'steven_roc_ssl'@'stevenroc.lan';
DROP USER IF EXISTS 'steven_roc_ssl'@'desktop-vsfb8b9.lan';

-- 重新创建，只保留需要的条目，全部强制 X509
CREATE USER 'steven_roc_ssl'@'192.168.10.%' 
  IDENTIFIED BY 'xiaocaoNB=' 
  REQUIRE X509;

CREATE USER 'steven_roc_ssl'@'192.168.11.%' 
  IDENTIFIED BY 'xiaocaoNB=' 
  REQUIRE X509;

-- 授予权限
GRANT ALL PRIVILEGES ON chanking.* TO 'steven_roc_ssl'@'192.168.10.%';
GRANT ALL PRIVILEGES ON chanking.* TO 'steven_roc_ssl'@'192.168.11.%';

FLUSH PRIVILEGES;

 

二、授权页面

授权数据库操作权限 web页面操作
GRANT ALL PRIVILEGES ON `chanking`.* TO ‘steven_roc_ssl’@’192.168.10.%’ WITH GRANT OPTION;

 

三、c++ 密码和ssl证书连接数据库

1、

cmake_minimum_required(VERSION 3.10)
project(MariaDB_SSL_Connection)

set(CMAKE_CXX_STANDARD 11)
set(CMAKE_CXX_STANDARD_REQUIRED ON)

# 查找 MariaDB 库
find_library(MARIADB_LIB mariadb REQUIRED)
find_path(MARIADB_INCLUDE_DIR mysql.h PATHS /usr/include/mariadb)

include_directories(${MARIADB_INCLUDE_DIR})

# 源文件
add_executable(list_tables_ssl_fixed list_tables_ssl_fixed.cpp)

# 链接 MariaDB 库
target_link_libraries(list_tables_ssl_fixed ${MARIADB_LIB} ssl crypto)

# 编译选项
target_compile_options(list_tables_ssl_fixed PRIVATE -Wall -Wextra)

2、

#include <mysql/mysql.h>
#include <iostream>
#include <string>
#include <cstdlib>
#include <unistd.h>
#include <sys/stat.h>
#include <cstring>
#include <libgen.h>
#include <limits.h>  // 添加这个头文件以使用 PATH_MAX
/*

mysql -u steven_roc -p -h 192.168.10.2


GRANT ALL PRIVILEGES ON *.* TO 'steven_roc_ssl'@'localhost' IDENTIFIED BY 'xiaocaoNB=' REQUIRE SSL;

FLUSH PRIVILEGES;

cd /home/stevenroc/MariaDBSSL_certs

# 编译
g++ list_tables_ssl_fixed.cpp -o list_tables_ssl_fixed \
    -I/usr/include/mariadb \
    -lmariadb \
    -lssl \
    -lcrypto \
    -std=c++11

# 设置密码环境变量
export DB_PASSWORD="xiaocaoNB="

# 运行
./list_tables_ssl_fixed
*/
// 检查文件是否存在
bool file_exists(const char* path) {
    struct stat buffer;
    return (stat(path, &buffer) == 0);
}

// 检查文件是否可读
bool file_readable(const char* path) {
    return (access(path, R_OK) == 0);
}

// 获取当前执行程序所在目录
std::string get_executable_dir() {
    char result[PATH_MAX];
    ssize_t count = readlink("/proc/self/exe", result, PATH_MAX);
    if (count != -1) {
        result[count] = '\0';
        char* dir = dirname(result);
        return std::string(dir);
    }
    return ".";
}

int main(int argc, char** argv) {
    MYSQL mysql;
    mysql_init(&mysql);
    
    // 获取当前执行程序所在目录
    std::string exe_dir = get_executable_dir();
    
    // 证书文件路径（相对于执行程序目录的 certs 文件夹）
    std::string client_key = exe_dir + "/certs/client-key.pem";
    std::string client_cert = exe_dir + "/certs/client-cert.pem";
    std::string ca_cert = exe_dir + "/certs/ca.pem";
    
    // 检查证书文件
    std::cout << "🔍 检查证书文件..." << std::endl;
    std::cout << "   执行程序目录: " << exe_dir << std::endl;
    
    if (!file_exists(client_key.c_str())) {
        std::cerr << "❌ 客户端私钥文件不存在: " << client_key << std::endl;
        return 1;
    }
    if (!file_readable(client_key.c_str())) {
        std::cerr << "❌ 客户端私钥文件不可读: " << client_key << std::endl;
        return 1;
    }
    std::cout << "✅ 客户端私钥文件: " << client_key << std::endl;
    
    if (!file_exists(client_cert.c_str())) {
        std::cerr << "❌ 客户端证书文件不存在: " << client_cert << std::endl;
        return 1;
    }
    if (!file_readable(client_cert.c_str())) {
        std::cerr << "❌ 客户端证书文件不可读: " << client_cert << std::endl;
        return 1;
    }
    std::cout << "✅ 客户端证书文件: " << client_cert << std::endl;
    
    if (!file_exists(ca_cert.c_str())) {
        std::cerr << "❌ CA 证书文件不存在: " << ca_cert << std::endl;
        return 1;
    }
    if (!file_readable(ca_cert.c_str())) {
        std::cerr << "❌ CA 证书文件不可读: " << ca_cert << std::endl;
        return 1;
    }
    std::cout << "✅ CA 证书文件: " << ca_cert << std::endl;
    
    // 设置 SSL 证书
    std::cout << "\n🔄 配置 SSL 证书..." << std::endl;
  //steven_roc_ssl   
   mysql_ssl_set(&mysql, client_key.c_str(), client_cert.c_str(), ca_cert.c_str(), NULL, NULL);
 //   mysql_ssl_set(&mysql, NULL, NULL, NULL, NULL, NULL);
    
    // 设置连接超时
    unsigned int timeout = 10;
    mysql_options(&mysql, MYSQL_OPT_CONNECT_TIMEOUT, &timeout);
    
    // 从环境变量获取密码
    const char* password = getenv("DB_PASSWORD");
    if (!password || strlen(password) == 0) {
        std::cerr << "❌ 请设置环境变量 DB_PASSWORD" << std::endl;
        std::cerr << "   示例: export DB_PASSWORD='your_actual_password'" << std::endl;
        return 1;
    }
    
    // 连接数据库（同时需要密码和 SSL 证书）
    std::cout << "🔄 正在连接到 chanking 数据库 (SSL + 密码验证)..." << std::endl;
    
    if (!mysql_real_connect(&mysql,
//			"192.168.10.2",		// //局域网电脑 密码和ssl证书连接 
                            "127.0.0.1",           // 主机
                            "steven_roc_ssl",          // 用户名
//			   "xiaocaoNB=",
                           password,              // 密码
                            "chanking",            // 数据库名
                            3306,                  // 端口
                            NULL,                  // Unix socket
                            CLIENT_SSL)) {         // 强制使用 SSL
        
        std::cerr << "❌ 连接失败: " << mysql_error(&mysql) << std::endl;
        std::cerr << "   错误码: " << mysql_errno(&mysql) << std::endl;
        
        // 检查是否是 SSL 相关错误
        if (mysql_errno(&mysql) == 2026) {
            std::cerr << "   可能原因: SSL 证书配置错误或服务端未启用 SSL" << std::endl;
        } else if (mysql_errno(&mysql) == 1045) {
            std::cerr << "   可能原因: 用户名或密码错误" << std::endl;
        }
        
        mysql_close(&mysql);
        return 1;
    }
    
    // 验证连接是否使用 SSL
    const char* ssl_cipher = mysql_get_ssl_cipher(&mysql);
    if (ssl_cipher && strlen(ssl_cipher) > 0) {
        std::cout << "✅ SSL 连接成功！加密方式: " << ssl_cipher << std::endl;
    } else {
        std::cerr << "❌ 警告: 连接未使用 SSL" << std::endl;
        mysql_close(&mysql);
        return 1;
    }
    
    // 查询所有表
    std::cout << "\n📋 正在查询 chanking 数据库的所有表..." << std::endl;
    
    if (mysql_query(&mysql, "SHOW TABLES")) {
        std::cerr << "❌ 查询失败: " << mysql_error(&mysql) << std::endl;
        mysql_close(&mysql);
        return 1;
    }
    
    MYSQL_RES *result = mysql_store_result(&mysql);
    if (!result) {
        std::cerr << "❌ 获取结果失败: " << mysql_error(&mysql) << std::endl;
        mysql_close(&mysql);
        return 1;
    }
    
    std::cout << "\n📋 chanking 数据库中的所有表：" << std::endl;
    std::cout << "─────────────────────────────" << std::endl;
    
    MYSQL_ROW row;
    int count = 0;
    while ((row = mysql_fetch_row(result))) {
        std::cout << "  " << ++count << ". " << row[0] << std::endl;
    }
    
    if (count == 0) {
        std::cout << "  (空数据库，没有表)" << std::endl;
    } else {
        std::cout << "─────────────────────────────" << std::endl;
        std::cout << "✅ 共 " << count << " 张表" << std::endl;
    }
    
    // 清理资源
    mysql_free_result(result);
    mysql_close(&mysql);
    
    std::cout << "\n✅ 操作完成" << std::endl;
    
    return 0;
}

3、cmake编译选项一、

mkdir build && cd build
cmake ..
make -j$(nproc)
./list_tables

4、直接编译选项2

mkdir build && 
mysql -u steven_roc -p -h 192.168.10.2


GRANT ALL PRIVILEGES ON *.* TO 'steven_roc_ssl'@'localhost' IDENTIFIED BY 'xiaocaoNB=' REQUIRE SSL;

FLUSH PRIVILEGES;

cd /home/stevenroc/MariaDBSSL_certs

# 编译
g++ list_tables_ssl_fixed.cpp -o list_tables_ssl_fixed \
    -I/usr/include/mariadb \
    -lmariadb \
    -lssl \
    -lcrypto \
    -std=c++11

# 设置密码环境变量
export DB_PASSWORD="xiaocaoNB="

# 运行
./list_tables_ssl_fixed
*/

标签：

您可能还喜欢...

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注