How To Show List All Databases on MySQL

Managing databases effectively is a crucial skill for developers, database administrators, and system engineers working with MySQL on Linux systems. One of the most fundamental operations you’ll perform regularly is listing all available databases. This seemingly simple task serves as the gateway to database management, helping you verify existing databases, confirm successful creations, or identify resources before performing maintenance tasks. Whether you’re a seasoned database professional or just starting your journey with MySQL, knowing the various methods to list databases efficiently will streamline your workflow and enhance your productivity.

In this comprehensive guide, we’ll explore multiple approaches to listing MySQL databases on Linux systems, from basic command-line operations to advanced filtering techniques and automation strategies. By the end, you’ll have a thorough understanding of how to view your database inventory using different methods tailored to various situations.

Prerequisites

Before diving into database listing techniques, ensure you have the necessary foundations in place to interact with MySQL effectively on your Linux system.

MySQL Server Requirements

To list databases, you need a properly functioning MySQL server installation on your Linux distribution. Most popular Linux distributions include MySQL in their package repositories. If you haven’t installed MySQL yet, you can typically do so using your distribution’s package manager:

For Debian/Ubuntu-based systems:

sudo apt update
sudo apt install mysql-server

For Red Hat/CentOS-based systems:

sudo yum install mysql-server

After installation, verify that your MySQL service is running:

sudo systemctl status mysql

If using MariaDB (a drop-in replacement for MySQL), the commands remain nearly identical, though the service might be named differently:

sudo systemctl status mariadb

Required User Permissions

Listing databases requires specific privileges in MySQL’s permission system. At minimum, a user needs the SHOW DATABASES privilege to view databases. Without this permission, users can only see databases to which they have been explicitly granted access.

The MySQL root user has full privileges by default, making it suitable for administrative tasks. However, for security best practices, consider creating purpose-specific users with appropriate permission levels:

CREATE USER 'dbviewer'@'localhost' IDENTIFIED BY 'password';
GRANT SHOW DATABASES ON *.* TO 'dbviewer'@'localhost';
FLUSH PRIVILEGES;

Basic Terminal Access

You’ll need terminal access to your Linux system. If working remotely, SSH access is required:

ssh username@your_server_ip

Familiarity with basic Linux commands will help navigate the system efficiently. The terminal is where you’ll execute all MySQL commands to list and manage databases.

Logging into MySQL Command Line

The MySQL command-line client is your primary interface for direct database interaction on Linux systems.

Basic Login Process

To access the MySQL command prompt, use the mysql command followed by authentication parameters:

mysql -u username -p

The -u flag specifies your MySQL username, while the -p flag prompts for a password. For security, avoid typing passwords directly in the command (which would be visible in command history):

# Not recommended (password visible):
mysql -u root -pYourPassword

# Recommended (password prompt):
mysql -u root -p

After successful authentication, you’ll see the MySQL prompt (mysql>), indicating you’re connected to the server and ready to execute queries.

Alternative Login Methods

For remote connections, specify the host using the -h flag:

mysql -u username -p -h remote_host

To avoid repetitive credential entry, consider using MySQL option files. Create a .my.cnf file in your home directory with secure permissions:

touch ~/.my.cnf
chmod 600 ~/.my.cnf

Then add your credentials:

[client]
user=username
password=your_password

With this configuration, simply typing mysql will authenticate automatically using the stored credentials.

Troubleshooting Login Issues

If you encounter “Access denied” errors, verify:

• Username spelling and capitalization
• Password correctness
• Host permissions (localhost vs. remote)
• MySQL service status

For connection failures, check:

sudo systemctl status mysql

If MySQL uses socket authentication on newer systems, you might need:

sudo mysql

Method 1: Using SHOW DATABASES Command

The SHOW DATABASES command is the most straightforward method to list all accessible databases on your MySQL server.

Command Syntax and Usage

After logging into the MySQL command line, simply type:

SHOW DATABASES;

The semicolon at the end is crucial as it terminates the SQL statement. While MySQL commands are case-insensitive, using uppercase for SQL keywords is a common convention that improves readability.

Understanding the Output

The command produces a table with a single column labeled “Database,” listing all databases the current user has permission to see. The output typically includes both user-created databases and system databases:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| my_application_db  |
| website_data       |
+--------------------+
6 rows in set (0.00 sec)

