2025年7月4日星期五

MySQL分析:使用AI驱动的CLI工具

MySQL分析:使用AI驱动的CLI工具

本文是 https://anothermysqldba.blogspot.com/2025/07/mysql-analysis-with-ai-powered-cli-tool.html 的中文翻译版。

作为MySQL DBA,我们经常在Linux终端窗口中工作。我们也喜欢可用的免费选项。这篇文章展示了一种方法,让我们可以在终端窗口中使用AI驱动的工具。你可以更新使用其他直接的AI提供商,但我将此示例设置为使用aimlapi.com,因为它免费提供有限使用的多个AI模型到你的终端,或者以很低的成本进行更多测试。

注:我不是AIMLAPI的付费发言人或任何其他身份 - 这只是一个简单的例子来突出这个想法。

问题

你正在查看一个有数百个表的遗留数据库,每个表都有复杂的关系和多年前做出的可疑设计决策。通常的过程包括:

  • 手动架构检查
  • 交叉引用文档(如果存在)
  • 运行多个EXPLAIN查询
  • 查阅最佳实践指南
  • 征求同事的第二意见

这需要时间,经常会遗漏一些东西。

基于CLI的方法

我们可以直接从CLI利用AI做很多事情。帮助MySQL分析只是这种方法如何与我们日常数据库任务配合的一个例子。通过将MySQL的原生功能与AI模型结合,全部通过简单的命令行界面访问,我们可以在不离开终端的情况下获得洞察。AIMLAPI提供对100多个AI模型的有限使用免费访问,使这种方法变得可行。对于更集中的测试,成本仍然非常合理。

工具:AIMLAPI CLI

所以这里有一个bash脚本,通过单一界面提供对100多个AI模型的访问:

#!/bin/bash
# 可访问100多个AI模型的AIMLAPI CLI工具
# 文件: ~/.local/bin/aiml

# 配置
DEFAULT_MODEL=${AIMLAPI_DEFAULT_MODEL:-"gpt-4o"}
MAX_TOKENS=${AIMLAPI_MAX_TOKENS:-2000}
TEMPERATURE=${AIMLAPI_TEMPERATURE:-0.7}
BASE_URL="https://api.aimlapi.com"
ENDPOINT="v1/chat/completions"

# 输出颜色代码
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
PURPLE='\033[0;35m'
CYAN='\033[0;36m'
NC='\033[0m' # 无颜色

# 打印彩色输出的函数
print_info() { echo -e "${BLUE}[信息]${NC} $1"; }
print_success() { echo -e "${GREEN}[成功]${NC} $1"; }
print_warning() { echo -e "${YELLOW}[警告]${NC} $1"; }
print_error() { echo -e "${RED}[错误]${NC} $1"; }
print_model() { echo -e "${PURPLE}[模型]${NC} $1"; }

# 热门模型快捷方式
declare -A MODEL_SHORTCUTS=(
    # OpenAI模型
    ["gpt4"]="gpt-4o"
    ["gpt4o"]="gpt-4o"
    ["gpt4mini"]="gpt-4o-mini"
    ["o1"]="o1-preview"
    ["o3"]="openai/o3-2025-04-16"
    
    # Claude模型  
    ["claude"]="claude-3-5-sonnet-20241022"
    ["claude4"]="anthropic/claude-sonnet-4"
    ["opus"]="claude-3-opus-20240229"
    ["haiku"]="claude-3-5-haiku-20241022"
    ["sonnet"]="claude-3-5-sonnet-20241022"
    
    # DeepSeek模型
    ["deepseek"]="deepseek-chat"
    ["deepseek-r1"]="deepseek/deepseek-r1"
    ["reasoner"]="deepseek-reasoner"
    
    # Google模型
    ["gemini"]="gemini-2.0-flash"
    ["gemini2"]="gemini-2.0-flash"
    ["gemini15"]="gemini-1.5-pro"
    
    # Meta Llama模型
    ["llama"]="meta-llama/Meta-Llama-3.1-70B-Instruct-Turbo"
    ["llama405b"]="meta-llama/Meta-Llama-3.1-405B-Instruct-Turbo"
    
    # Qwen模型
    ["qwen"]="qwen-max"
    ["qwq"]="Qwen/QwQ-32B"
    
    # Grok模型
    ["grok"]="x-ai/grok-beta"
    ["grok3"]="x-ai/grok-3-beta"
    
    # 专业模型
    ["coder"]="Qwen/Qwen2.5-Coder-32B-Instruct"
)

# 解析模型快捷方式的函数
resolve_model() {
    local model="$1"
    if [[ -n "${MODEL_SHORTCUTS[$model]}" ]]; then
        echo "${MODEL_SHORTCUTS[$model]}"
    else
        echo "$model"
    fi
}

# 使用jq创建JSON载荷的函数,确保正确转义
create_json_payload() {
    local model="$1"
    local prompt="$2"
    local system_prompt="$3"
    
    local temp_file=$(mktemp)
    echo "$prompt" > "$temp_file"
    
    if [ -n "$system_prompt" ]; then
        jq -n --arg model "$model" \
              --rawfile prompt "$temp_file" \
              --arg system "$system_prompt" \
              --argjson max_tokens "$MAX_TOKENS" \
              --argjson temperature "$TEMPERATURE" \
              '{
                model: $model,
                messages: [{role: "system", content: $system}, {role: "user", content: $prompt}],
                max_tokens: $max_tokens,
                temperature: $temperature
              }'
    else
        jq -n --arg model "$model" \
              --rawfile prompt "$temp_file" \
              --argjson max_tokens "$MAX_TOKENS" \
              --argjson temperature "$TEMPERATURE" \
              '{
                model: $model,
                messages: [{role: "user", content: $prompt}],
                max_tokens: $max_tokens,
                temperature: $temperature
              }'
    fi
    
    rm -f "$temp_file"
}

# 调用AIMLAPI的函数
call_aimlapi() {
    local prompt="$1"
    local model="$2"
    local system_prompt="$3"
    
    if [ -z "$AIMLAPI_API_KEY" ]; then
        print_error "AIMLAPI_API_KEY未设置"
        return 1
    fi
    
    model=$(resolve_model "$model")
    
    local json_file=$(mktemp)
    create_json_payload "$model" "$prompt" "$system_prompt" > "$json_file"
    
    local response_file=$(mktemp)
    local http_code=$(curl -s -w "%{http_code}" -X POST "${BASE_URL}/${ENDPOINT}" \
        -H "Content-Type: application/json" \
        -H "Authorization: Bearer $AIMLAPI_API_KEY" \
        --data-binary @"$json_file" \
        -o "$response_file")
    
    if [ "$http_code" -ne 200 ] && [ "$http_code" -ne 201 ]; then
        print_error "HTTP错误 $http_code"
        cat "$response_file" >&2
        rm -f "$json_file" "$response_file"
        return 1
    fi
    
    local content=$(jq -r '.choices[0].message.content // empty' "$response_file" 2>/dev/null)
    
    if [ -z "$content" ]; then
        content=$(jq -r '.choices[0].text // .message.content // .content // empty' "$response_file" 2>/dev/null)
    fi
    
    if [ -z "$content" ]; then
        local error_msg=$(jq -r '.error.message // .error // empty' "$response_file" 2>/dev/null)
        if [ -n "$error_msg" ]; then
            echo "API错误: $error_msg"
        else
            echo "错误: 无法解析API响应"
        fi
    else
        echo "$content"
    fi
    
    rm -f "$json_file" "$response_file"
}

