MySQL 在bash脚本中创建mysql数据库和用户
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33470753/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-31 21:22:08 来源:igfitidea点击:
Create mysql database and user in bash script
提问by David Rahrer
This seems like it should be simple and I swear this code has worked for months but it's not working now. I'm sure I'm just overly tired but I would appreciate a knowing nudge.
这看起来应该很简单，我发誓这段代码已经运行了几个月，但现在不起作用了。我敢肯定我只是太累了，但我会很感激的。
# create random password
PASSWDDB="$(openssl rand -base64 12)"
# replace "-" with "_" for database username
MAINDB=${USER_NAME//[^a-zA-Z0-9]/_}
# create database and user
mysql -e "CREATE DATABASE $MAINDB"
mysql -e "GRANT ALL PRIVILEGES ON $MAINDB.* TO $MAINDB@localhost IDENTIFIED BY '$PASSWDDB!'"
MAINDB is supplied previously in the script. What I end up with is a database, a user, and proper permissions and the user has a password - it's just not the password defined in $PASSWORDDB.
MAINDB 先前在脚本中提供。我最终得到的是一个数据库、一个用户和适当的权限，并且用户有一个密码——这不是 $PASSWORDDB 中定义的密码。
MariaDB 10.0.22 Ubuntu 14.04
MariaDB 10.0.22 Ubuntu 14.04
回答by Saad Ismail
This is what I use: https://raw.githubusercontent.com/saadismail/useful-bash-scripts/master/db.sh
这是我使用的：https: //raw.githubusercontent.com/saadismail/useful-bash-scripts/master/db.sh
In your case you can use this:
在你的情况下，你可以使用这个：
# create random password
PASSWDDB="$(openssl rand -base64 12)"
# replace "-" with "_" for database username
MAINDB=${USER_NAME//[^a-zA-Z0-9]/_}
# If /root/.my.cnf exists then it won't ask for root password
if [ -f /root/.my.cnf ]; then
mysql -e "CREATE DATABASE ${MAINDB} /*\!40100 DEFAULT CHARACTER SET utf8 */;"
mysql -e "CREATE USER ${MAINDB}@localhost IDENTIFIED BY '${PASSWDDB}';"
mysql -e "GRANT ALL PRIVILEGES ON ${MAINDB}.* TO '${MAINDB}'@'localhost';"
mysql -e "FLUSH PRIVILEGES;"
# If /root/.my.cnf doesn't exist then it'll ask for root password
else
echo "Please enter root user MySQL password!"
echo "Note: password will be hidden when typing"
read -sp rootpasswd
mysql -uroot -p${rootpasswd} -e "CREATE DATABASE ${MAINDB} /*\!40100 DEFAULT CHARACTER SET utf8 */;"
mysql -uroot -p${rootpasswd} -e "CREATE USER ${MAINDB}@localhost IDENTIFIED BY '${PASSWDDB}';"
mysql -uroot -p${rootpasswd} -e "GRANT ALL PRIVILEGES ON ${MAINDB}.* TO '${MAINDB}'@'localhost';"
mysql -uroot -p${rootpasswd} -e "FLUSH PRIVILEGES;"
fi
回答by MagePsycho
I have created a one which you may find useful:
我创建了一个你可能会觉得有用的：
#!/bin/bash
#
# Script to create MySQL db + user
#
# @author Raj KB <[email protected]>
# @website http://www.magepsycho.com
# @version 0.1.0
################################################################################
# CORE FUNCTIONS - Do not edit
################################################################################
#
# VARIABLES
#
_bold=$(tput bold)
_underline=$(tput sgr 0 1)
_reset=$(tput sgr0)
_purple=$(tput setaf 171)
_red=$(tput setaf 1)
_green=$(tput setaf 76)
_tan=$(tput setaf 3)
_blue=$(tput setaf 38)
#
# HEADERS & LOGGING
#
function _debug()
{
[ "$DEBUG" -eq 1 ] && $@
}
function _header()
{
printf "\n${_bold}${_purple}========== %s ==========${_reset}\n" "$@"
}
function _arrow()
{
printf "? $@\n"
}
function _success()
{
printf "${_green}? %s${_reset}\n" "$@"
}
function _error() {
printf "${_red}? %s${_reset}\n" "$@"
}
function _warning()
{
printf "${_tan}? %s${_reset}\n" "$@"
}
function _underline()
{
printf "${_underline}${_bold}%s${_reset}\n" "$@"
}
function _bold()
{
printf "${_bold}%s${_reset}\n" "$@"
}
function _note()
{
printf "${_underline}${_bold}${_blue}Note:${_reset} ${_blue}%s${_reset}\n" "$@"
}
function _die()
{
_error "$@"
exit 1
}
function _safeExit()
{
exit 0
}
#
# UTILITY HELPER
#
function _seekConfirmation()
{
printf "\n${_bold}$@${_reset}"
read -p " (y/n) " -n 1
printf "\n"
}
# Test whether the result of an 'ask' is a confirmation
function _isConfirmed()
{
if [[ "$REPLY" =~ ^[Yy]$ ]]; then
return 0
fi
return 1
}
function _typeExists()
{
if [ $(type -P ) ]; then
return 0
fi
return 1
}
function _isOs()
{
if [[ "${OSTYPE}" == * ]]; then
return 0
fi
return 1
}
function _checkRootUser()
{
#if [ "$(id -u)" != "0" ]; then
if [ "$(whoami)" != 'root' ]; then
echo "You have no permission to run ./mysql-create-db-user.sh --host=localhost --database=test-db --user=test-user
as non-root user. Use sudo"
exit 1;
fi
}
function _printPoweredBy()
{
cat <<"EOF"
Powered By:
__ ___ ___ __
/ |/ /__ ____ ____ / _ \___ __ ______/ / ___
/ /|_/ / _ `/ _ `/ -_) ___(_-</ // / __/ _ \/ _ \
/_/ /_/\_,_/\_, /\__/_/ /___/\_, /\__/_//_/\___/
/___/ /___/
>> Store: http://www.magepsycho.com
>> Blog: http://www.blog.magepsycho.com
################################################################
EOF
}
################################################################################
# SCRIPT FUNCTIONS
################################################################################
function generatePassword()
{
echo "$(openssl rand -base64 12)"
}
function _printUsage()
{
echo -n "$(basename mysql -uroot -p$*PASSWORD_OF_MYSQL_ROOT_USER* -e "CREATE DATABASE $MAINDB"
mysql -uroot -p$*PASSWORD_OF_MYSQL_ROOT_USER* -e "GRANT ALL PRIVILEGES ON $MAINDB.* TO $MAINDB@localhost IDENTIFIED BY '$PASSWDDB!'"
) [OPTION]...
Create MySQL db & user.
Version $VERSION
Options:
-h, --host MySQL Host
-d, --database MySQL Database
-u, --user MySQL User
-p, --pass MySQL Password (If empty, auto-generated)
-h, --help Display this help and exit
-v, --version Output version information and exit
Examples:
$(basename # create random password
PASSWDDB="$(openssl rand -base64 12)"
# replace "-" with "_" for database username
MAINDB=${USER_NAME//[^a-zA-Z0-9]/_}
mysql -u root -p<<MYSQL_SCRIPT
CREATE DATABASE $MAINDB;
CREATE DATABASE ${MAINDB} /*\!40100 DEFAULT CHARACTER SET utf8 */;
CREATE USER ${MAINDB}@localhost IDENTIFIED BY '${PASSWDDB}';
GRANT ALL PRIVILEGES ON ${MAINDB}.* TO '${MAINDB}'@'localhost';
FLUSH PRIVILEGES;
MYSQL_SCRIPT
) --help
"
_printPoweredBy
exit 1
}
function processArgs()
{
# Parse Arguments
for arg in "$@"
do
case $arg in
-h=*|--host=*)
DB_HOST="${arg#*=}"
;;
-d=*|--database=*)
DB_NAME="${arg#*=}"
;;
-u=*|--user=*)
DB_USER="${arg#*=}"
;;
-p=*|--pass=*)
DB_PASS="${arg#*=}"
;;
--debug)
DEBUG=1
;;
-h|--help)
_printUsage
;;
*)
_printUsage
;;
esac
done
[[ -z $DB_NAME ]] && _error "Database name cannot be empty." && exit 1
[[ $DB_USER ]] || DB_USER=$DB_NAME
}
function createMysqlDbUser()
{
SQL1="CREATE DATABASE IF NOT EXISTS ${DB_NAME};"
SQL2="CREATE USER '${DB_USER}'@'%' IDENTIFIED BY '${DB_PASS}';"
SQL3="GRANT ALL PRIVILEGES ON ${DB_NAME}.* TO '${DB_USER}'@'%';"
SQL4="FLUSH PRIVILEGES;"
if [ -f /root/.my.cnf ]; then
$BIN_MYSQL -e "${SQL1}${SQL2}${SQL3}${SQL4}"
else
# If /root/.my.cnf doesn't exist then it'll ask for root password
_arrow "Please enter root user MySQL password!"
read rootPassword
$BIN_MYSQL -h $DB_HOST -u root -p${rootPassword} -e "${SQL1}${SQL2}${SQL3}${SQL4}"
fi
}
function printSuccessMessage()
{
_success "MySQL DB / User creation completed!"
echo "################################################################"
echo ""
echo " >> Host : ${DB_HOST}"
echo " >> Database : ${DB_NAME}"
echo " >> User : ${DB_USER}"
echo " >> Pass : ${DB_PASS}"
echo ""
echo "################################################################"
_printPoweredBy
}
################################################################################
# Main
################################################################################
export LC_CTYPE=C
export LANG=C
DEBUG=0 # 1|0
_debug set -x
VERSION="0.1.0"
BIN_MYSQL=$(which mysql)
DB_HOST='localhost'
DB_NAME=
DB_USER=
DB_PASS=$(generatePassword)
function main()
{
[[ $# -lt 1 ]] && _printUsage
_success "Processing arguments..."
processArgs "$@"
_success "Done!"
_success "Creating MySQL db and user..."
createMysqlDbUser
_success "Done!"
printSuccessMessage
exit 0
}
main "$@"
_debug set +x
Usage:
用法：
##代码##For more details:
http://www.blog.magepsycho.com/bash-script-create-mysql-database-user-optional-password/
更多详情：http:
//www.blog.magepsycho.com/bash-script-create-mysql-database-user-optional-password/
回答by Manish Gaikwad
##代码##
This will work for you.
这对你有用。
