08 - Advanced Database Artifacts Flashcards
In what cases do we need to analyze databases?
- Fraud
- Hacking
- Corporate/Governmental Espionage
- etc.
What are the difficulties of DBMS Analysis?
- Large organisations
- server-level machines
- accessing the files
- privacy / confidentiality
What are the five layers of a MySQL database?
-
Connectors
- Database application. Does not include important forensics artefacts.
-
Connection Manager
- Handles user connection to the MySQL system
-
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.
-
Storage Engines
- Sub-system that manages tables. MySQL works normally with two: MyISAM, InnoDB.
-
File System / Main storage
- Data, Index, Logs, Backups as files in the file system
What are the MySQL default data directories for Linux and Windows?
-
Linux
- /usr/local/mysql/data
- /var/lib/mysql….
-
Windows
- C:\Program Data\MySQL\
- C:\Documents & Settings\All User\Application Data\MySQL
How can you locate the MySQL config?
- Look into my.cnf or my.ini
- Run # mysqld –verbose –help
- SQL Query: mysql> SHOW VARIABLES LIKE ‘datadir’
- Run $ mysqladmin variables
How does the MySQL Data Directory Structure looks like?
- 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.
What are the two most common MySQL Storage Engine and what are they for?
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
What are FRM files?
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
How large is the FRM header?
64 Byte
At what offset does the FRM file key information start?
0x1000
What are the important information of the .frm File Key Information Section?
- 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…
What are the important information of the .frm File Column Information Section?
- 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)
What type of logs are present in MySQL?
-
MySQL logs (Server level)
- Error, binary, general query, slow query, relay
-
Storage engine logs (Storage level)
- InnoDB: undo log, redo log
What are the default names of the following logs:
- Error log
- Binary log
- General query log
- Slow query log
- HOSTNAME.err
- HOSTNAME-bin.XXXXX
- HOSTNAME.log
- HOSTNAME-slow.log
How can you display the binary log?
# 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).