# 带参数解析的主函数
main() {
    local model="$DEFAULT_MODEL"
    local system_prompt=""
    local prompt=""
    local piped_input=""
    
    if [ -p /dev/stdin ]; then
        piped_input=$(cat)
    fi
    
    # 解析参数
    while [[ $# -gt 0 ]]; do
        case $1 in
            -m|--model)
                model="$2"
                shift 2
                ;;
            -s|--system)
                system_prompt="$2"
                shift 2
                ;;
            *)
                prompt="$*"
                break
                ;;
        esac
    done
    
    # 处理输入
    if [ -n "$piped_input" ] && [ -n "$prompt" ]; then
        prompt="$prompt

要分析的数据如下:
$piped_input"
    elif [ -n "$piped_input" ]; then
        prompt="请分析这些数据:

$piped_input"
    elif [ -z "$prompt" ]; then
        echo "用法: aiml [选项] \"提示\""
        echo "       命令 | aiml [选项]"
        exit 1
    fi
    
    local resolved_model=$(resolve_model "$model")
    print_info "正在查询 $resolved_model..."
    
    local response=$(call_aimlapi "$prompt" "$model" "$system_prompt")
    
    echo ""
    print_model "来自 $resolved_model 的响应:"
    echo "----------------------------------------"
    echo "$response" 
    echo "----------------------------------------"
}

# 检查依赖项
check_dependencies() {
    command -v curl >/dev/null 2>&1 || { print_error "需要curl但未安装。"; exit 1; }
    command -v jq >/dev/null 2>&1 || { print_error "需要jq但未安装。"; exit 1; }
}

check_dependencies
main "$@"

这个脚本通过简单的快捷方式如claude4gpt4grok3等提供对各种AI模型的访问。AIMLAPI为所有这些模型提供有限使用的免费访问,额外测试的成本合理。适合想要在不超出预算的情况下进行实验的DBA。

脚本功能

脚本包含全面的帮助。aiml --help显示的内容如下:

AIMLAPI CLI工具 - 访问100多个AI模型
=======================================
用法: aiml [选项] "提示"
     命令 | aiml [选项]
核心选项:
  -m, --model 模型          要使用的模型 (默认: gpt-4o)
  -t, --tokens 数字         最大令牌数 (默认: 2000)
  -T, --temperature 浮点数  温度 0.0-2.0 (默认: 0.7)
  -s, --system 提示         模型行为的系统提示
输入/输出选项:
  -f, --file 文件           从文件读取提示
  -o, --output 文件         将响应保存到文件
  -r, --raw                 原始输出 (无格式/颜色)
信息选项:
  -l, --list               列出热门模型快捷方式
  --get-models             从API获取所有可用模型
  -c, --config             显示当前配置
  -v, --verbose            启用详细输出
  -d, --debug              显示调试信息
  -h, --help               显示此帮助
基本示例:
  aiml "解释量子计算"
  aiml -m claude "审查这段代码"
  aiml -m deepseek-r1 "逐步解决这个数学问题"
  aiml -m grok3 "AI的最新发展是什么?"
  aiml -m coder "优化这个Python函数"
管道示例:
  ps aux | aiml "分析这些进程"
  netstat -tuln | aiml "解释这些网络连接"
  cat error.log | aiml -m claude "诊断这些错误"
  git diff | aiml -m coder "审查这些代码更改"
  df -h | aiml "分析磁盘使用情况并建议清理"
文件操作:
  aiml -f prompt.txt -o response.txt
  aiml -f large_dataset.csv -m llama405b "分析这些数据"
  cat script.py | aiml -m coder -o review.md "代码审查"
模型类别和快捷方式:
  OpenAI:     gpt4, gpt4mini, o1, o3
  Claude:     claude, opus, haiku, sonnet, claude4
  DeepSeek:   deepseek, deepseek-r1, reasoner
  Google:     gemini, gemini2, gemma
  Meta:       llama, llama3, llama4, llama405b
  Qwen:       qwen, qwen2, qwq
  Grok:       grok, grok3, grok3mini
  编程:       coder, codestral
高级用法:
  aiml -m claude -s "你是安全专家" "审计这段代码"
  aiml -m deepseek-r1 -t 3000 "复杂推理任务"
  aiml -v -m grok3 "带详细日志的详细查询"
  aiml -d "调试模式以解决API问题"
模型发现:
  aiml -l                   # 显示热门快捷方式
  aiml --get-models         # 从API获取所有可用模型
  aiml --config             # 显示当前配置
环境变量:
  AIMLAPI_API_KEY          - 您的AIMLAPI密钥 (必需)
  AIMLAPI_DEFAULT_MODEL    - 默认模型 (可选)
  AIMLAPI_MAX_TOKENS       - 默认最大令牌数 (可选)
  AIMLAPI_TEMPERATURE      - 默认温度 (可选)
专业提示:
  • 使用 coder 进行编程任务和代码审查
  • 使用 deepseek-r1 进行复杂推理和数学问题
  • 使用 claude4 进行详细分析和长篇内容
  • 使用 grok3 获取当前事件和实时信息
  • 使用 gpt4mini 进行快速问题以节省API成本
  • 直接管道命令输出: 命令 | aiml "分析这个"
  • 使用 -v 获取详细输出以查看正在使用的模型
  • 使用 --get-models 查看所有100多个可用模型

通过简单界面访问100多个AI模型!

示例:City表

