主页 / user-66619

the_velour_fog's questions

Martin Hope
the_velour_fog
Asked: 2016-01-27 17:07:26 +0800 CST
  • 2

我写了一个 shell 脚本，它在 MySQL 服务器上创建了一个数据库和用户。用户和数据库将由 Web 应用程序使用，通常每次网站安装只执行一次此任务，但有时（例如测试脚本）脚本需要运行多次。
目前该脚本假定用户始终存在并且只是先发制人地删除，即

DELETE FROM mysql.user WHERE User = '${web_app_sql_user}';

我也尝试过，但是如果您相信drop user ${web_app_sql_user}@localhost;的话，如果用户不存在，那将失败！哈哈

脚本

#!/bin/bash
echo -e "Creating a SQL user and database for the webapp"

read -s -p "enter SQL root user password: " password

cat <<- EOF | mysql -v -uroot -p$password
    DROP DATABASE IF EXISTS \`${web_app_database}\`;
    CREATE DATABASE \`${web_app_database}\` CHARACTER SET utf8 COLLATE utf8_general_ci;

    -- Deal with possibility user already exists!
    DELETE FROM mysql.user WHERE User = '${web_app_sql_user}';
    -- drop user ${web_app_sql_user}@localhost;
    FLUSH PRIVILEGES;

    CREATE USER '${web_app_sql_user}'@localhost IDENTIFIED BY '${web_app_sql_user_password}';
    -- CREATE USER '${web_app_sql_user}'@localhost IDENTIFIED BY '${web_app_sql_user_password}';
    GRANT ALL ON ${web_app_database}.* TO '${web_app_sql_user}'@'localhost';

    GRANT USAGE ON * . * TO  '${web_app_sql_user}'@'localhost' IDENTIFIED BY  '${web_app_sql_user_password}' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
    GRANT ALL PRIVILEGES ON  \`${web_app_database}\` . * TO  '${web_app_sql_user}'@'localhost';

    FLUSH PRIVILEGES;
EOF

运行脚本时的结果

--------------
DROP DATABASE IF EXISTS `web_app_database`
--------------

--------------
CREATE DATABASE `web_app_database` CHARACTER SET utf8 COLLATE utf8_general_ci
--------------

--------------
DELETE FROM mysql.user WHERE User = 'web_app_user'
--------------

--------------
FLUSH PRIVILEGES
--------------

--------------
CREATE USER 'web_app_user'@localhost IDENTIFIED BY ''
--------------

ERROR 1396 (HY000) at line 17: Operation CREATE USER failed for 'web_app_user'@'localhost'

MySQL 有可能处理这些情况吗？

  • MySQL 服务器版本：5.5.46-0ubuntu0.14.04.2 (Ubuntu)

根据 michael-sqlbot 的回答更新了脚本

read -s -p "enter SQL root user password: " password
cat <<- EOF | mysql -v -uroot -p$password
    DROP DATABASE IF EXISTS \`${web_app_database}\`;
    CREATE DATABASE \`${web_app_database}\` CHARACTER SET utf8 COLLATE utf8_general_ci;
    GRANT ALL PRIVILEGES ON  \`${web_app_database}\` . * TO  '${web_app_sql_user}'@'localhost';
    FLUSH PRIVILEGES;
EOF
mysql