Reverse Engineering a MySQL Database: A Developer's Guide

What INFORMATION_SCHEMA exposes about a MySQL schema

For backend developers who have inherited an existing MySQL database; schema introspection, relationship modeling, and offline diagrams are explained where they appear.

When you connect to a MySQL instance to discover its architecture, your client queries the INFORMATION_SCHEMA data dictionary. The MySQL 8.4 manual describes INFORMATION_SCHEMA as providing access to database metadata: information about the server such as the name of a database or table, the data type of a column, or access privileges, and it notes that other terms used for this information are data dictionary and system catalog[1]. Because introspection reads metadata rather than scanning data pages, reverse engineering a schema generates minimal overhead and does not lock application tables.

The core structural dictionary tables include TABLES, COLUMNS, and STATISTICS. TABLES exposes the storage engine, row format, and table comments. COLUMNS defines the ordinal position, data type, nullability, and default value for every field. STATISTICS details indexes, listing index names, indexed column sequences, and uniqueness rules.

To map relationships between tables, introspection tools query KEY_COLUMN_USAGE and join it against REFERENTIAL_CONSTRAINTS. Per the MySQL 8.4 manual, the KEY_COLUMN_USAGE table describes which key columns have constraints, and if the constraint is a foreign key, COLUMN_NAME is the column of the foreign key, not the column that the foreign key references[2]. For foreign key constraints, REFERENCED_TABLE_NAME and REFERENCED_COLUMN_NAME contain the parent table and target key.

You can inspect declared foreign keys directly in SQL by querying these views for your database:

SELECT 
 kcu.TABLE_NAME AS child_table,
 kcu.COLUMN_NAME AS foreign_key_column,
 kcu.REFERENCED_TABLE_NAME AS parent_table,
 kcu.REFERENCED_COLUMN_NAME AS primary_key_column,
 rc.UPDATE_RULE,
 rc.DELETE_RULE
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
 ON kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
 AND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
WHERE kcu.TABLE_SCHEMA = 'shop_db'
 AND kcu.REFERENCED_TABLE_NAME IS NOT NULL;

Reading structure with SHOW CREATE TABLE and mysqldump

Command-line utilities and administrative statements provide immediate textual representations of table definitions in MySQL. When you evaluate an individual table or need to inspect exact table options, the MySQL 8.4 manual states that SHOW CREATE TABLE shows the CREATE TABLE statement that creates the named table, requires some privilege for that table, and also works with views[3].

Running SHOW CREATE TABLE reveals the active storage engine, character sets, check constraints, indexes, and declared foreign key constraints:

SHOW CREATE TABLE orders;