以下是如何通过实际MySQL表分析来演示这个工具的作用。我将使用三个不同的AI模型分析经典World数据库(来自https://dev.mysql.com/doc/index-other.html 示例数据库)的City表。

命令

mysql --login-path=klarson world -e "show create table City\G" | \
aiml --model claude4 "使用MySQL专家的观点分析这个表"

这个命令:

  1. 从MySQL提取表结构
  2. 通过管道发送到我们的AI工具
  3. 从Claude Sonnet 4获取分析

结果

Claude Sonnet 4分析

Claude 4提供了组织良好的分析:

优势:

  • 适合InnoDB效率的正确AUTO_INCREMENT主键
  • 维护引用完整性的外键约束
  • 适合常见查询的合适索引策略

发现的问题:

  • 存储效率低下: 对可变长度城市名称使用CHAR(35)浪费空间
  • 字符集限制: latin1字符集不适合国际城市名称
  • 次优索引: name_key索引仅覆盖前5个字符

建议的改进:

-- Claude建议的优化结构
CREATE TABLE `City` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(35) NOT NULL,
  `CountryCode` CHAR(3) NOT NULL,
  `District` VARCHAR(20) NOT NULL,
  `Population` int UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  KEY `name_idx` (`Name`),
  KEY `country_name_idx` (`CountryCode`, `Name`),
  KEY `population_idx` (`Population`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) 
    REFERENCES `Country` (`Code`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4080 
  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Grok 3 Beta分析

mysql --login-path=klarson world -e "show create table City\G" | \
aiml --model grok3 "使用MySQL专家的观点分析这个表"

Grok 3提供了详尽而详细的分析,涵盖:

技术深度分析:

  • 性能影响分析: 详细评估了部分索引限制
  • 存储引擎优势: 确认了InnoDB选择用于事务完整性
  • 数据类型优化: 带示例的详细节省空间建议

高级考虑:

  • 城市名称搜索的全文索引建议
  • 带特定命令的字符集迁移程序
  • 大数据集的分区策略

实施指南:

-- Grok的字符集迁移建议
ALTER TABLE City CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 全文索引建议
ALTER TABLE City ADD FULLTEXT INDEX name_fulltext (Name);

GPT-4o分析

mysql --login-path=klarson world -e "show create table City\G" | \
aiml --model gpt4 "使用MySQL专家的观点分析这个表"

GPT-4o专注于实用的、可直接执行的改进:

实用评估:

  • 验证了AUTO_INCREMENT主键设计
  • 确认了外键约束对数据完整性的好处
  • 识别了全球应用的字符集限制

ready-to-implement建议:

  • 用于立即优化的特定ALTER TABLE命令
  • 查询模式分析建议
  • 索引效果评估标准

多模型分析的力量

这种方法有价值的原因是获得三个不同的观点:

  1. Claude 4: 提供带有具体代码解决方案的详细结构化分析
  2. Grok 3: 提供带有高级优化策略的全面覆盖
  3. GPT-4o: 提供实用的、可直接执行的建议

每个模型都带来独特的优势:

  • 不同的焦点: 存储优化 vs 性能 vs 可维护性
  • 不同的深度级别: 从快速胜利到架构改进
  • 多样的分析风格: 结构化 vs 全面 vs 实用

超越MySQL:其他CLI示例

由于我们可以将任何命令输出通过管道传递给AI工具,这里有一些其他有用的示例:

系统管理

# 分析系统进程
ps aux | aiml "哪些进程使用了最多资源?"

# 检查磁盘使用情况
df -h | aiml "分析磁盘使用情况并建议清理"

# 网络连接
netstat -tuln | aiml "解释这些网络连接"

# 系统日志
tail -50 /var/log/syslog | aiml "这些日志中有令人担忧的错误吗?"

文件和目录分析

# 大文件
find /var -size +100M | aiml "按类型组织这些大文件"

# 权限问题
ls -la /etc/mysql/ | aiml "检查这些文件权限的安全性"

# 配置审查
cat /etc/mysql/my.cnf | aiml "审查这个MySQL配置"

日志分析

# Apache日志
tail -100 /var/log/apache2/error.log | aiml "总结这些Web服务器错误"

# 认证日志
grep "Failed password" /var/log/auth.log | aiml "分析这些失败的登录尝试"

重点是你可以通过管道传递几乎任何内容来获得快速分析,而无需离开终端。

实施工作流程

设置说明

1. 安装依赖项:

# 安装所需工具
sudo apt install curl jq mysql-client

# 创建脚本目录
mkdir -p ~/.local/bin

# 使脚本可执行
chmod +x ~/.local/bin/aiml

2. 配置API访问:

# 从https://aimlapi.com获取免费AIMLAPI密钥(有限使用的免费层)
export AIMLAPI_API_KEY="your-free-api-key-here"
echo 'export AIMLAPI_API_KEY="your-free-api-key-here"' >> ~/.bashrc

3. 测试设置:

# 验证配置
aiml --config

# 测试基本功能
echo "SELECT VERSION();" | aiml "解释这个SQL"

实用使用模式

快速表分析

# 分析特定表
mysql -e "SHOW CREATE TABLE users\G" mydb | \
aiml -m claude4 "分析这个MySQL表结构"

比较不同模型观点

# 获得同一个表的多个观点
TABLE_DDL=$(mysql -e "SHOW CREATE TABLE orders\G" ecommerce)

echo "$TABLE_DDL" | aiml -m claude4 "MySQL表分析"
echo "$TABLE_DDL" | aiml -m grok3 "性能优化审查" 
echo "$TABLE_DDL" | aiml -m gpt4 "实用改进建议"

分析多个表

# 数据库中所有表的快速分析
mysql -e "SHOW TABLES;" mydb | \
while read table; do
  echo "=== 分析 $table ==="
  mysql -e "SHOW CREATE TABLE $table\G" mydb | \
  aiml -m gpt4mini "快速评估这个表"
done

索引分析

# 审查索引使用和优化
mysql -e "SHOW INDEX FROM tablename;" database | \
aiml -m deepseek "为这个MySQL表建议索引优化"

查询性能分析

# 分析慢查询
mysql -e "SHOW PROCESSLIST;" | \
aiml -m grok3 "识别这些MySQL进程中的潜在性能问题"

为什么AIMLAPI使DBA能够做到这一点

合理成本的免费访问: AIMLAPI为100多个AI模型提供有限使用的免费访问,额外测试的价格非常合理。这使得想要在不承诺昂贵订阅的情况下进行实验的DBA成为完美选择。

模型多样性: 访问来自不同提供商(OpenAI、Anthropic、Google、Meta等)的模型意味着你可以获得不同的观点和专业领域。

无供应商锁定: 你可以试验不同的模型来找到最适合你特定需求的模型,而无需长期承诺。

终端原生: 保持在你舒适的Linux环境中,你已经在那里进行MySQL工作。

模型选择指南

不同的模型在MySQL分析的不同方面表现出色:

# 用于详细结构分析
aiml -m claude4 "全面的表结构审查"

# 用于性能导向分析  
aiml -m grok3 "性能优化建议"

# 用于快速实用建议
aiml -m gpt4 "立即可操作的改进"

# 用于关于权衡的复杂推理
aiml -m deepseek-r1 "复杂优化权衡分析"

# 用于成本效益的快速检查
aiml -m gpt4mini "简要表评估"

高级技术

自定义系统提示

将分析调整到你的特定环境:

# 电商焦点
aiml -m claude4 -s "你正在分析高流量电商网站的表" \
"审查这个表的可扩展性"

# 安全焦点
aiml -m grok3 -s "你是一个专注于安全的数据库分析师" \
"这个表结构的安全评估"

# 遗留系统焦点
aiml -m gpt4 -s "你正在帮助将遗留系统迁移到现代MySQL" \
"这个表的现代化建议"

自动报告

# 生成全面的数据库分析报告
DB_NAME="production_db"
REPORT_FILE="analysis_$(date +%Y%m%d).md"

echo "# $DB_NAME 数据库分析报告" > "$REPORT_FILE"
echo "生成于 $(date)" >> "$REPORT_FILE"

for table in $(mysql -Ns -e "SHOW TABLES;" "$DB_NAME"); do
  echo "" >> "$REPORT_FILE"
  echo "## 表: $table" >> "$REPORT_FILE"
  
  mysql -e "SHOW CREATE TABLE $table\G" "$DB_NAME" | \
  aiml -m claude4 "提供这个MySQL表的简洁分析" >> "$REPORT_FILE"
done

性能优化工作流程

# 全面的性能分析
mysql -e "SHOW CREATE TABLE heavy_table\G" db | \
aiml -m grok3 "性能瓶颈分析"

# 跟进索引建议
mysql -e "SHOW INDEX FROM heavy_table;" db | \
aiml -m deepseek "索引优化策略"

# 获取实施计划
aiml -m gpt4 "为这些优化创建逐步实施计划"

这种方法的实际好处

速度: 在几秒钟而不是几小时内获得专家级分析
多重视角: 不同的模型捕捉不同的问题
学习工具: 每次分析都能学到关于MySQL优化的新知识
成本效益: 由于AIMLAPI的免费层和合理定价,这种强大的分析是可访问的
一致性: 跨不同表和数据库的可重复分析
文档: 容易生成报告并与团队分享发现

获得最佳结果的提示

  1. 从结构开始: 总是从SHOW CREATE TABLE开始进行全面分析
  2. 使用具体提示: 你的请求越具体,分析就越好
  3. 比较模型: 不同的模型在不同方面表现出色 - 使用多重视角
  4. 验证建议: 总是在生产环境之前在开发环境中测试AI建议
  5. 迭代: 使用后续问题深入特定建议

今天开始

这种方法的美妙之处在于其简单性和成本效益。只需几个命令,你就可以:

  1. 从https://aimlapi.com获取免费的AIMLAPI密钥(包括免费层)
  2. 安装脚本(5分钟)
  3. 立即开始分析你的MySQL表
  4. 试验不同的模型以查看哪些最适合你的需求
  5. 将免费层用于常规分析,仅为集中测试付费

Windows用户(快速选项)

我不是Windows用户,但如果你需要在Windows上运行这个,最简单的方法是:

  1. 安装WSL2(Windows Linux子系统)
  2. 从Microsoft Store安装Ubuntu
  3. 在WSL2内遵循上述Linux设置

这为你提供了一个合适的Linux环境,脚本将完全按设计工作。

这不是要替换DBA专业知识 - 而是在保持在终端环境中的同时增强它。AI提供快速分析并捕捉你可能遗漏的东西,而你提供上下文并做出最终决定。

无论你是处理单个表还是拥有数百个表的复杂数据库,这个工作流程都能扩展以满足你的需求。由于AIMLAPI为额外使用提供合理成本的免费访问,你可以试验并找到你特定用例的完美组合,而无需预算担忧。


MySQL强大的内省功能与AI分析的结合为DBA创建了一个既实用又成本效益的工作流程。在你的下一个数据库优化项目中尝试一下 - 你可能会对出现的洞察感到惊讶,所有这些都在你舒适的终端环境中。

2024年2月25日星期日

MariaDB 加密(静态数据)

下面是一个简单的示例,显示了 MariaDB 加密的几个选项。 

您必须考虑要加密的内容。数据(传输中的数据)或实例中的数据(静态数据)的通信。 

本文将重点介绍使用在 Amazon Linux 上运行的 AWS 免费套餐节点的静态数据选项。我将在 2 个不同的实例上使用全局数据库来显示通过加密更新当前表以及将自动加密的新加载表。 


首先,我们将从安装开始......对于这个演示来说快速而简单。 

 # yum -y install mariadb105-server
# vi /etc/my.cnf.d/mariadb-server.cnf
# cat  /etc/my.cnf.d/mariadb-server.cnf | grep server_id 
server_id=100

# cat  /etc/my.cnf.d/mariadb-server.cnf | grep server_id
server_id=200
# systemctl start mariadb.service
# mysql
MariaDB [(none)]> status
--------------
mysql  Ver 15.1 Distrib 10.5.23-MariaDB, for Linux (x86_64) using  EditLine wrapper
Connection id: 3
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.5.23-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db     characterset: latin1
Client characterset: utf8
Conn.  characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 27 sec
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         100 |
+-------------+
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         200 |
+-------------+


我们将在实例 server_id 100 上加载全局数据库。 


# mysql < world.sql
# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.5.23-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         100 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| world              |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         200 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)  


现在我们可以看到两个实例当前都没有使用加密。 


MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         100 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;
Empty set (0.000 sec)

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         200 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;
Empty set (0.000 sec)


现在,我将在两个系统上设置随机密钥并对其进行加密。 

#  mkdir /etc/mysql/

#  mkdir /etc/mysql/encryption/
# (echo -n "1;" ; openssl rand -hex 32 ) | sudo tee -a  /etc/mysql/encryption/keyfile
# (echo -n "2;" ; openssl rand -hex 32 ) | sudo tee -a  /etc/mysql/encryption/keyfile
#  (echo -n "100;" ; openssl rand -hex 32 ) | sudo tee -a  /etc/mysql/encryption/keyfile

# cat /etc/mysql/encryption/keyfile
1;466139b487d5735b536a10502b0607d2b96dfa58a7f95ce4847d98ef585af8b5
2;a0f533c2e459dc64e0aeb1f0f8c9dfb496571e71001ca60fac35c8bbc6361096
100;4b4bfd61af39d57e068385cf1a023bdfc5972ed414a24167067ca5256fc314e6

# cat /etc/mysql/encryption/keyfile
1;f8b1b250d3bf7159e2abd15be146367415a80d3781bbcf7d96b26640d7efdc8a
2;c7eaccedd0ef561f0c08d461abfd651947230b08c71aec67c6064bbedb6408ec
100;90475521b6eb6be3c1ec02770f3e8f80b34130f37d25c89b51cf10ec6a7c5bb6

openssl rand -hex 128 > /etc/mysql/encryption/keyfile.key
openssl enc -aes-256-cbc -md sha1    -pass file:/etc/mysql/encryption/keyfile.key    -in /etc/mysql/encryption/keyfile    -out /etc/mysql/encryption/keyfile.enc

 ls -ltr /etc/mysql/encryption/
total 12
-rw-r--r--. 1 root root 203 Feb 24 23:39 keyfile
-rw-r--r--. 1 root root 257 Feb 24 23:40 keyfile.key
-rw-r--r--. 1 root root 224 Feb 24 23:41 keyfile.enc


现在我们可以设置 cnf 文件来启用插件以及加密选项. 


# vi /etc/my.cnf.d/mariadb-server.cnf
[mariadb]
## File Key Management
plugin_load_add = file_key_management
loose_file_key_management_filename = /etc/mysql/encryption/keyfile
loose_file_key_management_filename = /etc/mysql/encryption/keyfile.enc
loose_file_key_management_filekey = FILE:/etc/mysql/encryption/keyfile.key
loose_file_key_management_encryption_algorithm = AES_CBC

## InnoDB/XtraDB Encryption Setup
innodb_encrypt_log = ON
innodb_encrypt_temporary_tables=ON
innodb_encryption_threads=4
innodb_encrypt_tables=ON
innodb_default_encryption_key_id=1

## Aria Encryption Setup
aria_encrypt_tables = ON

## Temp & Log Encryption
encrypt-tmp-disk-tables = 1
encrypt-tmp-files = 1
encrypt_binlog = ON


重新启动 MariaDB,以便我们可以检查当前状态. 
 

systemctl restart mariadb.service
mysql
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         100 |
+-------------+
1 row in set (0.001 sec)

MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;
+----------------------------+-------------------+----------------+
| NAME                       | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------------------------+-------------------+----------------+
| innodb_system              |                 1 |              1 |
| mysql/innodb_table_stats   |                 1 |              1 |
| mysql/innodb_index_stats   |                 1 |              1 |
| mysql/transaction_registry |                 1 |              1 |
| mysql/gtid_slave_pos       |                 1 |              1 |
| world/city                 |                 1 |              1 |
| world/country              |                 1 |              1 |
| world/countrylanguage      |                 1 |              1 |
+----------------------------+-------------------+----------------+
8 rows in set (0.000 sec)

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         200 |
+-------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;
+----------------------------+-------------------+----------------+
| NAME                       | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------------------------+-------------------+----------------+
| innodb_system              |                 1 |              1 |
| mysql/innodb_index_stats   |                 1 |              1 |
| mysql/gtid_slave_pos       |                 1 |              1 |
| mysql/innodb_table_stats   |                 1 |              1 |
| mysql/transaction_registry |                 1 |              1 |
+----------------------------+-------------------+----------------+
5 rows in set (0.000 sec)


同时将世界数据上传到实例server_id 200。 

# mysql < world.sql
# mysql 
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         200 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;
+----------------------------+-------------------+----------------+
| NAME                       | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------------------------+-------------------+----------------+
| innodb_system              |                 1 |              1 |
| mysql/innodb_index_stats   |                 1 |              1 |
| mysql/gtid_slave_pos       |                 1 |              1 |
| mysql/innodb_table_stats   |                 1 |              1 |
| mysql/transaction_registry |                 1 |              1 |
| world/city                 |                 1 |              1 |
| world/country              |                 1 |              1 |
| world/countrylanguage      |                 1 |              1 |
+----------------------------+-------------------+----------------+
8 rows in set (0.000 sec)


根据 information_schema.INNODB_TABLESPACES_ENCRYPTION 我们现在已加密。但是,它们不会在架构级别显示它。虽然他们说如果它出现在  INNODB_TABLESPACES_ENCRYPTION 表中,它就会被加密,但我更愿意确定并在表和模式中看到它。 


MariaDB [(none)]> show create table world.city\G

*************************** 1. row ***************************
       Table: city
Create Table: CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.001 sec)


到目前为止,您可以看到在  重新启动或加载架构和数据后,两个实例都已计入 INNODB_TABLESPACES_ENCRYPTION 架构中。 

所以...对表格进行一些修改将会有所帮助... 


MariaDB [world]> ALTER TABLE city ENCRYPTED=Yes  ENCRYPTION_KEY_ID=1;
Query OK, 0 rows affected (0.074 sec)
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [world]> ALTER TABLE country ENCRYPTED=Yes  ENCRYPTION_KEY_ID=1;
Query OK, 0 rows affected (0.031 sec)
Records: 0  Duplicates: 0  Warnings: 0
MariaDB [world]> ALTER TABLE countrylanguage  ENCRYPTED=Yes  ENCRYPTION_KEY_ID=1;
Query OK, 0 rows affected (0.033 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [world]> show create table city\G
*************************** 1. row ***************************
       Table: city
Create Table: CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci `ENCRYPTED`=Yes `ENCRYPTION_KEY_ID`=1
1 row in set (0.000 sec)


这很简单等等。到目前为止...现在我们需要启用二进制日志并检查更多信息。 


vi /etc/my.cnf.d/mariadb-server.cnf
log_bin=demo

cat /etc/my.cnf.d/mariadb-server.cnf | grep log_bin
log_bin=demo

# systemctl restart mariadb.service

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         100 |
+-------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
            File: demo.000001
        Position: 363
    Binlog_Do_DB:
Binlog_Ignore_DB:

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|         200 |
+-------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
            File: demo.000001
        Position: 363
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.000 sec)


通过查看二进制日志来检查...... 


mariadb-binlog--base64-output=DECODE-ROWS --verbose  demo.000001

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#240225  0:06:06 server id 100  end_log_pos 256 CRC32 0x04ce3741  Start: binlog v 4, server v 10.5.23-MariaDB-log created 240225  0:06:06 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

# at 256

# Encryption scheme: 1, key_version: 1, nonce: eb7991b210f3f4d2f7f21537

# The rest of the binlog is encrypted!

ERROR: Error in Log_event::read_log_event(): 'Event decryption failure', data_len: 2400465656, event_type: 240

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;


很高兴看到它说它现在正在加密。 


MariaDB [world]> create table city2 like city;
Query OK, 0 rows affected (0.013 sec)

MariaDB [world]> insert into city2 select * from city;
Query OK, 4079 rows affected (0.078 sec)
Records: 4079  Duplicates: 0  Warnings: 0

MariaDB [world]> show create table city2\G
*************************** 1. row ***************************
       Table: city2
Create Table: CREATE TABLE `city2` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci `ENCRYPTED`=Yes `ENCRYPTION_KEY_ID`=1
1 row in set (0.000 sec)


I want to see these transactions though in the binlog.. how? You can use mariadb_binlog along with --read-from-remote-server to be able to see the data in the logs...


mariadb-binlog  --base64-output=DECODE-ROWS --verbose --read-from-remote-server   demo.000001 | more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240225  0:06:06 server id 100  end_log_pos 256 CRC32 0x04ce3741  Start: binlog v 4, server v 10.5.23-MariaDB-log created 240225  0:06:06 at startup
ROLLBACK/*!*/;
# at 256
#240225  0:06:06 server id 100  end_log_pos 296 CRC32 0x0c89f3bb  Ignorable
# Ignorable event type 164 (Start_encryption)
# at 296
#240225  0:06:06 server id 100  end_log_pos 325 CRC32 0x535776a2  Gtid list []
# at 325
#240225  0:06:06 server id 100  end_log_pos 363 CRC32 0x2ac4a61b  Binlog checkpoint demo.000001
# at 363
#240225  0:09:40 server id 100  end_log_pos 405 CRC32 0x93e10dc4  GTID 0-100-1 ddl
/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;
/*!100001 SET @@session.gtid_domain_id=0*//*!*/;
/*!100001 SET @@session.server_id=100*//*!*/;
/*!100001 SET @@session.gtid_seq_no=1*//*!*/;
# at 405
#240225  0:09:40 server id 100  end_log_pos 501 CRC32 0x39269040  Query thread_id=5 exec_time=0 error_code=0
use `world`/*!*/;
SET TIMESTAMP=1708819780/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0, @@session.explicit_defaults_
for_timestamp=0/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=utf8,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table city2 like city
/*!*/;
# at 501
#240225  0:09:49 server id 100  end_log_pos 543 CRC32 0xde82b753  GTID 0-100-2 trans
/*!100001 SET @@session.gtid_seq_no=2*//*!*/;
START TRANSACTION
/*!*/;
# at 543
# at 602
#240225  0:09:49 server id 100  end_log_pos 602 CRC32 0x05bbb9e6  Annotate_rows:
#Q> insert into city2 select * from city
#240225  0:09:49 server id 100  end_log_pos 661 CRC32 0x9e0b4e0d  Table_map: `world`.`city2` mapped to number 21
# at 661


希望这至少可以让你开始...... 


资源

https://mariadb.com/kb/en/securing-mariadb-encryption/  

2020年11月12日星期四

使用您的FRM文件获取Schema,然后导入idb文件。

总的来说,这是一个您永远不必做的主题...为什么?因为您创建的备份是正确的...您已经测试并知道了备份的工作原理,所以您可以仅还原那些备份并获取丢失的架构和相关数据... 

但是,角落办公室中的一个实例..您从来没有进行设置..那不是那么重要...只是崩溃了,现在您认为了您实际上是如何使用它的... 

一切都没有丢失。  

MySQL不久前发布了他们的MySQL实用程序,之后被MySQL Shell所取代。  

mysqlfrm仍然非常方便,但是当需要通过快速简单的命令从FRM文件中提取模式时,这是一个简单的安装。 

mysqlfrm --diagnostic city.frm
# WARNING: Cannot generate character set or collation names without the --server option. # CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for city.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(160) DEFAULT NULL,
  `CountryCode` char(12) NOT NULL,
  `District` char(80) NOT NULL,
  `Population` int(11) NOT NULL,
PRIMARY KEY `PRIMARY` (`ID`),
KEY `CountryCode` (`CountryCode`),
KEY `popkey` (`Population`)
) ENGINE=InnoDB;

