Reverse Engineering a MySQL Database: A Developer's Guide

What INFORMATION_SCHEMA exposes about a MySQL schema

A MySQL database that has been in production for years arrives with credentials, a ticket that assumes you know it, and no diagram. Everything needed to draw one is already in the server: MySQL keeps the structure in INFORMATION_SCHEMA, which the 8.4 manual describes as 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[1].

The examples below run against a schema called shop_db with two tables in it:

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

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 ON UPDATE CASCADE
) ENGINE=InnoDB;

The structural dictionary tables you read most are TABLES, COLUMNS and STATISTICS. TABLES carries the storage engine, the row format and the table comment. COLUMNS gives the ordinal position, data type, nullability and default of every field. STATISTICS lists the indexes with their column order and their uniqueness.

Relationships take two of those views joined together. The 8.4 manual says KEY_COLUMN_USAGE describes which key columns have constraints, and that 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]; REFERENCED_TABLE_NAME and REFERENCED_COLUMN_NAME hold the parent side. REFERENTIAL_CONSTRAINTS adds the two referential actions, whose possible values are CASCADE, SET NULL, SET DEFAULT, RESTRICT and NO ACTION[3]:

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;

One row for one relationship is easy to read. A schema with two hundred tables answers the same query with a list you then have to hold in your head, which is the point at which a diagram stops being a nicety.

Reading structure with SHOW CREATE TABLE and mysqldump

For a single table, MySQL hands you the definition as SQL. The 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[4].

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 ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

The statement comes back with the engine and the character set filled in, which the original CREATE TABLE never spelled out. For the whole database, mysqldump writes the DDL of every table, view, trigger and routine into one script, and --no-data leaves the rows out[5].

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

Both give you exact SQL text, and both leave the relationships as something you trace by hand down a file. A dump of two hundred tables tells you that fk_orders_customer exists; it does not show you that six other tables hang off customers, and it cannot show a relationship the server was never told about.

Why a MyISAM schema introspects with no relations

The storage engine decides whether MySQL records a foreign key at all. The 8.4 manual explains that foreign keys let you cross-reference related data across tables and keep it consistent, with the constraint defined on the child table, and that among its restrictions the parent and child tables must use the same storage engine[6]. On an InnoDB table, CREATE TABLE validates the FOREIGN KEY clause, builds the supporting index if it is missing, and writes the constraint into the data dictionary.

A MyISAM table takes the same statement and keeps none of it. The manual is explicit: for storage engines other than InnoDB and NDB, MySQL Server parses and ignores the FOREIGN KEY syntax in CREATE TABLE statements[7]. No syntax error is raised, and nothing reaches KEY_COLUMN_USAGE, so the relationship exists only in the mind of whoever wrote the script.

InnoDB schemas reach the same state by another route. An application whose ORM enforces referential integrity in code, a service that owns its own tables, or a sharded layout that cannot afford cross-shard constraints all leave columns like customer_id sitting in several tables with nothing in the catalog tying them together. Before you conclude that introspection missed something, check what the tables are made of:

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

Two InnoDB tables, so the diagram will show the relationship. Where that column reads MyISAM, the engine is why the boxes come out unconnected, and the manual describes MyISAM as a small-footprint engine whose table-level locking limits performance in read/write workloads[8].

Connecting DbSchema over JDBC to MySQL

DbSchema reads those same catalogs through a JDBC driver and turns them into a diagram. In MySQL the two words mean one thing, since the manual states that CREATE SCHEMA is a synonym for CREATE DATABASE[9], so the schema you pick in DbSchema is the database catalog itself.

DbSchema builds the URL in the jdbc:mysql://host:port/database form and downloads the MySQL JDBC driver for you, so nothing has to be put on a classpath by hand. To turn a live database into an interactive MySQL ER diagram:

1. Start DbSchema and choose Connect to Database on the Welcome Screen.
2. Pick MySQL from the list of database types.
3. Fill in the Server Host, the Port (3306 unless it was changed), the Database User and the Password on the Connection tab.
4. Click Test Connection to check that the server answers.
5. Click Connect, then select the database catalog to read.

