#!/bin/bash
# binlog_manager.sh - Binlog管理脚本

MYSQL_USER="root"
MYSQL_PASSWORD="password"
BINLOG_DIR="/data/mysql/binlog"

# 查看binlog列表
list_binlogs() {
    echo "=== Binlog列表 ==="
    mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW BINARY LOGS;"
}

# 查看当前binlog位置
show_master_status() {
    echo "=== Master状态 ==="
    mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW MASTER STATUS;"
}

# 清理旧的binlog
purge_old_logs() {
    local days=${1:-7}
    echo "清理${days}天前的binlog..."
    mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL ${days} DAY);"
}

# 备份binlog
backup_binlog() {
    local backup_dir="/backup/binlog/$(date +%Y%m%d)"
    mkdir -p "$backup_dir"
    
    echo "备份binlog到 $backup_dir..."
    cp -r "$BINLOG_DIR"/* "$backup_dir/" 2>/dev/null || true
    
    echo "Binlog备份完成"
}

# 主函数
main() {
    case "$1" in
        "list")
            list_binlogs
            ;;
        "status")
            show_master_status
            ;;
        "purge")
            purge_old_logs "${2:-7}"
            ;;
        "backup")
            backup_binlog
            ;;
        *)
            echo "用法: $0 {list|status|purge [天数]|backup}"
            ;;
    esac
}

main "$@"

#!/bin/bash
# slow_query_analyzer.sh - 慢查询分析脚本

MYSQL_USER="root"
MYSQL_PASSWORD="password"

# 分析慢查询
analyze_slow_queries() {
    echo "=== 慢查询分析 ==="
    
    # 从slowlog中提取查询
    mysqldumpslow -s t -t 10 /data/mysql/log/slow.log 2>/dev/null || true
    
    # 从performance_schema分析
    mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "
        SELECT 
            DIGEST AS '查询签名',
            COUNT_STAR AS '执行次数',
            SUM_TIMER_WAIT/1000000000000 AS '总耗时(秒)',
            AVG_TIMER_WAIT/1000000000000 AS '平均耗时(秒)',
            SUM_ROWS_EXAMINED AS '扫描行数',
            SUM_ROWS_SENT AS '返回行数'
        FROM performance_schema.events_statements_summary_by_digest
        WHERE DIGEST IS NOT NULL
        ORDER BY SUM_TIMER_WAIT DESC
        LIMIT 10;
    "
}

# 检查未使用索引的查询
check_missing_indexes() {
    echo "=== 未使用索引的查询 ==="
    mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "
        SELECT 
            OBJECT_SCHEMA AS '库名',
            OBJECT_NAME AS '表名',
            INDEX_NAME AS '索引名',
            COUNT_READ AS '读取次数'
        FROM performance_schema.table_io_waits_summary_by_index_usage
        WHERE INDEX_NAME IS NOT NULL
        AND COUNT_READ = 0
        AND OBJECT_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
        ORDER BY COUNT_READ ASC
        LIMIT 20;
    "
}

# 主函数
main() {
    analyze_slow_queries
    echo ""
    check_missing_indexes
}

main

#!/bin/bash
# mysql_replication_setup.sh - MySQL主从复制配置脚本

set -euo pipefail

# 配置参数
MASTER_HOST="192.168.1.100"
MASTER_PORT=3306
MASTER_USER="repl"
MASTER_PASSWORD="password"
SLAVE_HOST="192.168.1.101"
SLAVE_PORT=3306
BACKUP_DIR="/backup/mysql"

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*"
}

# 主库操作
setup_master() {
    log "配置主库..."
    
    mysql -u root -p"$MYSQL_ROOT_PASSWORD" << EOF
-- 创建复制用户
CREATE USER IF NOT EXISTS '$MASTER_USER'@'%' IDENTIFIED BY '$MASTER_PASSWORD';
GRANT REPLICATION SLAVE ON *.* TO '$MASTER_USER'@'%';
FLUSH PRIVILEGES;

-- 查看主库状态
SHOW MASTER STATUS;
EOF
}

# 备份主库数据
backup_master() {
    log "备份主库数据..."
    
    local backup_file="$BACKUP_DIR/mysql_backup_$(date +%Y%m%d_%H%M%S).sql.gz"
    
    mkdir -p "$BACKUP_DIR"
    
    mysqldump \
        --single-transaction \
        --master-data=2 \
        --flush-logs \
        --all-databases \
        -- gzip > "$backup_file"
    
    log "备份完成: $backup_file"
    echo "$backup_file"
}

# 从库操作
setup_slave() {
    local backup_file="$1"
    
    log "配置从库..."
    
    # 恢复数据
    log "恢复数据..."
    gunzip < "$backup_file" | mysql -u root -p"$MYSQL_ROOT_PASSWORD"
    
    # 获取binlog位置
    local log_file=$(grep "MASTER_LOG_FILE=" "$backup_file" | head -1 | sed "s/.*MASTER_LOG_FILE='\([^']*\)'.*/\1/")
    local log_pos=$(grep "MASTER_LOG_POS=" "$backup_file" | head -1 | sed "s/.*MASTER_LOG_POS=\([0-9]*\).*/\1/")
    
    log "Binlog位置: $log_file:$log_pos"
    
    # 配置复制
    mysql -u root -p"$MYSQL_ROOT_PASSWORD" << EOF
STOP SLAVE;

CHANGE MASTER TO
    MASTER_HOST='$MASTER_HOST',
    MASTER_PORT=$MASTER_PORT,
    MASTER_USER='$MASTER_USER',
    MASTER_PASSWORD='$MASTER_PASSWORD',
    MASTER_LOG_FILE='$log_file',
    MASTER_LOG_POS=$log_pos,
    GET_MASTER_PUBLIC_KEY=1;

START SLAVE;
EOF
    
    log "从库配置完成"
}