#...done.


因此,现在您拥有丢失的架构...重建数据库或表。对于这个例子的目的,我会说,我们刚刚从世界DB丢失了城市的数据。 

$ cp  city.ibd  / tmp /  

$ cp city.ibd /tmp/
mysql> LOCK TABLES city WRITE;
mysql> ALTER TABLE city DISCARD TABLESPACE;

cp city.ibd /edb/local/mysql/data/rundeck/
chown tmdba:dba /edb/local/mysql/data/rundeck/city.ibd

mysql> ALTER TABLE city IMPORT TABLESPACE;
mysql> UNLOCK TABLES;
mysql> SELECT COUNT(*) FROM city;


2020年9月22日星期二

MySQL mysql_config_editor与期望

 这只是一条注释,旨在帮助可能会在其自动化工具中使用mysql_config_editor命令的任何人。 

mysql_config_editor不使用密码参数,因此在尝试使用mysql_config_editor的.my.cnf文件中设置密码之前可能拥有的自动化工具会失败。 

尽管使用期望工具,这是可能的并且非常简单。 

 yum -y install expect  

它也适用于apt-get。 


因此,在此示例中,我将显示一个简单的bash脚本版本。 

1 ..我的登录路径不起作用... 

mysql --login-path=local

ERROR 1045 (28000): Access denied for user


