41

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?

Improve this question

20 Answers 20

Reset to default
45

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:

if [ -d /var/lib/mysql/databasename ] ; then 
    # Do Stuff ...
fi

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

This is actually a cleaner way because folders are registered in the information_schema as a database. Additionally, user authentication is not required (in other words, no logging in). +1 !!!
This is a more elegant and reliable solution.. I LIKE IT :D
depends on file_per_table if i remember correctly. and the path depends on the operating system.
@scones, no, there's a directory for each database regardless of whether you use innodb_file_per_table, at least up to MySQL 5.7. There are metadata files under that directory no matter what storage engine your tables use. I'm not sure if that changes with MySQL 8.0, since they changed the way InnoDB metadata is managed.
i know i'm really late, but i just wanna say, altough it does work, you have to be in mysql group or chmod 754. When not, permission denied
|
39

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

#!/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:

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

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

Improve this answer

4 Comments

+1 but fwiw you need to connect as a user with privileges to that database, or else the database will be missing from the results.
@billk Tested and you are correct, I guess for this to work you would need to run the script with sudo.
No, you don't need sudo, you just need to run mysqlshow --user=XXX --password=YYY options to specify MySQL credentials for a user with enough privileges to see the database in question.
Maybe my version of mysqlshow is different, but it doesn't work this way. If I specify a database name as the last parameter, it either lists it by itself under the database heading or gives an unknown database error (if it doesn't exist). If I don't specify a database, it gives me the database list. The version info for my mysqlshow says "mysqlshow Ver 9.10 Distrib 5.1.58, for debian-linux-gnu (i686)"
20 
mysqlshow "test" > /dev/null 2>&1 && echo "Database exists."

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

Improve this answer

8 Comments

I think this answer wins
This definitely doesn't work, at least on my version of mysqlshow, because the command returns success whether or not the DB exists, so you have to parse the output.
mysqlshow works for me, but using mysql is another alternative mysql "test" -e exit > /dev/null 2>&1 && echo "Database exists.
@gtd pipe the output to a file of your choice, instead of /dev/null. You can expect to see a warning such as "Using a password on the command line..." followed on the next line by "mysqlshow: Unknown database 'myDatabaseName' ".
@KGCybeX: I understand that, my point is that this answer just doesn't work, I don't need a tutorial on how to fix it.
|
13

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

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.

Improve this answer

1 Comment

Best answer in my eyes. It may be smart to check in file system if a database exists on the server. But it's the professional way to check with MySQL commands to be on the safe side.
7

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.

Improve this answer

1 Comment

What if I have 2 databases like abcd and abcd_xyz?
6

Use the -e option to the mysql command. It will let you execute any query (assuming the right credentials).

This may be an example:

if mysql "DATABASE_NAME" -e exit > /dev/null 2>&1; then
    echo "Exists"
else
    echo "Not exists"
fi
Improve this answer

Comments

4

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
Improve this answer

Comments

4

Another solution without 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:

  • 0 - there is no such database
  • 1 - the database was found

Notes:

  • MYSQL_PWD to disable the warning:

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

  • --skip-column-names to hide columns

  • --batch to disable borders like +-----------+

Improve this answer

Comments

2

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.

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.

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

#!/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
Improve this answer

Comments

1

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

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

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

Improve this answer

Comments

1

Following command should do the trick for both the cases,

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

4 Comments

This is wrong. This command will return always "YES" because mysqlshow will show an empty list of databases, without fail because the command will be executed properly. You will need to parse the output to know if the list if empty or not.
@Fabricio The man pages show a signature of mysqlshow [db_name [tbl_name [col_name]]], it exits non-zero if what you specify is missing. Returns a list of the next level down if it does.
@SteveBuzonas, did your tried your code? I did it and it always return YES.
I didn't provide any code. I showed a snippet from the man pages. Your mysqlshow must be a different version, because the version I have will work with this solution. If you look at man mysqlshow you should see that signature. You shouldn't need to parse anything because if it doesn't exist it will exit non-zero, if it does exist it will output the tables and exit 0.
1

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

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.

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.)

Improve this answer

Comments

1

Also you can ask to use the database and then handle the exit code.

$ if mysql -uroot -pxxx -e "USE mysql"; then echo "exists"; fi
exists

$ if mysql -uroot -pxxx -e "USE doesnotexist"; then echo "exists"; fi
ERROR 1049 (42000) at line 1: Unknown database 'doesnotexist'

Or inspect $? after the call.

Improve this answer

Comments

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

Comments

0

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

mysqlshow $DOMAIN %

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

Improve this answer

Comments

0 
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.

Improve this answer

4 Comments

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review
@G4M1TG How does this not answer the question? I agree that almost every code-only answer could be improved by explaining why it works, but that doesn't make this a non-answer.
give more description with ans.
Hope it's clearer now. @G4M1TG if you still have questions on this just ask.
0

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.

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:

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

Comments

0

FWIW, the auth_socket plugin makes this much easier. The question may be super old, but there are still people like me coming here for inspiration.

If your script is running as root, you can do this:

DBNAME="what_you_are_looking_for"
DBEXISTS="$(mysql -u root -e "show databases like '$DBNAME'" --batch --skip-column-names)"

If the database exists, then $DBNAME = $DBEXISTS.

If the database does not exist, then $DBEXISTS = "".

Both should have an exit status of 0, so you can still use non-zero statuses to report errors, rather than letting a non-existent database appear as an error.

Improve this answer

Comments

0

mysqlshow is a good tool for this, here is test to check the presence of the database database_name

if mysqlshow -p${MYSQL_ROOT} 2>/dev/null| grep -q "database_name"
then
    echo "Database exist."
else
    echo "Database does not exist."
fi

Or a simple oneliner

echo "Database "`mysqlshow -p${MYSQL_ROOT} 2>/dev/null| grep -q "database_name"  || echo "does not "`"exist."
Improve this answer

Comments

0

Here's how i did it inside a bash script:

#!/bin/sh

DATABASE_USER=*****
DATABASE_PWD=*****
DATABASE_NAME=my_database

if mysql -u$DATABASE_USER -p$DATABASE_PWD -e "use $DATABASE_NAME";
then
echo "Database $DATABASE_NAME already exists. Exiting."
exit
else
echo Create database
mysql -u$DATABASE_USER -p$DATABASE_PWD -e "CREATE DATABASE $DATABASE_NAME"
fi
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.