The first four databases in this example are system databases created automatically by MySQL for internal operations. Any additional databases are user-created and contain your application data.

Practical Examples

To save the database list to a variable in a script:

SELECT SCHEMA_NAME FROM information_schema.SCHEMATA;

To find the total number of databases:

SELECT COUNT(*) FROM information_schema.SCHEMATA;

For vertical output format (useful for many databases):

SHOW DATABASES\G

Security Considerations

The SHOW DATABASES command only displays databases the current user has permission to see. This behavior provides a built-in security mechanism where restricted databases remain invisible to unauthorized users.

Database administrators should regularly audit which databases are visible to which users as part of security protocols. You can restrict the SHOW DATABASES privilege selectively using:

REVOKE SHOW DATABASES ON *.* FROM 'limited_user'@'localhost';

Method 2: Using SHOW SCHEMAS Command

MySQL treats the terms “schema” and “database” as synonyms, providing alternative syntax for the same operation.

SCHEMAS vs DATABASES in MySQL

While some database systems distinguish between schemas and databases, MySQL uses these terms interchangeably. A schema in MySQL is identical to a database – both represent a collection of tables, views, stored procedures, and other database objects.

This equivalence stems from MySQL’s architectural design but offers syntax flexibility for developers coming from other database systems.

Command Syntax and Examples

To list all databases using this alternative syntax:

SHOW SCHEMAS;

The output is identical to SHOW DATABASES:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| my_application_db  |
| website_data       |
+--------------------+
6 rows in set (0.00 sec)

Practical Applications

If you’re working with multiple database systems or migrating from PostgreSQL (which uses schemas as subdivisions within databases), the SHOW SCHEMAS syntax might feel more natural.

This command is also useful in cross-platform scripts where target systems might have different database architecture terminology. Using both commands in documentation helps ensure clarity regardless of a user’s background.

Method 3: Using mysqlshow Utility

For those who prefer operating outside the MySQL prompt, Linux provides the mysqlshow utility, which offers database listing functionality directly from the bash shell.

Understanding the mysqlshow Tool

The mysqlshow utility is a command-line tool specifically designed to display database structure information. It comes pre-installed with most MySQL installations and provides a convenient way to check databases without entering the MySQL interactive shell.

Basic Syntax and Options

To list all databases using mysqlshow:

mysqlshow -u username -p

Authentication works similarly to the regular MySQL client. After entering your password, you’ll see all accessible databases.

For more detailed information, use the verbose flag:

mysqlshow -u username -p --status

This displays additional information about each database, including the number of tables.

Advanced Usage Examples

To list tables within a specific database:

mysqlshow -u username -p database_name

To filter databases using wildcards:

mysqlshow -u username -p "app_%"

This displays only databases with names starting with “app_”.

For detailed count information:

mysqlshow -u username -p --count

This shows the number of tables, rows, and other statistics for each database.

Performance Considerations

The mysqlshow utility is particularly useful for scripting and automation tasks. It’s more efficient than the interactive MySQL client when you only need to check database structure information without performing additional operations.

For very large database environments, mysqlshow sometimes offers better performance than running equivalent queries directly, as it optimizes the information gathering process.

One-Line Database Listing Commands

For quick checks and automation scripts, one-line commands provide efficient ways to list databases without entering the interactive MySQL environment.

Direct Command Execution

The MySQL client’s -e (execute) flag allows running commands directly from the terminal:

mysql -u username -p -e "SHOW DATABASES;"

This executes the command and immediately returns to the shell prompt.

For cleaner output without table formatting:

mysql -u username -p -N -e "SHOW DATABASES;"

The -N flag removes the header row and separator lines.

Security Considerations

When using one-line commands, password security becomes important. To prevent password exposure:

Use environment variables:

export MYSQL_PWD='your_password'
mysql -u username -e "SHOW DATABASES;"
unset MYSQL_PWD

Or leverage the MySQL defaults file:

mysql --defaults-file=~/.my.cnf -e "SHOW DATABASES;"

Practical Examples

Piping results to a file:

mysql -u username -p -N -e "SHOW DATABASES;" > databases.txt