设置这个与期望 

您可以通过bash脚本执行此操作。  

expect <<EOD

spawn mysql_config_editor set --login-path=local --host=localhost --user=root --password 

expect "password"

send  -- "<PASSWORD>\r"

interact

EOD


现在可以了...

mysql --login-path=local

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1002

2020年3月16日星期一

MySQL和Docker ...一個簡單的設置

MySQL和Docker ...不是新概念,人們已經使用Docker已有一段時間了。 對於剛開始為此發展的人來說,可能會有一些障礙。

儘管MySQL在本地運行良好,但如果要在不同版本的MySQL上測試代碼,則很容易擁有多個版本是很好的。

多年來,一種選擇當然是Giuseppe Maxia的https://mysqlsandbox.net/ 這是一個非常有效的解決方案,能夠啟動多個實例並測試複製等。

在跨不同版本的MySQL進行測試時,Docker現在也是另一個經常使用的場景。 下面僅介紹一些步驟,以輕鬆安裝多個版本。 我使用OSX,因此這些示例適用於OSX。

您需要Docker才能啟動,當然,Docker Desktop是一個方便的工具,使您能夠輕鬆獲得訪問權限。

設置好Docker之後,就可以為MySQL準備好環境了。

在這裡,我創建了一個Docker文件夾,其中包含MySQL數據目錄,配置文件以及mysql-files目錄(如果需要)。

