MySQL System Databases: mysql, information

Every MySQL server comes with four databases you did not create. They hold the server’s own information. You will read from them often, and you should rarely write to them.

query.sql

SQL

SHOW DATABASES;

Output

OUTPUT

+--------------------+
| Database           |
+--------------------+
| information_schema |
| learn              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set

Database	What it holds	Can you change it?
`mysql`	Users, privileges, roles, time zones, help text, server-internal tables	No, use `CREATE USER`, `GRANT` and so on instead
`information_schema`	Read-only views that describe your databases, tables, columns, indexes and privileges	No, read-only
`performance_schema`	Server performance data: what queries ran, waits, memory, locks	No (it can be configured)
`sys`	Friendly views built on `performance_schema`, for easier performance queries	No

`information_schema`: describe your data

This is the standard way to ask “what exists?”:

query.sql

SQL

CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, price DECIMAL(8,2));

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'learn' AND table_name = 'products'
ORDER BY ordinal_position;

Output

OUTPUT

+-------------+-----------+-------------+
| COLUMN_NAME | DATA_TYPE | IS_NULLABLE |
+-------------+-----------+-------------+
| id          | int       | NO          |
| name        | varchar   | NO          |
| price       | decimal   | YES         |
+-------------+-----------+-------------+
3 rows in set

It is based on the SQL standard, so the idea works in other databases too (the details differ).

`mysql`: accounts and privileges

The user accounts live in mysql.user, and you can read them with a suitable account:

query.sql

SQL

SELECT user, host, plugin FROM mysql.user WHERE user IN ('root', 'mysql.sys') ORDER BY user, host;

Output

OUTPUT

+-----------+-----------+-----------------------+
| user      | host      | plugin                |
+-----------+-----------+-----------------------+
| mysql.sys | localhost | caching_sha2_password |
| root      | localhost | caching_sha2_password |
+-----------+-----------+-----------------------+
2 rows in set

Never UPDATE or INSERT into these tables yourself. Use CREATE USER, ALTER USER and GRANT, which also refresh the server’s in-memory copy and keep everything consistent. In MySQL 8 the data dictionary tables are hidden and managed by the server, which protects you from many past mistakes.

`performance_schema` and `sys`

These answer “what is the server doing, and what is slow?”. For example, to see the slowest statement groups:

query.sql

SQL

SELECT 1 AS later_in_the_course;

Output

OUTPUT

+---------------------+
| later_in_the_course |
+---------------------+
|                   1 |
+---------------------+
1 row in set

(We will use sys views such as sys.statement_analysis in the Query Performance chapter. They exist only when performance_schema is on, which is the default.)

Safety rules

Never create your own tables inside mysql, sys, information_schema or performance_schema.
Never run DROP DATABASE mysql. The server can no longer manage accounts.
A backup of “all databases” includes mysql so that you can restore users too. Plan for that (Backup and Restore).
Application accounts do not need access to any of them.

info

A common question

“Where is my data stored?” Not in these. Your tables are in the databases you created. The system databases are the server’s own notebook.

information_schema: describe your data

mysql: accounts and privileges

performance_schema and sys

Safety rules

A common question

`information_schema`: describe your data

`mysql`: accounts and privileges

`performance_schema` and `sys`