SchemaSpyでER図を自動作成 on Docker│システムガーディアン株式会社

DBに接続させるとER図を出力してくれる【SchemaSpy】のご紹介です！

1 条件
2 セットアップ
3 SchemaSpyにアクセスして確認しよ～！
4 んんっ…!!
- 4.1 外部キー制約のあるテーブルを作って表示させる

条件

AWS EC2 Amazon Linux2
Dockerインストール済
docker-composeインストール済

セットアップ

ディレクトリの作成

mkdir -p $HOME/project/{wordpress,db_data,git,shell,docker}
mkdir -p $HOME/project/docker/{nginx_for_schemaspy,schemaspy}
mkdir -p $HOME/project/docker/schemaspy/config
mkdir -p $HOME/project/schemaspy

mkdir -p $HOME/project/{wordpress,db_data,git,shell,docker}

mkdir -p $HOME/project/docker/{nginx_for_schemaspy,schemaspy}

mkdir -p $HOME/project/docker/schemaspy/config

mkdir -p $HOME/project/schemaspy

SchemaSpy設定ファイル作成

$ vi $HOME/project/docker/schemaspy/config/schemaspy.properties


# type of database. Run with -dbhelp for details
schemaspy.t=mysql
# optional path to alternative jdbc drivers.
schemaspy.dp=/app/mysql-connector-java.jar
# database properties: host, port number, name user, password
schemaspy.host=mysql57
schemaspy.port=3306
schemaspy.db=<★DB名>
schemaspy.u=root
schemaspy.p=root
# output dir to save generated files
schemaspy.o=/app/html
# db scheme for which generate diagrams
schemaspy.s=<★DB名>

$ vi $HOME/project/docker/schemaspy/config/schemaspy.properties

# type of database. Run with -dbhelp for details

schemaspy.t=mysql

# optional path to alternative jdbc drivers.

schemaspy.dp=/app/mysql-connector-java.jar

# database properties: host, port number, name user, password

schemaspy.host=mysql57

schemaspy.port=3306

schemaspy.db=<★DB名>

schemaspy.u=root

schemaspy.p=root

# output dir to save generated files

schemaspy.o=/app/html

# db scheme for which generate diagrams

schemaspy.s=<★DB名>

接続情報を入力します。

SchemaSpy Dockerfile作成

$ vi $HOME/project/docker/schemaspy/Dockerfile



FROM openjdk:8u121-jdk-alpine
 
ENV DRIVER_URL http://central.maven.org/maven2/mysql/mysql-connector-java/5.1.45/mysql-connector-java-5.1.45.jar
ENV APP_URL https://github.com/schemaspy/schemaspy/releases/download/v6.0.0-rc2/schemaspy-6.0.0-rc2.jar
 
WORKDIR /app
 
