블로그 이미지
bedbmsguru

Notice

Recent Post

Recent Comment

Recent Trackback

Archive

calendar

1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28
  • total
  • today
  • yesterday
2018. 10. 26. 21:39 maridb&mysql

모든 테이블의 FOREIGN KEY 정보 조회
SELECT constraint_name, table_name, referenced_table_name FROM information_schema.REFERENTIAL_CONSTRAINTS

FOREIGN KEY 정보 삭제
ALTER TABLE 테이블이름 DROP FOREIGN KEY 제약조건이름

'maridb&mysql' 카테고리의 다른 글

procedure 생성 템플릿  (0) 2018.10.26
Function 템플릿  (0) 2018.10.26
mariadb (mysql)백업  (0) 2018.10.26
mariadb 설치  (0) 2017.12.26
innobackupex dependency 오류 해결  (0) 2017.12.21
posted by bedbmsguru
2018. 10. 26. 21:39 maridb&mysql

mysql 전체 DB 백업
mysqldump -uroot -p -A > backup.sql

1) procedure, function, trigger 까지 모두 백업하기(옵션 --routines  --trigger)
$ mysqldump --routines  --triggers -u유저이름 -p패스워드 DB명 > 백업파일

mysql 지정된 DB 백업
mysqldump -uroot --routines  --triggers  -ppass DBname > backup.sql

mysql 특정 db의 특정 테이블만 백업
* dump 데이터베이스의 test 테이블만 백업합니다
mysqldump -uroot -p dump test > dumptest.sql

mysql schema 정보만 백업
mysqldump -uroot -p --no-data schemainfo > schemainfo.sql


모든 db 복원
mysql -uroot -p < backup.sql

'maridb&mysql' 카테고리의 다른 글

procedure 생성 템플릿  (0) 2018.10.26
Function 템플릿  (0) 2018.10.26
Foreign key 정보조회 및 삭제  (0) 2018.10.26
mariadb 설치  (0) 2017.12.26
innobackupex dependency 오류 해결  (0) 2017.12.21
posted by bedbmsguru
2018. 10. 26. 21:37 Zabbix

1.read me참고하여 zabbix web관리자 페이지에 Value mapping 등록

2.zabbix --> externalscripts 디렉토리에 에 script 파일 추가
     (1) echo.something
#!/bin/sh
#
#begin
#Script: echo.something
#Version: 2.0
#Author: Jean-Jacques Martrès (jjmartres |at| gmail |dot| com)
#Description: This script echo a string
#License: GPL2
#
#This script is intended for use with Zabbix > 2.0
#
#USAGE:
#  as a script:      echo.somthing "some string"
#  as an item:       echo.something["some string"]

[ -z "$@" ] && exit 1
echo "$@"
    (2)advsnmp.discovery

#!/usr/bin/perl
use strict;
# script: advsnmp.discovery
# version: 2.0
# author: Simon Kowallik <sk simonkowallik.com>
# description: Zabbix low level discovery script for advanced index discovery
# license: GPL2

#USAGE:
#advsnmp.discovery[{HOST.IP},"-v2c -cpublic",.1.2.3.4.5.6,offset.length,offset.length,offset.lentgh
#                      ^            ^             ^              ^             ^            ^
#                      |            |             |              |             |            |
#ARGV                  0            1             2              3             4            5
#                      |            |             |              |             |            |
#                 IP/DNS Name       |             |              |             |            |
#                             SNMP Settings    SNMP OID       INDEX_offset.INDEX_length
#
#OID: .0.0.0.0.0.0.1.0.2.1.1.4.4.0
#will be cut to:  .1.0.2.1.1.4.4.0
#
#     .     1 .     0 .     2 .     1 .     1 .     4 .     4 .     0
#0=>'', 1=>'1', 2=>'0', 3=>'2', 4=>'1', 5=>'1', 6=>'4', 7=>'4', 8=>'0'
#
#         $_offset     $_len
#                 \   /
#config: INDEX1 -> 1.1
#        INDEX2 -> 3.2
#        INDEX3 -> 5.3
#
#   INDEX1   INDEX2   INDEX3
#      \       |       /
#     .[1].0.[2.1].[1.4.4].0
#
#
#EXAMPLE:
# OIDs to query:
# .0.0.0.0.0.0.1.0.2.1.1.4.4.0
# .0.0.0.0.0.0.2.0.3.6.1.4.4.0
# .0.0.0.0.0.0.3.0.4.5.1.4.4.0
# .0.0.0.0.0.0.1.0.2.4.5.3.4.0
# .0.0.0.0.0.0.2.0.2.2.5.3.4.0
# .0.0.0.0.0.0.3.0.2.9.5.3.4.0
#
# Discovery Rule with "external check"
# Key: advsnmp.discovery[{HOST.IP},"-v2c -cpublic",.0.0.0.0.0.0,1.1,3.2,5.3]
#
# Idexes will be:
# ADVSNMPINDEX1 ADVSNMPINDEX2 ADVSNMPINDEX3
#       1            2.1          1.4.4
#       2            3.6          1.4.4
#       3            4.5          1.4.4
#       1            2.4          5.3.4
#       2            2.2          5.3.4
#       3            2.9          5.3.4


