MySQL is one of the most widely used relational database management systems (RDBMS) today. It is a robust database platform that allows you to create and manage 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: enumerate the databases that reside on the server, display tables of a given database, view user roles and privileges, access restrictions, and so on.
This article will explain the ins and outs of how to enumerate MySQL databases via command prompt.
Prerequisites for enumerating databases
MySQL
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
- Download and run the MySQL installer directly from its official website, going through the setup process to set up and install MySQL server and other tools.
MySQL.
To conveniently execute MySQL commands using the command line, you will need to add the path of the MySQL executable to your system 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 variable environment This
section guides you to add the MySQL executable path to your system variable environment if you are running XAMPP or WAMP on a Windows computer
.
First, launch your Windows file explorer and navigate to This PC. Click the drive where you installed the WAMP or XAMPP package (C:).
If you are 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 your full path.
Click on the Start menu and search for “path”. Click Edit 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 your changes by clicking OK.
Now that the path has been added, you can run MySQL commands in the terminal.
Sign in to
MySQL To enumerate MySQL databases, the user must
be authorized to access all databases, or 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 it with your username. The default username for MySQL is root, and the password is empty (there is no password by default).
Display databases within the MySQL server
Now that you are logged in, you can list the MySQL databases present on the server by running the command SHOW DATABASES: SHOW DATABASES;
In return, you get all the databases present in the storage:
databases that are stored.
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 information related to databases and other objects (views, user privileges, tables, constraints, etc.) stored on the MySQL server.
Filtering
database output results Previously, it returned all databases on the server
MySQL with SHOW DATABASES, but often you have to filter the database output, mainly when there are many databases on the server
.
The LIKE clause filters the result of the SHOW DATABASE based on a specified pattern. Here is the general syntax:
SHOW DATABASES AS ”;
It must be a string that represents the pattern you want to match. The string must end with the percent symbol, %, indicating one or more characters.
For example, if you want to display only databases whose names begin with the letter w, you must run the following:
DISPLAY DATABASES AS ‘w%’;
This is
the filtered output:
Using the information schema to
query table metadata
Earlier, you saw how the database information_schema stores all information related to databases, tables, and other objects in the MySQL server environment
.
The schemata table information_schema used 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 begin with “samp” or “word,” you can combine several other clauses to perform a complex query:
SELECT schema_name OF information_schema.schemata WHERE schema_name AS ‘samp%’ OR schema_name AS ‘word%’;
Here is the result:
.
In addition, you have the Tables table in the information_schema database, which contains information about all the tables. Similarly, you can perform a query to retrieve only tables that match a specified pattern.
For example, the following query returns schema information only for WordPress tables, only tables whose names begin with “wp_”:
SELECT * FROM information_schema.tables where table_name AS ‘wp_%’;
Here is 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 error of using a semicolon at the end of statements
.
Another is to use an invalid SQL syntax or misspelled table/column name. To avoid this, check the name of the table or column to make sure it is spelled correctly. Be sure to check their 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 the code more readable and less susceptible to errors.
So instead:
select * from information_schema.tables where table_name as ‘wp_%’;
Do this:
SELECT * FROM information_schema.tables where table_name AS ‘wp_%’; Avoid using SELECT
* Avoid using SELECT*
in your SQL queries. Your request is unclear because you can’t always know what you’ll return. Instead, specify the columns that you want to select from the table.
So instead:
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
bugs easier is to indent your code. It makes it more readable!
Database administrators
Alternatively, you can choose to manage your databases without writing SQL by using a database administrator. This allows users to access database administration functions without writing SQL queries. This software connects to a MySQL server and provides a user interface to expose database functions. Once connected, the user interface will display all databases on the server. The appearance varies depending on the 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 admin 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 databases on your MySQL server. The capabilities to see which databases are on the server, view specific tables and information from them, and access information about user roles and privileges are crucial tasks. Fortunately, using SQL from your command line can make all of this a breeze.
When your database management needs to extend beyond lookup tables, Kinsta can help. Learn more about our scalable database hosting options today!