結論

簡単に監視ソフトのDMLをまとめと思ったが、ボリュームあるからやめた

nagios は外部キー無し、テーブル名にプレフィックスありの特徴があります

Schema

ER

Nagios

Zabix

Table List

Nagios

mysql> show tables;
+----------------------------------------+
| Tables_in_nagios                       |
+----------------------------------------+
| nagios_acknowledgements                |
| nagios_commands                        |
| nagios_commenthistory                  |
| nagios_comments                        |
| nagios_configfiles                     |
| nagios_configfilevariables             |
| nagios_conninfo                        |
| nagios_contact_addresses               |
| nagios_contact_notificationcommands    |
| nagios_contactgroup_members            |
| nagios_contactgroups                   |
| nagios_contactnotificationmethods      |
| nagios_contactnotifications            |
| nagios_contacts                        |
| nagios_contactstatus                   |
| nagios_customvariables                 |
| nagios_customvariablestatus            |
| nagios_dbversion                       |
| nagios_downtimehistory                 |
| nagios_eventhandlers                   |
| nagios_externalcommands                |
| nagios_flappinghistory                 |
| nagios_host_contactgroups              |
| nagios_host_contacts                   |
| nagios_host_parenthosts                |
| nagios_hostchecks                      |
| nagios_hostdependencies                |
| nagios_hostescalation_contactgroups    |
| nagios_hostescalation_contacts         |
| nagios_hostescalations                 |
| nagios_hostgroup_members               |
| nagios_hostgroups                      |
| nagios_hosts                           |
| nagios_hoststatus                      |
| nagios_instances                       |
| nagios_logentries                      |
| nagios_notifications                   |
| nagios_objects                         |
| nagios_processevents                   |
| nagios_programstatus                   |
| nagios_runtimevariables                |
| nagios_scheduleddowntime               |
| nagios_service_contactgroups           |
| nagios_service_contacts                |
| nagios_service_parentservices          |
| nagios_servicechecks                   |
| nagios_servicedependencies             |
| nagios_serviceescalation_contactgroups |
| nagios_serviceescalation_contacts      |
| nagios_serviceescalations              |
| nagios_servicegroup_members            |
| nagios_servicegroups                   |
| nagios_services                        |
| nagios_servicestatus                   |
| nagios_statehistory                    |
| nagios_systemcommands                  |
| nagios_timedeventqueue                 |
| nagios_timedevents                     |
| nagios_timeperiod_timeranges           |
| nagios_timeperiods                     |
+----------------------------------------+
60 rows in set (0.02 sec)

Zabix

mysql> show tables;
+----------------------------+
| Tables_in_zabix            |
+----------------------------+
| acknowledges               |
| actions                    |
| alerts                     |
| application_discovery      |
| application_prototype      |
| application_template       |
| applications               |
| auditlog                   |
| auditlog_details           |
| autoreg_host               |
| conditions                 |
| config                     |
| corr_condition             |
| corr_condition_group       |
| corr_condition_tag         |
| corr_condition_tagpair     |
| corr_condition_tagvalue    |
| corr_operation             |
| correlation                |
| dbversion                  |
| dchecks                    |
| dhosts                     |
| drules                     |
| dservices                  |
| escalations                |
| event_recovery             |
| event_tag                  |
| events                     |
| expressions                |
| functions                  |
| globalmacro                |
| globalvars                 |
| graph_discovery            |
| graph_theme                |
| graphs                     |
| graphs_items               |
| group_discovery            |
| group_prototype            |
| groups                     |
| history                    |
| history_log                |
| history_str                |
| history_text               |
| history_uint               |
| host_discovery             |
| host_inventory             |
| hostmacro                  |
| hosts                      |
| hosts_groups               |
| hosts_templates            |
| housekeeper                |
| httpstep                   |
| httpstep_field             |
| httpstepitem               |
| httptest                   |
| httptest_field             |
| httptestitem               |
| icon_map                   |
| icon_mapping               |
| ids                        |
| images                     |
| interface                  |
| interface_discovery        |
| item_application_prototype |
| item_condition             |
| item_discovery             |
| item_preproc               |
| items                      |
| items_applications         |
| maintenances               |
| maintenances_groups        |
| maintenances_hosts         |
| maintenances_windows       |
| mappings                   |
| media                      |
| media_type                 |
| opcommand                  |
| opcommand_grp              |
| opcommand_hst              |
| opconditions               |
| operations                 |
| opgroup                    |
| opinventory                |
| opmessage                  |
| opmessage_grp              |
| opmessage_usr              |
| optemplate                 |
| problem                    |
| problem_tag                |
| profiles                   |
| proxy_autoreg_host         |
| proxy_dhistory             |
| proxy_history              |
| regexps                    |
| rights                     |
| screen_user                |
| screen_usrgrp              |
| screens                    |
| screens_items              |
| scripts                    |
| service_alarms             |
| services                   |
| services_links             |
| services_times             |
| sessions                   |
| slides                     |
| slideshow_user             |
| slideshow_usrgrp           |
| slideshows                 |
| sysmap_element_trigger     |
| sysmap_element_url         |
| sysmap_shape               |
| sysmap_url                 |
| sysmap_user                |
| sysmap_usrgrp              |
| sysmaps                    |
| sysmaps_elements           |
| sysmaps_link_triggers      |
| sysmaps_links              |
| task                       |
| task_close_problem         |
| task_remote_command        |
| task_remote_command_result |
| timeperiods                |
| trends                     |
| trends_uint                |
| trigger_depends            |
| trigger_discovery          |
| trigger_tag                |
| triggers                   |
| users                      |
| users_groups               |
| usrgrp                     |
| valuemaps                  |
+----------------------------+
134 rows in set (0.02 sec)

