第120集MySQL数据库迁移与运维实战

1. MySQL迁移概述

MySQL数据库迁移是运维工作中的重要环节，涉及数据备份、迁移、验证和性能优化等多个方面。本文将详细介绍MySQL数据库迁移与运维的实战经验，包括数据迁移策略、备份恢复、性能优化、故障处理的完整解决方案。

1.1 核心功能

数据迁移: 不同MySQL实例间的数据迁移
备份恢复: 数据备份和恢复策略
性能优化: 数据库性能调优和监控
故障处理: 常见故障的诊断和处理
运维自动化: 自动化运维脚本和工具

1.2 技术架构

1
2
3

源MySQL → 数据导出 → 数据转换 → 目标MySQL → 数据验证
    ↓        ↓        ↓        ↓        ↓
备份策略 → 迁移工具 → 格式转换 → 集群部署 → 一致性检查

2. 环境准备

2.1 系统要求检查

#!/bin/bash
# check_system.sh - 系统环境检查脚本
# @author 运维实战

# 日志函数
log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1"
}

# 检查系统版本
check_os_version() {
    log "检查系统版本..."

    if [ -f /etc/os-release ]; then
        . /etc/os-release
        log "操作系统: $NAME $VERSION"
    else
        log "无法确定操作系统版本"
    fi
}

# 检查内存使用情况
check_memory() {
    log "检查内存使用情况..."

    TOTAL_MEM=$(free -h | grep Mem | awk '{print $2}')
    USED_MEM=$(free -h | grep Mem | awk '{print $3}')
    AVAIL_MEM=$(free -h | grep Mem | awk '{print $7}')

    log "总内存: $TOTAL_MEM"
    log "已使用: $USED_MEM"
    log "可用内存: $AVAIL_MEM"
}

# 检查磁盘空间
check_disk_space() {
    log "检查磁盘空间..."

    df -h | grep -E "(/$|/var|/tmp|/home)" | while read line; do
        log "磁盘使用: $line"
    done
}

# 检查网络连接
check_network() {
    log "检查网络连接..."

    # 检查源数据库连接
    if ping -c 3 source-mysql-server > /dev/null 2>&1; then
        log "源数据库连接正常"
    else
        log "源数据库连接失败"
        return 1
    fi

    # 检查目标数据库连接
    if ping -c 3 target-mysql-server > /dev/null 2>&1; then
        log "目标数据库连接正常"
    else
        log "目标数据库连接失败"
        return 1
    fi
}

# 检查MySQL版本
check_mysql_version() {
    log "检查MySQL版本..."

    # 检查源MySQL版本
    SOURCE_VERSION=$(mysql -h source-mysql-server -u root -p'password' -e "SELECT VERSION();" 2>/dev/null | tail -1)
    log "源MySQL版本: $SOURCE_VERSION"

    # 检查目标MySQL版本
    TARGET_VERSION=$(mysql -h target-mysql-server -u root -p'password' -e "SELECT VERSION();" 2>/dev/null | tail -1)
    log "目标MySQL版本: $TARGET_VERSION"
}

# 主函数
main() {
    log "开始系统环境检查..."

    check_os_version
    check_memory
    check_disk_space

    if check_network; then
        check_mysql_version
    else
        log "网络连接检查失败"
        exit 1
    fi

    log "系统环境检查完成"
}

# 执行主函数
main "$@"

2.2 MySQL配置检查

#!/bin/bash
# check_mysql_config.sh - MySQL配置检查脚本
# @author 运维实战

# 配置参数
SOURCE_HOST="source-mysql-server"
SOURCE_USER="root"
SOURCE_PASSWORD="password"
SOURCE_PORT="3306"

TARGET_HOST="target-mysql-server"
TARGET_USER="root"
TARGET_PASSWORD="password"
TARGET_PORT="3306"

# 日志函数
log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1"
}

# 检查MySQL连接
check_mysql_connection() {
    local host=$1
    local user=$2
    local password=$3
    local port=$4

    log "检查MySQL连接: $host:$port"

    if mysql -h $host -P $port -u $user -p$password -e "SELECT 1;" > /dev/null 2>&1; then
        log "MySQL连接正常: $host:$port"
        return 0
    else
        log "MySQL连接失败: $host:$port"
        return 1
    fi
}

