MySQL combined with the database analysis tool SchemaSpy _ MySQL

This article describes how to use SchemaSpy, a database analysis tool, in MySQL. if you want to use SchemaSpy, you can refer to it recently. I am working with a bunch of old customers, they asked them to spend time explaining the new tools they used in their new environment. One of the most useful tools I have found is SchemaSpy.

SchemaSpy is a Java-developed tool that requires the support of java 5 or later. it is mainly used to analyze the metadata of data models in databases and generate visual display based on browsers. You can click here to learn about the table hierarchy, parent-child table relationships, and so on, mainly through HTML links or object relationship graphs. It is also designed to help solve the problem of database Association failures caused by constraints.

One thing I like about ERD is that it can quickly generate a graphical expression to show that the table in the database is referenced most often, the tables that have been replaced can be deleted (why do you want to save the data you don't need ?). By understanding the relationship between database tables rather than simply viewing query statements, this can speed up the initial step and promote the development process to make it more effective. Another advantage of SchemaSpy is that it is a command line tool that generates html pages after each execution. Therefore, it is particularly suitable for crontab, so that the page can be automatically adjusted after the model is changed. Pretty good, right?

This tool is easy to use and can help you do a lot of things. The main reason is that if your database has a foreign key, you will be happy. if you do not have it, you should not be too desperate. You still have more work to wait for you. In most cases, SchemSpy can accomplish tasks well, but sometimes you need to give it a little bit of information in the form of a metadata file.

Install SchemaSpy and related

Download the jar file of the latest SchemaSpy version (the version of SchemaSpy is 5.0.0)
Confirm that you have a Java JRE that can run on your operating platform
You need a Java driver for your database-I'm using MySQL Connector/J
Install graphiz package

Create the mysql. properties file

When the requirement is not clear, I like to establish it so that the command line can be short and neat. This is the property file I used for my Percona Server 5.6.10 sandbox:

The code is as follows:

Description = MySQL
Driver = com. mysql. jdbc. Driver
ConnectionSpec = jdbc: mysql: // 127.0.0.1: 5610/schemaspy
DriverPath =/usr/share/java/mysql-connector-java.jar

Schema example:

CREATE TABLE `parent` ( `parent_id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`parent_id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1CREATE TABLE `child_A` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `parent_id` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1CREATE TABLE `child_B` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `parent_id` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `parent_id` (`parent_id`), CONSTRAINT `child_B_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`parent_id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1

Note that the table child_A implicitly uses parent_id as the foreign key to associate with parent, but an explicit foreign key parent_id is created in the table child_ B. This will affect how SchemaSpy identifies relationships and whether you need to provide a prompt.
Run SchemaSpy (with foreign keys)

If you run SchemaSpy without a metadata file, you can see the relationship between the parent table and the child_ B table, as shown in:

The table child_A can also be added for display. if your tag specifies their relationship box, however, this function usually takes effect only when your sub-table and parent table use the same name (parent_id in this example. Your environment looks like a lot of stores. in the parent table, the name is id, and in its child table, they represent parent_id. this situation may be familiar to developers, however, SchemaSpy has no help at all. That is when metadata files are needed to show their talents.

Configure the metadata file

The metadata file is in XML format. The following configuration instance is used to link the child_A table to the parent table.

<?xml version="1.0" encoding="UTF-8"?>
 
  
   Main Production Database. Percona, Michael Coburn
  
  
   
   
 
   
   
    
   
   
 
  
 

For more complex configuration examples, see.
Run SchemaSpy

The following syntax is used to connect to the MySQL database. SchemaSy also supports most popular relational data.

java -jar schemaSpy_5.0.0.jar -t mysql -u msandbox_ro -p msandbox -meta schemaspy.meta.xml -o /var/www/schemaspy/

-O option to identify the output file directory of SchemaSpy. make sure that this directory can be operated by the web server. It should be mentioned that I create a mysql. properties file in the directory where the jar package is located, so that I do not need to enter any server: Port and database name information.
View SchemaSpy output

Shows the complete link View of the three tables:

Closing feelings

Although I mentioned the link bar, SchemaSpy is the most useful feature. To avoid the illusion that SchemaSpy only has this function, I would like to mention more feature columns:

• Table -- name, number of sub-tables, parent table, column count, row count, and annotation -- a large number of values are used to preview the table size at a high level and a quick way is, locate a specific table using the browsing search attribute.
• Restriction-list the foreign key restrictions specified in the database (this does not include the restrictions configured through the metadata file !).
• Exception-indicate the possible field/table relationship based on the name. a table without an index is identified as an empty field and a unique field (vomit !), Single list, the name of the Child Growth field; empty characters instead of tables with the actual sqlnull value; in the case of a major error, this can be used as a general database check item, or browsing details.
• Field: The list of all fields in the database. it is convenient to filter the names to check whether there are clear restrictions on ignoring them, and then write them to your metadata file.
• Contribution-this is a free software. John Currier asks for your contribution. generally, it has more reasons to spend more energy and time maintaining the software, not just complaints from his wife.

Finally, do not forget to write the configured SchemaSpy to crontab for automation.

If you are using another ERD tool, how is it compared with SchemaSpy? Please comment freely. Thank you for your patience.