How to List All MySQL Databases in Linux

This guide provides an in-depth, step-by-step approach to listing MySQL databases in a Linux environment. From logging into the MySQL command line to utilizing advanced SQL queries and automating database lists, this guide will equip you with all the techniques and best practices needed for effective MySQL management. We’ll also cover troubleshooting tips and security measures to ensure safe database operations.

For a more detailed understanding of MySQL database management on a VPS, including installation, database control, and advanced configurations, check out our Guide to Install and Manage MySQL.

1. How to Login in MySQL on Linux?

Before going ahead and working with MySQL databases on Linux, you need to log in to the MySQL client first. Different login methods are necessary to make it to the command line interface in the case of systems having specific configurations, user permissions, and security requirements. Below, we show how to log in to your database and manage your database easily.

Command Guide for Login

1. Login Using the MySQL CLI (Command Line Interface):

Access the MySQL databases client by entering the following command in your terminal:

mysql -u [username] -p

Change [username] to your actual MySQL database username—one like root. When you do hit Enter, the system asks you for the password for that particular user. Once inside, you’ll be greeted with the chance to use a MySQL shell to enter necessary commands managing databases, tables and so much more. The method is a secure and widely used approach, as it involves authenticating the user.

2. Login Without Prompting for a Password:

If you frequently log in to MySQL Databases and want to avoid re-entering your password every time, there are safe methods of automating this process; make sure it is done responsibly, especially on a personal or secure system.

Method 1: Use -p followed directly by your password:

To log in without an interactive password prompt, you can provide the password directly in the command:

mysql -u [username] -p[password]

Put the username and password of your MySQL in place of [username] and [password].

Note: On shared systems, this method could be more secure, as its password will show up in the command history and process list. In a secure environment, use this with caution.

Method 2: Use a .my.cnf Configuration File:

To secure your automatic login to MySQL with a .my.cnf configuration file, create that file in your home directory. Type in the terminal.

nano ~/.my.cnf

Inside the file, enter your MySQL login details to enable automatic login:

[client]
user=[username]
password=[password]

Insert your actual MySQL username and password. Once you save the file, you should limit permissions as well to secure your data. The following command will be used in limiting access so that only you can read or write to the file:

Once the file is secured with this command, it will stop someone from having access to that file. This process will remove the save credentials opt. Within these settings, MySQL will log you in automatically without the requirement of a password while not doing so much as compromising security. It is good practice if you have a ton of logins to do that you’d like to do automatically (like, every day).

chmod 600 ~/.my.cnf

Now, you can log in by typing mysql alone, as MySQL will automatically use the stored credentials.

2. How to list all databases in MySQL?

After login, the subsequent step is to list all MySQL databases. Such a command is mainly used for checking the structure of your MySQL environment and, above all, checking access permissions, preparing for backups, etc.

Commands to List Databases

1. SHOW DATABASES:

To list all accessible databases, type the following command in the MySQL shell:

SHOW DATABASES;

The command shown gives a list of all databases the currently logged in user has permission to access. The output also includes system databases such as mysql, information_schema, and performance_schema, which store database configuration information, permission, and performance metrics. This help command will give us a general map of the environment of the database so that we can manage and organize our databases within MySQL.

2. Listing Databases via Terminal Using Environment Variables:

It can save some repetitive formatting if you do lists of databases often by using environment variables to set your MySQL credentials. This approach lets you stream your commands by temporarily storing your username and password stream within the terminal session.

To set up the environment variables, enter the following in your terminal:

export MYSQL_USER="your_username"
export MYSQL_PASS="your_password"

Replace “your_username” and “your_password” with your actual MySQL credentials. These variables are now accessible in MySQL commands, preventing the need to type your credentials each time.

To list all databases using these variables, execute:

mysql -u $MYSQL_USER -p$MYSQL_PASS -e "SHOW DATABASES;"