# 检查MySQL配置
check_mysql_config() {
    local host=$1
    local user=$2
    local password=$3
    local port=$4

    log "检查MySQL配置: $host:$port"

    # 检查关键配置参数
    mysql -h $host -P $port -u $user -p$password -e "
        SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
        SHOW VARIABLES LIKE 'max_connections';
        SHOW VARIABLES LIKE 'innodb_log_file_size';
        SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
        SHOW VARIABLES LIKE 'sync_binlog';
    " 2>/dev/null
}

# 检查数据库大小
check_database_size() {
    local host=$1
    local user=$2
    local password=$3
    local port=$4

    log "检查数据库大小: $host:$port"

    mysql -h $host -P $port -u $user -p$password -e "
        SELECT 
            table_schema AS 'Database',
            ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
        FROM information_schema.tables 
        GROUP BY table_schema
        ORDER BY SUM(data_length + index_length) DESC;
    " 2>/dev/null
}

# 检查表结构
check_table_structure() {
    local host=$1
    local user=$2
    local password=$3
    local port=$4
    local database=$5

    log "检查表结构: $host:$port - $database"

    mysql -h $host -P $port -u $user -p$password -e "
        USE $database;
        SHOW TABLES;
    " 2>/dev/null
}

# 主函数
main() {
    log "开始MySQL配置检查..."

    # 检查源MySQL
    if check_mysql_connection $SOURCE_HOST $SOURCE_USER $SOURCE_PASSWORD $SOURCE_PORT; then
        check_mysql_config $SOURCE_HOST $SOURCE_USER $SOURCE_PASSWORD $SOURCE_PORT
        check_database_size $SOURCE_HOST $SOURCE_USER $SOURCE_PASSWORD $SOURCE_PORT
    fi

    # 检查目标MySQL
    if check_mysql_connection $TARGET_HOST $TARGET_USER $TARGET_PASSWORD $TARGET_PORT; then
        check_mysql_config $TARGET_HOST $TARGET_USER $TARGET_PASSWORD $TARGET_PORT
        check_database_size $TARGET_HOST $TARGET_USER $TARGET_PASSWORD $TARGET_PORT
    fi

    log "MySQL配置检查完成"
}

# 执行主函数
main "$@"

3. 数据备份脚本

3.1 全量备份脚本

#!/bin/bash
# mysql_full_backup.sh - MySQL全量备份脚本
# @author 运维实战

# 配置参数
SOURCE_HOST="source-mysql-server"
SOURCE_USER="root"
SOURCE_PASSWORD="password"
SOURCE_PORT="3306"
BACKUP_DIR="/backup/mysql"
LOG_FILE="/var/log/mysql_backup.log"
RETENTION_DAYS=7

# 日志函数
log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a $LOG_FILE
}

# 创建备份目录
create_backup_dir() {
    log "创建备份目录..."

    BACKUP_DATE=$(date +%Y%m%d_%H%M%S)
    BACKUP_PATH="$BACKUP_DIR/$BACKUP_DATE"

    mkdir -p $BACKUP_PATH

    if [ $? -eq 0 ]; then
        log "备份目录创建成功: $BACKUP_PATH"
    else
        log "备份目录创建失败"
        exit 1
    fi
}

# 执行全量备份
execute_full_backup() {
    log "执行全量备份..."

    # 使用mysqldump进行全量备份
    mysqldump -h $SOURCE_HOST -P $SOURCE_PORT -u $SOURCE_USER -p$SOURCE_PASSWORD \
        --all-databases \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        --lock-tables=false \
        --add-drop-database \
        --add-drop-table \
        --extended-insert \
        --quick \
        --compress \
        > $BACKUP_PATH/full_backup.sql

    if [ $? -eq 0 ]; then
        log "全量备份完成: $BACKUP_PATH/full_backup.sql"

        # 检查备份文件大小
        BACKUP_SIZE=$(ls -lh $BACKUP_PATH/full_backup.sql | awk '{print $5}')
        log "备份文件大小: $BACKUP_SIZE"
    else
        log "全量备份失败"
        exit 1
    fi
}

# 压缩备份文件
compress_backup() {
    log "压缩备份文件..."

    cd $BACKUP_PATH
    gzip full_backup.sql

    if [ $? -eq 0 ]; then
        log "备份文件压缩完成"

        # 检查压缩后文件大小
        COMPRESSED_SIZE=$(ls -lh full_backup.sql.gz | awk '{print $5}')
        log "压缩后文件大小: $COMPRESSED_SIZE"
    else
        log "备份文件压缩失败"
        exit 1
    fi
}

