By showing a list of all databases, you can see what databases are present and decide which one you want to use. This can be useful for database administrators who need to manage the database server, such as deleting or optimizing unnecessary database performance.
If you want to create a backup of all the databases on your MySQL server, showing a list will allow you to easily see which one needs to be backed up and which one you can restore.
This post aims to show the users how they can view all the Databases in MySQL on Linux while covering the following.
- Logging in to MySQL
- View (list) all MySQL Databases on the System
- Viewing Filtered MySQL Databases
- View Databases Without Using MySQL Shell (in Terminal)
In the following sections, the points mentioned above are explained in detail.
Logging in to MySQL
To use MySQL, you must log in, which in our case is ‘root’ using the command below.
$ sudo mysql -u user -p
You can replace ‘user’ with any Username permitted to use MySQL and remove the flag ‘-p’ if you haven’t set any password, which is blank by default; to continue, press the “Enter” key to log in.
View (list) all MySQL Databases on the System
Viewing all MySQL Databases in list format is a simple task that requires the execution of the following command once you’re logged in to MySQL.
> VIEW DATABASES;
The Linux Terminal may be case-sensitive, but MySQL’s Shell isn’t. It would be best if you used a semicolon ‘;’ to mark the end of your command, as seen above, where we’ve viewed all MySQL Databases.
Another way to display all MySQL Databases is using this command.
> SHOW SCHEMAS;
Viewing Filtered MySQL Databases
To filter and view MySQL Database(s), we use the ‘like’ clause, as seen below.
> SHOW DATABASES LIKE ‘test%’;
Here, the ‘%’ means either zero, one, or multiple characters, and we’ve viewed the Databases whose name starts with ‘test’.
You can also use the ‘%’ before the query that would display the Databases ending with the keyword after it, as seen below.
> SHOW DATABASES LIKE ‘%DB’;
Now, there’s a Database in MySQL called information_schema that has all information of the currently saved Databases. It can be used to filter Databases, as seen below. This is highly efficient for applying more than one filter.
> SELECT schema_name
FROM information_schema.schemata
WHERE schema_name LIKE name1%' OR
schema_name LIKE name2%';
As seen in the above image, we’ve viewed a filtered list of MySQL Databases that starts with either ‘test’ or ‘new’ and to know more about it, use this command in the terminal.
$ mysql --help
View Databases Without Using MySQL Shell (in Terminal)
Using the following command, users can view MySQL Databases using the Terminal.
$ mysql -u user -p -e 'SHOW DATABASES;'
In the above image, the flag ‘e’ stands for mysqlshow, which displays the database stored in MySQL Server. This is highly recommended for users who are involved in shell scripting using MySQL Databases.
Conclusion
A list of all databases can be useful for managing and maintaining a MySQL server. It can help administrators delete unnecessary databases or optimize performance, and it can also assist with creating backups or restoring databases from backups. This article has provided a detailed guide to seeing a list of all databases in MySQL.