# global options
my $GLOB_SNMPW = "-OQn";
my $SNMPWALK_BIN = "/usr/bin/snmpwalk";

# never modify these values, as we have to pass them back to Zabbix within the JSON element!
my $OPT_HOST = $ARGV[0];
my $OPT_SNMPW = $ARGV[1];
my $OPT_OID = $ARGV[2];

# build config
my %OPT_CONFIG;
my $OPT_CONFIG_STRING;
for (my $cnt = 3; $cnt < scalar(@ARGV); $cnt++) {
  my ($_offset, $_len) = split(/\./, $ARGV[$cnt]);
  $OPT_CONFIG{$_offset} = $_len;
  $OPT_CONFIG_STRING = $OPT_CONFIG_STRING . ',' . $ARGV[$cnt];
}

#variables
my %INDEXES;

# assign OPT_OID to opt_oid_escape
my $opt_oid_escape = $OPT_OID;
# prepend a dot (.) to opt_oid_escape in case OPT_OID was specified without one
if($opt_oid_escape !~ m/^\./) {
  $opt_oid_escape = '.' . $opt_oid_escape;
}
# v1.1: cut off trailing dot, if it exists
if($opt_oid_escape =~ m/\.$/) {
  chop($opt_oid_escape);
}
# create snmpw_oid from sanitized OPT_OID
my $snmpw_oid = $opt_oid_escape;

# escape all dots in opt_oid_escape for future regexes
$opt_oid_escape =~ s/\./\\./g;


foreach my $snmpw_line (`$SNMPWALK_BIN $GLOB_SNMPW $OPT_SNMPW $OPT_HOST $snmpw_oid`)
{
    # remove newline
    chomp($snmpw_line);

    # split OID = VALUE
    my ($oid, $value) = split(/ = /, $snmpw_line);

    # sanitize $value, remove start/end quotes
    $value =~ s/^"//;
    $value =~ s/"$//;
    $value =~ s/"/\\"/g;
    $value =~ s/\\/\\\\/g;


    # remove OPT_OID part from $oid
    # and we will get the trailing OID part, where we will extract the INDEX
    #
    # before: .0.0.0.0.0.0.1.0.2.1.0
    $oid =~ s/$opt_oid_escape//;
    # after: .1.0.2.1.0


    # split rest of oid into array
    # index N ($OPT_INDEX) is ARRAY[N] -> ARRAY[$OPT_INDEX]
    my @oid_arr = split(/\./, $oid);
    #contains: 0=>'', 1=>'1', 2=>'0', 3=>'2', 4=>'1', 5=>'0'

    # put indexes into @%indexes
    my $line_index;
    # fetch offset from OPT_CONFIG
    foreach my $_offset (sort keys %OPT_CONFIG) {
      # set $index to index (OID part)
      my $index = $oid_arr[$_offset];
      # when complete $index by looking at len (stored in $OPT_CONFIG{$_offset})
      for (my $i = 1; $i < $OPT_CONFIG{$_offset}; $i++) {
        #v1.1: next if oid_arr element is empty. this can happen for dymanic length indexes
        next if($oid_arr[$_offset+$i] =~ m/^$/);
        # attach next OID part for len of index
        $index = $index .'.'. $oid_arr[$_offset+$i];
      }
      $line_index = $line_index . ";$index";
    }
    # assign value to INDEXES Hash. INDEX => VALUE
    $INDEXES{$line_index} = $value;
}

#
# print JSON object
#

print "{\n";
# from Zabbix 2.0.0rc1 the array name has changed to 'data'
print "\t\"data\":[\n";

my $first_line = 1;
#for (my $i = 0; $i < $#INDEXES; $i++) {
foreach my $line_index (sort keys %INDEXES) {

    # print if it is not the first line
    if($first_line) {
      # we are at first line
      $first_line = 0;
    } else {
      # we are not at first line
      print "\t,\n";
    }

    print "\t\t{\n";

    # print all INDEXES
    my $ctr = 1;
    foreach my $index ( split(/;/, $line_index) ) {
      next if (!defined($index));
      print "\t\t\"{#ADVSNMPINDEX$ctr}\": \"$index\",\n";
      $ctr++
    }
    # print value if there are indexes (ctr would be 2 for one index)
    if ($ctr > 1) {
      print "\t\t\"{#ADVSNMPVALUE}\":\"$INDEXES{$line_index}\"\n";
      print "\t\t}";
    } else {
      # if there is no index, close JSON
      print "\t\t}";
 }

} #for

print "\n\t]\n";
print "}\n";

  1. 첨부된 xml파일 import 할것




'Zabbix' 카테고리의 다른 글

Linux DISK IO 모니터링  (0) 2018.11.30
zabbix Agent 설치  (0) 2018.10.26
brocade SAN fc port zabbix 모니터링  (0) 2018.10.26
오라빅스 TableSpace 모니터링 쿼리 수정  (0) 2018.09.13
Zabbix Linux Disk IO 모니터링  (0) 2018.09.07
posted by bedbmsguru