# 验证备份文件
verify_backup() {
    log "验证备份文件..."

    # 检查备份文件是否存在
    if [ -f "$BACKUP_PATH/full_backup.sql.gz" ]; then
        log "备份文件存在"

        # 检查备份文件完整性
        if gzip -t $BACKUP_PATH/full_backup.sql.gz; then
            log "备份文件完整性验证通过"
        else
            log "备份文件完整性验证失败"
            exit 1
        fi
    else
        log "备份文件不存在"
        exit 1
    fi
}

# 清理过期备份
cleanup_old_backups() {
    log "清理过期备份..."

    # 删除超过保留期的备份
    find $BACKUP_DIR -type d -mtime +$RETENTION_DAYS -exec rm -rf {} \;

    if [ $? -eq 0 ]; then
        log "过期备份清理完成"
    else
        log "过期备份清理失败"
    fi
}

# 生成备份报告
generate_backup_report() {
    log "生成备份报告..."

    REPORT_FILE="$BACKUP_PATH/backup_report.txt"

    echo "MySQL全量备份报告" > $REPORT_FILE
    echo "备份时间: $(date)" >> $REPORT_FILE
    echo "源主机: $SOURCE_HOST:$SOURCE_PORT" >> $REPORT_FILE
    echo "备份目录: $BACKUP_PATH" >> $REPORT_FILE
    echo "备份文件: full_backup.sql.gz" >> $REPORT_FILE
    echo "文件大小: $(ls -lh $BACKUP_PATH/full_backup.sql.gz | awk '{print $5}')" >> $REPORT_FILE
    echo "================================" >> $REPORT_FILE

    log "备份报告生成: $REPORT_FILE"
}

# 主函数
main() {
    log "开始MySQL全量备份..."

    # 创建备份目录
    create_backup_dir

    # 执行全量备份
    execute_full_backup

    # 压缩备份文件
    compress_backup

    # 验证备份文件
    verify_backup

    # 清理过期备份
    cleanup_old_backups

    # 生成备份报告
    generate_backup_report

    log "MySQL全量备份完成"
}

# 执行主函数
main "$@"

3.2 增量备份脚本

#!/bin/bash
# mysql_incremental_backup.sh - MySQL增量备份脚本
# @author 运维实战

# 配置参数
SOURCE_HOST="source-mysql-server"
SOURCE_USER="root"
SOURCE_PASSWORD="password"
SOURCE_PORT="3306"
BACKUP_DIR="/backup/mysql/incremental"
LOG_FILE="/var/log/mysql_incremental_backup.log"
BINLOG_DIR="/var/lib/mysql"

# 日志函数
log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a $LOG_FILE
}

# 创建备份目录
create_backup_dir() {
    log "创建备份目录..."

    BACKUP_DATE=$(date +%Y%m%d_%H%M%S)
    BACKUP_PATH="$BACKUP_DIR/$BACKUP_DATE"

    mkdir -p $BACKUP_PATH

    if [ $? -eq 0 ]; then
        log "备份目录创建成功: $BACKUP_PATH"
    else
        log "备份目录创建失败"
        exit 1
    fi
}

# 刷新二进制日志
flush_binary_logs() {
    log "刷新二进制日志..."

    mysql -h $SOURCE_HOST -P $SOURCE_PORT -u $SOURCE_USER -p$SOURCE_PASSWORD -e "FLUSH BINARY LOGS;"

    if [ $? -eq 0 ]; then
        log "二进制日志刷新完成"
    else
        log "二进制日志刷新失败"
        exit 1
    fi
}

# 获取当前二进制日志文件
get_current_binlog() {
    log "获取当前二进制日志文件..."

    CURRENT_BINLOG=$(mysql -h $SOURCE_HOST -P $SOURCE_PORT -u $SOURCE_USER -p$SOURCE_PASSWORD -e "SHOW MASTER STATUS\G" | grep File | awk '{print $2}')

    if [ -n "$CURRENT_BINLOG" ]; then
        log "当前二进制日志文件: $CURRENT_BINLOG"
        echo $CURRENT_BINLOG
    else
        log "无法获取当前二进制日志文件"
        exit 1
    fi
}