mkdir ~/Docker ;

mkdir ~/Docker/mysql_data;
mkdir ~/Docker/mysql-files;
mkdir ~/Docker/cnf;

現在在mysql_data


cd ~/Docker/mysql_data;
mkdir 8.0;
mkdir 5.7;
mkdir 5.6;
mkdir 5.5;


現在,我為該示例設置簡單的cnf文件。 要注意的主要事情是綁定地址。 這樣做是為了確保它對我們開放,使其可以在docker之外訪問MySQL。 您還可以注意到,對於每個MySQL docker實例,這些文件都可以用來設置其他配置信息。



cd ~/Docker/cnf;

cat my.8.0.cnf
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= /var/lib/mysql-files
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
bind-address = 0.0.0.0
port=3306
server-id=80


# Custom config should go here
!includedir /etc/mysql/conf.d/

cat my.5.7.cnf
[mysqld]
bind-address = 0.0.0.0
server-id=57
max_allowed_packet=32M

$ cat my.5.6.cnf
[mysqld]
bind-address = 0.0.0.0
server-id=56

$ cat my.5.5.cnf
[mysqld]
bind-address = 0.0.0.0
server-id=55


好的,現在我們已經設置了配置文件,我們需要構建docker。 生成命令要注意的幾件事。

--name我們為docker設置了一個命名引用。

