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

MySQL is one of today's most commonly used relational databases management systems (RDBMS). It's a robust database platform that lets you create and maintaining scalable databases mostly using a structured query language (SQL).

The MySQL server is the environment where the databases are located -- and where they are accessed. As an administrator for the server it is common to retrieve details about this environment , including the databases that live on the server, and displaying tables from a particular database, displaying rights and roles for users as well as accessing restrictions etc.

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

The prerequisites to list MySQL databases

It is necessary to have the MySQL server locally on your machine in order to get started. If you do not have MySQL, there are several methods to set it up:

• Download and run the MySQL installer directly via their official site. following the installation process to configure and install the MySQL server and other tools.

To conveniently run MySQL commands with commands, it is necessary to include the MySQL executable's URL to the system's environment. If you've have installed MySQL with option 2, this step is unnecessary, so feel free to bypass the following section.

Make sure to add the MySQL path to your system's Variables Environment

This guide will help you in how to add an MySQL executable path into your system's variable environment when you're using XAMPP or WAMP on a Windows computer.

Then, open your Windows Explorer and navigate to this computer. Click the drive where you've installed the WAMP or XAMPP package ( C:).

If you're running XAMPP then go to the xampp and then mysql > bin and copy the full path to the bin folder. For WAMP go to your-wamp version > bin > mysql your-mysql-version > bin to its full path.

Click on the Start menu and search for "path." Click Modify the system's environment variable.

Click the Environment Variables under Startup and Recovery then select the PATH variable and then click edit.

Then, select New and copy the complete address of the MySQL executable (which you copied earlier).

Save the adjustments when you click "OK.".

Now that you have added the path it is possible to use MySQL commands using the terminal.

Login to MySQL

To list MySQL databases, the user must be authorized to access all databases, or you must set an overall SHOW Databases privilege that grants access to everyone.

Make sure your MySQL server is functioning prior to logging in via the command prompt

mysql -u-p

Make sure to replace it with your username. The default username for MySQL is root and the password is blank (there's no password in default).

Create Databases inside the MySQL Server

Once you've logged into and have logged in, you are able to list MySQL databases present in the server using the SHOW DATABASES command:

SHOW DATABASES

As a result, you receive the entire database that is within the storage

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

Information_schema database information_schema database is an unmodifiable database that holds all details related to databases and other objects (views as well as user privileges, tables, constraints, etc.) that are stored on MySQL. MySQL server.

Finding the Filter Results from the Database Output

In the past, you could return all of the databases on the MySQL server with SHOW Databases However, often you have to filter the data returned by the database, particularly in the case of databases that are numerous that are on the server.

It is the LIKE clause filters the output of SHOW DataBASE by referencing a particular pattern. The general syntax is:

SHOW DATABASES LIKE '';

The string must be that represents the pattern you wish to match. The end of the string must be the symbol for percentage, %, that indicates one or more characters.

As an example, if you want to display just the databases whose names start with the letter W You can accomplish this using the following commands:

SHOW DATABASES LIKE "w%'

Here's the filtering result:

Utilizing the 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.

The information_schema database utilizes the schemata table to store data about all databases. To filter databases, you can perform a complex search to query the schema table specifically for databases.

For example, if you are looking for databases with names that start with "samp" or "word," you can combine several other clauses to make a complex search:

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

The result is:

In addition, you have an additional table called the tables table in the information_schema database, which provides information about the tables. Additionally, you could run queries to search only the tables that match certain patterns.

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

The result is:

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

Common Problems and Best Methods

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

A different issue is using an incorrect SQL syntax, or a wrongly spelled table/column name. To avoid this, cross-check the name of the column or table to make sure it's spelled correctly. Check your spelling as well.

Below are some additional best practices to keep in mind.

Use uppercase characters for SQL Keywords

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

Instead of:

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

Try this:

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

Avoid Using SELECT *

Avoid using SELECT * in your SQL queries. It's unclear as it isn't possible to know exactly what the query will produce. Make sure to specify the columns you want to select in the tableau.

Instead of:

SELECT * EXCEPT(phone) from users.profile

Follow these steps:

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

You can indent your Code

Another tip to make finding errors easier is to indent your code. This makes your code more accessible!

Database Managers

You can also opt to manage your database without having to write SQL with the help of the database manager. This allows users access to database management functions without needing to write SQL queries. This software connects to the MySQL server and provides an interface for users to access the database functions. After connecting it will open the UI will show all databases that are on the server. The design and style of the UI differ among management tools, however the procedure is the same.

Summary

As a server administrator, you need to be able to efficiently and accurately retrieve details about the databases on you MySQL server. Ability to identify which databases are available on the server, view specific tables and the information in them, and access information about user roles and privileges are all essential tasks. It is good to know that using SQL from your command line can make the process easy.

Reduce time, money and increase site performance:

• Instant help 24/7 support from WordPress experts in hosting, 24 hours a day.
• Cloudflare Enterprise integration.
• Global audience reach with 35 data centers around the world.
• Optimization using our built-in Application to monitor performance.