# 复制二进制日志文件
copy_binary_logs() {
    log "复制二进制日志文件..."

    # 获取需要复制的二进制日志文件列表
    BINLOG_FILES=$(mysql -h $SOURCE_HOST -P $SOURCE_PORT -u $SOURCE_USER -p$SOURCE_PASSWORD -e "SHOW BINARY LOGS;" | grep -v "Log_name" | awk '{print $1}')

    for binlog_file in $BINLOG_FILES; do
        # 跳过当前正在使用的二进制日志文件
        if [ "$binlog_file" != "$CURRENT_BINLOG" ]; then
            log "复制二进制日志文件: $binlog_file"

            # 复制二进制日志文件
            cp $BINLOG_DIR/$binlog_file $BACKUP_PATH/

            if [ $? -eq 0 ]; then
                log "二进制日志文件复制成功: $binlog_file"
            else
                log "二进制日志文件复制失败: $binlog_file"
            fi
        fi
    done
}

# 压缩二进制日志文件
compress_binary_logs() {
    log "压缩二进制日志文件..."

    cd $BACKUP_PATH

    for binlog_file in *.bin; do
        if [ -f "$binlog_file" ]; then
            log "压缩二进制日志文件: $binlog_file"
            gzip $binlog_file

            if [ $? -eq 0 ]; then
                log "二进制日志文件压缩成功: $binlog_file"
            else
                log "二进制日志文件压缩失败: $binlog_file"
            fi
        fi
    done
}

# 清理二进制日志文件
cleanup_binary_logs() {
    log "清理二进制日志文件..."

    # 清理已备份的二进制日志文件
    mysql -h $SOURCE_HOST -P $SOURCE_PORT -u $SOURCE_USER -p$SOURCE_PASSWORD -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);"

    if [ $? -eq 0 ]; then
        log "二进制日志文件清理完成"
    else
        log "二进制日志文件清理失败"
    fi
}

