O'Reilly logo

The Database Hacker's Handbook: Defending Database Servers by Bill Grindlay, John Heasman, Chris Anley, David Litchfield

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Examining the Logical Database Architecture

This section covers the following:

  • Schemas, tables, views, and so on.
  • Does the database support batched queries?
  • Does the database support procedures, functions, and triggers?

MySQL Logical Database Architecture

MySQL has a relatively simple default system schema. The MySQL database contains the following tables:

mysql> show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv    |
| db              |
| func            |
| help_category   |
| help_keyword    |
| help_relation   |
| help_topic      |
| host            |
| tables_priv     |
| user            |
+-----------------+

Tables in MySQL that are created with the default MyISAM storage engine are stored by default in separate files, three files per table. For each database, there is a directory beneath the MySQL root directory with the same name as the database. Within this directory, there are normally three files per table, <tablename>.frm (the definition of the structure of the table), <tablename>.MYI (which contains details of any indexes available on the table), and <tablename>.MYD (which contains the actual data for the table).

This file-per-table approach leads to a peculiarity that is almost unique to MySQL. On most platforms, the files that make up the tables are not held locked, with the exception of the mysql/user.myd and .myi files. This means that, should users gain the ability to modify the table files, they are effectively modifying the table data itself. Also, on most platforms it is possible ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required