Lecture 14: Advanced topic: DBMS Forensics

Question 1

Name the log files at two different level

Answer 1

MySQL server level, five log files:
- Error, binary, general query (connect and disconnect, statement information), slow query (text of statements), relay

Storage engine logs:
- undo and redo log

Question 2

Explain the InnoDB log files

Answer 2

Undo log
* Rollback transactions

Redo log
* Crash recovery

Question 3

Name three types of MySQL Cache

Answer 3

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

Question 4

What can be derived from query cache analysis:

Answer 4

• Recent accessed database
• Misuse DBMS server
• Reconstruct history of SQL execution

Question 5

Challenges of query cache analysis

Answer 5

• Only SELECT statement
• Frequently updating table
• Hash values
• Case sensitive: ‘select’ vs. ‘SELECT’
• SQL_NO_CACHE

Question 6

Name other MySQL Caches

Answer 6

• Record cache
• Table cache
• Hostname cache
• Heap table cache