Listing Databases in MySQL
As a MySQL administrator, it’s essential to be able to list the databases in your database management system (DBMS) to understand the structure of your database and perform various operations. In this article, we’ll guide you on how to list the databases in MySQL.
Step 1: Accessing the MySQL System Information
Before listing the databases, you need to access the MySQL system information. To do this, follow these steps:
- Log in to your MySQL server using a client tool such as the MySQL command-line client, phpMyAdmin, or the MySQL GUI client.
- Type the following command to display the system information:
SHOW DATABASES; - Replace
SHOWwith the word "SHOW" (case-insensitive).
H2. What the List of Databases Shows
The SHOW DATABASES command lists the databases in your MySQL server. Here’s what the list shows:
| Database | Owner | Create Time | File Size |
|---|---|---|---|
| information_schema | MySQL | 2022-01-01 12:00:00 | 20,422,440 |
| mysql | MySQL | 2022-01-01 12:00:00 | 20,422,440 |
| performance_schema | MySQL | 2022-01-01 12:00:00 | 15,642,020 |
| table_name | MySQL | 2022-01-01 12:00:00 | 15,642,020 |
| users | MySQL | 2022-01-01 12:00:00 | 8,600,176 |
| wiki | MySQL | 2022-01-01 12:00:00 | 24,149,400 |
| **memcache` | MySQL | 2022-01-01 12:00:00 | 9,435,884 |
| myisam | MySQL | 2022-01-01 12:00:00 | 55,443,424 |
| **InnoDB` | MySQL | 2022-01-01 12:00:00 | 28,362,180 |
| **shmldata` | MySQL | 2022-01-01 12:00:00 | 27,672,340 |
H2. Additional Databases
There are several other databases in MySQL that you can access by modifying the SHOW DATABASES command. Here are a few examples:
| Database | Owner | Create Time | File Size |
|---|---|---|---|
| math | MySQL | 2022-01-01 12:00:00 | 7,671,416 |
| learning | MySQL | 2022-01-01 12:00:00 | 4,378,742 |
| **phpmyadmin` | MySQL | 2022-01-01 12:00:00 | 6,387,104 |
H2. Querying the Database
Once you have accessed the list of databases, you can query each database to retrieve specific information. Here are a few examples:
| Database | Query | Result |
|---|---|---|
| information_schema | SELECT TABLE_NAME, TABLE_SCHEMA, TABLE.Engine FROM information_schema Tables WHERE TABLE_SCHEMA = ‘mysql’; | Table Name, Table Schema, Engine |
| users | *SELECT FROM users;** | *User_id, User_name, User_pass, User_email, User_role,… |
| wiki | *SELECT FROM wiki;** | *ID, Title, Text,… |
H2. Important Notes
- Performance Impact: Querying the list of databases can impact performance, especially if you have a large number of databases. It’s recommended to query only the databases you need to check.
- Database Locking: Some databases, such as InnoDB and MyISAM, have locking mechanisms that can block other operations. Be cautious when modifying database structures or querying data.
- Security: Make sure to handle sensitive data, such as passwords and database credentials, securely.
In conclusion, listing the databases in MySQL is a fundamental aspect of database administration. By following the steps outlined in this article, you’ll be able to access the list of databases and perform various operations to manage your MySQL database. Remember to always follow best practices to maintain the performance and security of your database.