MySQL SHOW DATABASES lists all databases the current user has access to. The team that uses SQL SELECT schema_name FROM information_schema.SCHEMATA has the same info with filtering. The team that uses mysqladmin has it for scripts. The user only sees databases they have any privilege on, so a non-root user sees fewer DBs than root.
Table of contents
- The SHOW DATABASES command
- Equivalent SQL
- The mysqladmin command
- System schemas
- Listing user databases by size
- MySQL 8.0 changes
- FAQ
The SHOW DATABASES command
After mysql -u root -p:
mysql> SHOW DATABASES;Returns:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| appdb |
+--------------------+The team that uses root sees all databases. The team that uses a non-root user sees only databases they have privileges on (typically excludes mysql, performance_schema).
Equivalent SQL
SELECT schema_name FROM information_schema.SCHEMATA
WHERE schema_name NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');The team that uses SQL has filterable output - exclude system schemas, sort, join with metadata.
The mysqladmin command
For scripts:
mysqladmin -u root -p urisOr as a SQL-style command:
mysql -u root -p -e "SHOW DATABASES;"The team that uses mysql -e has shell-friendly output for automation.
System schemas
MySQL has built-in schemas:
mysql: user accounts, privileges, plugins.information_schema: metadata about all objects.performance_schema: runtime performance data.sys: helper views on performance_schema.
The team that excludes these from app queries has cleaner output. The team that needs to manage MySQL itself uses them.
Listing user databases by size
Find the largest databases:
SELECT table_schema AS db,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;The team that uses this finds unexpectedly large DBs.
MySQL 8.0 changes
MySQL 8.0 added the sys schema with helper views like sys.schema_tables_with_full_table_scans. The team that uses sys schema views has useful pre-built queries.
FAQ
Why don’t I see all databases?
Your user doesn’t have privileges on the missing DBs. Root sees all; regular users see only what they have access to. The team that grants privileges sees more in SHOW DATABASES.
What’s the difference between SHOW DATABASES and SHOW SCHEMAS?
Same thing. SHOW SCHEMAS is a synonym for SHOW DATABASES in MySQL (and required syntax in standard SQL).
Can I filter SHOW DATABASES?
Yes - SHOW DATABASES LIKE 'app%'. The team that uses patterns finds specific DBs.
How do I see database sizes?
Use the information_schema.tables query above, or SELECT table_schema, SUM(data_length+index_length) FROM information_schema.tables GROUP BY table_schema.
What’s the mysql schema for?
Stores user accounts, privileges, plugins, time zone info. The team that modifies it (creating users, setting passwords) needs mysql access.
If you are sizing the infrastructure for the kind of project this post covers, the RunxBuild hosting calculator is the right place to model the line items. The compute, the memory, the storage, the bandwidth, the database - each one is a separate number, and the team’s mental model for the platform is the sum of those numbers. The RunxBuild dashboard is where the team sees the actual usage in one place.
Useful related references: