Draw an ER diagram of a MySQL database
Run mysqldump --no-data, paste the result, and get the database as DBML that DB
Planner draws as an ER diagram: every table, key, index, and InnoDB foreign key, without a
connection to the server.
Get a clean dump
mysqldump --no-data --skip-comments mydb > schema.sql--no-data leaves the rows out, and --skip-comments drops the
version banner. The dump is already in the form the converter reads: backticked names, and
each table’s keys and constraints inside its own CREATE TABLE.
What a mysqldump contains, and what happens to it
PRIMARY KEY,UNIQUE KEY, andKEYlines become the primary key and the table’s indexes, each keeping its name.AUTO_INCREMENTbecomes an auto-incrementing column.CONSTRAINT ... FOREIGN KEY ... REFERENCESbecomes a relationship, with its name and itsON DELETErule.- An inline
enum('a','b')is lifted into a named enum, since DBML declares enums once and columns refer to them. - Defaults, including
CURRENT_TIMESTAMP, are kept.tinyint(1)staystinyint(1): whether it means a boolean is your convention, not the database’s.
ENGINE=, DEFAULT CHARSET=, and collations describe storage, not
structure, and are ignored. Views, triggers, and stored routines are left out.
A diagram shows what the database enforces
MySQL schemas are more likely than most to have relationships that live only in the
application: a user_id column, an index on it, and no constraint. The converter
draws constraints, so those tables arrive unconnected, and that is useful to know before
anyone trusts the data. Adding the reference is one line of DBML, and the migration generator will write the ALTER TABLE that makes it real.
Diagramming another database? The same route works for PostgreSQL and SQL Server.
MySQL ER diagram questions
How do I create an ER diagram from a MySQL database?
Dump the structure with mysqldump --no-data, paste the file above to turn it into DBML, and paste the DBML into a DB Planner project. Tables are drawn as cards and InnoDB foreign keys as lines between the columns they join.
Why does my diagram have tables but no lines between them?
Because the database has no foreign key constraints. MyISAM tables cannot have them, and many MySQL schemas rely on naming alone, with a customer_id column that nothing enforces. The diagram shows the constraints that exist; you can add the missing references in the DBML.
What happens to a MySQL ENUM column?
It becomes an enum of its own, named after the table and column, such as customers_status_enum, with the column pointing at it. On the diagram that is a separate card listing the allowed values.
Is UNSIGNED kept?
No. A bigint unsigned column is read as bigint, because the DBML the converter writes records the base type. If the distinction matters to you, add it back to the column type in the DBML after converting.
Put your schema on the board
Start on the free plan, no card needed. Pro is $5 a month when you outgrow it.
Start designing free