From a shell script, how can I check whether a MySQL database exists?
Solution 1:
I realize this was answered a long time ago, but it seems much cleaner to me to do this:
mysql -u root -e 'use mydbname'
If the database exists, this will produce no output and exit with returncode == 0.
If the database does not exist, this will produce an error message on stderr and exit with returncode == 1. So you'd do something like this:
if ! mysql -u root -e 'use mydbname'; then
...do stuff to create database...
fi
This operates nicely with shell scripts, doesn't require any processing of the output, and doesn't rely on having local filesystem access.
Solution 2:
mysql -e "SHOW DATABASES LIKE 'foo'"
should help you.
http://dev.mysql.com/doc/refman/5.1/en/show-databases.html
Solution 3:
Databases in MySQL are folders in the filesystem. That make it damn easy to find whether a database exists:
test -d "/var/lib/mysql/databasename"
In this case, /var/lib
is the datadir of MySQL.
The nice thing about this snippet is that it won't need a MySQL daemon running, neither does it need credentials. Of course the user running the command must be allowed to descend into that directory.
Solution 4:
From http://www.jbmurphy.com/2011/02/08/quick-check-if-a-mysql-database-exists/ this is more akin to what I wanted:
DBNAME="dblookingfor"
DBEXISTS=$(mysql --batch --skip-column-names -e "SHOW DATABASES LIKE '"$DBNAME"';" | grep "$DBNAME" > /dev/null; echo "$?")
if [ $DBEXISTS -eq 0 ];then
echo "A database with the name $DBNAME already exists."
else
echo " database $DBNAME does not exist."
fi
Solution 5:
A bit hacky, but this will print 1 if foo does not exist, 0 otherwise:
mysqlshow -u root foo 2>&1 >/dev/null | awk ' { print /Unknown database/ }'