Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.1Mb
PDF (A4) - 38.1Mb
PDF (RPM) - 37.3Mb
HTML Download (TGZ) - 10.2Mb
HTML Download (Zip) - 10.3Mb
HTML Download (RPM) - 8.9Mb
Man Pages (TGZ) - 217.0Kb
Man Pages (Zip) - 329.9Kb
Info (Gzip) - 3.5Mb
Info (Zip) - 3.5Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  MySQL sys Schema  /  Using the sys Schema

26.2 Using the sys Schema

You can make the sys schema the default schema so that references to its objects need not be qualified with the schema name:

mysql> USE sys;
Database changed
mysql> SELECT * FROM version;
+-------------+-----------------+
| sys_version | mysql_version   |
+-------------+-----------------+
| 1.5.0       | 5.7.9-debug-log |
+-------------+-----------------+

(The version view shows the sys schema and MySQL server versions.)

To access sys schema objects while a different schema is the default (or simply to be explicit), qualify object references with the schema name:

mysql> SELECT * FROM sys.version;
+-------------+-----------------+
| sys_version | mysql_version   |
+-------------+-----------------+
| 1.5.0       | 5.7.9-debug-log |
+-------------+-----------------+

Examples in this chapter usually assume sys as the default schema.

The sys schema contains many views that summarize Performance Schema tables in various ways. Most of these views come in pairs, such that one member of the pair has the same name as the other member, plus a x$ prefix. For example, the host_summary_by_file_io view summarizes file I/O grouped by host and displays latencies converted from picoseconds to more readable values (with units);

mysql> SELECT * FROM host_summary_by_file_io;
+------------+-------+------------+
| host       | ios   | io_latency |
+------------+-------+------------+
| localhost  | 67570 | 5.38 s     |
| background |  3468 | 4.18 s     |
+------------+-------+------------+

The x$host_summary_by_file_io view summarizes the same data but displays unformatted picosecond latencies:

mysql> SELECT * FROM x$host_summary_by_file_io;
+------------+-------+---------------+
| host       | ios   | io_latency    |
+------------+-------+---------------+
| localhost  | 67574 | 5380678125144 |
| background |  3474 | 4758696829416 |
+------------+-------+---------------+

The view without the x$ prefix is intended to provide output that is more user friendly and easier for humans to read. The view with the x$ prefix that displays the same values in raw form is intended more for use with other tools that perform their own processing on the data. For additional information about the differences between non-x$ and x$ views, see Section 26.4.3, “sys Schema Views”.

To examine sys schema object definitions, use the appropriate SHOW statement or INFORMATION_SCHEMA query. For example, to examine the definitions of the session view and format_bytes() function, use these statements:

mysql> SHOW CREATE VIEW session;
mysql> SHOW CREATE FUNCTION format_bytes;

However, those statements display the definitions in relatively unformatted form. To view object definitions with more readable formatting, access the individual .sql files available from the sys schema development website at https://github.com/mysql/mysql-sys.

Neither mysqldump nor mysqlpump dump the sys schema by default. To generate a dump file, name the sys schema explicitly on the command line using either of these commands:

mysqldump --databases --routines sys > sys_dump.sql
mysqlpump sys > sys_dump.sql

To reinstall the schema from the dump file, use this command:

mysql < sys_dump.sql

User Comments
Sign Up Login You must be logged in to post a comment.