Counting databases:

mysql -u username -p -N -e "SHOW DATABASES;" | wc -l

Combining with shell scripting:

for db in $(mysql -u username -p -N -e "SHOW DATABASES;"); do
    echo "Found database: $db"
done

Filtering Database Lists

As the number of databases grows, filtering becomes essential for effective management.

Using LIKE Patterns

MySQL’s LIKE operator allows filtering database names with pattern matching:

SHOW DATABASES LIKE 'test%';

This displays only databases with names starting with “test”.

Common wildcards include:

• %: Matches any number of characters
• _: Matches exactly one character

For databases containing a specific substring:

SHOW DATABASES LIKE '%data%';

For exact pattern matching with a single character wildcard:

SHOW DATABASES LIKE 'app_v_';

Combining with grep for Advanced Filtering

For more complex filtering, combine MySQL output with the Linux grep utility:

mysql -u username -p -e "SHOW DATABASES;" | grep -v "schema"

This shows all databases except those containing “schema”.

For case-insensitive filtering:

mysql -u username -p -e "SHOW DATABASES;" | grep -i "user"

To filter out system databases:

mysql -u username -p -e "SHOW DATABASES;" | grep -v -E "information_schema|performance_schema|mysql|sys"

Practical Applications

Filtering is particularly useful for:

• Identifying databases following specific naming conventions
• Locating databases related to a particular application
• Excluding system databases for backup operations
• Finding databases that might need cleanup (like test or temporary databases)

Understanding System Databases

MySQL creates several system databases that serve specific functions for the database server’s operation.

information_schema Database

The information_schema database contains metadata about all other databases on the server. It’s a read-only database that provides information about:

• Database names and characteristics
• Tables, columns, and their properties
• Constraints and relationships
• Users and privileges

A useful query to explore database sizes:

SELECT table_schema AS 'Database', 
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)' 
FROM information_schema.TABLES 
GROUP BY table_schema;

mysql Database

The mysql database stores critical system information:

• User accounts and privileges
• Time zone information
• Help tables
• Server configuration data

This database should never be directly manipulated except through appropriate MySQL commands like CREATE USER or GRANT.

performance_schema and sys Databases

The performance_schema database collects server performance metrics in real-time, including:

• Query execution statistics
• Lock information
• Memory usage
• Thread activity

The sys database (introduced in MySQL 5.7) provides simplified views of performance_schema data, making it easier to identify performance bottlenecks:

SELECT * FROM sys.host_summary;

When to Interact with System Databases

System databases should generally be treated as read-only, with changes made only through appropriate MySQL commands rather than direct manipulation.

Always back up the mysql database before upgrading MySQL versions, as it contains critical configuration data.

Use information_schema for gathering system information and metadata, but rely on the sys database for performance tuning insights.

Additional Database Information Commands

Beyond simply listing databases, MySQL offers several commands to extract more detailed information.

Getting Database Size Information

To determine the size of all databases:

SELECT table_schema AS "Database Name",
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY (data_length + index_length) DESC;

For a specific database size:

SELECT table_schema AS "Database Name",
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE table_schema = "your_database_name"
GROUP BY table_schema;

Listing Tables Within Databases

After selecting a database with USE database_name;, list its tables:

SHOW TABLES;

For table count in each database:

SELECT table_schema AS "Database",
       COUNT(*) AS "Number of Tables"
FROM information_schema.TABLES
GROUP BY table_schema;

To view table structures:

DESCRIBE table_name;

or

SHOW CREATE TABLE table_name;

Database Status Information

Check database connection information:

SHOW PROCESSLIST;

For storage engine usage:

SELECT table_schema AS "Database",
       engine AS "Storage Engine",
       COUNT(*) AS "Number of Tables"
FROM information_schema.TABLES
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
GROUP BY table_schema, engine;

Automating Database Listing

Regular database inventory checks can be automated through scripts and scheduling tools.

Creating Shell Scripts

A basic database listing script:

#!/bin/bash

# Script to list MySQL databases
# Save as list_databases.sh and make executable with chmod +x list_databases.sh

USERNAME="your_username"
PASSWORD_FILE="/path/to/.mysql_secret"
PASSWORD=$(cat $PASSWORD_FILE)

