How to Quickly Visualize/Analyze a MySQL Database?
October 25, 2009
Once in a while we been in situations where you need to quickly understand/analyze a database given to you by others. We can go through each table and try to understand but it is usually lot simpler if you can visualize all the tables involved and relationships between those. Enter SchemaSpy: a free tool to do that. For sake of specifics, say the database given is MySQL and your pc is running some version of Windows.
A. Download and Install Pre-requsites (if you dont have already)
- Graphviz (http://www.graphviz.org/Download_windows.php)
- use MSI installer (see warnings specific to vista on the download page)
- Java (http://www.java.com/en/download/index.jsp)
- Download and install Java as usual.
- JDBC driver for mySQL ( http://dev.mysql.com/downloads/connector/j/5.1.html)
- Download driver and extract to a known location (say C:\mydblayout)
B. Download latest SchemaSpy Jar File from http://sourceforge.net/projects/schemaspy/files/ (In my case it is schemaSpy_4.1.1.jar) to a known location (using same C:\mydblayout)
Now run the schemaspy to generate report (html format) for database ”mytestdb” which is sitting at a local mySQL server.C:\mydblayout>java -jar schemaSpy_4.1.1.jar -t mysql -dp mysql-connector-java-5.1.10\mysql-connector-java-5.1.10\mysql-connector-java-5.1.10-bin.jar -db mysql -host localhost -u root -p mysecretpass -o mytestdb_report
See how -dp flag is used to point to jdbc driver. The above command creates a folder called “mytestdb_report” with all the nice html files. Just open the “index.html” file.
For example output see http://schemaspy.sourceforge.net/unifieddb/characters/index.html
For more details, look at web page of this wonderful SchemaSpy (http://schemaspy.sourceforge.net/) written by John Currier.