SchemaSpyというDBのスキーマを解析してテーブルの一覧やER図を出力してくれるツールがあります。
このツールの公式Dockerイメージが公開されており、非常に使いやすいので紹介させて頂きます。
https://hub.docker.com/r/schemaspy/schemaspy/
上記の記事を参考に laradock 環境の ER 図を作成しようとしたところ、少しハマったので共有いたします。
対象の mysql のバージョンは 5.7.19 でした。
- そのまま実行すると SSL に関する WARN が出ました。
❯ docker run -v "$PWD/schema:/output" --net="host" schemaspy/schemaspy:snapshot -t mysql -host 192.168.1.16:3306 -db laradock -u laradock -p secret
Running Main-Class org.springframework.boot.loader.JarLauncher
With drivers:jtds-1.3.1.jar, mariadb-java-client-1.1.10.jar
mysql-connector-java-6.0.6.jar, postgresql-42.1.1.jre7.jar
____ _ ____
/ ___| ___| |__ ___ _ __ ___ __ _/ ___| _ __ _ _
\___ \ / __| '_ \ / _ \ '_ ` _ \ / _` \___ \| '_ \| | | |
___) | (__| | | | __/ | | | | | (_| |___) | |_) | |_| |
|____/ \___|_| |_|\___|_| |_| |_|\__,_|____/| .__/ \__, |
|_| |___/
6.0.0-rc2
SchemaSpy generates an HTML representation of a database schema's relationships.
INFO - Starting Main v6.0.0-rc2 with PID 1 (/schemaspy-6.0.0-rc2.jar started by java in /)
INFO - No active profile set, falling back to default profiles: default
INFO - Configuration file not found
INFO - Starting schema analysis
INFO - Resolving dbType: mysql ->
/schemaspy-6.0.0-rc2.jar!/BOOT-INF/classes!/org/schemaspy/types/mysql.properties
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
Fri Dec 22 04:55:52 GMT 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
INFO - Connected to MySQL - 5.7.19
ERROR - Schema name can't be null
java.lang.IllegalArgumentException: Schema name can't be null
google 翻訳してみると useSSL = false とかする必要がありそうです。
Fri Dec 22 04:55:52 GMT 2017警告：サーバーの身元確認なしでSSL接続を確立することはお勧めできません。 MySQL 5.5.45+、5.6.26+、5.7.6+の要件明示的なオプションが設定されていない場合、デフォルトでSSL接続を確立する必要があります。 SSLを使用しない既存のアプリケーションに準拠するため、verifyServerCertificateプロパティは 'false'に設定されています。 useSSL = falseを設定するか、useSSL = trueを設定して明示的にSSLを無効にし、サーバー証明書の検証にtruststoreを指定する必要があります。
しばらくググってると document にそれっぽいことが書いてました。 -connprops useSSL\\=false をつければ良さそう。
[As an example running mysql with a new driver you’ll get warning
According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn’t set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to ‘false’. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
This can be omited by addind connection property useSSL=false
To add this connection property add following to commandline: -connprops useSSL\\=false
というわけで -connprops useSSL\\=false をつけて再実行。
❯ docker run -v "$PWD/schema:/output" --net="host" schemaspy/schemaspy:snapshot -t mysql -host 192.168.1.16:3306 -db laradock -u laradock -p secret -connprops useSSL\\=false
Running Main-Class org.springframework.boot.loader.JarLauncher
With drivers:jtds-1.3.1.jar, mariadb-java-client-1.1.10.jar
mysql-connector-java-6.0.6.jar, postgresql-42.1.1.jre7.jar
____ _ ____
/ ___| ___| |__ ___ _ __ ___ __ _/ ___| _ __ _ _
\___ \ / __| '_ \ / _ \ '_ ` _ \ / _` \___ \| '_ \| | | |
___) | (__| | | | __/ | | | | | (_| |___) | |_) | |_| |
|____/ \___|_| |_|\___|_| |_| |_|\__,_|____/| .__/ \__, |
|_| |___/
6.0.0-rc2
SchemaSpy generates an HTML representation of a database schema's relationships.
INFO - Starting Main v6.0.0-rc2 with PID 1 (/schemaspy-6.0.0-rc2.jar started by java in /)
INFO - No active profile set, falling back to default profiles: default
INFO - Configuration file not found
INFO - Starting schema analysis
INFO - Resolving dbType: mysql ->
/schemaspy-6.0.0-rc2.jar!/BOOT-INF/classes!/org/schemaspy/types/mysql.properties
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
INFO - Connected to MySQL - 5.7.19
ERROR - Schema name can't be null
WARN は消えました！
しかし、Schema name can't be null とのこと。しばらくググってると以下の情報を見つけました。
shibukk mysql5.6 の環境ではエラーになったので - s information_schema を付けたらうまくいきました
- s information_schema をつけると良さそうなので再実行。
❯ docker run -v "$PWD/schema:/output" --net="host" schemaspy/schemaspy:snapshot -t mysql -host 192.168.1.16:3306 -db laradock -u laradock -p secret -connprops useSSL\\=false -s information_schema
Running Main-Class org.springframework.boot.loader.JarLauncher
With drivers:jtds-1.3.1.jar, mariadb-java-client-1.1.10.jar
mysql-connector-java-6.0.6.jar, postgresql-42.1.1.jre7.jar
____ _ ____
/ ___| ___| |__ ___ _ __ ___ __ _/ ___| _ __ _ _
\___ \ / __| '_ \ / _ \ '_ ` _ \ / _` \___ \| '_ \| | | |
___) | (__| | | | __/ | | | | | (_| |___) | |_) | |_| |
|____/ \___|_| |_|\___|_| |_| |_|\__,_|____/| .__/ \__, |
|_| |___/
6.0.0-rc2
SchemaSpy generates an HTML representation of a database schema's relationships.
INFO - Starting Main v6.0.0-rc2 with PID 1 (/schemaspy-6.0.0-rc2.jar started by java in /)
INFO - No active profile set, falling back to default profiles: default
INFO - Configuration file not found
INFO - Starting schema analysis
INFO - Resolving dbType: mysql ->
/schemaspy-6.0.0-rc2.jar!/BOOT-INF/classes!/org/schemaspy/types/mysql.properties
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
INFO - Connected to MySQL - 5.7.19
INFO - Gathering schema details
Gathering schema details...........................(1sec)
Connecting relationships...........................(2sec)
Writing/graphing summary.INFO - Gathered schema details in 2 seconds
INFO - Writing/graphing summary
............(6sec)
Writing/diagramming detailsINFO - Completed summary in 6 seconds
INFO - Writing/diagramming details
(省略)
(6sec)
Wrote relationship details of 24 tables/views to directory '/output' in 18 seconds.
View the results by opening /output/index.html
INFO - Wrote table details in 6 seconds
INFO - Wrote relationship details of 24 tables/views to directory '/output' in 18 seconds.
INFO - View the results by opening /output/index.html
INFO - Started Main in 20.246 seconds (JVM running for 20.914)
うまくいきました！
というわけで最近のバージョンの MySql でやる場合は以下で実行するとよさそうです。
docker run -v "$PWD/schema:/output" --net="host" schemaspy/schemaspy:snapshot \
-t <DB種類> -host <DBホスト名/IP>:<ポート> -db <DB名> -u <DBユーザー名> -p <DBパスワード> -connprops useSSL\\=false -s information_schema