This command logs into MySQL using the stored environment variables and lists out all the accessible databases. The use of environment variables makes a line of a command short; that is how helpful it is for scripting and automation. Of course, they are session-specific, so once you close the terminal, they get cleared.

3. Filtering Database Names:

In environments with numerous databases, filtering by database name to locate specific ones can be helpful. MySQL allows filtering by using the LIKE clause in conjunction with the SHOW DATABASES command, which displays databases that match a particular pattern.

To list databases that begin with a certain letter or string, use the following command:

SHOW DATABASES LIKE p%';

The output for this command would be restricted to display only databases which begin with “p.” The initial “p” in the above regex will match any characters but is further followed by a wildcard %, which can represent all sorts of characters. The second thing this filtering approach does, which is especially important in complex database environments when you want to filter a collection of databases based on their naming, is handle cases where you need to make joins against a collection but don’t want to allow users to query across different naming space.

3. Using SQL Queries for Customized Listings

We have the INFORMATION_SCHEMA table from MySQL, which provides you with additional flexibility to list your databases for more advanced database management. The table you just created (companies) allows you to query through this table and customize the listing by selecting to include specific details, filtering by conditions, or excluding entries you don’t need. For users needing more than basic listing information out of a database, this method is perfect.

Commands for Customized Listings

1. List All Databases Using INFORMATION_SCHEMA:

To retrieve a list of all databases, query the INFORMATION_SCHEMA.SCHEMATA table. This functionality allows you to select specific database attributes or add further conditions. Use the following SQL command:

SELECT schema_name FROM information_schema.schemata;

The keys of the objects returned by this query are the names of all the databases we can access in our MySQL environment, with the flexibility to modify them depending on your requirements. Using INFORMATION_SCHEMA, you have more control over the displayed results, making it a perfectly useful tactic for complex database environments needing only selective access.

2. Exclude System Databases:

In MySQL, system databases such as MySQL, information_schema, performance_schema, and sys contain critical information for database management and are generally not required in everyday database queries. To focus solely on user-created databases, you can exclude these system databases from your query results.

To list only non-system databases, use the following SQL command:

SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

In the above query, we are fetching all the database names except the database names mentioned in the NOT IN clause. Using this method, administrators can learn about all the user-created databases in a production environment without any database-related entries. When you customize your query, only those databases that are relevant will be visible.

3. Customized Listings for Specific Database Properties:

INFORMATION_SCHEMA enables you to filter databases based on various other criteria. You find it very useful when optimizing server performance or when verifying database configurations across environments.

4. Automating Database Listings with Scripts

The work of making a list of databases can be automated to save time and check-up work for the DBAs. An easy way to run this command regularly is by creating a shell script that will enter this command on your behalf.

Creating a Shell Script to List Databases

1. Create a Script File:

Begin by creating a new shell script file. Open a terminal and use the following command to create a file named list_databases.sh:

nano list_databases.sh

2. Add Script Content:

Add the following code to list all MySQL databases.

mysql -u $MYSQL_USER -p$MYSQL_PASS -e "SHOW DATABASES;"

This script connects to MySQL using environment variables ($MYSQL_USER and $MYSQL_PASS) for the username and password, then executes the SHOW DATABASES command to display the list of databases. The script keeps your credentials separate from the script itself by using environment variables, enhancing security.

3. Make the Script Executable:

Once you add the content to the file, save and close it. Make the script executable by the following command:

chmod +x list_databases.sh

Now, you can run the script anytime with ./list_databases.sh, making database listing quick and repeatable.

Automating this process helps administrators manage databases more efficiently, especially in larger environments where regular checks are required.

4. Schedule the Script with Cron for Regular Listing:

It is possible to create a cron job where the script can be run on a frequency of running the database listing script. Cron is that scheduler at the heart of Linux that runs scripts or commands at specified times, thereby making it excellent for routine works such as monitoring of databases.

1. Edit the Crontab File:

Open the cron editor by running:

crontab -e

2. Add the Script to the Crontab:

