MySQL and MariaDB: listing users and databases

Short answer: SHOW DATABASES; lists every database the connected user can see; SELECT User, Host FROM mysql.user; lists every account, which SHOW DATABASES has no equivalent for on its own. Both work identically on MySQL and MariaDB, since MariaDB started as a drop-in fork and kept the same system tables and syntax for this.

SHOW DATABASES: what you can actually see

SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql               |
| performance_schema |
| sys                 |
| myapp               |
| wordpress           |
+--------------------+

information_schema, mysql, performance_schema and sys are system databases present on every install, not something you created — your own databases are whatever is left after those four. This list only shows databases the connected account has at least one privilege on: connect as root and you see everything; connect as an application account created for one database and you often see only that one, which is expected behaviour, not a bug.

Tables inside a database

USE myapp;
SHOW TABLES;
SHOW TABLES FROM myapp;    -- without switching first

Listing user accounts: there is no SHOW USERS

Neither MySQL nor MariaDB has a SHOW USERS statement — user accounts live in a real table, mysql.user, and you query it like any other table:

SELECT User, Host FROM mysql.user;

+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| root             | localhost |
| mariadb.sys      | localhost |
| myapp            | localhost |
| myapp            | %         |
+------------------+-----------+

The same user name with two different Host values is two separate accounts with potentially different passwords and privileges, not one account that happens to connect from two places — MySQL and MariaDB key every account on the (user, host) pair together. % means “any host”, so a server with both myapp@localhost and myapp@% picks whichever is the more specific match for where the connection actually came from.

Why this needs an account with access to mysql.user

A regular application account usually cannot read mysql.user at all and gets ERR_ACCESS_DENIED or an empty result — it needs the SELECT privilege on the mysql database specifically, which root or another administrative account has by default. If you only have a non-root account and need this list, connect as root once rather than trying to grant yourself extra privileges from a limited account.

Checking what a user is actually allowed to do

SHOW GRANTS;

SHOW GRANTS FOR 'myapp'@'localhost';

+---------------------------------------------------------------------+
| Grants for myapp@localhost                                          |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `myapp`@`localhost`                            |
| GRANT ALL PRIVILEGES ON `myapp`.* TO `myapp`@`localhost`             |
+---------------------------------------------------------------------+

USAGE ON *.* means no real global privileges, just the ability to connect; the line below it is where the actual access is — here, every privilege but only on the myapp database, which is the shape a well-scoped application account should have. An account you expect to be limited that instead shows GRANT ALL PRIVILEGES ON *.* has full admin rights on everything, which is worth catching before it is used anywhere untrusted.

Creating a user and giving it exactly one database

CREATE USER 'myapp'@'localhost' IDENTIFIED BY 'a-strong-password';
GRANT ALL PRIVILEGES ON myapp.* TO 'myapp'@'localhost';
FLUSH PRIVILEGES;

FLUSH PRIVILEGES is only needed after editing the grant tables directly (rare);CREATE USER and GRANT take effect immediately on their own, but running it anyway is harmless and a common habit from older guides. The full install-from-scratch walkthrough, including setting the root password itself, is in installing MariaDB/MySQL on Ubuntu and Debian.

Removing a user

DROP USER 'myapp'@'localhost';

Dropping the user does not drop its database — those are managed independently.DROP DATABASE myapp; removes the database and everything in it, with no undo, so back it up first with mysqldump if there is any doubt.

How big is each database?

SELECT table_schema AS db,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS size_mb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_mb DESC;

information_schema is itself a database — a virtual one built from the server’s own metadata — which is why this query reads from a table inside it rather than needing a separate command. It is the fastest way to see which database is actually using the disk space check disk usage showed filling up.

mysql: command not found, or Access denied

If the mysql client itself is missing, or the server rejects every password you try, those are both covered with their own fixes in MySQL Access denied for user ‘root’@‘localhost’, which also explains the socket-authentication default that trips people up on a fresh Debian or Ubuntu install.

Frequently asked