# List all databases
echo "All databases:"
mysql -u$USERNAME -p$PASSWORD -e "SHOW DATABASES;" 2>/dev/null

# Count user databases (excluding system ones)
echo -e "\nUser database count:"
mysql -u$USERNAME -p$PASSWORD -e "SELECT COUNT(*) AS 'User Databases' FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys');" 2>/dev/null

# Exit with success status
exit 0

For better security, use MySQL config files instead of storing passwords in scripts.

Scheduling Regular Checks with Cron

To schedule daily database listing at 2 AM:

crontab -e

Add the line:

0 2 * * * /path/to/list_databases.sh > /var/log/db_inventory_$(date +\%Y\%m\%d).log

For email notifications when new databases appear:

#!/bin/bash
# Save as monitor_new_databases.sh

PREVIOUS_FILE="/tmp/db_list_previous.txt"
CURRENT_FILE="/tmp/db_list_current.txt"
EMAIL="admin@example.com"

# Get current list of databases
mysql -u username -p$(cat /path/to/.mysql_secret) -N -e "SHOW DATABASES;" > $CURRENT_FILE

# If previous file exists, compare
if [ -f $PREVIOUS_FILE ]; then
    NEW_DBS=$(comm -23 $CURRENT_FILE $PREVIOUS_FILE)
    if [ ! -z "$NEW_DBS" ]; then
        echo "New databases detected: $NEW_DBS" | mail -s "Database Inventory Change" $EMAIL
    fi
fi

# Save current as previous for next run
cp $CURRENT_FILE $PREVIOUS_FILE

Output Processing

For JSON output:

mysql -u username -p -e "SELECT SCHEMA_NAME as database_name FROM information_schema.SCHEMATA;" --json

For CSV output:

mysql -u username -p -e "SELECT SCHEMA_NAME FROM information_schema.SCHEMATA;" -B | sed 's/\t/,/g' > databases.csv

Troubleshooting Common Issues

Even experienced administrators occasionally encounter issues when trying to list databases.

Permission Denied Errors

If you receive “Access denied” errors:

1. Verify user privileges:

   SHOW GRANTS FOR 'username'@'localhost';

2. Grant necessary permissions if missing:

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

3. Check if the user exists and from which hosts it can connect:

   SELECT user, host FROM mysql.user WHERE user = 'username';

Connection Problems

For “Can’t connect to MySQL server” errors:

1. Check if MySQL is running:

   sudo systemctl status mysql

2. Verify the server is listening on the expected port:

   sudo netstat -tlnp | grep mysql

3. Check firewall settings:

   sudo iptables -L | grep 3306

MySQL Service Issues

If the MySQL service fails to start:

1. Check error logs:

   sudo tail -f /var/log/mysql/error.log

2. Check for configuration issues:

   mysqld --verbose --help

3. Verify disk space availability:

   df -h

Best Practices and Security Considerations

Implementing consistent practices improves database management efficiency and security.

Naming Conventions

Establish clear naming patterns for databases:

• Use lowercase with underscores for readability
• Include environment indicators (e.g., app_prod, app_dev)
• Consider including version information for multiple versions
• Avoid spaces and special characters

Document your naming conventions for team consistency.

Security Best Practices

Protect your database environment:

• Create purpose-specific users instead of using root
• Grant minimum necessary privileges
• Use encrypted connections with SSL/TLS
• Regularly audit user permissions
• Implement network-level restrictions where possible

A secure approach to listing databases:

mysql --defaults-file=~/.my.cnf --ssl -e "SHOW DATABASES;"

Documentation Recommendations

Maintain comprehensive database documentation:

• Create a database inventory document
• Document purpose, owner, and criticality for each database
• Keep creation and expected lifecycle information
• Track size growth patterns
• Document backup and recovery procedures

Automation can help keep documentation current:

#!/bin/bash
# Generate database inventory document
mysql -u username -p$(cat /path/to/.mysql_secret) -e "
SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)',
    COUNT(DISTINCT table_name) AS 'Tables',
    (SELECT COUNT(*) FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA = table_schema) AS 'Routines',
    MAX(create_time) AS 'Last Table Created'
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY table_schema;" > /path/to/database_inventory.txt