How To List MySQL Databases (Step-by-Step Code Tutorial)

MySQL is one of today’s most widely used relational database management systems (RDBMS). It’s a robust database platform that allows for creating and managing scalable databases, mainly using a structured query language (SQL).

The MySQL server is the environment in which the databases reside — and where they are accessed. As a server administrator, you will often need to retrieve details about this environment — listing the databases that live on the server, displaying tables from a particular database, viewing user roles and privileges, accessing constraints, and so on.

This article will explain the ins and outs of how to list MySQL databases via the command prompt.

Prerequisites To List MySQL Databases

You must have the MySQL server running on your local machine to get started. If you don’t have MySQL, there are a few ways to install it:

• Install WAMPServer, XAMPP, MAMP, or any other software distribution stack that includes MySQL.
• Download and run the MySQL installer directly from their official website, going through the setup process to configure and install the MySQL server and other tools.

To conveniently run MySQL commands using the command line, you’ll need to add the MySQL executable’s path to your system’s environment. If you installed MySQL using option two, this step is unnecessary, so feel free to skip the next section.

Add the MySQL Path To Your System’s Variables Environment

This section guides you on adding the MySQL executable path to your system’s variable environment if you’re running XAMPP or WAMP on a Windows computer.

First, launch your Windows file explorer and navigate to This PC. Click the drive where you’ve installed the WAMP or XAMPP package (C:).

If you’re running XAMPP, navigate to xampp > mysql > bin and copy the full path to the bin folder. For WAMP, navigate through {your-wamp-version} > bin > mysql > {your-mysql-version} > bin to its full path.

Click the Start menu and search for “path.” Click Edit the system environment variable.

Then, click Environment Variables under Startup and Recovery, select the PATH variable and click Edit.

Next, click New and paste the full path to your MySQL executable (which you copied earlier).

Then, save the changes by clicking OK.

Now that the path has been added, you can execute MySQL commands in the terminal.

Login To MySQL

To list MySQL databases, the user must be authorized to access all databases, or you must set a global SHOW DATABASES privilege that grants access to all users.

Make sure your MySQL server is running before logging in via the command prompt:

mysql -u  -p

NOTE: replace  with your username. The default username for MySQL is root, and the password is empty (there’s no password by default).

Show Databases Inside the MySQL Server

Now that you’re logged in, you can list MySQL databases present in the server by executing the SHOW DATABASES command:

SHOW DATABASES;

In return, you get all the databases present in the storage:

Out of the six databases returned, information_schema and performance_schema are the default databases that are automatically generated when you install MySQL.

The information_schema database is a non-modifiable database that stores all the information related to databases and other objects (views, user privileges, tables, constraints, etc.) stored in the MySQL server.

Filtering Results of the Database Output

Previously, you returned all of the databases on the MySQL server with SHOW DATABASES, but you often have to filter the database output, mainly when there are many databases on the server.

The LIKE clause filters the result of SHOW DATABASE based on a specified pattern. Here’s the general syntax:

SHOW DATABASES LIKE '';

It must be a string representing the pattern you want to match. The string must end with the percentage symbol, %, which denotes one or more characters.

For example, if you want to display just the databases whose names start with the letter w, you do so by running the following:

SHOW DATABASES LIKE 'w%';

Here’s the filtered result:

Using Information Schema to Query Table Metadata

Earlier, you saw how the information_schema database stores all the information related to databases, tables, and other objects in the MySQL server environment.

The information_schema database makes use of the schemata table to store information about all databases. For database filtering, you can perform a complex search to query the schema table for specific databases.

For example, if you want databases whose names start with either “samp” or “word,” you can combine several other clauses to make a complex query:

SELECT schema_name FROM information_schema.schemata WHERE schema_name LIKE 'samp%' OR schema_name LIKE 'word%';

Here’s the result:

In addition, you have the tables table from the information_schema database, which contains information about all tables. Similarly, you can perform a query to retrieve only the tables that match a specified pattern.

For example, the following query returns the schema information of only the WordPress tables — only the tables whose names start with “wp_”:

SELECT * FROM information_schema.tables WHERE table_name LIKE 'wp_%';

Here’s the result:

Other tables found in information_schema include columns, constraints, table_constraints, check_constraints, and referential_constraints.

Common Issues and Best Practices

One of the most common causes of errors when executing SQL is the failure to use a semicolon at the end of statements.

Another is using an invalid SQL syntax or an incorrectly spelled table/column name. To avoid this, cross-check the table or column name to ensure it is spelled correctly. Be sure to cross-check your syntax as well.

Here are some other best practices to keep in mind.

Use Uppercase for SQL Keywords

When writing SQL code, always use uppercase for SQL keywords and lowercase for table names and column names. This makes your code more readable and less susceptible to errors.

So, instead of this:

select * from information_schema.tables where table_name like 'wp_%';

Do this:

SELECT * FROM information_schema.tables WHERE table_name LIKE 'wp_%';

Avoid Using SELECT *

Avoid using SELECT * in your SQL queries. Your request is unclear because you can’t always know what it will return. Instead, specify the columns you want to select from the table.

So instead of this:

SELECT * EXCEPT(phone) FROM users.profile

Do this:

SELECT name,
    dob,
    address,
    country,
    address,
FROM user.profile

Indent Your Code

Finally, one more tip to make finding errors easier is to indent your code. It makes it more readable!

Database Managers

Alternatively, you can choose to manage your databases without writing SQL through the use of a database manager. This allows users access to database management functions without needing to write SQL queries. This software connects to a MySQL server and provides a user interface to expose the database functions. Once connected, the UI will show all databases on the server. The look and feel vary across management tools, but the process is similar.

Several tools are available to choose from, including phpMyAdmin and Adminer, both of which are accessible through DevKinsta. The default management tool for DevKinsta is Adminer, as it is lightweight, easy, and fast, but phpMyAdmin can be accessed with ease.

Summary

As a server administrator, you need to be able to efficiently and accurately retrieve details about the databases on your MySQL server. The abilities to see which databases are on the server, view specific tables and the information from within them, and access information about user roles and privileges are all crucial tasks. Fortunately, using SQL from your command line can make this all a breeze.

When your database management needs to stretch beyond querying tables, Kinsta can help. Learn more about our scalable database hosting options today!

The post How To List MySQL Databases (Step-by-Step Code Tutorial) appeared first on Kinsta®.