# 检查复制状态
check_replication() {
    log "检查复制状态..."
    
    mysql -u root -p"$MYSQL_ROOT_PASSWORD" -e "
        SHOW SLAVE STATUS\G
    " | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Relay_Log_Pos"
}

# 主函数
main() {
    case "$1" in
        "master")
            setup_master
            ;;
        "backup")
            backup_master
            ;;
        "slave")
            if [[ -z "$2" ]]; then
                echo "用法: $0 slave <备份文件>"
                exit 1
            fi
            setup_slave "$2"
            ;;
        "check")
            check_replication
            ;;
        *)
            echo "用法: $0 {master|backup|slave <备份文件>|check}"
            ;;
    esac
}

main "$@"

#!/bin/bash
# mysql_monitor.sh - MySQL性能监控脚本

MYSQL_USER="root"
MYSQL_PASSWORD="password"

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*"
}

# 连接状态
check_connections() {
    log "=== 连接状态 ==="
    mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "
        SHOW STATUS LIKE 'Threads_connected';
        SHOW STATUS LIKE 'Max_used_connections';
        SHOW VARIABLES LIKE 'max_connections';
        SHOW STATUS LIKE 'Connection_errors%';
    "
}

# 缓存命中率
check_cache_hit_ratio() {
    log "=== 缓存命中率 ==="
    mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "
        SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
        SHOW STATUS LIKE 'Innodb_buffer_pool_reads';
        SHOW STATUS LIKE 'Key_read_requests';
        SHOW STATUS LIKE 'Key_reads';
        SHOW STATUS LIKE 'Qcache_hits';
        SHOW STATUS LIKE 'Qcache_inserts';
    "
}

# 查询缓存（如果启用）
check_query_cache() {
    log "=== 查询缓存 ==="
    mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "
        SHOW STATUS LIKE 'Qcache%';
        SHOW VARIABLES LIKE 'query_cache%';
    "
}

# 复制状态
check_replication() {
    log "=== 复制状态 ==="
    mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "
        SHOW SLAVE STATUS\G
    " | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Relay_Log_Pos|Exec_Master_Log_Pos"
}

# 锁状态
check_locks() {
    log "=== 锁等待 ==="
    mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "
        SELECT 
            waiting_pid AS '等待进程',
            waiting_query AS '等待查询',
            blocking_pid AS '阻塞进程',
            blocking_query AS '阻塞查询'
        FROM performance_schema.events_statements_current
        WHERE waiting_time > 1000;
    "
}

# 主函数
main() {
    check_connections
    check_cache_hit_ratio
    check_replication
    check_locks
}

main

#!/bin/bash
# mysql_full_backup.sh - MySQL全量备份脚本

set -euo pipefail

MYSQL_USER="root"
MYSQL_PASSWORD="password"
BACKUP_DIR="/backup/mysql/full"
DATE=$(date +%Y%m%d_%H%M%S)

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*"
}

# 创建备份目录
mkdir -p "$BACKUP_DIR"

# 全量备份
full_backup() {
    local backup_file="$BACKUP_DIR/mysql_full_$DATE.sql.gz"
    
    log "开始全量备份..."
    
    mysqldump \
        --single-transaction \
        --master-data=2 \
        --flush-logs \
        --routines \
        --triggers \
        --events \
        --all-databases \
        -- gzip > "$backup_file"
    
    log "全量备份完成: $backup_file"
    ls -lh "$backup_file"
}

