Database Administrations

Introduction to Database Administrations

by K. Yue

1. Introduction

Tasks of (Traditional) Database Administration (Hoffer):
1. Analyzing and designing databases
2. Selecting DBMS and software tools
3. Installing/upgrading DBMS
4. Tuning database performance
5. Improving query processing performance
6. Managing data security, privacy, and integrity
7. Data backup and recovery
You may add, for examples,
1. Account management
2. Capacity planning
3. Troubleshooting
4. Configuration
Job tasks described by Bureau of Labor Statistics (BLS): Database Administrator
1. Ensure that organizational data is secure
2. Back up and restore data to prevent data loss
3. Identify user needs to create and administer databases
4. Ensure that the database operates efficiently and without error
5. Make and test modifications to the database structure when needed
6. Maintain the database and update permissions
7. Merge old databases into new ones
Need separate courses, certifications, experience, etc.

2. System Catalog

Named collections of meta data in SQL DBMS.
Important for DB administrators and developers to know.
Usually stored in relations.
Common terms with similar meaning: data dictionary, catalog, etc.
Thee major databases that are included with MySQL: mysql, information_schema and performance_schema.
There is also an example database: Sakila.

Example:

MySQL database

Store information about the MySQL users.
For example, the user table stores users, passwords (encrypted) and privileges.
So, to create a new user, you can either use the CREATE USER command or by directly inserting a new row into the USER table.

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

Store information about all databases in MySQL.
Contents are views: cannot be updated.
Triggers cannot be defined on tables in information_schema.

Example:

It is more flexible than the SHOW command.

performance_schema database

contain performance related server data.

Example:

-- Show user account including some privilege information
select host, user, create_user_priv, show_db_priv
from user;

Example:

Tables are stored using different 'storage engines.' The default storage engines in MySQL is InnoDB. To find out the storage engines and the numbers of tables using them:

select t.engine, count(*) as `table counts`
from information_schema.tables t
group by t.engine
order by `table counts` desc;

Running in my machine:

+--------------------+--------------+
| engine             | table counts |
+--------------------+--------------+
| InnoDB             |          289 |
| MEMORY             |           67 |
| PERFORMANCE_SCHEMA |           52 |
| NULL               |           34 |
| MyISAM             |           26 |
| Aria               |           11 |
| CSV                |            2 |
+--------------------+--------------+
7 rows in set (0.10 sec)

One row has 'NULL" as the storage engine, i.e. no storage engine. Those are for 'views,' which are not physically stored.

select if (t.engine is null, 'No engine (view)', t.engine) as engine,
count(*) as `table counts`
from information_schema.tables t
group by engine
order by `table counts` desc;

Result:

+--------------------+--------------+
| engine             | table counts |
+--------------------+--------------+
| InnoDB             |          289 |
| MEMORY             |           67 |
| PERFORMANCE_SCHEMA |           52 |
| No engine (view)   |           34 |
| MyISAM             |           26 |
| Aria               |           11 |
| CSV                |            2 |
+--------------------+--------------+
7 rows in set, 1 warning (0.10 sec)

Notes:

A MySQL account is composed of two parts: user and host.
Same user name but different hosts mean different accounts.
%: match any IP hosts.
localhost: may use Socket in Unix system, or map to 127.0.0.1.

Tools

There are many DB tools available with GUI.
Two popular choices relevant to this class:
HeidiSQL
PHPMyAdmin: Web-based.

HeidiSQL:

Hedi1

Classroom example follows.

PHPMyAdmin:

To PHPMyAdmin:

Make sure that PHP and PHPMyAdmin are enabled in the Web Server.
For local machine, usually go to: http://localhost/phpmyadmin/.
Use carefully as it is Web accessible.

Classroom example follows.