1. Overview
MySQL is a popular database program. While working with MySQL, we might have to check whether a database exists using the command line or a shell script before working with the tables in that database.
In this tutorial, we’ll discuss how to check whether a database exists using command-line utilities.
2. Using mysql
The MySQL command-line client program, mysql, is one of the tools that we can use for checking the existence of a MySQL database. It’s generally used as an interactive SQL shell, but it’s also possible to execute SQL statements from the command line.
2.1. Using the use Statement
Let’s use mysql from the command line:
$ mysql -u user -e "use students"
$ echo $?
0
Here, we checked the existence of the students database. We used the -e option of mysql to execute the SQL statement “use students”. The use statement is for using the specified database, in our example, the students database, as the current database. Since the database existed, we got an exit status of 0 which means success.
The -u option of mysql is for specifying the MySQL username to connect to the server.
Let’s run the mysql client program once more for a non-existing database:
$ mysql -u user -e "use nonexistingDB"
ERROR 1049 (42000) at line 1: Unknown database 'nonexistingDB'
$ echo $?
1
We tried to use a non-existing database, namely nonexistingDB, and we got an error message. The error message reported that the database wasn’t known. In addition, the exit status of running the client program was 1, which means failure.
Therefore, we can check the exit status for determining whether the database exists or not in a shell script:
$ result=$(mysql -u user -e "use students")
$ if [[ $result -eq 0 ]]
then
echo "The database exists"
else
echo "The database doesn’t exist"
fi
2.2. Using the show databases Statement
The “use students” SQL statement isn’t the only SQL statement that we can use to check the existence of the students database. We can also use the “show databases” SQL statement:
$ mysql -u user -e "show databases"
+--------------------+
| Database |
+--------------------+
| information_schema |
| grades |
| log |
| mysql |
| run |
| staff |
| students |
+--------------------+
The “show databases” command listed all the databases on the server. The students database that we checked before is among the available databases as expected. We can further filter the output using the grep command:
$ mysql -u user -e "show databases" | grep students
students
$ echo $?
0
So, since grep could find the name of the students database in the output of mysql client program, the exit status was 0.
Let’s run the same command for a non-existing database:
$ mysql -u user -e "show databases" | grep nonexistingDB
$ echo $?
1
Now, we got an exit status of 1, as expected.
3. Using mysqlshow
The mysqlshow program is an alternative to mysql. It’s a client program to display database, table, and column information.
Let’s inspect it with an example:
$ mysqlshow --user user --host localhost --socket /tmp/mysql.user.sock students
Database: students
+-------------+
| Tables |
+-------------+
| description |
| name |
+-------------+
$ echo $?
0
We specified the user to connect to the MySQL server using the –user option. The username was user. The –host option’s for specifying the host on which the MySQL server is running. In our case, it’s the localhost. The –socket option’s the socket file to connect to the server. It was the socket file /tmp/mysql.user.sock. Finally, we specified the students database.
The output of running mysqlshow listed the tables in the database. The students database is in the list. The exit status of the command execution was 0.
Let’s also check a non-existing database and see the result:
$ mysqlshow --user user --host localhost --socket /tmp/mysql.user.sock nonexistingDB
mysqlshow: Unknown database 'nonexistingDB'
$ echo $?
1
The exit status was 1, as expected. Hence, we can use mysqlshow for checking the existence of a database.
4. Using mysqlcheck
The mysqlcheck is a table maintenance utility. It can be used for checking, analyzing, and repairing tables. But we can also use it for checking the existence of a database:
$ mysqlcheck --user user --host localhost --socket /tmp/mysql.user.sock students
students.description OK
students.names OK
$ echo $?
0
The options passed to mysqlcheck were the same as the ones we passed to mysqlshow. mysqlcheck checked the tables in the database and listed the status of each table. Since the database exists, the exit status was 0.
Let’s try to use mysqlcheck with a non-existing database and see what happens:
$ mysqlcheck --user user --host localhost --socket /tmp/mysql.user.sock nonexistingDB
mysqlcheck: Got error: 1049: Unknown database 'nonexistingDB' when selecting the database
$ echo $?
2
Running mysqlcheck gave an error this time as expected. The exit status was 2.
Therefore, mysqlcheck is another alternative for checking the existence of a database.
5. Conclusion
In this article, we discussed different methods for checking the existence of a database from the command line.
First, we examined the mysql client program. We used the use and show databases SQL statements using the -e option.
The second program was mysqlshow which shows which databases exist and their tables.
Finally, we discussed mysqlcheck. Although it’s mainly used for maintenance, we saw that we can also use it for checking the existence of a database.