In the crontab file, tell the schedule and the path of your script. Like to run the script every day at midnight, add the following line:

0 0 * * * /path/to/list_databases.sh >> /path/to/database_list.log

This configuration tells Cron to execute list_databases.sh at 12:00 AM daily. Adjust the timing as needed to match your scheduling requirements.

3. Save and Exit:
   So, after you have added your entry, save it by closing the crontab editor; now, Cron will run your script on its own at the scheduled time, and you will receive regular updates regarding database listings without any manual intervention.

Scheduling with Cron is a powerful way to automate repetitive tasks and ensure consistent database monitoring.

5. Troubleshooting Common Issues in MySQL

Sometimes, you may need help with working with MySQL databases. The one problem is there is the “Access Denied” error for users to list or access databases. It is solvable. Here are the steps to diagnose and solve this issue.

5.1 “Access Denied” Error

When you get an ‘Access Denied‘ error, this is generally caused by missing credentials of too few permissions.

1. Verify Username and Password:

Make sure you’ve got the right username and password matching your MySQL account credentials. Please make sure there are no mistakes, and it’s the right MySQL user that you are using the correct credentials.

2. Grant Database Access Privileges:

Grant the necessary privileges if the user lacks sufficient permissions to access or list databases. Use the following command to provide the SHOW DATABASES privilege to a specific user:

GRANT SHOW DATABASES ON *.* TO 'username'@'localhost';
FLUSH PRIVILEGES;

1. Replace ‘username’ with the actual MySQL username. This command grants the user permission to view the databases. FLUSH PRIVILEGES; ensures that the changes take effect immediately.

Following these steps, you can resolve access-related issues and ensure the user has the necessary permissions to manage databases.

3. Check Network Access:

If you want to access MySQL remotely, ensure your server allows remote connections. Open the MySQL port (default 3306) and configure firewall rules accordingly.

5.2 Network and Connection Issues

Network issues can prevent MySQL from accepting remote connections. Here’s what to check:

1. Firewall Settings:

Check firewall rules to ensure they allow traffic on port 3306. Depending on your Linux distribution, use ufw (Ubuntu) or firewall (CentOS) to open the port.

2. SSL and Configuration Settings:

If secure connections are enforced, confirm SSL settings. Additionally, review MySQL configuration in /etc/mysql/my.cnf to ensure the server accepts connections from external IPs.

6. What are the Best Practices for using MySQL

Secure database management is crucial to protect your data and prevent unauthorized access. Below are essential practices for safeguarding your MySQL environment.

Security Tips for MySQL Database Management

1. Limit Access to Sensitive Credentials:

Store credentials in protected files (.my.cnf) and set appropriate permissions to avoid unauthorized access.

chmod 600 ~/.my.cnf

2. Avoid Using Root for Routine Tasks:
   • Avoid using the root user for daily operations; instead, create dedicated MySQL accounts with limited privileges based on roles.
3. Regularly Audit User Access:
   • Keep a close eye on MySQL users and permissions. Regularly remove unused accounts and double-check privileges to bolster security.
4. Update MySQL Regularly:
   • Keeping MySQL up-to-date is essential for security, as updates often include fixes for vulnerabilities.
5. Monitor Access Logs:
   • Monitor MySQL logs for unusual access patterns. Set up alerts for failed login attempts to swiftly address security threats.

For more advanced security configurations and VPS-specific considerations, see our Ultimate Guide to MySQL on VPS: Installation Steps, Database Control, and Advanced Tips.

7. Conclusion

Listing MySQL databases is a basic skill in database administration. Once you master both basic and advanced methods, as well as automation scripts, you will be able to simplify database management on your Linux VPS. Furthermore, adhering to security best practices will create an environment that keeps your data safe. Create and manage MySQL databases on your VPS, including installation and configuration information; read our Ultimate Guide to MySQL on VPS.

About the writer

Vinayak Baranwal wrote this article. Use the provided link to connect with Vinayak on LinkedIn for more insightful content or collaboration opportunities.