在這裡,我們將配置文件,數據目錄和mysql-files目錄映射到docker。 這使我們可以輕鬆調整my.cnf文件等。
-v〜/ Docker / cnf / my.8.0.cnf:/etc/mysql/my.cnf
-v〜/ Docker / mysql_data / 8.0:/ var / lib / mysql
-v〜/ Docker / mysql文件:/ var / lib / mysql文件

我們希望能夠在docker之外訪問這些MySQL實例,因此我們需要相應地發布和映射端口。
-p 3306:3306這意味著3306在docker內部的3306本地
-p 3307:3306這意味著3307在docker內部3306本地
-p 3308:3306這意味著3308在docker內部3306本地
-p 3309:3306這意味著3309在docker內部3306本地

然後,我們還要傳遞幾個環境變量。
-e MYSQL_ROOT_HOST =%-e MYSQL_ROOT_PASSWORD = <在此處設置密碼>

所以放在一起...


docker run --restart always --name mysql8.0 -v ~/Docker/cnf/my.8.0.cnf:/etc/mysql/my.cnf -v ~/Docker/mysql_data/8.0:/var/lib/mysql -v ~/Docker/mysql-files:/var/lib/mysql-files -p 3306:3306 -d -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here> mysql:8.0

docker run --restart always --name mysql5.7 -v ~/Docker/cnf/my.5.7.cnf:/etc/mysql/my.cnf -v ~/Docker/mysql_data/5.7:/var/lib/mysql -v ~/Docker/mysql-files:/var/lib/mysql-files -p 3307:3306 -d -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here> mysql:5.7

docker run --restart always --name mysql5.6 -v ~/Docker/cnf/my.5.6.cnf:/etc/mysql/my.cnf -v ~/Docker/mysql_data/5.6:/var/lib/mysql -v ~/Docker/mysql-files:/var/lib/mysql-files -p 3308:3306 -d -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here> mysql:5.6

docker run --restart always --name mysql5.5 -v ~/Docker/cnf/my.5.5.cnf:/etc/mysql/my.cnf -v ~/Docker/mysql_data/5.5:/var/lib/mysql -v ~/Docker/mysql-files:/var/lib/mysql-files -p 3309:3306 -d -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here> mysql:5.5

每次執行以上命令後,您都應該返回一個ID。
示例:3cb07d7c21476fbf298648986208f3429ec664167d8eef7fed17bf9ee3ce6316

您可以通過Docker桌面輕鬆啟動/重新啟動和訪問每個Docker終端,或者只需記下相關ID即可通過終端執行。

Docker桌面還會顯示您傳遞的所有變量,以便您進行驗證。
當然,您也可以在此處訪問CLI,輕鬆停止或啟動或銷毀它。


$ docker exec -it 3cb07d7c21476fbf298648986208f3429ec664167d8eef7fed17bf9ee3ce6316 /bin/sh; exit
# mysql -p

如果Docker容器已經在運行,您現在可以通過localhost終端訪問MySQL。

$ mysql --host=localhost --protocol=tcp --port=3306 -p -u root

現在,如果您遇到任何訪問問題,請記住確保MySQL帳戶正確並且您的端口和映射正確。
  • 在“讀取初始通信數據包”時失去與MySQL服務器的連接
  • 錯誤1045(28000):用戶'root'@'192.168.0.5'的訪問被拒絕(使用密碼:是)

現在,您可以看到所有設備均已啟動並且可用,並且服務器ID與我們為每個cnf文件的eariler設置的設置匹配。

$ mysql --host=localhost --protocol=tcp --port=3306 -e "Select @@hostname, @@version, @@server_id "
+--------------+-----------+-------------+
| @@hostname | @@version | @@server_id |
+--------------+-----------+-------------+
| 58e9663afe8d | 8.0.19 | 80 |
+--------------+-----------+-------------+
$ mysql --host=localhost --protocol=tcp --port=3307 -e "Select @@hostname, @@version, @@server_id "
+--------------+-----------+-------------+
| @@hostname | @@version | @@server_id |
+--------------+-----------+-------------+
| b240917f051a | 5.7.29 | 57 |
+--------------+-----------+-------------+
$ mysql --host=localhost --protocol=tcp --port=3308 -e "Select @@hostname, @@version, @@server_id "
+--------------+-----------+-------------+
| @@hostname | @@version | @@server_id |
+--------------+-----------+-------------+
| b4653850cfe9 | 5.6.47 | 56 |
+--------------+-----------+-------------+
$ mysql --host=localhost --protocol=tcp --port=3309 -e "Select @@hostname, @@version, @@server_id "
+--------------+-----------+-------------+
| @@hostname | @@version | @@server_id |
+--------------+-----------+-------------+
| 22e169004583 | 5.5.62 | 55 |
+--------------+-----------+-------------+


2019年7月13日星期六

MySQL如何恢復表空間

MySQL如何恢復表空間?

這不是新的信息,但我沒有多說,所以現在為那些需要它的人解決它。

如果您丟失了ibd文件......您將丟失數據。 因此,如果您有一個可用的副本..或者即使您從另一個數據庫同步,您仍然可以導入它。 什麼/你如何失去表空間?

這是一個恢復表空間的簡單示例。



mysql> Create database demo;

mysql> use demo;

