Mysql-Linux安装与备份

今天在检索MySQL时，看到了MySQL一种比较优秀的一种数据备份恢复方案，特此小计一下。

#!/bin/bash

#函数返回非零，程序立即终止
set -e

script_name=$(basename "$0")

# 函数：询问用户输入，附带默认值提示
# 使用示例：
# 用户输入的名字，没有默认值
#name=$(ask_for_input "Please enter your name")
#
# 用户输入的年龄，带有默认值
#age=$(ask_for_input "Please enter your age" "30")
user_input() {
    # $1 是提示信息
    # $2 是默认值
    local prompt="$1"
    local default_value="$2"
    local response

    # 打印提示信息，如果提供了默认值，则显示在提示之后
    if [ -n "$default_value" ]; then
        read -p "$prompt [$default_value]: " response
    else
        read -p "$prompt: " response
    fi

    # 如果用户没有输入任何内容，并且提供了默认值，则使用默认值
    if [ -z "$response" ] && [ -n "$default_value" ]; then
        response="$default_value"
    fi

    # 输出用户的输入或默认值
    echo "$response"
}

# 确保脚本以root权限运行
is_root() {
	if [ "$(id -u)" -ne 0 ]; then
		echo "This script must be run as root. Please use sudo."
		return 1 #需要Root用户权限
	fi
}


# 函数：下载MySQL
download_mysql() {
	# 配置
	MYSQL_VERSION=$(user_input "Please enter MySQL version" "8.0.36")
	DOWNLOAD_URL="https://dev.mysql.com/get/Downloads/MySQL-${MYSQL_VERSION%.*}/mysql-${MYSQL_VERSION}-linux-glibc2.12-x86_64.tar.gz"

    echo "Downloading MySQL version $MYSQL_VERSION..."
    wget "$DOWNLOAD_URL" -O /tmp/mysql-${MYSQL_VERSION}.tar.gz
    if [ $? -ne 0 ]; then
        echo "Failed to download MySQL."
        return -1 #MySQL下载失败
    fi
}