# 备份binlog
backup_binlog() {
    local binlog_backup_dir="$BACKUP_DIR/binlog_$DATE"
    mkdir -p "$binlog_backup_dir"
    
    log "备份binlog..."
    
    # 获取当前的binlog位置
    mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW BINARY LOGS;" | tail -n +2 | awk '{print $1}' | while read log; do
        mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW BINLOG EVENTS IN '$log'" | grep -v "Log_name" > "$binlog_backup_dir/$log.sql"
    done
    
    log "Binlog备份完成: $binlog_backup_dir"
}

# 清理旧备份
cleanup_old_backups() {
    local retention_days=${1:-7}
    
    log "清理${retention_days}天前的备份..."
    find "$BACKUP_DIR" -type d -mtime +$retention_days -exec rm -rf {} \; 2>/dev/null || true
    
    log "清理完成"
}

# 主函数
main() {
    full_backup
    backup_binlog
    cleanup_old_backups "${1:-7}"
    
    log "备份任务完成"
}

main "$@"

#!/bin/bash
# mysql_incremental_backup.sh - MySQL增量备份脚本

set -euo pipefail

MYSQL_USER="root"
MYSQL_PASSWORD="password"
BACKUP_DIR="/backup/mysql/incremental"
DATE=$(date +%Y%m%d)

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*"
}

mkdir -p "$BACKUP_DIR/$DATE"

# 获取上次备份的binlog位置
get_last_position() {
    local pos_file="$BACKUP_DIR/.last_position"
    
    if [[ -f "$pos_file" ]]; then
        cat "$pos_file"
    else
        mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW MASTER STATUS;" | tail -1 | awk '{print $1, $2}'
    fi
}

# 增量备份
incremental_backup() {
    local last_log=$(get_last_position | awk '{print $1}')
    local last_pos=$(get_last_position | awk '{print $2}')
    
    log "上次位置: $last_log:$last_pos"
    
    # 刷新日志创建新的binlog
    mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "FLUSH LOGS;"
    
    # 备份binlog
    mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW BINARY LOGS;" | tail -n +2 | awk '{print $1}' | while read log; do
        if [[ "$log" > "$last_log" ]] || [[ "$log" == "$last_log" ]]; then
            mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW BINLOG EVENTS IN '$log'" > "$BACKUP_DIR/$DATE/$log.sql"
        fi
    done
    
    # 保存当前位置
    mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW MASTER STATUS;" | tail -1 | awk '{print $1, $2}' > "$BACKUP_DIR/.last_position"
    
    log "增量备份完成: $BACKUP_DIR/$DATE"
}

# 主函数
main() {
    incremental_backup
}

main

#!/bin/bash
# mysql_restore.sh - MySQL数据恢复脚本

set -euo pipefail

MYSQL_USER="root"
MYSQL_PASSWORD="password"
BACKUP_DIR="/backup/mysql/full"

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*"
}

# 全量恢复
full_restore() {
    local backup_file="$1"
    
    log "开始全量恢复: $backup_file"
    
    # 停止复制
    mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "STOP SLAVE;"
    
    # 恢复数据
    gunzip < "$backup_file" | mysql -u$MYSQL_USER -p$MYSQL_PASSWORD
    
    log "全量恢复完成"
}

# Point-in-time恢复
pitr_restore() {
    local full_backup="$1"
    local target_time="$2"  # 格式: '2024-01-01 12:00:00'
    
    log "开始Point-in-time恢复，目标时间: $target_time"
    
    # 恢复全量备份
    full_restore "$full_backup"
    
    # 应用binlog到目标时间
    # 需要手动执行以下命令：
    # mysqlbinlog --stop-datetime="$target_time" /path/to/binlog | mysql
    
    log "Point-in-time恢复完成"
}

# 主函数
main() {
    case "$1" in
        "full")
            if [[ -z "$2" ]]; then
                echo "用法: $0 full <备份文件>"
                exit 1
            fi
            full_restore "$2"
            ;;
        "pitr")
            if [[ -z "$3" ]]; then
                echo "用法: $0 pitr <备份文件> <目标时间>"
                exit 1
            fi
            pitr_restore "$2" "$3"
            ;;
        *)
            echo "用法: $0 {full <备份文件>|pitr <备份文件> <目标时间>}"
            ;;
    esac
}

main "$@"

# 定时备份任务
0 2 * * * /opt/scripts/mysql_full_backup.sh
30 * * * * /opt/scripts/mysql_incremental_backup.sh