Confirmation

Setup

Source

# nagios
curl https://raw.githubusercontent.com/NagiosEnterprises/ndoutils/master/db/mysql.sql > nagios-mysql.sql
# zabix
svn export svn://svn.zabbix.com/trunk/create/src/schema.tmpl .
svn export svn://svn.zabbix.com/trunk/create/bin/gen_schema.pl . && sed -i -e 's/\.\.\/src\///' gen_schema.pl
perl gen_schema.pl mysql > zabix-mysql.sql
rm *.tmpl *.pl

DB

# create mysql server
docker run -p 3306:3306 -e MYSQL_ALLOW_EMPTY_PASSWORD=true  -v <path to your dir>:/var/lib/mysql -d mysql:latest

# mysql variables
dbhost=127.0.0.1

# create databases
mysql -h $dbhost -u root -e "drop database if exists nagios; create database nagios;"
mysql -h $dbhost -u root -e "drop database if exists zabix; create database zabix;"

# create nagios
mysql -h $dbhost -u root -D nagios -e "$(cat nagios-mysql.sql)"
# create zabix
mysql -h $dbhost -u root -D zabix -e "$(cat zabix-mysql.sql)"

Reverse Engineering

Launch MySQLWorkBench
Create Model

備考

余談

workbench はリバースエンジニアリングの時は、いい感じでレイアウトしてくれるが、autolayout は結構雑なレイアウトになる
実行コマンド

# !/bin/sh

exec_func=$1

function dl_src()
{
	# nagios
	curl https://raw.githubusercontent.com/NagiosEnterprises/ndoutils/master/db/mysql.sql > nagios-mysql.sql
	# zabix
	svn export svn://svn.zabbix.com/trunk/create/src/schema.tmpl .
	svn export svn://svn.zabbix.com/trunk/create/bin/gen_schema.pl . && sed -i -e 's/\.\.\/src\///' gen_schema.pl
	perl gen_schema.pl mysql > zabix-mysql.sql
	rm *.tmpl *.pl
}


function db_setup()
{
	# create database
	dbhost=127.0.0.1
	mysql -h $dbhost -u root -e "drop database if exists nagios; create database nagios;"
	mysql -h $dbhost -u root -e "drop database if exists zabix; create database zabix;"
	mysql -h $dbhost -u root -D nagios -e "$(cat nagios-mysql.sql)"
	mysql -h $dbhost -u root -D zabix -e "$(cat zabix-mysql.sql)"
}


if [[ "${exec_func}" =~ (dl_src|db_setup) ]]; then
	$exec_func
else
	cat <<EOF
$0 [dl_src] [db_setup]
EOF
fi

Reference

svn://svn.zabibix.com (githubはメンテしてない？) svn://svn.zabbix.com/trunk/create/src/schema.tmpl
Nagios ndoutils
wb reverse engineering
【5分でできる】MySQL/MariaDBをリバースエンジニアリングして、ER図を作成する