08 - Advanced Database Artifacts

Question 1

In what cases do we need to analyze databases?

Answer 1

• Fraud
• Hacking
• Corporate/Governmental Espionage
• etc.

Question 2

What are the difficulties of DBMS Analysis?

Answer 2

• Large organisations
• server-level machines
• accessing the files
• privacy / confidentiality

Question 3

What are the five layers of a MySQL database?

Answer 3

1. Connectors
   
   • Database application. Does not include important forensics artefacts.
2. Connection Manager
   
   • Handles user connection to the MySQL system
3. Query Processing
   
   • Most interesting: Query cache & buffers. Stores frequently used information. There are three common ways to create caching: manual caching, internal caching, mem cache distributed system.
4. Storage Engines
   
   • Sub-system that manages tables. MySQL works normally with two: MyISAM, InnoDB.
5. File System / Main storage
   
   • Data, Index, Logs, Backups as files in the file system

Question 4

What are the MySQL default data directories for Linux and Windows?

Answer 4

• Linux
  
  • /usr/local/mysql/data
  • /var/lib/mysql….
• Windows
  
  • C:\Program Data\MySQL\
  • C:\Documents & Settings\All User\Application Data\MySQL

Question 5

How can you locate the MySQL config?

Answer 5

• Look into my.cnf or my.ini
• Run # mysqld –verbose –help
• SQL Query: mysql> SHOW VARIABLES LIKE ‘datadir’
• Run $ mysqladmin variables

Question 6

How does the MySQL Data Directory Structure looks like?

Answer 6

• Each database has a database directory.
• Tables, views, triggers correspond to files in the database directory. E.g: *.frm file (used to define the format of a table).
• Other files: process ID, status and log files, DES key file, server’s SSL certificate.

Question 7

What are the two most common MySQL Storage Engine and what are they for?

Answer 7

MySQL can use different storage engines. Two most popular engines are InnoDB & MyISAM.

Features are:

• transaction support (Memory or storage space)
• table-level features
• locking
• index implementation, foreign keys support, buffering, file storage, backup

Question 8

What are FRM files?

Answer 8

Each table has a .frm file. Used to define the format of a table. File name is also the table name. Normally it is 2GB or 4GB in size.

• Header Sig (0x00 for 2 Bytes): 0xFE 01
• FRM ver (0x02 for 1 Byte)
• DB type (0x03 for 1 Byte) // InnoDB: 12, MyISAM: 9

Other important information in .frm file:

• 0x2F (4Bytes) - Key length
• 0x33 (4 Bytes) - MySQL Version ID

Question 9

How large is the FRM header?

Answer 9

64 Byte

Question 10

At what offset does the FRM file key information start?

Answer 10

0x1000

Question 11

What are the important information of the .frm File Key Information Section?

Answer 11

• 0x1000 (1) : 0: no key
• 0x1001 (1) : Number of keys
• 0x1003 - 0x100A (0) : Key header
• 0x100E - 0x1016 (9) : Key part
• 0x1017 - : Separator + Name of key + separator…

Question 12

What are the important information of the .frm File Column Information Section?

Answer 12

• 0x2100 (2) : Fixed value (01 00)
• 0x2102 (2) : Number of columns
• …
• Before EOF : Column names

Column entry is 17 Bytes in size. Column names are at the EOF (before FF 00 w/ separator FF).

Data Type Definition: FE= char, 02=smallint, 03=int)

Question 13

What type of logs are present in MySQL?

Answer 13

• MySQL logs (Server level)
  
  • Error, binary, general query, slow query, relay
• Storage engine logs (Storage level)
  
  • InnoDB: undo log, redo log

Question 14

What are the default names of the following logs:

1. Error log
2. Binary log
3. General query log
4. Slow query log

Answer 14

1. HOSTNAME.err
2. HOSTNAME-bin.XXXXX
3. HOSTNAME.log
4. HOSTNAME-slow.log

Question 15

How can you display the binary log?

Answer 15

# mysqlbinlog mysql-bin.00001 > mysql-bin00001.txt

The binary log is a set of log files that contain information about data modifications made to a MySQL server instance. The log is enabled by starting the server with the –log-bin option. It contains all statements that update data (maybe passwords).

Question 16

How many log files does the storage engine InnoDB create?

Answer 16

By default, InnoDB creates two redo log files (or just log files) ib_logfile0 and ib_logfile1 within the data directory of MySQL. In MySQL versions 5.6.8 and above, the default size of each redo log file is 48MB each:

Undo log

• Rollback transactions

Redo log

• Crash recovery
• ib_logfile0, ib_logfile1

Question 17

What are MySQL caches and what are the three types of MySQL cache?

Answer 17

Caching: return data from a query quickly.

• Manual cache tables
• Internal cache (query cache)
• Distributed cache

Question 18

For what is cache analysis useful?

Answer 18

• Recent accessed database
• Misuse DBMS server (unauth. Access or SQL injection attacks).
• Can be used to reconstruct history of SQL execution

Question 19

What are drawbacks of analyzing MySQL query cache?

Answer 19

• Only SELECT statement
• Not efficient for frequently updating table
• Hash values
• Case sensitive: ‘select’ vs ‘SELECT’
• SQL_NO_CACHE

Question 20

What are other MySQL caches?

Answer 20

• Record cache - Contains all of the records in a table
• Table cache - Contains the most recently used table
• Hostname cache - Used for quick lookup and name resolving
• Heap table cache - Group by OR Distinct table