RUN apk --update add graphviz ttf-dejavu && \
    apk --update add --virtual .builddep tzdata wget libressl && \
    cp /usr/share/zoneinfo/Asia/Tokyo /etc/localtime && \
    wget -O mysql-connector-java.jar ${DRIVER_URL} && \
    wget -O schemaspy.jar ${APP_URL} && \
    apk del .builddep && \
    rm -rf /var/cache/apk/*

$ vi $HOME/project/docker/schemaspy/Dockerfile

FROM openjdk:8u121-jdk-alpine

ENV DRIVER_URL http://central.maven.org/maven2/mysql/mysql-connector-java/5.1.45/mysql-connector-java-5.1.45.jar

ENV APP_URL https://github.com/schemaspy/schemaspy/releases/download/v6.0.0-rc2/schemaspy-6.0.0-rc2.jar

WORKDIR /app

RUN apk --update add graphviz ttf-dejavu && \

apk --update add --virtual .builddep tzdata wget libressl && \

cp /usr/share/zoneinfo/Asia/Tokyo /etc/localtime && \

wget -O mysql-connector-java.jar ${DRIVER_URL} && \

wget -O schemaspy.jar ${APP_URL} && \

apk del .builddep && \

rm -rf /var/cache/apk/*

SchemaSpy用Nginx設定ファイル作成

$ vi $HOME/project/docker/nginx_for_schemaspy/default.conf


server {
    listen                80 default;
    server_name           localhost;
 
    root                  /var/www/html;
    charset               utf-8;
    disable_symlinks      off;
    index                 index.html;
 
    access_log            off;
    error_log             off;
 
    sendfile              on;
    tcp_nodelay           on;
    tcp_nopush            on;
    keepalive_timeout     120s;
 
    gzip                  on;
    gzip_http_version     1.0;
    gzip_disable          “MSIE [1-6].(?!.*SV1)”;
    gzip_comp_level       1;
    gzip_proxied          any;
    gzip_vary             on;
    gzip_buffers          4 8k;
    gzip_min_length       1100;
    gzip_types            text/plain
                          text/xml
                          text/css
                          application/xml
                          application/xhtml+xml
                          application/rss+xml
                          application/atom_xml
                          application/json
                          application/javascript
                          application/x-javascript
                          application/x-httpd-php;
}

$ vi $HOME/project/docker/nginx_for_schemaspy/default.conf

server {

listen 80 default;

server_name localhost;

root /var/www/html;

charset utf-8;

disable_symlinks off;

index index.html;

access_log off;

error_log off;

sendfile on;

tcp_nodelay on;

tcp_nopush on;

keepalive_timeout 120s;

gzip on;

gzip_http_version 1.0;

gzip_disable “MSIE [1-6].(?!.*SV1)”;

gzip_comp_level 1;

gzip_proxied any;

gzip_vary on;

gzip_buffers 4 8k;

gzip_min_length 1100;

gzip_types text/plain

text/xml

text/css

application/xml

application/xhtml+xml

application/rss+xml

application/atom_xml

application/json

application/javascript

application/x-javascript

application/x-httpd-php;

}

docker-compose.yml作成

# vi $HOME/project/docker-compose.yml
 
 

version: '3.3'

services:
   wordpress:
     container_name: "wordpress"
     image: wordpress:latest
     depends_on:
       - mysql57
     restart: always
     environment:
       WORDPRESS_DB_HOST: mysql57:3306
       WORDPRESS_DB_USER: wordpress
       WORDPRESS_DB_PASSWORD: wordpress
       WORDPRESS_DB_NAME: wordpress
     ports:
     - "80:80"
     volumes:
       - ./wordpress:/var/www/html

   mysql57:
     image: mysql:5.7
     container_name: "mysql57"
     volumes:
       - ./db_data:/var/lib/mysql
     restart: always
     environment:
       MYSQL_ROOT_PASSWORD: root
       MYSQL_DATABASE: wordpress
       MYSQL_USER: wordpress
       MYSQL_PASSWORD: wordpress
   pma:
     container_name: "pma"
     image: phpmyadmin/phpmyadmin:latest
     environment:
       PMA_HOST: mysql57
     ports:
     - "8080:80"
     restart: always
     links:
       - mysql57

   nginx_for_schemaspy:
     image: nginx:1.13.9-alpine
     container_name: nginx_for_schemaspy
     volumes:
       - ./schemaspy:/var/www/html:ro
       - ./docker/nginx_for_schemaspy/default.conf:/etc/nginx/conf.d/default.conf:ro
     ports:
       - "8081:80"
     environment:
       - LANG=ja_JP.UTF-8
       - TZ=Asia/Tokyo
     command: "nginx -g 'daemon off;'"

   schemaspy:
     build: ./docker/schemaspy
     image: treetips/schemaspy-mysql
     container_name: schemaspy
     volumes:
       - ./schemaspy:/app/html:rw
       - ./docker/schemaspy/config/schemaspy.properties:/app/schemaspy.properties:ro
     environment:
       - LANG=ja_JP.UTF-8
       - TZ=Asia/Tokyo
     working_dir: "/app"
     command: "java -jar schemaspy.jar"


volumes:
   db_data:
   wordpress:
   schemaspy:

# vi $HOME/project/docker-compose.yml

version: '3.3'

services:

wordpress:

container_name: "wordpress"

image: wordpress:latest

depends_on:

- mysql57

restart: always

environment:

WORDPRESS_DB_HOST: mysql57:3306

WORDPRESS_DB_USER: wordpress

WORDPRESS_DB_PASSWORD: wordpress

WORDPRESS_DB_NAME: wordpress

ports:

- "80:80"

volumes:

- ./wordpress:/var/www/html

mysql57:

image: mysql:5.7

container_name: "mysql57"

volumes:

- ./db_data:/var/lib/mysql

restart: always

environment:

MYSQL_ROOT_PASSWORD: root

MYSQL_DATABASE: wordpress

MYSQL_USER: wordpress

MYSQL_PASSWORD: wordpress

pma:

container_name: "pma"

image: phpmyadmin/phpmyadmin:latest

environment:

PMA_HOST: mysql57

ports:

- "8080:80"

restart: always

links:

- mysql57

nginx_for_schemaspy:

image: nginx:1.13.9-alpine

container_name: nginx_for_schemaspy

volumes:

- ./schemaspy:/var/www/html:ro

- ./docker/nginx_for_schemaspy/default.conf:/etc/nginx/conf.d/default.conf:ro

ports:

- "8081:80"

environment:

- LANG=ja_JP.UTF-8

- TZ=Asia/Tokyo

command: "nginx -g 'daemon off;'"

schemaspy:

build: ./docker/schemaspy

image: treetips/schemaspy-mysql

container_name: schemaspy

volumes:

- ./schemaspy:/app/html:rw

- ./docker/schemaspy/config/schemaspy.properties:/app/schemaspy.properties:ro

environment:

- LANG=ja_JP.UTF-8

- TZ=Asia/Tokyo

working_dir: "/app"

command: "java -jar schemaspy.jar"

volumes:

db_data:

wordpress:

schemaspy:

起動させる

$ docker-compose build --no-cache

1	$ docker-compose build --no-cache

SchemaSpyにアクセスして確認しよ～！

http://IPアドレス:8081/

んんっ…!!

WordPressでやったら外部キー制約ないので面白くないのだが…。

外部キー制約のあるテーブルを作って表示させる

外部キーのあるテーブルのスキーマを用意してみました。

わかりやすいですね。

お疲れ様です。

@see

SchemaSpyの環境をdocker-composeで構築

最新情報をチェックしよう！

フォローする

SchemaSpyでER図を自動作成 on Docker

条件

セットアップ

SchemaSpyにアクセスして確認しよ～！

んんっ…!!

外部キー制約のあるテーブルを作って表示させる

AWS Trend Micro WAF 簡単な検証をしてみる

WordPressマルチサイトドメインお引越しの記録

AWSの最新記事8件

AWSは落ちないってわけじゃないという事

AWS EC2のログをCloudWatch Logsに出力