Perform MySQL Forensics
Perform MySQL Forensics in this MySQL database is one of the extensively used open source databases and freely available with unrestricted redistribution, providing users with full access to the source code. The database can contain different pluggable storage engines to suit the application. It supports transactions with the integration InnoDB or BDB storage engines for safer handling of parallel write operations required in enterprise environments.
As per the information security policies, administrators need to audit high-performance databases regularly to ensure the data integrity and data security. They should even be able to detect database manipulations.
Information auditing needs to be performed on regularly to find out if any part of the database is altered intentionally or accidentally by users at any point of time through bypassing auditing system. Such suspected behavior is to be inspected and analyzed by Database forensic investigators. The forensic approach for MySQL databases varies with the database engine used in the db server.
Related Product : Computer Hacking Forensic Investigator | CHFI
Internal Architecture of MySQL
The architecture of MySQL is based on a tiered architecture, which is the combination of subsystems and support components interacting with one another to read, analyze and execute the queries made to the database server, and return the results.
- The connectors act as a medium for the clients to connect to the SQL Server
- The Connection Pool handles all the client connection needs such as user authentication, memory checks, thread processing, caches, etc.
- These are the ancillary tools grouped by administration and enterprise services
- The SQL Interface, Parser, Optimizer, caches, and buffers collectively form a key part of the database server.
- The SQL Interface layer acts as an interface by accepting the SQL statements and delivers the result to the user. In spite of Iv1001 having the majority of the options that are not ANSI compliant, the layer supports the ANSI SQL standard. Whenever the client sends a request to the server through network communication pathways, the server creates a thread. Then the server analyzes the SQL command and stores the parts in an internal data structure.
- Parser validates the SQL queries entered by a user. It constructs a query structure, which denotes the query statement (SQL) within the memory as a tree structure. This tree structure consists of a group of tables and field names referenced, join conditions, etc., and its main task is to check the accuracy of SQL statement. Once the SQL statement is validated, the Parser passes the query structure to the query processor and then, to the query optimizer.
- The Query Optimizer’s primary task is to validate the existence of tables, and the level of access to the application for a user. The Optimizer then returns the errors (if any) and the control returns to the thread manager or listener. The optimizer works on the SELECT-PROJECT-JOIN strategy that attempts to restructure the query.
- A set of library methods designed to implement a specific query also control its execution and return the results from each of the execution methods using the network communication pathways library.
- The query cache plays a vital role in query optimization and execution subsystem. It caches the query structure as well as the query results.
- Caches and buffers ensure that commonly used data are made available in an efficient way. MySQL database server contains various types of caches, including table cache, key cache, privilege cache, Hostname Cache, Record Cache; and miscellaneous caches such as join buffer cache.
Storage engines are used to create, read, and update data within a database. Various Storage engines supported by MySQL are:
1. InnoDB (default storage engine in MySQL version >= 5.5.5)
- Is often used when you need to use transactions
- Supports traditional ACID (Atomicity, Consistency, Isolation, Durability) transactions and foreign key constraints
- Provides 64TB storage limit per table
- Offers MVCC/Snapshot reads
- Supports crash recovery
- Used in On-Line Transaction Processing (OLTP) systems
2. MyISAM (default storage engine in My SQL version < 5.5.5)
- Provides unlimited data storage
- Offers data compression technique
- Handles high-speed data loads
- Enables efficient storage
3. Memory
- It is an in-memory table that Implements a hashing mechanism for faster retrieval of commonly used data
- Offer validity for the data stored in memory only during the MySQL session
4. BDB
- Stands for Berkeley database
- Considered an alternative to InnoDB
- Supports additional transaction methods such as COMMIT and ROLLBACK
5. ARCHIVE
- Provides unlimited storage limit in a compressed format
- Well known for storing and retrieving huge volumes of seldom-accessed archival or historical data
- Supports automatic data compression
- Offers MVCC (Multiversion concurrency control)/Snapshot read
- Limits operations to INSERT and SELECT
6. BLACKHOLE
- Allows the system to write the data, however, that data is never saved
- Writes SQL statements to logs when binary logging is enabled
- Allows Database administrators to disable data injection in the database temporarily by swapping the table type
7. MERGE
- Built using a series of MyISAM tables (with tuple layout or schema)
- Ensures that all the tables reside on the same system
- Blocks the replace operation
- Performs search and sort options more quickly
- Allows users to access data using SELECT, UPDATE, INSERT, and DELETE operations
- Widely used in very large database (VLDB) applications like data warehousing
8. FEDERATED
- Creates a single table reference from one or more database systems
- Hence, similar to Merge, but allows data/tables linkage across database servers
- Enables data translation during storage and retrievals
- Supports all SQL operations
- Does not require middleware for remote data access
- Extensively used in distributed or data mart environments
Also Read : Examine Evidence files using SQL Server Management Studio and ApexSQL DBA
Structure of the Data Directory
The data directory contains databases, tables and status files handled by the server. These data directories are organized in a tree-like structures by following hierarchic& structure of the Unix or Windows file systems:
- Every database corresponds to a directory under the data directory
- The tables of a database, correspond to the files of database directory
The default path to the data directory is mentioned below for the windows based machines
C:\ProgramData\MySQL\MySQL Server 5.n\ (or) C:\rnysql \data.
Status and log files stored in data directory include:
- Process ID file (HOSTNAME.pid), contains the process ID created when the server starts
- Error log (HOSTNAME.err), contains the information associated with the startup and shutdown events, and errors
- General query log (HOSTNAME.log), logs the client connections and activities
- Binary log (HOSTNAME-bin.nnnnnn), contains the events that describe the changes occurred in the database
- Binary log index (HOSTNAIV1E-bin.index), contains the list of all the binary log files currently available in the data directory
- Relay log (HOSTNAMErelay-bin.n), contains the events that describe the changes occurred in the database
- Relay log index (HOSTNAMErelay-bin.index), contains the list of all the relay log files currently available in the data directory
- Master info file (master.info) created by a replication slave server, that contains the essential parameters used for connecting to the master slave
- Relay log info file (relay-log.info) created by a replication slave server, that contains the status of relay log processing
- Slow query log (HOSTNAMEslow.log), a text fire that contains statements which take longer processing time
MySQL data directories contain all the databases managed by the servers. Their arrangement is shown in the screenshot below.
- Every database corresponds to a directory under the data directory
- Tables, views, and triggers of a database, correspond to the files of database directories
- The storage structures vary from the hierarchical implementation of databases.
- Database structures alter as per the storage engines used by MySQL.
MyISAM Storage Engine
In a non-partitioned filesystem, MyISAM storage engine stores each MyISAM table in three files. The files contain names that start with the table name and will have an extension to indicate the file type. The file extensions are .frm, .1v1Y1 and .NAYD. The .frm file stores the table format, the .MYI file stores the file index, while the .MYD file stores table data.
InnoDB Storage Engine
Table contents of InnoDB are represented by using tablespaces, and are of two types:
-
The shared tablespace
Users can configure InnoDB to use one tablespace file per table. In this shared tablespace, each InnoDB table contains two table-specific files in the database directory: The .frm file (as usual) and a .ibd file that contains the table’s data and indexes.
-
Individual tablespacas
Users can configure InnoDB to use one tablespace file per table. In this individual tablespace, each InnoDB table has two table-specific files in the database directory: The .frm file (as usual) and a .ibd file that contains the table’s data and indexes.
MySQL Forensics
MySQL is an open source relational database. Data entered in a MySQL database is duplicated and stored in multiple locations. Therefore, any users deleting data in the database either accidentally or intentionally will not completely delete the data. Forensic investigators can examine all the files containing a copy of the deleted data (in the data folder) and recover it.
MySQL Utility Programs For Forensic Analysis
The following utility programs help investigators to perform the forensic examination.
1. Mysqldump
– The utility allows you to dump a database or a collection of databases for backup purposes
– It generates a .sql file with CREATE table, DROP table and INSERT into the SQL statement of the source database
– It executes the .sql file on the destination database to restore the original database.
– Syntax: mysqldump [options] [db_name [tbl_ name …]]
2. mysqlaccess
– Checks the access privileges defined for host name, user name, etc.
– Validates access using the user, db, and host tables
– Syntax: mysqlaccess [host name [user name [cap name]]] [options]
3. myisamlog
– Processes the contents of MyISAM log file and perform recovery operation, display version information, , depending on the situation
– The default operations of the utility are update(-u) and recovery(-r)
– Syntax: myisamlog [options] [logfile-name [tbl_name] …]
4. myisamchk
– Views the status of the MyISAM table or checks, repairs, or optimizes them.
– Syntax: myisamchk [options] tbl name …
5. mysqlbinlog
– Reads the binary log files directly and displays them in text format
– Displays the content of bin logs (mysql-bin.nnnnnn) in text format
– Syntax: mysqlbinloig [options] log-file . . .
6. mysqldbexport
– Export metadata/data definitions
– Produces output in a variety of formats by making data extraction easier and
– suitable for the external application
– Syntax: mysqldbexport –server=user:pass@host:port:socket dbl, db2, db3
Questions related to this topic
- How do I find MySQL data directory?
- How do I view a SQL database file?
- How do I search for a specific data in a SQL database?
- What is a data directory?
This Blog Article is posted by
Infosavvy, 2nd Floor, Sai Niketan, Chandavalkar Road Opp. Gora Gandhi Hotel, Above Jumbo King, beside Speakwell Institute, Borivali West, Mumbai, Maharashtra 400092
Contact us – www.info-savvy.com
