Introduction to Database Administrations
by K. Yue
1. Introduction
2. System Catalog
Example:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| test |
| world |
| yue_exp |
+--------------------+
7 rows in set (0.00 sec)
>
MySQL database
Example:
Try to add a new user by inserting into the user table: http://dev.mysql.com/doc/refman/5.6/en/adding-users.html. There are problems.
use mysql;
Try:
INSERT INTO user (Host,User,Password,select_priv, insert_priv, update_priv,ssl_cipher,x509_issuer,x509_subject)
VALUES ('localhost', 'abcde', PASSWORD('IloveDBMS'), 'Y', 'Y', 'Y', 'NULL', 'NULL', 'NULL');
FLUSH PRIVILEGES;
information_schema database
Example:
mysql> SELECT table_name, table_type, row_format, table_rows, avg_row_length
-> FROM information_schema.tables
-> WHERE table_schema = 'information_schema'
-> ORDER BY table_name DESC;
+---------------------------------------+-------------+------------+------------+----------------+
| table_name | table_type | row_format | table_rows | avg_row_length |
+---------------------------------------+-------------+------------+------------+----------------+
| VIEWS | SYSTEM VIEW | Dynamic | NULL | 0 |
| USER_PRIVILEGES | SYSTEM VIEW | Fixed | NULL | 1986 |
| TRIGGERS | SYSTEM VIEW | Dynamic | NULL | 0 |
| TABLE_PRIVILEGES | SYSTEM VIEW | Fixed | NULL | 2372 |
| TABLE_CONSTRAINTS | SYSTEM VIEW | Fixed | NULL | 2504 |
| TABLESPACES | SYSTEM VIEW | Fixed | NULL | 6951 |
| TABLES | SYSTEM VIEW | Fixed | NULL | 9450 |
| STATISTICS | SYSTEM VIEW | Fixed | NULL | 5753 |
| SESSION_VARIABLES | SYSTEM VIEW | Fixed | NULL | 3268 |
| SESSION_STATUS | SYSTEM VIEW | Fixed | NULL | 3268 |
| SCHEMA_PRIVILEGES | SYSTEM VIEW | Fixed | NULL | 2179 |
| SCHEMATA | SYSTEM VIEW | Fixed | NULL | 3464 |
| ROUTINES | SYSTEM VIEW | Dynamic | NULL | 0 |
| REFERENTIAL_CONSTRAINTS | SYSTEM VIEW | Fixed | NULL | 4814 |
| PROFILING | SYSTEM VIEW | Fixed | NULL | 308 |
| PROCESSLIST | SYSTEM VIEW | Dynamic | NULL | 0 |
| PLUGINS | SYSTEM VIEW | Dynamic | NULL | 0 |
| PARTITIONS | SYSTEM VIEW | Dynamic | NULL | 0 |
| PARAMETERS | SYSTEM VIEW | Dynamic | NULL | 0 |
| KEY_COLUMN_USAGE | SYSTEM VIEW | Fixed | NULL | 4637 |
| INNODB_TRX | SYSTEM VIEW | Fixed | NULL | 4534 |
| INNODB_LOCK_WAITS | SYSTEM VIEW | Fixed | NULL | 599 |
| INNODB_LOCKS | SYSTEM VIEW | Fixed | NULL | 31244 |
| INNODB_CMP_RESET | SYSTEM VIEW | Fixed | NULL | 25 |
| INNODB_CMPMEM_RESET | SYSTEM VIEW | Fixed | NULL | 29 |
| INNODB_CMPMEM | SYSTEM VIEW | Fixed | NULL | 29 |
| INNODB_CMP | SYSTEM VIEW | Fixed | NULL | 25 |
| GLOBAL_VARIABLES | SYSTEM VIEW | Fixed | NULL | 3268 |
| GLOBAL_STATUS | SYSTEM VIEW | Fixed | NULL | 3268 |
| FILES | SYSTEM VIEW | Fixed | NULL | 2677 |
| EVENTS | SYSTEM VIEW | Dynamic | NULL | 0 |
| ENGINES | SYSTEM VIEW | Fixed | NULL | 490 |
| COLUMN_PRIVILEGES | SYSTEM VIEW | Fixed | NULL | 2565 |
| COLUMNS | SYSTEM VIEW | Dynamic | NULL | 0 |
| COLLATION_CHARACTER_SET_APPLICABILITY | SYSTEM VIEW | Fixed | NULL | 195 |
| COLLATIONS | SYSTEM VIEW | Fixed | NULL | 231 |
| CHARACTER_SETS | SYSTEM VIEW | Fixed | NULL | 384 |
+---------------------------------------+-------------+------------+------------+----------------+
37 rows in set (0.08 sec)
performance_schema database
Example:
-- Number of schema with no table.
select count(*)
from information_schema.schemata s
left join information_schema.tables t on s.schema_name = t.table_schema
where t.table_name is null;
-- Show user account including some privilege information
select host, user, create_user_priv, show_db_priv
from user;
Notes:
Tools
HeidiSQL:
Classroom example follows.
PHPMyAdmin:
To PHPMyAdmin:
Classroom example follows.