CREATE TABLE orders (
 order_id int NOT NULL AUTO_INCREMENT,
 customer_id int NOT NULL,
 placed_at datetime NOT NULL,
 total_amount decimal(10,2) NOT NULL,
 PRIMARY KEY (order_id),
 KEY idx_customer (customer_id),
 CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

For an entire database, mysqldump extracts the complete schema structure across all tables, views, triggers, and routines. Passing the --no-data flag instructs mysqldump to bypass table rows completely, writing only the DDL statements into a single SQL script[4].

mysqldump -u root -p --no-data --routines --events shop_db > shop_db_schema.sql

While SHOW CREATE TABLE and mysqldump produce precise SQL text, text files force you to trace foreign key references by hand across hundreds of lines of DDL. Text dumps cannot construct interactive diagrams or reveal implicit relations that your database engine was never configured to enforce.

Why a MyISAM schema introspects with no relations

Storage engine selection dictates whether MySQL records and enforces foreign key constraints. The MySQL 8.4 manual explains that foreign keys permit cross-referencing related data across tables and that foreign key constraints help keep that related data consistent, with the constraint defined on the child table; among its conditions and restrictions, parent and child tables must use the same storage engine[5]. When an application executes a CREATE TABLE statement containing a FOREIGN KEY definition on an InnoDB table, MySQL validates the clause, creates the supporting index if it does not already exist, and writes the constraint metadata into the data dictionary.

Legacy schemas built on the MyISAM storage engine behave differently. MyISAM does not support foreign key constraints[6]. When a CREATE TABLE script with a FOREIGN KEY clause runs against MyISAM, the server parses the clause without throwing a syntax error, but silently discards the constraint definition. The relationship is never written to disk, never recorded in the MySQL data dictionary, and never surfaced in INFORMATION_SCHEMA.KEY_COLUMN_USAGE.

Application-level architectures frequently produce the same outcome on InnoDB tables. Frameworks using Object-Relational Mapping (ORM) patterns, microservice data stores, and horizontally sharded architectures often manage referential integrity inside application logic rather than through database constraints. In these databases, columns such as customer_id or account_id exist across tables, but MySQL holds no foreign key definitions in its system catalog.

Before assuming an introspection tool failed to parse your schema relationships, check the storage engine of your tables:

SELECT TABLE_NAME, ENGINE, TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'shop_db';

If the query returns MyISAM across your tables, or if your application code manages integrity without database-level constraints, your reverse-engineered model will draw every table as an isolated entity with no connecting relationship lines.

Connecting DbSchema over JDBC to MySQL

DbSchema queries metadata catalogs through a standard JDBC driver interface to convert system views into a graphical diagram. In MySQL, the terms database and schema refer to the same catalog object, so when you connect DbSchema to MySQL, selecting a schema points directly to the targeted database catalog.

DbSchema connects to MySQL using the standard connection URL format jdbc:mysql://host:port/database. When you establish a new connection, DbSchema downloads the verified MySQL JDBC driver automatically, eliminating manual classpath configuration and driver installation steps.

To reverse-engineer a live MySQL database into an interactive MySQL ER diagram:

1. Launch DbSchema and click Connect to Database on the start screen.
2. Select MySQL from the database vendor list.
3. Enter your host address, port (3306 by default), database name, and authentication credentials.
4. Select the target database catalog to introspect.
5. Click Connect to download the JDBC driver, query INFORMATION_SCHEMA, and generate the visual diagram canvas.

The introspection process reads table definitions, column types, primary keys, and foreign keys directly from the live catalog, organizing the tables into a visual canvas where you can inspect schema layout and index coverage immediately.

What the offline design model file holds

Connecting to a database creates an offline design model file saved locally in standard XML format. This model file completely decouples architecture review and schema documentation from live production database connections.

Because the model file stores schema definitions in plain-text XML, you can open and inspect the file in any text editor, commit it to version control systems like Git, and diff structural changes between releases without exporting separate DDL scripts.

The design model file preserves several distinct layers of metadata:

• Physical schema definitions including tables, data types, nullability, default values, and primary keys
• Declared and virtual foreign key relationships
• Diagram canvas layouts, custom table positions, color codings, and callout annotations
• Saved SQL queries and execution parameters from the integrated SQL editor
• Visual Query Builder join configurations and filter states

The offline model file holds structural metadata only. It does not cache or export table data rows, so you can design schemas, explore relationships, and share the model with your team without handling sensitive production data in local files. A free SQL client is enough to open a model file, adjust visual layouts, and run queries against the live database.

Drawing the relations with virtual foreign keys

When you reverse-engineer a MyISAM database or an ORM-driven schema without declared constraints, the diagram displays tables without relationship lines. Adding database-level constraints with ALTER TABLE to fix the diagram is often impossible on production systems, where adding constraints requires table locks, risks breaking legacy data, or conflicts with sharding strategies. Dropping a constraint later needs the same ALTER TABLE statement, so a mistaken constraint on a live table is not simply thrown away.

Virtual foreign keys solve this problem inside the design model. A virtual foreign key is a relationship definition stored exclusively within the local model file. It connects columns across tables visually without executing ALTER TABLE statements or modifying schema objects in MySQL.

Consider an e-commerce schema where orders and customers share a relation that was never declared in the database engine:

CREATE TABLE customers (
 customer_id int PRIMARY KEY,
 company_name varchar(100) NOT NULL
) ENGINE=MyISAM;

CREATE TABLE orders (
 order_id int PRIMARY KEY,
 customer_id int NOT NULL,
 total_amount decimal(10,2) NOT NULL
) ENGINE=MyISAM;

In DbSchema, you create a virtual foreign key by dragging the customer_id column from the orders table box and dropping it onto the customer_id column in the customers table box. In the relationship dialog, confirm the join type and select Virtual Foreign Key.

Creating the virtual foreign key draws the relationship line on the diagram canvas and makes relational data exploration possible. When you open the Relational Data Explorer, selecting a record in the customers table automatically loads and filters the related records from the orders table over the virtual key, so you can traverse relationships across your schema without altering production tables.

Reviewing the full schema

Reverse engineering an inherited MySQL database provides immediate clarity on table structures, column definitions, and index coverage — a starting point for documenting a schema nobody wrote down. When databases lack engine-level constraints due to MyISAM storage engines or application-managed ORM layers, introspection through standard catalog queries only tells half the story.

Download DbSchema Community Edition, connect to your MySQL database over JDBC, and reverse-engineer your tables into an interactive ER diagram. By defining virtual foreign keys inside your local model file, you can map out hidden relationships and explore cross-table data without touching live production constraints.

Frequently asked questions

How do I extract a MySQL database structure without data?

You can extract the structure using the mysqldump utility with the --no-data flag. This produces a logical backup containing only the SQL statements required to recreate the tables and constraints, without exporting any of the actual rows.

Why does my MySQL schema show no foreign keys?

If your schema was built using the MyISAM storage engine, foreign keys are parsed but ignored and never saved to INFORMATION_SCHEMA. Applications relying on an ORM to enforce integrity in code will also leave the database itself with no declared relations.

Where does MySQL store foreign key information?

MySQL stores foreign key definitions in the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table. It contains the schema name, table name, column name, and the referenced table and column for every constraint the InnoDB engine enforces.

What is the difference between a database and a schema in MySQL?

In MySQL, a database and a schema are treated as the exact same object. When you connect using a JDBC driver, selecting a database in the connection dialog is equivalent to selecting a schema, unlike in PostgreSQL or Oracle where schemas reside inside databases.

How can I draw relations in a MySQL diagram if the database lacks foreign keys?

You can use DbSchema to create virtual foreign keys. These relations live exclusively in the offline XML design model file to connect tables visually and allow data exploration, without requiring an ALTER TABLE command or locking production tables.

Sources

1. dev.mysql.com
2. dev.mysql.com
3. dev.mysql.com
4. dev.mysql.com
5. dev.mysql.com
6. dev.mysql.com