The fullpath to access MySQL CLI.

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

Posted on

MySQL is one in all right now’s most generally used relational database administration techniques (RDBMS). It’s a sturdy database platform that permits for creating and managing scalable databases, primarily utilizing a structured question language (SQL).

The MySQL server is the surroundings through which the databases reside — and the place they’re accessed. As a server administrator, you’ll usually must retrieve particulars about this surroundings — itemizing the databases that dwell on the server, displaying tables from a specific database, viewing person roles and privileges, accessing constraints, and so forth.

This text will clarify the ins and outs of easy methods to record MySQL databases by way of the command immediate.

Stipulations To Record MySQL Databases

You need to have the MySQL server working in your native machine to get began. In case you don’t have MySQL, there are a number of methods to put in it:

  • Set up WAMPServer, XAMPP, MAMP, or some other software program distribution stack that features MySQL.
  • Obtain and run the MySQL installer immediately from their official web site, going by means of the setup course of to configure and set up the MySQL server and different instruments.

To conveniently run MySQL instructions utilizing the command line, you’ll want so as to add the MySQL executable’s path to your system’s surroundings. In case you put in MySQL utilizing possibility two, this step is pointless, so be happy to skip the subsequent part.

Add the MySQL Path To Your System’s Variables Setting

This part guides you on including the MySQL executable path to your system’s variable surroundings should you’re working XAMPP or WAMP on a Home windows laptop.

First, launch your Home windows file explorer and navigate to This PC. Click on the drive the place you’ve put in the WAMP or XAMPP package deal (C:).

In case you’re working XAMPP, navigate to xampp > mysql > bin and replica the complete path to the bin folder. For WAMP, navigate by means of {your-wamp-version} > bin > mysql > {your-mysql-version} > bin to its full path.

The fullpath to access MySQL CLI.
The total path to the bin folder.

Click on the Begin menu and seek for “path.” Click on Edit the system surroundings variable.

Then, click on Setting Variables below Startup and Restoration, choose the PATH variable and click on Edit.

Subsequent, click on New and paste the complete path to your MySQL executable (which you copied earlier).

Editing environment variables.
Modifying the surroundings variable.

Then, save the modifications by clicking OK.

Now that the trail has been added, you possibly can execute MySQL instructions within the terminal.

Login To MySQL

To record MySQL databases, the person should be licensed to entry all databases, or you could set a worldwide SHOW DATABASES privilege that grants entry to all customers.

Be sure your MySQL server is working earlier than logging in by way of the command immediate:

mysql -u  -p

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

Logging into MySQL through the terminal.
Logging in to MySQL.

Present Databases Contained in the MySQL Server

Now that you simply’re logged in, you possibly can record MySQL databases current within the server by executing the SHOW DATABASES command:


In return, you get all of the databases current within the storage:

Showing MySQL databases.
A listing of databases which are in storage.

Out of the six databases returned, information_schema and performance_schema are the default databases which are robotically generated whenever you set up MySQL.

The information_schema database is a non-modifiable database that shops all the data associated to databases and different objects (views, person privileges, tables, constraints, and many others.) saved within the MySQL server.

Filtering Outcomes of the Database Output

Beforehand, you returned all the databases on the MySQL server with SHOW DATABASES, however you usually need to filter the database output, primarily when there are a lot of databases on the server.

The LIKE clause filters the results of SHOW DATABASE primarily based on a specified sample. Right here’s the final syntax:


It should be a string representing the sample you need to match. The string should finish with the proportion image, %, which denotes a number of characters.

For instance, if you wish to show simply the databases whose names begin with the letter w, you accomplish that by working the next:


Right here’s the filtered end result:

The filtered database response when utilizing ‘w%’.

Utilizing Info Schema to Question Desk Metadata

Earlier, you noticed how the information_schema database shops all the data associated to databases, tables, and different objects within the MySQL server surroundings.

The information_schema database makes use of the schemata desk to retailer details about all databases. For database filtering, you possibly can carry out a posh search to question the schema desk for particular databases.

For instance, if you need databases whose names begin with both “samp” or “phrase,” you possibly can mix a number of different clauses to make a posh question:

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

Right here’s the end result:

Using MySQL's
The outcomes of the complicated question.

As well as, you’ve gotten the tables desk from the information_schema database, which comprises details about all tables. Equally, you possibly can carry out a question to retrieve solely the tables that match a specified sample.

For instance, the next question returns the schema data of solely the WordPress tables — solely the tables whose names begin with “wp_”:

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

Right here’s the end result:

Listing the wp_tables MySQL database table.
The outcomes of the schema data of solely the WordPress tables.

Different tables present in information_schema embrace columns, constraints, table_constraints, check_constraints, and referential_constraints.

Widespread Points and Finest Practices

One of the crucial widespread causes of errors when executing SQL is the failure to make use of a semicolon on the finish of statements.

One other is utilizing an invalid SQL syntax or an incorrectly spelled desk/column title. To keep away from this, cross-check the desk or column title to make sure it’s spelled appropriately. Make sure to cross-check your syntax as properly.

Listed below are another greatest practices to remember.

Use Uppercase for SQL Key phrases

When writing SQL code, all the time use uppercase for SQL key phrases and lowercase for desk names and column names. This makes your code extra readable and fewer vulnerable to errors.

So, as a substitute of this:

choose * from information_schema.tables the place table_name like 'wp_%';

Do that:

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

Keep away from Utilizing SELECT *

Keep away from utilizing SELECT * in your SQL queries. Your request is unclear as a result of you possibly can’t all the time know what it’s going to return. As an alternative, specify the columns you need to choose from the desk.

So as a substitute of this:

SELECT * EXCEPT(cellphone) FROM customers.profile

Do that:

SELECT title,
    deal with,
    deal with,
FROM person.profile

Indent Your Code

Lastly, yet one more tip to make discovering errors simpler is to indent your code. It makes it extra readable!

Database Managers

Alternatively, you possibly can select to handle your databases with out writing SQL by means of using a database supervisor. This permits customers entry to database administration capabilities while not having to put in writing SQL queries. This software program connects to a MySQL server and offers a person interface to show the database capabilities. As soon as related, the UI will present all databases on the server. The appear and feel differ throughout administration instruments, however the course of is analogous.

DevKinsta's database manager.
DevKinsta’s database supervisor.

A number of instruments can be found to select from, together with phpMyAdmin and Adminer, each of that are accessible by means of DevKinsta. The default administration device for DevKinsta is Adminer, as it’s light-weight, simple, and quick, but phpMyAdmin can be accessed with ease.


As a server administrator, you want to have the ability to effectively and precisely retrieve particulars in regards to the databases in your MySQL server. The talents to see which databases are on the server, view particular tables and the data from inside them, and entry details about person roles and privileges are all essential duties. Happily, utilizing SQL out of your command line could make this all a breeze.

When your database administration must stretch past querying tables, Kinsta may also help. Study extra about our scalable database hosting options right now!

Save time, prices and maximize website efficiency with:

  • Immediate assist from WordPress internet hosting specialists, 24/7.
  • Cloudflare Enterprise integration.
  • International viewers attain with 35 knowledge facilities worldwide.
  • Optimization with our built-in Software Efficiency Monitoring.

All of that and far more, in a single plan with no long-term contracts, assisted migrations, and a 30-day-money-back-guarantee. Check out our plans or talk to sales to search out the plan that’s best for you.

offshore vps