bash脚本——检查mysql数据库是否存在基于结果的执行操作

Is it possible from within a bash script to check if a mysql database exists. Depending on the result then perform another action or terminate the script?

是否可以在bash脚本中检查mysql数据库是否存在。根据结果执行另一个操作或终止脚本?

16 个解决方案

#1

Example script (Thanks to Bill Karwin for the --user and --password comment!):

示例脚本(感谢Bill Karwin提供的——用户和密码注释!)

#!/bin/bash
## --user=XXXXXX --password=XXXXXX *may* not be necessary if run as root or you have unsecured DBs but
##   using them makes this script a lot more portable.  Thanks @billkarwin
RESULT=`mysqlshow --user=XXXXXX --password=XXXXXX myDatabase| grep -v Wildcard | grep -o myDatabase`
if [ "$RESULT" == "myDatabase" ]; then
    echo YES
fi

These are what the commands look like when run at a prompt:

以下是在提示符下运行时的命令:

[root@host ~]# mysqlshow myDatabase
Wildcard: myDatabase
+------------------+
|    Databases     |
+------------------+
| myDatabase       |
+------------------+

If no DB exists, the output will look like this:

如果没有DB，输出将如下所示:

[root@host ~]# mysqlshow myDatabase
Wildcard: myDatabase
+-----------+
| Databases |
+-----------+
+-----------+

Then, parse the output and do what you need to based on if it exists or not!

然后，解析输出并根据输出是否存在进行所需的操作!

#2

I give +1 to answer by @chown, but here's another alternative: If the bash script is running locally with the MySQL instance, and you know the path to the datadir, you can test:

@chown给出+1的答案，但是还有另一种选择:如果bash脚本在本地运行，使用MySQL实例，并且您知道到达datadir的路径，您可以测试:

if [ -d /var/lib/mysql/databasename ] ; then ...

This also assumes your shell user running the script has filesystem-level privileges to read the contents of the MySQL datadir. This is often the case, but it is not certain.

这也假定运行该脚本的shell用户具有读取MySQL datadir内容的文件系统级特权。这种情况经常发生，但还不确定。

#3

mysqlshow "test" > /dev/null 2>&1 && echo "Database exists."

Depending on the exit status of the mysqlshow command, it will execute the following echo.

根据mysqlshow命令的退出状态，它将执行下面的echo。

#4

I couldn't get the accepted answer work for me (the grep in the quotes didn't work), so here is my version:

我无法得到大家认可的答案(引用中的grep不起作用)，所以我的版本是:

RESULT=`mysql -u $USER -p$PASSWORD --skip-column-names -e "SHOW DATABASES LIKE 'myDatabase'"`
if [ "$RESULT" == "myDatabase" ]; then
    echo "Database exist"
else
    echo "Database does not exist"
fi

I used the option --skip-column-names to remove the column names from the result.

我使用了这个选项——skip-column-name来从结果中删除列名。

#5

YES

是的

for db in $(mysql -u -p -N <<<"show databases like '%something%'")
do
  case $db in 
    "something")
      // do something
    ;;
    "something else")
      // do something else
    ;;
  esac
done

#6

Here is an alternate version:

以下是另一个版本:

 RESULT=`mysql -u$USER -p$PASSWORD -e "SHOW DATABASES" | grep $DATABASE`
 if [ "$RESULT" == "$DATABASE" ]; then
    echo "Database exist"
 else
    echo "Database does not exist"
 fi

IF there is a DB named abcd and we use -Fo after grep then for the search result of DB a/ab/abc the script will show the result Database exist.

如果有一个名为abcd的DB，我们在grep之后使用-Fo，那么对于DB a/ab/abc的搜索结果，脚本将显示结果数据库的存在。

#7

Another solution without grep:

另一个解决方案没有grep:

FOUND_DATABASES=`MYSQL_PWD="${DB_PASSWORD}" mysql \
 -u "${DB_PASSWORD}" \
 --skip-column-names \
 --batch \
 -e "SHOW DATABASES LIKE '${DB_NAME}'" | wc -l`

FOUND_DATABASES:

FOUND_DATABASES:

0 - there is no such database
0 -没有这样的数据库
1 - the database was found
1 -找到数据库

Notes:

注:

MYSQL_PWD to disable the warning:

MYSQL_PWD禁用警告:

mysql: [Warning] Using a password on the command line interface can be insecure.

mysql:[警告]在命令行界面上使用密码可能是不安全的。
--skip-column-names to hide columns

——skip-column-names隐藏列
--batch to disable borders like +-----------+

-批量禁用边框，如+-------- +

#8

use the -e option to the mysql command. It will let you execute any query (assuming the right credentials). I don't know of a query that return just the name of the database so you'd probably need to parse the results of show databases or show tables from dbname

对mysql命令使用-e选项。它将允许您执行任何查询(假设正确的凭证)。我不知道有哪个查询只返回数据库的名称，所以您可能需要解析show database的结果，或者从dbname中显示表

#9

It's easy enough to reliably tell if the database exists with mysqlshow. The trick is being able to reliably tell the difference between a database not existing, or some other failure. The version of mysqlshow I have exits with a '1' in either case, so it can't tell.

