#!/bin/bash

# 配置區(qū)（用戶可修改）
MYSQL_VERSION="8.0"          # 支持5.7/8.0
ROOT_PASSWORD="Sec@Pass123!" # 自定義root密碼
APP_DB_NAME="webapp_db"      # 創(chuàng)建數(shù)據(jù)庫(kù)名
APP_DB_USER="webapp_user"    # 應(yīng)用用戶名
APP_DB_PASS="App@Pass123!"   # 應(yīng)用用戶密碼
LISTEN_ADDR="0.0.0.0"        # 監(jiān)聽(tīng)地址

# 顏色定義
RED='\033[0;31m'
GREEN='\033[0;32m'
NC='\033[0m'

# 異常處理
trap 'echo -e "${RED}\n腳本被中斷！正在清理...${NC}"; exit 1' INT TERM

check_command() {
    if ! command -v $1 &> /dev/null; then
        echo -e "${RED}錯(cuò)誤：未找到 $1 命令${NC}"
        exit 1
    fi
}

# 環(huán)境檢測(cè)
os_check() {
    if [ -f /etc/redhat-release ]; then
        OS="centos"
    elif [ -f /etc/lsb-release ]; then
        OS="ubuntu"
    else
        echo -e "${RED}不支持的操作系統(tǒng)${NC}"
        exit 1
    fi
}

install_mysql() {
    case $OS in
        centos)
            rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
            yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-6.noarch.rpm
            dnf config-manager --disable mysql*
            dnf config-manager --enable mysql${MYSQL_VERSION//./}-community
            dnf install -y mysql-community-server
            ;;
        ubuntu)
            wget -O /tmp/mysql.deb https://dev.mysql.com/get/mysql-apt-config_0.8.24-1_all.deb
            DEBIAN_FRONTEND=noninteractive dpkg -i /tmp/mysql.deb
            apt-get update
            apt-get install -y mysql-server
            ;;
    esac
}

configure_mysql() {
    cat > /etc/mysql/conf.d/custom.cnf <<EOF
[mysqld]
bind-address = $LISTEN_ADDR
default_authentication_plugin=mysql_native_password
transaction_isolation = READ-COMMITTED
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
max_connections = 2000
innodb_buffer_pool_size = 1G
EOF

    systemctl restart mysqld
    systemctl enable mysqld
}

secure_installation() {
    if [ "$MYSQL_VERSION" == "8.0" ]; then
        temp_pass=$(grep 'temporary password' /var/log/mysqld.log | awk '{print $NF}')
        mysql -uroot -p"$temp_pass" --connect-expired-password -e \
          "ALTER USER 'root'@'localhost' IDENTIFIED BY '$ROOT_PASSWORD'; FLUSH PRIVILEGES;"
    else
        mysql_secure_installation <<EOF
y
$ROOT_PASSWORD
$ROOT_PASSWORD
y
y
y
y
EOF
    fi

    mysql -uroot -p"$ROOT_PASSWORD" -e \
      "CREATE DATABASE $APP_DB_NAME;
       CREATE USER '$APP_DB_USER'@'%' IDENTIFIED BY '$APP_DB_PASS';
       GRANT ALL PRIVILEGES ON $APP_DB_NAME.* TO '$APP_DB_USER'@'%';
       FLUSH PRIVILEGES;"
}

# 主執(zhí)行流程
main() {
    check_command wget
    os_check
    install_mysql
    configure_mysql
    secure_installation
    echo -e "${GREEN}安裝完成！"
    echo -e "Root密碼：$ROOT_PASSWORD"
    echo -e "應(yīng)用數(shù)據(jù)庫(kù)：$APP_DB_NAME"
    echo -e "應(yīng)用用戶：$APP_DB_USER / $APP_DB_PASS${NC}"
}

main

chmod +x mysql_auto_install.sh

sudo ./mysql_auto_install.sh

# 連接測(cè)試
mysql -u root -p"Sec@Pass123!" -e "SHOW DATABASES;"

# 查看用戶權(quán)限
mysql -u root -p"Sec@Pass123!" -e "SELECT user,host FROM mysql.user;"

[mysqld]
innodb_buffer_pool_size = 1G       # 緩存池大小
max_connections = 2000             # 最大連接數(shù)
thread_cache_size = 100            # 線程緩存
query_cache_type = 1               # 查詢緩存
slow_query_log = 1                 # 開啟慢查詢?nèi)罩?

# 添加mysqld_exporter監(jiān)控
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.14.0/mysqld_exporter-0.14.0.linux-amd64.tar.gz
tar xvf mysqld_exporter-*.tar.gz
cp mysqld_exporter /usr/local/bin/

# 每日凌晨全量備份
0 2 * * * /usr/bin/mysqldump -uroot -pPASSWORD --all-databases | gzip > /backup/mysql_$(date +\%F).sql.gz

# 檢查防火墻規(guī)則
iptables -L -n | grep 3306

# 驗(yàn)證用戶權(quán)限
SHOW GRANTS FOR 'webapp_user'@'%';