mysql 创建用户，修改密码等脚本
#!/bin/bash
set -e
# 颜色输出
green() { echo -e “\033[32m$1\033[0m”; }
yellow() { echo -e “\033[33m$1\033[0m”; }
red() { echo -e “\033[31m$1\033[0m”; }
blue() { echo -e “\033[34m$1\033[0m”; }
# 检查MySQL状态
check_mysql_status() {
echo “检查MySQL状态…”
if command -v mysql >/dev/null 2>&1; then
green “✓ MySQL客户端已安装”
mysql –version 2>/dev/null || mysql -V 2>/dev/null
return 0
else
red “✗ MySQL客户端未找到”
exit 1
fi
}
# 获取root密码
get_root_password() {
if [ -z “$MYSQL_ROOT_PASSWORD” ]; then
# 先尝试无密码连接
if mysql -u root -e “SELECT 1;” 2>/dev/null; then
yellow “✓ 使用无密码连接”
MYSQL_ROOT_PASSWORD=””
return 0
fi
# 如果需要密码
echo “请输入MySQL root密码: ”
read MYSQL_ROOT_PASSWORD
fi
}
# 执行MySQL命令
execute_mysql() {
if [ -z “$MYSQL_ROOT_PASSWORD” ]; then
mysql -u root -e “$1″ 2>/dev/null
else
mysql -u root -p”${MYSQL_ROOT_PASSWORD}” -e “$1” 2>/dev/null
fi
}
# 测试MySQL连接
test_mysql_connection() {
if execute_mysql “SELECT 1;” > /dev/null 2>&1; then
return 0
else
return 1
fi
}
# 确保MySQL连接
ensure_mysql_connection() {
check_mysql_status
get_root_password
if ! test_mysql_connection; then
red “无法连接到MySQL！”
echo “请检查：”
echo “1. MySQL服务是否运行: sudo systemctl start mysql”
echo “2. root密码是否正确”
exit 1
fi
}
# 生成随机密码
generate_password() {
# 使用多种方法生成密码
if command -v openssl >/dev/null 2>&1; then
openssl rand -base64 12 | tr -d ‘/+=’ | cut -c1-16
else
date +%s | sha256sum | base64 | head -c 16
fi
}
# 测试用户连接
test_user_connection() {
local user=$1
local password=$2
local host=$3
if [ “$host” = “localhost” ]; then
# 测试localhost连接
if mysql -u “$user” -p”$password” -e “SELECT 1;” 2>/dev/null; then
return 0
else
return 1
fi
else
# 测试远程主机连接
if mysql -u “$user” -p”$password” -h 127.0.0.1 -e “SELECT 1;” 2>/dev/null; then
return 0
else
return 1
fi
fi
}
# 1. 创建数据库和用户（修复版）
create_database_and_user() {
ensure_mysql_connection
yellow “创建数据库和用户…”
echo
echo “请输入用户名: ”
read DB_USER
echo “请输入数据库名: ”
read DB_NAME
echo “请输入用户密码 (留空自动生成): ”
read USER_PASSWORD_INPUT
if [ -z “$USER_PASSWORD_INPUT” ]; then
DB_USER_PASSWORD=$(generate_password)
yellow “自动生成密码: $DB_USER_PASSWORD”
else
DB_USER_PASSWORD=”$USER_PASSWORD_INPUT”
fi
echo “允许访问的主机 (% 表示任意主机，推荐使用localhost): ”
read ALLOWED_HOSTS
if [ -z “$ALLOWED_HOSTS” ]; then
ALLOWED_HOSTS=”localhost”
fi
# 创建数据库
blue “创建数据库: $DB_NAME”
if execute_mysql “CREATE DATABASE IF NOT EXISTS $DB_NAME CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;”; then
green “✓ 数据库创建成功”
else
red “✗ 数据库创建失败”
return
fi
# 删除已存在的用户（避免冲突）
execute_mysql “DROP USER IF EXISTS ‘$DB_USER’@’$ALLOWED_HOSTS’;” 2>/dev/null || true
# 创建用户
blue “创建用户: $DB_USER@$ALLOWED_HOSTS”
if execute_mysql “CREATE USER ‘$DB_USER’@’$ALLOWED_HOSTS’ IDENTIFIED BY ‘$DB_USER_PASSWORD’;”; then
green “✓ 用户创建成功”
else
red “✗ 用户创建失败”
return
fi
# 授予权限
blue “授予用户 $DB_USER 对数据库 $DB_NAME 的所有权限”
if execute_mysql “GRANT ALL PRIVILEGES ON $DB_NAME.* TO ‘$DB_USER’@’$ALLOWED_HOSTS’;”; then
green “✓ 权限授予成功”
else
red “✗ 权限授予失败”
return
fi
# 授予全局权限（确保可以连接）
execute_mysql “GRANT PROCESS ON *.* TO ‘$DB_USER’@’$ALLOWED_HOSTS’;”
execute_mysql “FLUSH PRIVILEGES;”
green “✓ 数据库和用户创建完成！”
echo “==========================================”
green “用户名: $DB_USER”
green “密码: $DB_USER_PASSWORD”
green “数据库: $DB_NAME”
green “允许访问: $ALLOWED_HOSTS”
echo “==========================================”
# 测试用户连接
echo
yellow “测试用户连接…”
if test_user_connection “$DB_USER” “$DB_USER_PASSWORD” “$ALLOWED_HOSTS”; then
green “✓ 用户连接测试成功！”
# 显示正确的连接命令
echo
blue “连接命令:”
if [ “$ALLOWED_HOSTS” = “localhost” ]; then
echo “mysql -u $DB_USER -p$DB_USER_PASSWORD $DB_NAME”
else
echo “mysql -u $DB_USER -p$DB_USER_PASSWORD -h 127.0.0.1 $DB_NAME”
fi
else
red “✗ 用户连接测试失败”
yellow “可能的原因和解决方案:”
echo “1. 如果使用 % 主机，请使用: mysql -u $DB_USER -p$DB_USER_PASSWORD -h 127.0.0.1 $DB_NAME”
echo “2. 检查MySQL认证插件: sudo mysql -u root -e \”SELECT user, host, plugin FROM mysql.user WHERE user=’$DB_USER’;\””
echo “3. 尝试创建localhost用户: sudo mysql -u root -e \”CREATE USER ‘$DB_USER’@’localhost’ IDENTIFIED BY ‘$DB_USER_PASSWORD’; GRANT ALL ON $DB_NAME.* TO ‘$DB_USER’@’localhost’;\””
fi
}
# 2. 查看所有数据库
show_databases() {
ensure_mysql_connection
yellow “所有数据库列表:”
execute_mysql “SHOW DATABASES;”
}
# 3. 查看所有用户
show_users() {
ensure_mysql_connection
yellow “所有用户列表:”
execute_mysql “SELECT User, Host, plugin FROM mysql.user WHERE User NOT IN (‘mysql.sys’, ‘mysql.session’, ‘mysql.infoschema’);”
}
# 4. 创建独立数据库
create_database_only() {
ensure_mysql_connection
yellow “创建独立数据库…”
echo
echo “请输入数据库名: ”
read DB_NAME
blue “创建数据库: $DB_NAME”
execute_mysql “CREATE DATABASE IF NOT EXISTS $DB_NAME CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;”
green “✓ 数据库创建完成: $DB_NAME”
}
# 5. 修改用户密码
change_user_password() {
ensure_mysql_connection
yellow “修改用户密码…”
echo
# 显示用户列表
blue “用户列表:”
execute_mysql “SELECT User, Host FROM mysql.user WHERE User NOT IN (‘mysql.sys’, ‘mysql.session’, ‘mysql.infoschema’);”
echo
echo “请输入要修改密码的用户名: ”
read TARGET_USER
echo “请输入主机 (默认localhost): ”
read TARGET_HOST
if [ -z “$TARGET_HOST” ]; then
TARGET_HOST=”localhost”
fi
echo “请输入新密码: ”
read NEW_PASSWORD
echo “确认新密码: ”
read CONFIRM_PASSWORD
if [ “$NEW_PASSWORD” != “$CONFIRM_PASSWORD” ]; then
red “密码不匹配！”
return
fi
execute_mysql “ALTER USER ‘$TARGET_USER’@’$TARGET_HOST’ IDENTIFIED BY ‘$NEW_PASSWORD’;”
execute_mysql “FLUSH PRIVILEGES;”
green “用户 $TARGET_USER@$TARGET_HOST 密码修改成功！”
}
# 6. 修复用户连接问题
fix_user_connection() {
ensure_mysql_connection
yellow “修复用户连接问题…”
echo
echo “请输入用户名: ”
read FIX_USER
blue “用户当前状态:”
execute_mysql “SELECT User, Host, plugin, authentication_string FROM mysql.user WHERE User=’$FIX_USER’;”
echo
echo “请选择修复方案:”
echo “1. 创建localhost用户（推荐）”
echo “2. 重置用户密码”
echo “3. 修改认证插件为mysql_native_password”
echo “4. 测试用户连接”
read -p “请选择 [1-4]: ” fix_choice
case $fix_choice in
1)
echo “请输入密码: ”
read PASSWORD
execute_mysql “CREATE USER IF NOT EXISTS ‘$FIX_USER’@’localhost’ IDENTIFIED BY ‘$PASSWORD’;”
execute_mysql “GRANT ALL PRIVILEGES ON *.* TO ‘$FIX_USER’@’localhost’;”
execute_mysql “FLUSH PRIVILEGES;”
green “✓ 已创建 $FIX_USER@localhost”
green “连接命令: mysql -u $FIX_USER -p$PASSWORD”
;;
2)
echo “请输入新密码: ”
read NEW_PASS
execute_mysql “ALTER USER ‘$FIX_USER’@’%’ IDENTIFIED BY ‘$NEW_PASS’;”
execute_mysql “ALTER USER ‘$FIX_USER’@’localhost’ IDENTIFIED BY ‘$NEW_PASS’;”
execute_mysql “FLUSH PRIVILEGES;”
green “✓ 密码已重置”
;;
3)
execute_mysql “ALTER USER ‘$FIX_USER’@’%’ IDENTIFIED WITH mysql_native_password BY ”;”
execute_mysql “ALTER USER ‘$FIX_USER’@’localhost’ IDENTIFIED WITH mysql_native_password BY ”;”
execute_mysql “FLUSH PRIVILEGES;”
green “✓ 认证插件已修改”
;;
4)
echo “请输入密码: ”
read TEST_PASS
if mysql -u “$FIX_USER” -p”$TEST_PASS” -e “SELECT 1;” 2>/dev/null; then
green “✓ localhost连接成功”
elif mysql -u “$FIX_USER” -p”$TEST_PASS” -h 127.0.0.1 -e “SELECT 1;” 2>/dev/null; then
green “✓ 127.0.0.1连接成功”
else
red “✗ 连接失败”
fi
;;
*)
red “无效选择”
;;
esac
}
# 主菜单
show_menu() {
clear
green “=====================================”
green ” MySQL 数据库管理工具”
green “=====================================”
green “1. 创建数据库和用户”
green “2. 查看所有数据库”
green “3. 查看所有用户”
green “4. 创建独立数据库”
green “5. 修改用户密码”
green “6. 修复用户连接问题”
green “0. 退出”
green “=====================================”
echo
}
main() {
# 检查MySQL状态
check_mysql_status
while true; do
show_menu
echo “请选择操作 [0-6]: ”
read choice
case $choice in
1) create_database_and_user ;;
2) show_databases ;;
3) show_users ;;
4) create_database_only ;;
5) change_user_password ;;
6) fix_user_connection ;;
0)
green “再见！”
exit 0
;;
*)
red “无效选择！”
;;
esac
echo
echo “按回车键继续…”
read dummy
done
}
# 启动主菜单
main