# 函数：安装MySQL
install_mysql() {

	download_mysql

    echo "Installing MySQL version $MYSQL_VERSION..."

    # 解压tar.gz包
    tar -xzf /tmp/mysql-${MYSQL_VERSION}.tar.gz -C /usr/local/src

    # 移动到合适的目录
    mv /usr/local/src/mysql-${MYSQL_VERSION} /usr/local/mysql

    # 创建软链接
    ln -s /usr/local/mysql/bin/* /usr/bin/

    # 配置环境变量（示例：添加到当前用户的.bashrc）
    echo 'export PATH=$PATH:/usr/local/mysql/bin' >> ~/.bashrc
    source ~/.bashrc

    # 初始化MySQL
    /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

    # 配置MySQL服务器
    /usr/local/mysql/bin/mysqld --daemonize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/mysql.pid

    # 安全配置
    /usr/local/mysql/bin/mysql_secure_installation

    echo "MySQL installation successful."
}

# 函数：更新MySQL
update_mysql() {
    echo "Updating MySQL..."

    # 停止MySQL服务
    service mysql stop

    # 重复安装步骤
    install_mysql

    # 启动MySQL服务
    service mysql start

    echo "MySQL updated successfully."
}

ENV_PATH="~/.mysql.env"

set_env() {
	# 配置
	MYSQL_USER=$(user_input "Please enter your user" "mysql")
	MYSQL_PASS=$(user_input "Please enter your password" "root")
	RESTORE_DB=$(user_input "Please enter default database" "")
	BACKUP_BASE_DIR=$(user_input "Please enter backup base dir" "/backup")
	FULL_BACKUP_DIR="$BACKUP_BASE_DIR/full"
	INCREMENTAL_BACKUP_DIR="$BACKUP_BASE_DIR/incremental"
	LAST_POSITION_FILE="$INCREMENTAL_BACKUP_DIR/last_position.txt"
	save_env > $ENV_PATH
}

#保存环境变量
save_env() {
	cat <<EOF
MYSQL_USER="$MYSQL_USER"
MYSQL_PASS="$MYSQL_PASS"
RESTORE_DB="$RESTORE_DB"
BACKUP_BASE_DIR="$BACKUP_BASE_DIR"
FULL_BACKUP_DIR="$FULL_BACKUP_DIR"
INCREMENTAL_BACKUP_DIR="$INCREMENTAL_BACKUP_DIR"
LAST_POSITION_FILE="$LAST_POSITION_FILE"
EOF
}

#加载.env文件中的环境变量
reload_env () {
	# 加载.env文件中的环境变量
	if [ -f $ENV_PATH ]; then
		source ./.env
	else
		set_env
	fi
}

# 创建备份目录
create_backup_directories() {
    echo "Creating backup directories if not exist..."
    mkdir -p "$FULL_BACKUP_DIR"
    mkdir -p "$INCREMENTAL_BACKUP_DIR"
}

# 全量备份函数
full_backup() {
    create_backup_directories
    local backup_filename="$RESTORE_DB_$(date +%Y%m%d%H%M%S).sql.gz"
    echo "Starting full backup..."
    mysqldump -u "$MYSQL_USER" -p"$MYSQL_PASS" --all-databases --single-transaction | gzip > "$FULL_BACKUP_DIR/$backup_filename"
    if [ $? -eq 0 ]; then
        echo "Full backup successful: $backup_filename"
    else
        echo "Full backup failed."
        exit 1
    fi
}

# 增量备份函数
incremental_backup() {
    create_backup_directories
    local current_binlog_file
    local current_binlog_pos

    if [ -f "$LAST_POSITION_FILE" ]; then
        current_binlog_file=$(head -n 1 "$LAST_POSITION_FILE")
        current_binlog_pos=$(tail -n 1 "$LAST_POSITION_FILE")
    else
        current_binlog_file=$(mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "SHOW MASTER STATUS;" | grep -m 1 file | awk '{print $2}')
        current_binlog_pos=4
    fi

    echo "Starting incremental backup from position $current_binlog_pos in file $current_binlog_file..."
    mysqlbinlog --start-position="$current_binlog_pos" "$current_binlog_file" | mysql -u "$MYSQL_USER" -p"$MYSQL_PASS"

    local new_binlog_pos=$(mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "SHOW MASTER STATUS\G" | grep -m 1 'Position' | awk '{print $3}')
    echo "$current_binlog_file:$new_binlog_pos" > "$LAST_POSITION_FILE"
}

# 数据恢复函数
restore_data() {
    local restore_time=$1
    local full_backup_file
    local incremental_files=($(ls -t "$FULL_BACKUP_DIR"/*.sql.gz))

    if [ -z "$restore_time" ]; then
        echo "Error: No restore time provided."
        exit 1
    fi

    full_backup_file=${incremental_files[0]}
    if [ ! -f "$FULL_BACKUP_DIR/$full_backup_file" ]; then
        echo "Error: Full backup file does not exist."
        exit 1
    fi

    echo "Restoring from full backup: $full_backup_file"
    gzip -cd "$FULL_BACKUP_DIR/$full_backup_file" | mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" "$RESTORE_DB"

    echo "Applying incremental backups up to $restore_time..."
    local last_file=""
    for file in $(ls -t "$INCREMENTAL_BACKUP_DIR"/*); do
        file_name=$(basename "$file")
        if [[ $file_name < "$restore_time" ]]; then
            mysqlbinlog "$file" | mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" "$RESTORE_DB"
            last_file="$file"
        else
            break
        fi
    done

    if [ -n "$last_file" ]; then
        echo "Updating last binlog position to $(basename "$last_file")."
        echo "$(basename "$last_file")" > "$LAST_POSITION_FILE"
    fi

    echo "Restore complete."
}

# 函数：检查是否已设置定时任务
check_cron_jobs() {
	script_name
    local full_cron_exists=$(crontab -l | grep -F "$script_name full" | wc -l)
    local incremental_cron_exists=$(crontab -l | grep -F "$script_name incremental" | wc -l)

    if [ "$full_cron_exists" -gt 0 ] && [ "$incremental_cron_exists" -gt 0 ]; then
        echo "Cron jobs for full and incremental backups already exist."
        return 0
    else
        return 1
    fi
}

# 函数：设置定时任务
setup_cron_jobs() {
    # 询问用户备份频率
    local full_freq=$(ask_for_input "Please enter full backup frequency (days, e.g., '1' for daily)" "1")
    local incremental_freq=$(ask_for_input "Please enter incremental backup frequency (hours, e.g., '1' for hourly)" "1")

    # 删除现有的备份相关的cron任务
    crontab -l | grep -v "$0" | crontab -

    # 添加新的备份任务
    # 假设full_backup.sh和incremental_backup.sh是备份脚本的文件名
    (crontab -l 2>/dev/null; echo "0 */${full_freq} * * * $0 full") | crontab -
    (crontab -l 2>/dev/null; echo "0 * * * * $0 incremental") | crontab -

    echo "Cron jobs for full and incremental backups have been set."
}

# 
init_mysql() {
    # 保存环境变量到.mysql.env
    save_env > "$ENV_PATH"

    # 设置定时任务
    if ! check_cron_jobs; then
        setup_cron_jobs
    else
        echo "Existing cron jobs found. No changes made."
    fi

}

# 主函数，根据命令行参数调用相应的备份或恢复函数
main() {
    if [ $# -eq 0 ]; then
        echo "Usage: $0 {install|update|initialize|full|incremental|restore}"
        exit 1
    fi
	
	reload_env

    case "$1" in
		install)
            install_mysql
            ;;
		update)
            update_mysql
            ;;
		initialize)
            init_mysql
            ;;
        full)
            full_backup
            ;;
        incremental)
            if [ -z "$2" ]; then
                echo "Cron job mode: running incremental backup."
                incremental_backup
            else
                echo "Error: Unexpected argument for incremental."
                exit 1
            fi
            ;;
        restore)
            if [ -z "$2" ]; then
                echo "Error: Restore time not provided."
                exit 1
            else
                restore_data "$2"
            fi
            ;;
        *)
            echo "Error: Invalid command. Use 'full', 'incremental', or 'restore'."
            exit 1
            ;;
    esac
}

# 执行主函数，并传递所有命令行参数
main "$@"

注: 上述脚本仍未测试，请使用需谨慎！谨慎！再谨慎！
有问题请留言说明，谢谢了~