mysql> CREATE TABLE `demotable` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `dts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB;


現在我們存儲一些數據......


mysql> INSERT INTO demotable (id) VALUES (NULL);
Query OK, 1 row affected (0.10 sec)

mysql> INSERT INTO demotable (id) VALUES (NULL);
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM demotable;
+----+---------------------+
| id | dts |
+----+---------------------+
| 1 | 2019-07-12 23:31:34 |
| 2 | 2019-07-12 23:31:35 |
+----+---------------------+
2 rows in set (0.00 sec)


好的,現在讓我們打破它..


# systemctl stop mysqld
# cd /var/lib/mysql/demo/
# ls -ltr
total 80
-rw-r-----. 1 mysql mysql 114688 Jul 12 23:31 demotable.ibd
# mv demotable.ibd /tmp/

# systemctl start mysqld
# mysql demo

mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| demotable |
+----------------+
1 row in set (0.00 sec)

mysql> desc demotable;
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| dts | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
2 rows in set (0.01 sec)

mysql> INSERT INTO demotable (id) VALUES (NULL);
ERROR 1812 (HY000): Tablespace is missing for table `demo`.`demotable`.


破損和丟失的表空間......現在我們可以恢復它了..


demo]# cp /tmp/demotable.ibd .

mysql> ALTER TABLE demotable DISCARD TABLESPACE;

demo]# cp /tmp/demotable.ibd .
demo]# ls -ltr
total 112
-rw-r-----. 1 root root 114688 Jul 12 23:50 demotable.ibd
demo]# chown mysql:mysql demotable.ibd
demo]# mysql demo
mysql> ALTER TABLE demotable IMPORT TABLESPACE;
ERROR 1034 (HY000): Incorrect key file for table 'demotable'; try to repair it

mysql> REPAIR TABLE demotable;
+----------------+--------+----------+---------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------+--------+----------+---------------------------------------------------------+
| demo.demotable | repair | note | The storage engine for the table doesn't support repair |
+----------------+--------+----------+---------------------------------------------------------+


現在註意我們還有另一個錯誤..這通常與tmpdir可用的空間有關,而且無論如何修復都不適用於.ibd。


mysql> select @@tmpdir;
+----------+
| @@tmpdir |
+----------+
| /tmp |
+----------+

# vi /etc/my.cnf
tmpdir=/var/lib/mysql-files/

# systemctl restart mysqld
# mysql demo


OK只使用了mysql-files目錄。
現在我們可以再試一次。


mysql> ALTER TABLE demotable IMPORT TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.61 sec)

mysql> INSERT INTO demotable (id) VALUES (NULL);
Query OK, 1 row affected (0.11 sec)

mysql> SELECT * FROM demotable;
+----+---------------------+
| id | dts |
+----+---------------------+
| 1 | 2019-07-12 23:31:34 |
| 2 | 2019-07-12 23:31:35 |
| 3 | 2019-07-12 23:56:08 |
+----+---------------------+


好的工作。
現在,如果您只有一張桌子,這一切都很簡單。 但是100多歲......

當然,自動化它,並使用您的information_schema來提供幫助。

再做幾個副本進行測試。

mysql> create table demotable1 like demotable;
Query OK, 0 rows affected (0.51 sec)

mysql> create table demotable2 like demotable;
Query OK, 0 rows affected (1.04 sec)

mysql> create table demotable3 like demotable;
Query OK, 0 rows affected (0.74 sec)

mysql> create table demotable4 like demotable;
Query OK, 0 rows affected (2.21 sec)


打破他們所有..

demo]# mv *.ibd /tmp/


現在使用您的information_schema.tables表,您可以構建所需的所有命令。

# vi build_discard.sql
# cat build_discard.sql
SELECT CONCAT(" ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," DISCARD TABLESPACE; ") as CMD FROM information_schema.TABLES WHERE TABLE_SCHEMA='demo';

# vi build_import.sql
# cat build_import.sql
SELECT CONCAT(" ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," IMPORT TABLESPACE; ") as CMD FROM information_schema.TABLES WHERE TABLE_SCHEMA='demo';



# mysql -N < build_import.sql > import_tablespace.sql
# mysql -N < build_discard.sql | mysql demo

demo]# cp /tmp/*.ibd .
demo]# chown mysql:mysql *.ibd
# systemctl restart mysqld
# mysql demo < import_tablespace.sql
# mysql demo

mysql> INSERT INTO demotable (id) VALUES (NULL);
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO demotable1 (id) VALUES (NULL);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO demotable2 (id) VALUES (NULL);
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO demotable3 (id) VALUES (NULL);
^[[AQuery OK, 1 row affected (0.37 sec)

mysql> INSERT INTO demotable4 (id) VALUES (NULL);
Query OK, 1 row affected (0.12 sec)



它奏效了。 

MySQL Binlogs ::如何恢復

所以我意識到在最近出現這種情況後我沒有發表過關於此的帖子。

以下是場景:在午夜進行備份,他們使用每個數據庫的MySQL轉儲。 然後在第二天上午十點數據庫崩潰。 在我被調用之前發生了一系列事件,但他們把它帶到了MyISAM表的數據庫版本和表空間中缺少的IBD文件。

所以選項1,從備份恢復會讓我們到午夜,我們會丟失數小時的數據。 選項2,我們重新導入1000的ibd文件並保留所有內容。 然後我們有選項3,從備份恢復,然後應用binlogs進行最近的更改。

為了使它更有趣,他們沒有我被告知的所有ibd文件,我確實看到一些丟失。 所以不確定這是怎麼可能的,但是選項2變成了無效選項。 當然,他們希望盡可能減少數據丟失,因此我們選擇了3。

為了安全地做到這一點,我在端口3307下啟動了另一個MySQL實例。這使我有了一個安全的工作場所,同時流量對端口3306實例上的MyISAM數據具有讀訪問權限。

一旦所有備份轉儲文件解壓縮並導入3307實例,我就可以專注於binlog文件。

起初,這個概念聽起來比實際風險要大得多。 它實際上很簡單直接。

首先,您必須找到您之後的數據。 通過查看binlog文件,您可以了解哪些文件是相關的。 在我的情況下,他們設法重置了binlog,因此117文件中有2個日期範圍。

首先對於binlog審查,以下命令以人類可讀的格式輸出數據。
mysqlbinlog --defaults-file=/root/.my.cnf --base64-output=DECODE-ROWS --verbose mysql-bin.000117 > review_mysql-bin.000117.sql

*注意......小心運行上面的命令。 請注意,我將文件直接轉儲到binlog所在的位置。 因此,確認您的文件名有效。 這個mysql-bin.000117.sql與這個mysql-bin.000117 .sql不同。 您將使用第二個選項和.sql之前的空格來丟失binlog。

現在保存數據,以便可以應用它。 由於我有幾個binlogs,我創建了一個文件,我想要仔細檢查時間範圍。


mysqlbinlog --defaults-file=/root/.my.cnf --start-datetime="2019-07-09 00:00:00" --stop-datetime="2019-07-10 00:00:00" mysql-bin.000117 > binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf mysql-bin.000118 >> binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf mysql-bin.000119 >> binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf --start-datetime="2019-07-10 00:00:00" --stop-datetime="2019-07-10 10:00:00" mysql-bin.000117 >> binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf --stop-datetime="2019-07-10 10:00:00" mysql-bin.000120 >> binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf --stop-datetime="2019-07-10 10:00:00" mysql-bin.000121 >> binlog_restore.sql

mysql --socket=/var/lib/mysql_restore/mysql.sock -e "source /var/lib/mysql/binlog_restore.sql"

現在我將這些binlog中的所有數據應用於給定的時間範圍。 客戶端仔細檢查了所有數據,並非常高興能夠全部恢復。

對於這種情況存在幾種不同的選擇,這恰好與客戶一起鍛煉。

一旦驗證的all在恢復的版本上沒問題,它就是一個簡單的停止兩個數據庫,移動數據目錄(想要保持datadir默認完整),chown目錄只是為了安全並啟動MySQL。 現在,已恢復的實例已在端口3306上啟動。