# 生成增量备份报告
generate_incremental_report() {
    log "生成增量备份报告..."

    REPORT_FILE="$BACKUP_PATH/incremental_backup_report.txt"

    echo "MySQL增量备份报告" > $REPORT_FILE
    echo "备份时间: $(date)" >> $REPORT_FILE
    echo "源主机: $SOURCE_HOST:$SOURCE_PORT" >> $REPORT_FILE
    echo "备份目录: $BACKUP_PATH" >> $REPORT_FILE
    echo "二进制日志文件:" >> $REPORT_FILE

    # 列出备份的二进制日志文件
    ls -lh $BACKUP_PATH/*.gz >> $REPORT_FILE

    echo "================================" >> $REPORT_FILE

    log "增量备份报告生成: $REPORT_FILE"
}

# 主函数
main() {
    log "开始MySQL增量备份..."

    # 创建备份目录
    create_backup_dir

    # 刷新二进制日志
    flush_binary_logs

    # 获取当前二进制日志文件
    CURRENT_BINLOG=$(get_current_binlog)

    # 复制二进制日志文件
    copy_binary_logs

    # 压缩二进制日志文件
    compress_binary_logs

    # 清理二进制日志文件
    cleanup_binary_logs

    # 生成增量备份报告
    generate_incremental_report

    log "MySQL增量备份完成"
}

# 执行主函数
main "$@"

4. 数据迁移脚本

4.1 数据迁移脚本

#!/bin/bash
# mysql_migration.sh - MySQL数据迁移脚本
# @author 运维实战

# 配置参数
SOURCE_HOST="source-mysql-server"
SOURCE_USER="root"
SOURCE_PASSWORD="password"
SOURCE_PORT="3306"

TARGET_HOST="target-mysql-server"
TARGET_USER="root"
TARGET_PASSWORD="password"
TARGET_PORT="3306"

MIGRATION_DATABASE="test_db"
BACKUP_DIR="/backup/mysql"
LOG_FILE="/var/log/mysql_migration.log"

# 日志函数
log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a $LOG_FILE
}

# 检查源数据库连接
check_source_connection() {
    log "检查源数据库连接..."

    if mysql -h $SOURCE_HOST -P $SOURCE_PORT -u $SOURCE_USER -p$SOURCE_PASSWORD -e "SELECT 1;" > /dev/null 2>&1; then
        log "源数据库连接正常"
        return 0
    else
        log "源数据库连接失败"
        return 1
    fi
}

# 检查目标数据库连接
check_target_connection() {
    log "检查目标数据库连接..."

    if mysql -h $TARGET_HOST -P $TARGET_PORT -u $TARGET_USER -p$TARGET_PASSWORD -e "SELECT 1;" > /dev/null 2>&1; then
        log "目标数据库连接正常"
        return 0
    else
        log "目标数据库连接失败"
        return 1
    fi
}

# 创建目标数据库
create_target_database() {
    log "创建目标数据库..."

    mysql -h $TARGET_HOST -P $TARGET_PORT -u $TARGET_USER -p$TARGET_PASSWORD -e "CREATE DATABASE IF NOT EXISTS $MIGRATION_DATABASE;"

    if [ $? -eq 0 ]; then
        log "目标数据库创建成功: $MIGRATION_DATABASE"
    else
        log "目标数据库创建失败"
        exit 1
    fi
}

# 导出源数据库
export_source_database() {
    log "导出源数据库..."

    EXPORT_FILE="$BACKUP_DIR/${MIGRATION_DATABASE}_export.sql"

    mysqldump -h $SOURCE_HOST -P $SOURCE_PORT -u $SOURCE_USER -p$SOURCE_PASSWORD \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        --lock-tables=false \
        --add-drop-database \
        --add-drop-table \
        --extended-insert \
        --quick \
        --compress \
        $MIGRATION_DATABASE > $EXPORT_FILE

    if [ $? -eq 0 ]; then
        log "源数据库导出成功: $EXPORT_FILE"

        # 检查导出文件大小
        EXPORT_SIZE=$(ls -lh $EXPORT_FILE | awk '{print $5}')
        log "导出文件大小: $EXPORT_SIZE"
    else
        log "源数据库导出失败"
        exit 1
    fi
}

# 导入目标数据库
import_target_database() {
    log "导入目标数据库..."

    EXPORT_FILE="$BACKUP_DIR/${MIGRATION_DATABASE}_export.sql"

    mysql -h $TARGET_HOST -P $TARGET_PORT -u $TARGET_USER -p$TARGET_PASSWORD $MIGRATION_DATABASE < $EXPORT_FILE

    if [ $? -eq 0 ]; then
        log "目标数据库导入成功"
    else
        log "目标数据库导入失败"
        exit 1
    fi
}

# 验证数据迁移
verify_migration() {
    log "验证数据迁移..."

    # 比较表数量
    SOURCE_TABLES=$(mysql -h $SOURCE_HOST -P $SOURCE_PORT -u $SOURCE_USER -p$SOURCE_PASSWORD -e "USE $MIGRATION_DATABASE; SHOW TABLES;" | wc -l)
    TARGET_TABLES=$(mysql -h $TARGET_HOST -P $TARGET_PORT -u $TARGET_USER -p$TARGET_PASSWORD -e "USE $MIGRATION_DATABASE; SHOW TABLES;" | wc -l)

    log "源数据库表数量: $SOURCE_TABLES"
    log "目标数据库表数量: $TARGET_TABLES"

    if [ $SOURCE_TABLES -eq $TARGET_TABLES ]; then
        log "表数量验证通过"
    else
        log "表数量验证失败"
        exit 1
    fi

    # 比较记录数量
    mysql -h $SOURCE_HOST -P $SOURCE_PORT -u $SOURCE_USER -p$SOURCE_PASSWORD -e "USE $MIGRATION_DATABASE; SHOW TABLES;" | grep -v "Tables_in" | while read table; do
        SOURCE_COUNT=$(mysql -h $SOURCE_HOST -P $SOURCE_PORT -u $SOURCE_USER -p$SOURCE_PASSWORD -e "USE $MIGRATION_DATABASE; SELECT COUNT(*) FROM $table;" | tail -1)
        TARGET_COUNT=$(mysql -h $TARGET_HOST -P $TARGET_PORT -u $TARGET_USER -p$TARGET_PASSWORD -e "USE $MIGRATION_DATABASE; SELECT COUNT(*) FROM $table;" | tail -1)

        log "表 $table 记录数量 - 源: $SOURCE_COUNT, 目标: $TARGET_COUNT"

        if [ $SOURCE_COUNT -eq $TARGET_COUNT ]; then
            log "表 $table 记录数量验证通过"
        else
            log "表 $table 记录数量验证失败"
            exit 1
        fi
    done
}

# 生成迁移报告
generate_migration_report() {
    log "生成迁移报告..."

    REPORT_FILE="$BACKUP_DIR/migration_report.txt"

    echo "MySQL数据迁移报告" > $REPORT_FILE
    echo "迁移时间: $(date)" >> $REPORT_FILE
    echo "源数据库: $SOURCE_HOST:$SOURCE_PORT/$MIGRATION_DATABASE" >> $REPORT_FILE
    echo "目标数据库: $TARGET_HOST:$TARGET_PORT/$MIGRATION_DATABASE" >> $REPORT_FILE
    echo "================================" >> $REPORT_FILE

    # 添加表信息
    echo "表信息:" >> $REPORT_FILE
    mysql -h $TARGET_HOST -P $TARGET_PORT -u $TARGET_USER -p$TARGET_PASSWORD -e "USE $MIGRATION_DATABASE; SHOW TABLES;" >> $REPORT_FILE

    log "迁移报告生成: $REPORT_FILE"
}

# 主函数
main() {
    log "开始MySQL数据迁移..."

    # 检查源数据库连接
    if ! check_source_connection; then
        exit 1
    fi

    # 检查目标数据库连接
    if ! check_target_connection; then
        exit 1
    fi

    # 创建目标数据库
    create_target_database

    # 导出源数据库
    export_source_database

    # 导入目标数据库
    import_target_database

    # 验证数据迁移
    verify_migration

    # 生成迁移报告
    generate_migration_report

    log "MySQL数据迁移完成"
}

# 执行主函数
main "$@"

5. 性能优化脚本

5.1 性能调优脚本

#!/bin/bash
# mysql_performance_tune.sh - MySQL性能调优脚本
# @author 运维实战

# 配置参数
MYSQL_HOST="localhost"
MYSQL_USER="root"
MYSQL_PASSWORD="password"
MYSQL_PORT="3306"
LOG_FILE="/var/log/mysql_performance_tune.log"

# 日志函数
log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a $LOG_FILE
}

# 分析MySQL性能
analyze_performance() {
    log "分析MySQL性能..."

    # 获取性能指标
    mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD -e "
        SHOW STATUS LIKE 'Connections';
        SHOW STATUS LIKE 'Uptime';
        SHOW STATUS LIKE 'Questions';
        SHOW STATUS LIKE 'Slow_queries';
        SHOW STATUS LIKE 'Qcache_hits';
        SHOW STATUS LIKE 'Innodb_buffer_pool_hit_rate';
    " 2>/dev/null
}

# 优化InnoDB配置
optimize_innodb() {
    log "优化InnoDB配置..."

    # 获取当前InnoDB配置
    mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD -e "
        SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
        SHOW VARIABLES LIKE 'innodb_log_file_size';
        SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
        SHOW VARIABLES LIKE 'innodb_flush_method';
    " 2>/dev/null

    # 优化InnoDB配置
    mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD -e "
        SET GLOBAL innodb_buffer_pool_size = 1073741824;  -- 1GB
        SET GLOBAL innodb_log_file_size = 268435456;       -- 256MB
        SET GLOBAL innodb_flush_log_at_trx_commit = 2;
        SET GLOBAL innodb_flush_method = O_DIRECT;
    " 2>/dev/null

    log "InnoDB配置优化完成"
}

# 优化查询缓存
optimize_query_cache() {
    log "优化查询缓存..."

    # 获取当前查询缓存配置
    mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD -e "
        SHOW VARIABLES LIKE 'query_cache_type';
        SHOW VARIABLES LIKE 'query_cache_size';
        SHOW VARIABLES LIKE 'query_cache_limit';
    " 2>/dev/null

    # 优化查询缓存配置
    mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD -e "
        SET GLOBAL query_cache_type = 1;
        SET GLOBAL query_cache_size = 134217728;  -- 128MB
        SET GLOBAL query_cache_limit = 1048576;   -- 1MB
    " 2>/dev/null

    log "查询缓存配置优化完成"
}

# 优化连接配置
optimize_connections() {
    log "优化连接配置..."

    # 获取当前连接配置
    mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD -e "
        SHOW VARIABLES LIKE 'max_connections';
        SHOW VARIABLES LIKE 'max_connect_errors';
        SHOW VARIABLES LIKE 'connect_timeout';
    " 2>/dev/null

    # 优化连接配置
    mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD -e "
        SET GLOBAL max_connections = 1000;
        SET GLOBAL max_connect_errors = 100000;
        SET GLOBAL connect_timeout = 10;
    " 2>/dev/null

    log "连接配置优化完成"
}

# 优化慢查询日志
optimize_slow_query_log() {
    log "优化慢查询日志..."

    # 启用慢查询日志
    mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD -e "
        SET GLOBAL slow_query_log = 1;
        SET GLOBAL long_query_time = 2;
        SET GLOBAL log_queries_not_using_indexes = 1;
    " 2>/dev/null

    log "慢查询日志配置完成"
}

# 分析慢查询
analyze_slow_queries() {
    log "分析慢查询..."

    # 使用pt-query-digest分析慢查询日志
    if command -v pt-query-digest > /dev/null 2>&1; then
        SLOW_LOG_FILE="/var/log/mysql/slow.log"

        if [ -f "$SLOW_LOG_FILE" ]; then
            pt-query-digest $SLOW_LOG_FILE > /tmp/slow_query_analysis.txt
            log "慢查询分析完成: /tmp/slow_query_analysis.txt"
        else
            log "慢查询日志文件不存在: $SLOW_LOG_FILE"
        fi
    else
        log "pt-query-digest工具未安装"
    fi
}

# 优化表结构
optimize_tables() {
    log "优化表结构..."

    # 获取所有数据库
    DATABASES=$(mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -v "Database" | grep -v "information_schema" | grep -v "performance_schema" | grep -v "mysql" | grep -v "sys")

    for database in $DATABASES; do
        log "优化数据库: $database"

        # 获取数据库中的所有表
        TABLES=$(mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD -e "USE $database; SHOW TABLES;" | grep -v "Tables_in")

        for table in $TABLES; do
            log "优化表: $database.$table"

            # 优化表
            mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD -e "USE $database; OPTIMIZE TABLE $table;" 2>/dev/null

            # 分析表
            mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD -e "USE $database; ANALYZE TABLE $table;" 2>/dev/null
        done
    done

    log "表结构优化完成"
}

# 保存配置
save_config() {
    log "保存配置..."

    mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD -e "FLUSH PRIVILEGES;" 2>/dev/null

    log "配置保存完成"
}

# 主函数
main() {
    log "开始MySQL性能调优..."

    # 分析性能
    analyze_performance

    # 优化InnoDB配置
    optimize_innodb

    # 优化查询缓存
    optimize_query_cache

    # 优化连接配置
    optimize_connections

    # 优化慢查询日志
    optimize_slow_query_log

    # 分析慢查询
    analyze_slow_queries

    # 优化表结构
    optimize_tables

    # 保存配置
    save_config

    log "MySQL性能调优完成"
}

# 执行主函数
main "$@"

6. 故障处理脚本

6.1 故障诊断脚本

#!/bin/bash
# mysql_troubleshoot.sh - MySQL故障诊断脚本
# @author 运维实战

# 配置参数
MYSQL_HOST="localhost"
MYSQL_USER="root"
MYSQL_PASSWORD="password"
MYSQL_PORT="3306"
LOG_FILE="/var/log/mysql_troubleshoot.log"

# 日志函数
log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a $LOG_FILE
}

# 检查MySQL服务状态
check_mysql_service() {
    log "检查MySQL服务状态..."

    # 检查MySQL进程
    MYSQL_PID=$(ps aux | grep mysqld | grep -v grep | awk '{print $2}')

    if [ -n "$MYSQL_PID" ]; then
        log "MySQL进程运行中: PID $MYSQL_PID"
        return 0
    else
        log "MySQL进程未运行"
        return 1
    fi
}

# 检查MySQL连接
check_mysql_connection() {
    log "检查MySQL连接..."

    if mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SELECT 1;" > /dev/null 2>&1; then
        log "MySQL连接正常"
        return 0
    else
        log "MySQL连接失败"
        return 1
    fi
}

# 检查MySQL错误日志
check_error_log() {
    log "检查MySQL错误日志..."

    # 查找错误日志文件
    ERROR_LOG=$(mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW VARIABLES LIKE 'log_error';" 2>/dev/null | grep log_error | awk '{print $2}')

    if [ -f "$ERROR_LOG" ]; then
        log "错误日志文件: $ERROR_LOG"

        # 检查最近的错误
        tail -50 $ERROR_LOG | grep -i error
    else
        log "错误日志文件不存在: $ERROR_LOG"
    fi
}

# 检查MySQL配置
check_mysql_config() {
    log "检查MySQL配置..."

    # 检查配置文件
    CONFIG_FILES=("/etc/mysql/my.cnf" "/etc/my.cnf" "/usr/etc/my.cnf" "~/.my.cnf")

    for config_file in "${CONFIG_FILES[@]}"; do
        if [ -f "$config_file" ]; then
            log "配置文件存在: $config_file"

            # 检查关键配置
            grep -E "^(bind-address|port|datadir|log-error|pid-file)" $config_file
        fi
    done
}

# 检查系统资源
check_system_resources() {
    log "检查系统资源..."

    # 检查内存使用
    free -h

    # 检查磁盘空间
    df -h

    # 检查CPU使用
    top -bn1 | grep "Cpu(s)"

    # 检查I/O使用
    iostat -x 1 1
}

# 检查网络连接
check_network() {
    log "检查网络连接..."

    # 检查端口监听
    netstat -tlnp | grep $MYSQL_PORT

    # 检查防火墙
    iptables -L | grep $MYSQL_PORT
}

# 检查数据库状态
check_database_status() {
    log "检查数据库状态..."

    # 检查数据库列表
    mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" 2>/dev/null

    # 检查进程列表
    mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW PROCESSLIST;" 2>/dev/null

    # 检查状态信息
    mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW STATUS;" 2>/dev/null
}

# 修复常见问题
fix_common_issues() {
    log "修复常见问题..."

    # 检查并修复权限问题
    MYSQL_DATA_DIR=$(mysql -h $MYSQL_HOST -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW VARIABLES LIKE 'datadir';" 2>/dev/null | grep datadir | awk '{print $2}')

    if [ -d "$MYSQL_DATA_DIR" ]; then
        chown -R mysql:mysql $MYSQL_DATA_DIR
        chmod 755 $MYSQL_DATA_DIR
        log "修复数据目录权限"
    fi

    # 检查并修复日志目录权限
    MYSQL_LOG_DIR="/var/log/mysql"

    if [ -d "$MYSQL_LOG_DIR" ]; then
        chown -R mysql:mysql $MYSQL_LOG_DIR
        chmod 755 $MYSQL_LOG_DIR
        log "修复日志目录权限"
    fi
}

# 重启MySQL服务
restart_mysql_service() {
    log "重启MySQL服务..."

    # 停止服务
    systemctl stop mysql

    # 等待服务停止
    sleep 5

    # 启动服务
    systemctl start mysql

    # 检查服务状态
    if systemctl is-active --quiet mysql; then
        log "MySQL服务重启成功"
        return 0
    else
        log "MySQL服务重启失败"
        return 1
    fi
}

# 主函数
main() {
    log "开始MySQL故障诊断..."

    # 检查MySQL服务状态
    if ! check_mysql_service; then
        log "MySQL服务未运行，尝试启动..."
        systemctl start mysql
        sleep 5
    fi

    # 检查MySQL连接
    if ! check_mysql_connection; then
        log "MySQL连接失败，检查配置..."
        check_mysql_config
        check_error_log
        check_system_resources
        check_network

        # 修复常见问题
        fix_common_issues

        # 重启服务
        restart_mysql_service
    else
        log "MySQL服务运行正常"
        check_database_status
    fi

    log "MySQL故障诊断完成"
}

# 执行主函数
main "$@"

7. 总结

MySQL数据库迁移与运维是运维工作中的重要组成部分。通过本文的详细介绍，我们了解了：

数据迁移: 使用mysqldump进行数据迁移
备份恢复: 全量备份和增量备份策略
性能优化: 数据库性能调优和监控
故障处理: 常见故障的诊断和处理
运维自动化: 脚本化运维提高效率

通过合理的运维策略和工具，可以确保MySQL数据库的稳定运行和高性能。

运维实战要点:

数据迁移前做好备份，确保数据安全
定期进行性能分析和优化
建立完善的监控和告警机制
故障处理要有完整的诊断流程
运维自动化提高工作效率

代码注解说明:

日志函数: 统一日志格式，便于问题追踪
错误处理: 完善的错误检查和异常处理
配置管理: 灵活的配置参数管理
监控告警: 实时监控和告警机制
自动化运维: 脚本化运维提高效率