MySQL

#!/usr/bin/env bash
set -euo pipefail

# =========================================================
# Debian 12 一键安装 default-mysql-server（MariaDB）
# 默认行为：
# 1. 使用 Debian 自带仓库安装
# 2. 默认开启远程访问
# 3. 默认创建远程 root
# 4. 默认远程 root 密码：DigVPS.COM
# 5. 本机 root 可直接 mysql 登录
# 6. 可选：当前 sudo 用户本地直接 mysql 登录
# =========================================================

# ===== 默认参数 =====
ENABLE_REMOTE_ROOT="${ENABLE_REMOTE_ROOT:-1}"
REMOTE_ROOT_HOST="${REMOTE_ROOT_HOST:-%}"
REMOTE_ROOT_PASSWORD="${REMOTE_ROOT_PASSWORD:-DigVPS.COM}"
MYSQL_PORT="${MYSQL_PORT:-3306}"
BIND_ADDRESS="${BIND_ADDRESS:-0.0.0.0}"
CREATE_LOCAL_SUDO_SOCKET_USER="${CREATE_LOCAL_SUDO_SOCKET_USER:-1}"

MYSQL_CNF="/etc/mysql/mariadb.conf.d/50-server.cnf"
TUNING_CNF="/etc/mysql/mariadb.conf.d/99-local-init.cnf"

# ===== 基础检查 =====
if [[ "${EUID}" -ne 0 ]]; then
  echo "请用 root 运行，或 sudo bash $0"
  exit 1
fi

source /etc/os-release

if [[ "${ID:-}" != "debian" || "${VERSION_ID:-}" != "12" ]]; then
  echo "本脚本仅支持 Debian 12，当前系统：${PRETTY_NAME:-unknown}"
  exit 1
fi

REAL_USER="${SUDO_USER:-}"

export DEBIAN_FRONTEND=noninteractive

echo "==> 清理之前残留的 Oracle MySQL APT 仓库配置（如果有）"
rm -f /etc/apt/sources.list.d/mysql.list
rm -f /usr/share/keyrings/mysql.gpg

echo "==> 更新 APT"
apt-get update

echo "==> 安装 default-mysql-server"
apt-get install -y default-mysql-server

echo "==> 启动并设置开机自启"
systemctl enable --now mariadb

echo "==> 等待数据库就绪"
for i in {1..30}; do
  if mysqladmin ping >/dev/null 2>&1; then
    break
  fi
  sleep 1
done

echo "==> 测试 root 本地登录"
if ! mysql -uroot -e "SELECT VERSION();" >/dev/null 2>&1; then
  echo "本地 root 无法登录，请检查服务："
  echo "systemctl status mariadb --no-pager"
  exit 1
fi

echo "==> 配置监听地址和端口"
cp -a "${MYSQL_CNF}" "${MYSQL_CNF}.bak.$(date +%s)" || true

if grep -Eq '^[#[:space:]]*bind-address[[:space:]]*=' "${MYSQL_CNF}"; then
  sed -ri "s/^[#[:space:]]*bind-address[[:space:]]*=.*/bind-address = ${BIND_ADDRESS}/" "${MYSQL_CNF}"
else
  printf "\n[mysqld]\nbind-address = %s\n" "${BIND_ADDRESS}" >> "${MYSQL_CNF}"
fi

if grep -Eq '^[#[:space:]]*port[[:space:]]*=' "${MYSQL_CNF}"; then
  sed -ri "s/^[#[:space:]]*port[[:space:]]*=.*/port = ${MYSQL_PORT}/" "${MYSQL_CNF}"
else
  printf "port = %s\n" "${MYSQL_PORT}" >> "${MYSQL_CNF}"
fi

echo "==> 写入基础配置"
cat > "${TUNING_CNF}" <<'EOF'
[mysqld]

skip_name_resolve = 1

character-set-server = utf8mb4
collation-server = utf8mb4_general_ci

server_id = 1
log_bin = mysql-bin
binlog_format = ROW

expire_logs_days = 7
max_binlog_size = 1000M
sync_binlog = 1

innodb_file_per_table = 1
EOF

echo "==> 重启 MariaDB"
systemctl restart mariadb
sleep 2

echo "==> 确保 root 本地仍可登录"
mysql -uroot -e "SELECT USER(), CURRENT_USER(), VERSION();"

if [[ "${CREATE_LOCAL_SUDO_SOCKET_USER}" == "1" && -n "${REAL_USER}" && "${REAL_USER}" != "root" ]]; then
  echo "==> 为 sudo 用户创建本地免密账号：${REAL_USER}"
  mysql -uroot <<SQL
CREATE USER IF NOT EXISTS '${REAL_USER}'@'localhost' IDENTIFIED VIA unix_socket;
GRANT ALL PRIVILEGES ON *.* TO '${REAL_USER}'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
SQL
fi