Everything in that sequence reads. DbSchema queries the catalogs, draws the tables, the column types, the primary keys and the foreign key lines, and writes the result into a design model on your own machine. Nothing is written to MySQL until you deliberately ask for it later, which is why running this against production during working hours is a reasonable thing to do. Ticking Read Only Connection on the Settings tab makes that guarantee explicit for the whole session.

What the offline design model file holds

The connection produces a .dbs design model file, which is XML you can open in a text editor. Once it exists, the diagram, the documentation and the review work no longer need the database: you can read the model on a train, and a colleague can read it without credentials to your production server.

Because it is plain text, the file goes into Git next to the application code, and a structural change between two releases is a diff rather than a pair of DDL scripts you have to compare by eye. The model carries several layers at once:

• Tables, data types, nullability, default values and primary keys
• Declared foreign keys and virtual foreign keys
• Diagram layouts, table positions, colors and callout notes
• SQL Editors with the statements you saved in them
• Query Builders with their tables, joins and ticked columns

Structure is all it holds. No table rows are cached or exported into it, so the file you commit carries no production data, and sharing the design with a contractor does not share the customers in it. Reading and writing the model file is a Pro feature; the free SQL client in the Community edition connects, reverse-engineers and runs queries without saving a model.

Drawing the relations with virtual foreign keys

A MyISAM schema, or an InnoDB one whose integrity lives in the application, comes out of introspection as a page of unconnected boxes. Adding real constraints with ALTER TABLE to fix the picture is usually out of the question on a live system: the statement has to be scheduled, existing rows may not satisfy it, and dropping a constraint later is another ALTER TABLE against the same table.

A virtual foreign key puts the relationship in the model instead. In the DbSchema diagram, drag the customer_id column of orders onto the customer_id column of customers, and DbSchema asks whether the link is a real foreign key or a virtual one. Choose virtual and the connector line appears immediately, written to the model file with no statement sent to MySQL, so the database is exactly as it was; the difference between real and virtual keys is where the definition lives, not what it draws.

From there the virtual key behaves like a declared one everywhere it matters in DbSchema. The Relational Data Editor opens customers and orders side by side over it, and selecting a customer refilters the orders pane to that customer's rows, cascading further for as many levels as you have drawn. The Query Builder follows the same virtual key when you click the arrow next to a column to add the related table, so the generated SELECT carries a join condition MySQL itself never declared.

Reverse engineering an inherited MySQL database gives you the table structures, the column definitions and the index coverage in an afternoon, which is where documenting a schema nobody wrote down starts. Download DbSchema at https://dbschema.com/download.html, connect over JDBC, and let it draw your tables. Connecting, reverse-engineering, the interactive diagrams and the SQL editor are in the free Community edition; saving the model to a .dbs file, and with it the virtual foreign keys and the Relational Data Editor that browses over them, are in Pro.

Frequently asked questions

How do I extract a MySQL database structure without data?

Run mysqldump with the --no-data flag, adding --routines and --events to include stored programs. The result is a plain SQL script holding the CREATE statements for the tables, views, triggers and routines, and no rows at all, which makes it small enough to commit and diff.

Why does my MySQL schema show no foreign keys?

Which engines keep the constraint, and which parse it and drop it, is covered in why a MyISAM schema introspects with no relations. One more case catches InnoDB tables: the 8.4 manual states that InnoDB does not currently support foreign keys for tables with user-defined partitioning, and that this covers the parent table as well as the child[6].

Where does MySQL store foreign key information?

INFORMATION_SCHEMA.KEY_COLUMN_USAGE holds the child column and the referenced table and column for every constraint, and INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS holds the ON UPDATE and ON DELETE rules for the same constraint name. Joining the two on CONSTRAINT_NAME and CONSTRAINT_SCHEMA gives the full relationship.

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

The two words name one thing in MySQL, which connecting DbSchema over JDBC to MySQL sources from the manual. PostgreSQL and Oracle put schemas inside a database, so a connection there picks the database first and the schema second, while in MySQL the database you select in the DbSchema Connection Dialog is the schema as well. Creating a MySQL database therefore creates what those engines would call a schema.

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

The drag that creates one, and what it leaves untouched in MySQL, is in drawing the relations with virtual foreign keys. A virtual key also links a view to a table, which the DbSchema documentation names as one of the cases it exists for, next to MyISAM and legacy schemas.

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
7. dev.mysql.com
8. dev.mysql.com
9. dev.mysql.com