如果数据库存在mysqlshow，就很容易可靠地判断它是否存在。诀窍在于能够可靠地区分不存在的数据库或其他失败的数据库。mysqlshow的版本，我在任何一种情况下都有带有'1'的出口，所以它不能识别。

Here's what I came up with to handle it. Adjust your mysqlshow command accordingly, or put your credentials in to a chmod 600'd ~/.my.cnf file.

这是我想出的办法。相应地调整mysqlshow命令，或者将您的凭证放入chmod 600d ~/.my.cnf文件。

This works on Ubuntu 12 + 14. I haven't tested it in other environments yet:

这适用于Ubuntu 12 + 14。我还没有在其他环境中进行测试:

#!/bin/bash -u

# Takes 1 argument. Aborts the script if there's a false negative.
function mysql_db_exists () {
  local DBNAME="$1"
  # Underscores are treated as wildcards by mysqlshow.
  # Replace them with '\\_'. One of the underscores is consumed by the shell to keep the one mysqlshow needs in tact.
  ESCAPED_DB_NAME="${DBNAME//_/\\\_}"
  RESULT="$(mysqlshow "$ESCAPED_DB_NAME" 2>&1)"; EXITCODE=$?
  if [ "$EXITCODE" -eq 0 ]; then
    # This is never a false positive.
    true
  else
    if echo "$RESULT" | grep -iq "Unknown database"; then
      # True negative.
      false
    else
      # False negative: Spit out the error and abort the script.
      >&2 echo "ERR (mysql_db_exists): $RESULT"
      exit 1
    fi
  fi
}

if mysql_db_exists "$1"; then
  echo "It definitely exists."
else
  echo "The only time you see this is when it positively does not."
fi

#10

I also used a slightly different version from chown's.

我还使用了与chown稍有不同的版本。

result=$(mysqlshow --user=root --password=12345 dbname | grep -v Wildcard | grep -ow dbname)

结果=$(mysqlshow -user=root -password=12345 dbname | grep -v通配符| grep -ow dbname)

The above executes the given command and assigns the returned value to result. And the w option matches dbname exactly.

上面执行给定的命令并将返回的值赋给result。w选项完全匹配dbname。

#11

If it helps, I did this for MariaDB on Debian Stretch:

如果有帮助的话，我为MariaDB做了一个关于Debian的延伸:

DB_CHECK=$(mysqlshow "${DB_NAME}" | grep "Unknown database") 1> /dev/null
if [ ! -z "${DB_CHECK}" ]; then
    echo "Database found."
else
    echo "Database not found."
fi

Short explanation: The result of mysqlshow for database name in variable $DB_NAME is checked for "Unknown database". If that string is found it's put into variable $DB_CHECK. Then finally the -z comparison checks if the $DB_CHECK variable is empty.

简短说明:在变量$DB_NAME中检查mysqlshow的结果为“未知数据库”。如果找到该字符串，则将其放入变量$DB_CHECK中。最后，-z比较检查$DB_CHECK变量是否为空。

If $DB_CHECK is empty then "Unknown database" did not appear in the mysqlshow response. Probably not 100% reliable, like if the connection failed or whatever. (I've not tested that.)

如果$DB_CHECK为空，则mysqlshow响应中没有出现“未知数据库”。可能不是100%可靠的，比如连接失败之类的。(我不是测试。)

#12

if [ $(mysqlshow DB 1>/dev/null 2>/dev/null) -eq 0 ]; then
    echo "DB found"
fi

#13

mysqlshow will not show underscore characters '_' in the database name.

mysqlshow将不会在数据库名称中显示下划线字符“_”。

mysqlshow $DOMAIN %

https://dev.mysql.com/doc/refman/5.1/en/mysqlshow.html

#14

mysql_user=<you_db_username>
mysql_pass=<you_db_passwrod>
target_db=<your_db_name>
if [ "`mysql -u${mysql_user} -p${mysql_pass} -e 'show databases;' | grep ${target_db}`" == "${target_db}" ]; then
  echo "Database exist"
else
  echo "Database does not exist"
fi

This executes a MySQL query to get all DB names, then greps to check that the required database exists.

它执行一个MySQL查询以获取所有DB名称，然后greps检查所需的数据库是否存在。

#15

Following command should do the trick for both the cases,

下面的命令应该对这两种情况都有效，

mysqlshow "DB_NAME" &> /dev/null && echo "YES" || echo "NO"

#16

The mysqlshow path requires parsing the output (at least for the version of mysql I have) because it always returns success. Dale makes a very good point about differentiating between failures.

mysqlshow路径需要解析输出(至少对于mysql版本是这样)，因为它总是返回成功。戴尔提出了一个关于区分失败的很好的观点。

However, if you know that everything is running and you have correct credentials, etc, and you want to tell only whether the DB exists are not you can do it in one line with a blank sql command:

但是，如果您知道一切都在运行，并且您有正确的凭据等，并且您只想知道DB是否存在，那么您可以使用空白的sql命令在一行中完成:

> mysql -uroot -ppassword good_db -e ''
> echo $?
0
> mysql -uroot -ppassword bad_db -e ''
ERROR 1049 (42000): Unknown database 'busker_core_locala'
> echo $?
1

#1