if [[ "${ENABLE_REMOTE_ROOT}" == "1" ]]; then
  echo "==> 开启远程 root"
  mysql -uroot <<SQL
CREATE USER IF NOT EXISTS 'root'@'${REMOTE_ROOT_HOST}' IDENTIFIED BY '${REMOTE_ROOT_PASSWORD}';
ALTER USER 'root'@'${REMOTE_ROOT_HOST}' IDENTIFIED BY '${REMOTE_ROOT_PASSWORD}';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'${REMOTE_ROOT_HOST}' WITH GRANT OPTION;
FLUSH PRIVILEGES;
SQL
fi

echo "==> 最终检查"
mysql -uroot -e "SELECT VERSION();"
mysql -uroot -e "SELECT User,Host,plugin FROM mysql.user ORDER BY User,Host;"

cat <<EOF

================ 安装完成 ================

已安装：
  default-mysql-server（Debian 12 实际为 MariaDB）

本机 root 登录：
  mysql
或：
  sudo mysql

服务名：
  systemctl status mariadb

远程访问：
  已默认开启

监听地址：
  ${BIND_ADDRESS}

端口：
  ${MYSQL_PORT}

远程 root：
  root@${REMOTE_ROOT_HOST}

远程 root 默认密码：
  ${REMOTE_ROOT_PASSWORD}

远程登录命令：
  mysql -h 服务器IP -P ${MYSQL_PORT} -uroot -p

注意：
1. 还需要放行防火墙/安全组的 ${MYSQL_PORT}
2. 当前默认密码是公开口令，装完强烈建议立刻修改
3. 建议把 REMOTE_ROOT_HOST 改成固定管理 IP，不要长期使用 %

==========================================
EOF

#!/usr/bin/env bash
set -e

ROOT_PASSWORD=${ROOT_PASSWORD:-DigVPS.COM}
REPL_USER=${REPL_USER:-repl}
REPL_PASSWORD=${REPL_PASSWORD:-DigVPS-Repl}

APT_PKG=mysql-apt-config_0.8.36-1_all.deb
APT_URL=https://dev.mysql.com/get/${APT_PKG}

CONF=/etc/mysql/mysql.conf.d/99-digvps.cnf

echo "=============================="
echo " DigVPS MySQL Installer"
echo "=============================="

if [ "$EUID" -ne 0 ]; then
  echo "Please run as root"
  exit
fi

echo "Installing dependencies..."
apt update
apt install -y wget gnupg lsb-release debconf-utils ca-certificates

echo "Downloading MySQL APT repo..."
wget -qO /tmp/${APT_PKG} ${APT_URL}

echo "Configuring MySQL repository..."

cat <<EOF | debconf-set-selections
mysql-apt-config mysql-apt-config/select-server select mysql-innovation
mysql-apt-config mysql-apt-config/select-product select Ok
EOF

dpkg -i /tmp/${APT_PKG} || apt -f install -y

echo "Installing MySQL..."
apt update
DEBIAN_FRONTEND=noninteractive apt install -y mysql-server

SERVER_ID=$((RANDOM % 10000 + 1))

echo "Writing MySQL config..."

cat > ${CONF} <<EOF
[mysqld]

bind-address=0.0.0.0

server-id=${SERVER_ID}

log_bin=mysql-bin
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
binlog_format=ROW
binlog_row_image=FULL
relay_log=relay-bin

gtid_mode=ON
enforce_gtid_consistency=ON

slow_query_log=ON
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=2

skip_name_resolve

innodb_buffer_pool_size=512M
innodb_redo_log_capacity=512M
EOF

echo "Validating MySQL config..."
mysqld --validate-config

echo "Starting MySQL..."
systemctl enable mysql
systemctl restart mysql

echo "Waiting MySQL ready..."

for i in {1..30}; do
  if mysqladmin ping >/dev/null 2>&1; then
    break
  fi
  sleep 1
done

echo "Configuring users..."

mysql --protocol=socket -uroot <<EOF

ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;

CREATE USER IF NOT EXISTS 'root'@'%' IDENTIFIED BY '${ROOT_PASSWORD}';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

CREATE USER IF NOT EXISTS '${REPL_USER}'@'%' IDENTIFIED BY '${REPL_PASSWORD}';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO '${REPL_USER}'@'%';

FLUSH PRIVILEGES;

EOF

echo
echo "=============================="
echo " MySQL Installed Successfully"
echo "=============================="

mysql -V

echo
echo "Local login:"
echo "mysql"
echo
echo "Remote login:"
echo "mysql -h SERVER_IP -u root -p"
echo "Password: ${ROOT_PASSWORD}"

echo
echo "Replication user:"
echo "User: ${REPL_USER}"
echo "Password: ${REPL_PASSWORD}"

echo
echo "Master status:"
mysql -e "SHOW BINARY LOG STATUS" -E || true