태그 Archives: mysql

zabbix-agent2 추가 템플릿 적용

감시대상에 zabbix-agent2 를 설치 했다면 아래와 같이 추가 플러그인을 통해서 좀더 상세한 감사를 할 수 있다.
Centos는 6 이상 부터 zabbix-agent2 를 사용할 수 있고, Ubuntu는 18.04 이상 부터 zabbix-agent2 를 사용할 수 있다.

1. docker

docker.sock 파일 권한을 주어야(usermod) 하기 때문에 zabbix 계정의 서브 그룹으로 docker 를 추가 해야 작동 한다.

~]# sudo -u zabbix zabbix_agent2 -c /etc/zabbix/zabbix_agent2.conf -t docker.containers

cannot fetch data: get "http://1.28/info": dial unix /var/run/docker.sock: connect: permission denied.

~]# usermod -aG docker zabbix

~]# systemctl restart zabbix-agent2.service

2025-02-17_161342

2. smartctl 을 이용한 디스크 장애 사전 탐지

리눅스

물리적인 HDD 가연결된 서버의 경우 smartctl 을 설치 해서 디스크의 장애를 사전에 탐지할 수 있다.(가상 서버는 불필요 하겠지 ‘ㅅ’a)
smartctl v7.1 이상을 요구 하기 때문에 Ubuntu 18.04 이상 // Rockylinux/Almalinux 8 이상에서 사용이 가능 하며 아래의 명령어로 설치 할 수 있다.

~]# dnf install -y smartmontools

~]# smartctl -V

smartctl 7.1 2019-12-30 r5022 [x86_64-linux-5.11.0-34-generic] (local build)

zabbix 계정에 smartctl 명령어 위치를 확인하고 password 없이 smartctl 사용 권한을 부여 한다.

~]# which smartctl

/usr/sbin/smartctl

~]# echo "zabbix ALL=NOPASSWD: /usr/sbin/smartctl" > /etc/sudoers.d/zabbix

smartctl 명령어 및 zabbix_agent2 명령어를 실행해서 테스트를 하고 정상적 이라면 zabbix-agent2 서비스를 재시작 한다.

~]# smartctl --scan

~]# smartctl -a /dev/sda

~]# sudo -u zabbix zabbix_agent2 -c /etc/zabbix/zabbix_agent2.conf -t smart.disk.discovery

~]# systemctl restart zabbix-agent2.service

윈도우

https://sourceforge.net/projects/smartmontools/files/ 에서 smartmontools-7.4-1.win32-setup.exe 설치 파일을 받아 설치 한다.

워드패드를 관리자 권한으로 실행 한뒤에 C:\Program Files\Zabbix Agent 2\zabbix_agent2.d\plugins.d\smart.conf 파일을 불러와서 Plugins.Smart.Path을 다음과 같이 수정 한다.

1	Plugins.Smart.Path=C:\PROGRA~1\SMARTM~1\bin\smartctl.exe

설치 파일(exe)을 설치 할때 %PATH%를 유져로 잡아주기 때문에 그냥 cmd 나 smartctl 에서의 명령어 사용은 문제가 없지만 zabbix agent 2 서비스에서 호출을 해야 하기 때문에 다음 그림과 같이 시스템 변수로 %PATH% 를 잡아준다.

2025-02-27_173546 2025-02-27_173606 2025-02-27_173649 2025-02-27_173755

powershell 을 관리자 모드로 실행 해서 테스트를 하고 정상적 이라면 zabbix-agent2 서비스를 재시작 한다.

PS C:\> smartctl --scan

PS C:\> smartctl -a /dev/sda

PS C:\> cd 'C:\Program Files\Zabbix Agent 2\'

PS C:\> .\zabbix_agent2.exe -t smart.disk.discovery

PS C:\> Get-Service -DisplayName "Zabbix Agent 2" | Restart-Service

zabbix-server 콘테이너 안에서 zabbix_get 으로 클라이언트에 호출을 할 수 있다. (포트 10051)

~]# docker exec -it zabbix-server bash

:~$ zabbix_get -s [감시대상IP] -k [smart.disk.get | smart.disk.discovery | smart.attribute.discovery]

:~$ zabbix_get -s 192.168.0.110 -k smart.disk.discovery

호출을 했을때 마지막에
exit status 2 는 zabbix-agent2 가 smartctl 을 실행 할 수 없는 경우이다. 리눅스의 경우 sudo 권한 윈도우의 경우 path 가 문제일 가능성이 높다.
exit status 4 코드가 발생할 경우 디스크의 S.M.A.R.T 가 꺼져 있어서 확인할 수 없는 경우 이다. (레이드카드<->smartctl 호환성 문제)

웹 UI 에서 세팅

zabbix 관리자 화면에서 생성된 호스트 템플릿에 SMART by zabbix agent 2를 추가 한다.

2025-02-18_132009

smartctl 은 RAID카드가 장착된 경우 한계가 있다. ( https://www.smartmontools.org/wiki/Supported_RAID-Controllers )

2025-02-28_091214

위 표를 보면 알겠지만 Linux에서는 대부분 지원 하지만 Windows 일 경우 지원하는 하드웨어가 좀 한정적이다.

문제점1

smartmontools 버전이 7.0 이하 일때는….

지원되지 않는 OS 일경우 smartctl 명령어를 최신버전을 컴파일 해서 설치 한다. (gcc-c++, make 가 설치되어 있어야 한다.)
(Rockylinux 8.5 에서는 smartctl 버전이 7.1 이지만 정상 동작 하지 않았고 7.4를 깔면 동작이 가능했다.)

메뉴얼: https://www.smartmontools.org/wiki/Download#Installfromthesourcetarball
다운로드: https://sourceforge.net/projects/smartmontools/files/

~]# cd /opt

~]# tar xvfzp smartmontools-7.4.tar.gz

~]# cd smartmontools-7.4

~]# ./configure

~]# make

~]# make install

make install 을 하면 centos 7 기준 으로 /usr/local/sbin/smartctl 위치에 설치 된다.

sudo 권한 부여 및 /etc/zabbix/zabbix_agent2.d/plugins.d/smart.conf 파일을 수정한다.

1 2	~]# echo "zabbix ALL=(ALL) NOPASSWD: /usr/local/sbin/smartctl" > /etc/sudoers.d/zabbix ~]# sed -i "s+# Plugins.Smart.Path=+Plugins.Smart.Path=/usr/local/sbin/smartctl+g" /etc/zabbix/zabbix_agent2.d/plugins.d/smart.conf

문제점2

레이드 카드가 달린 HP 서버 linux 에서는 cciss 으로 명령어를 수집을 하긴 하는데 문제가 발생 하였다.
명령어 호출을 어찌하는지 아래 명령어로 확인 하는데만 26초가 소요가 되었다.

~]# zabbix_agent2 -t smart.disk.discovery -v |grep "executing smartctl command"

sudo -n smartctl -j -V

sudo -n smartctl --scan -j

sudo -n smartctl --scan -d sat -j

sudo -n smartctl -a /dev/nvme0 -j

sudo -n smartctl -a /dev/nvme1 -j

sudo -n smartctl -a /dev/sda -d 3ware,0 -j

sudo -n smartctl -a /dev/sda -d areca,1 -j

sudo -n smartctl -a /dev/sda -d cciss,0 -j

sudo -n smartctl -a /dev/sda -d sat -j

sudo -n smartctl -a /dev/sda -d scsi -j

sudo -n smartctl -a /dev/sdb -d 3ware,0 -j

sudo -n smartctl -a /dev/sdb -d areca,1 -j

sudo -n smartctl -a /dev/sdb -d cciss,0 -j

sudo -n smartctl -a /dev/sdb -d sat -j

sudo -n smartctl -a /dev/sdb -d scsi -j

...

서버에 존재하지도 않는 3ware, areca 등을 호출하면서 에러메세지 리턴을 json 으로 파싱하면서 많은 시간을 쓴다는걸 확인 하였고 다음과 같이 해결 하였다.
먼저 위 명령어를 참조 해서 정상적인 동작을 하는 명령어를 찾는다. (SMART 가 Enabled 으로 나오는 명령어를 찾는다)

~]# smartctl -a /dev/sda -d cciss,0

...

Serial number: WB0000F1

SMART support is: Enabled

...

~]# smartctl -a /dev/sdb -d cciss,1

...

Serial number: WB0000GG

SMART support is: Enabled

...

zabbix agent 가 실행하는 명령어를 보면 nvme0, nvme1 호출은 정상적이다.
하지만 sdb 는 cciss,1 이 아닌 cciss,0 으로만 호출했기 때문에 정상 호출이 안되어서 인식하지 못한다.
그래서 그에 대응할 수 있도록 sd[x] -d scsi에 반응 하도록 하고 스 이외 명령어 실행은 빠르게 종료 되도록 exit 2로 반환 하도록 스크립트를 만들어 적용 한다.

~]# mkdir -p /etc/zabbix/script

~]# touch /etc/zabbix/script/smartctl_enteroa

~]# chmod 755 /etc/zabbix/script/smartctl_enteroa

#!/usr/bin/env bash

# RAID HPE P408i-a SR Gen10 1.34

# /dev/nvme0 -d nvme

# /dev/nvme1 -d nvme

# /dev/sda -d scsi -> /dev/sda -d cciss,0

# /dev/sdb -d scsi -> /dev/sdb -d cciss,1

# /dev/sdc -d scsi -> /dev/sdc -d cciss,2

# /dev/sdd -d scsi -> /dev/sdd -d cciss,3

# /dev/sde -d scsi -> /dev/sde -d cciss,4

##########################################################

#SMARTCTL=/usr/local/sbin/smartctl

SMARTCTL=/usr/sbin/smartctl # need to change to fit the Server.

RAIDCARD=cciss # need to change to fit the Server.

#RAIDCARD=areca

#RAIDCARD=3ware

if [[ -n $(awk '/-j -V/{print}

/nvme/{print}

/scsi/{print}

/--scan -j/{print}/--scan -d sat -j/{print}' <<< $@) ]];then

### only run with scsi but it change to cciss,[X]

$SMARTCTL $(sed "s/sda -d scsi/sda -d $RAIDCARD,0/g;

s/sdb -d scsi/sdb -d $RAIDCARD,1/g;

s/sdc -d scsi/sdc -d $RAIDCARD,2/g;

s/sdd -d scsi/sdd -d $RAIDCARD,3/g;

s/sde -d scsi/sde -d $RAIDCARD,4/g" <<< $@)

else

exit 2

exit 0

sudo 권한에 생성한 스크립트의 실행 권한을 추가 한다.

1	zabbix ALL=NOPASSWD: /usr/sbin/smartctl, /etc/zabbix/script/smartctl_enteroa

/etc/zabbix/zabbix_agent2.d/plugins.d/smart.conf 에서 만든 스크립트을 실행하도록 한다.

1	Plugins.Smart.Path=/etc/zabbix/script/smartctl_enteroa

zabbix agent 2 를 재시작 한다.

1	~]# systemctl restart zabbix-agent2.service

문제점3

Dell 서버에서는 또 다른 문제가 확인이 되었다.
디스크가 12개 정도 주렁주렁 달린 서버의 경우 위와 같은 방법으로 스크립트만 별도 작성해서 적용 하였을때에도 반환 속도가 4 ~ 7초 정도로 확인이 되었다.

#!/usr/bin/env bash

# Dell - Broadcom / LSI MegaRAID SAS 2208 [Thunderbolt] (rev 05)

# /dev/bus/0 -d megaraid,0

# /dev/bus/0 -d megaraid,1

# ...

# /dev/bus/0 -d megaraid,10

# /dev/bus/0 -d megaraid,11

##########################################################

SMARTCTL=/usr/local/sbin/smartctl

### 3ware,0 / areca,1 / cciss,0 / sat / scsi / megaraid

if [[ -n $(awk '/-j -V/{print}

/--scan -j/{print}

/--scan -d sat -j/{print}

/megaraid/{print}' <<< $@) ]];then

$SMARTCTL $@

else

exit 2

exit 0

smartctl 호출했을때 시간이 3초를 넘어가는 경우 Timeout occurred while gathering data 라는 메세지를 log 에서 확인이 가능하다.
/etc/zabbix/zabbix_agent2.conf 및 /etc/zabbix/zabbix_agent2.d/plugins.d/smart.conf 에 Timeout=, PluginTimeout=, Plugins.Smart.Timeout= 옵션이 있으나
설정해도 3초가 넘어가면 시간 초과로 데이터 수집이 되지 않는다. (유명무실한 옵션….)

~]# docker exec zabbix-server time zabbix_get -s 192.168.0.200 -p 10050 -k smart.disk.discovery

...

... infomation

...

real 0m 4.19s

user 0m 0.00s

sys 0m 0.00s

3. mysql

감시 대상 DB에 zbx_monitor 용 어카운트를 생성해야 한다.

1 2	~]# mysql -h127.0.0.1 -uroot -p mysql password: 루트패스워드

1 2	MySQL [mysql]> CREATE USER 'zbx_monitor'@'%' IDENTIFIED BY '비밀번호'; MySQL [mysql]> GRANT REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON . TO 'zbx_monitor'@'%';

이후 웹 UI – 호스트 설정 에서 다음과 같이 템플릿 Mysql by Zabbix agent 2을 추가 한다.

2025-02-17_173105

메크로 메뉴로 이동해서 다음과 같이 {$MYSQL.DSN}, {$MYSQL.USER}, {$MYSQL.PASSWORD}을 입력 한다. (관리 > 매크로 로는 설정이 되지 않는 부분이다.)
DSN 에는 tcp://localhost:3306을 입력하고, 패스워드는 텍스트 => 비밀 문자 형식으로 하여 보이지 않도록 한다.

2025-03-07_113701

Docker 으로 생성된 DB 일경우 아래와 같이 {$MYSQL.DSN} 부분을 tcp://[ContainerIP]:3306으로 지정 해야 한다.

2025-03-07_113758

4. postgres

마찬가지로 감시 대상 DB에 zbx_monitor 용 어카운트를 생성해야 한다.

1 2	~]# psql -u Dba-ID -p -h localhost postgres password: 루트패스워드

1 2	postgres=> CREATE USER zbx_monitor WITH PASSWORD '비밀번호' INHERIT; postgres=> GRANT pg_monitor TO zbx_monitor;

pg_hba.conf 파일에 하단에 아래 내용을 추가 한다. (경로: /etc/postgresql/[버전]/main/pg_hba.conf)

### for zabbix agent 2

host all zbx_monitor localhost trust

host all zbx_monitor 127.0.0.1/32 md5

host all zbx_monitor ::1/128 scram-sha-256

설정한 이후 postgra 서비스를 재시작 해야 한다.

이후 웹UI 에서의 설정은 다음과 같이 PostgreSQL by Zabbix agent 2를 템플릿에 선언하고 매크로에 {$PG.PASSWORD}를 설정하면 된다.
2025-03-07_165623
2025-03-07_165752

python 에서 mysql 접속 하기

서버사이드 프로그램을 짜더라도 DB에 접근 하여 데이터를 가져다가 작동을 하게 하는 경우가 많다.

python의 경우 프로그래밍 언어 이고 사용자 층도 두껍고 오래 되었기 때문에 대부분 드라이버가 제공이 된다.

그래서 필요한 내용을 설치하여 import 하여 사용 하면 된다 🙂

하지만 db 정보가 소스에 삽입되어 있는 것은 좋지 못하기 때문에 YAML 형식의 문서로 config 파일을 생성하고

그 config 파일을 python 에서 읽어서 DB 접속을 해야 한다. (json 은 시인성이 좋지만 주석을 첨부 할 수 없고/xml은 시인성이 너무 떨어진다.)

---

MYSQLDB:

DBHOST: 127.0.0.1

DBPORT: 3306

DBNAME: databasename

DBUSER: sqlusename

DBPASS: userpassword

CHARSET: utf8

DBCA: /some/where/ca.pem ### mysql-ssl 접속을 위한 Root CA

DBKEY: /some/where/ccc.key ### mysql-ssl 접속을 위한 Keyfile

DBCERT: /some/where/ccc.cert ### mysql-ssl 접속을 위한 Certificate

#!/usr/bin/env python

#-*- coding: utf-8 -*-

import yaml

import pymysql

def mysqldbcon():

readyaml = yaml.safe_load_all(file('/opt/test/config.yaml', 'r')) ### 경로를 모두 지정하는것이 좋다.

for a in readyaml:

for b, c in a.items():

if b == 'MYSQLDB':

conn = pymysql.connect(

host = c['DBHOST'], port = c['DBPORT'],

db = c['DBNAME'], user = c['DBUSER'], password = c['DBPASS'],

#ssl = { 'ssl': { 'ca': c['DBCA'], 'key': c['DBKEY'], 'cert': c['DBCERT'] } }, ### ssl 접속을 쓰는 것을 추천하지만...

charset = c['CHARSET']

)

return conn

def main():

db = mysqldbcon()

with db.cursor(pymysql.cursors.DictCursor) as cur:

sqlquery = 'SELECT VERSION()'

cur.execute(sqlquery)

rows = cur.fetchall()

for a in rows:

print a['VERSION()']

db.close()

if __name__ == "__main__":

main()

exit(0)

~]# ./test.py

10.3.14-MariaDB-log

여담으로 python은 기본적으로 CentOS linux 에 대부분 설치되어 있으나 import 하는 pymysql 과 yaml은 설치 되어 있지 않기 때문에 아래와 같이 pip를 설치 하고 pip으로 설치 한다..

~]# yum install python2-pip

...

~]# pip install pymysql pyyaml

...

YAML의 경우 python 혼자만 쓰는 설정파일일 경우 info.py 를 만들고 import 하는게 편하지만 다른 언어의 프로그램 이나 로직과 겸용해야 할때 필요하겠지..

데이터베이스 마이그레이션

EUC-KR 에서 UTF-8 로 DB마이그레이션을 진행할때 캐릭터셋 전환에 따라 일부 문자가 유실 되거나 깨지게 되어 dump된 파일의 복구가 곤란한 경우가 발생하였다.

sql은 복원중 에러가 발생을 할경우 하위 내용을 복구를 하지 않기 때문에..
이러한 경우 보통 sql 파일을 하나하나 수정해가면서 다시 부어넣는 짓을 해야한다 =_=a

DB용량이 작다면 하나하나 수정해줄수 있지만 100M단위를 넘어가는 파일을 변경하긴 어렵다.
때문에 테이블 생성정보와 데이터를 따로 따로 백업을 하고 먼저 테이블을 생성한뒤에 복구하는 방법을 쓰기로 하고 스크립트를 작성하였다.

1 2	~]# mysqldump -u아이디 -p 데이터베이스명 --add-drop-database -d > 테이블.sql ~]# mysqldump -u아이디 -p 데이터베이스명 --skip-extended-insert --no-create-info -c > 데이터.sql

위와 같이 원본서버에서 백업을 할때 데이터를 분리 한뒤에 iconv를 이용해서 캐릭터셋 치환을 하고 캐릭터셋 선언을 바꾸어 준다.

~]# sed -i 's/DEFAULT CHARSET=euckr/DEFAULT CHARSET=utf8/g;1 i\SET NAMES UTF8;' 테이블.sql

~]# iconv -fEUCKR -tUTF8 -c 테이블.sql > 새테이블.sql

~]# iconv -fEUCKR -tUTF8 -c 데이터.sql > 새데이터.sql

이중 -c 옵션은 에러가 나는 문자열을 제외하고 치환하는 옵션이다.
가급적 에러가 나지 않는게 좋겠지만 ‘ㅅ’…

새로운 데이터 베이스에 먼저 테이블 복원을 한다.

1	~]# mysql -u[아이디] -p [데이터베이스명] < 새테이블.sql

아래와 같은 스크립트작성을 한 뒤에 실행한다.

#!/bin/bash

sqlfile="새데이터.sql"

IFS=$'\n'

for a in $(grep ^INSERT $sqlfile)

mysql -u[아이디] -p[데이터베이스패스워드] [데이터베이스명] -s -N -e "$a"

if [[ $? -eq 1 ]];then

echo $c >> error_query.sql

done

IFS 를 통해 구분자를 엔터(\n) 으로 지정한뒤 for 문을 돌려 한줄한줄 복원을 시도 하고
에러가나는 구분은 error_query.sql 파일로 별도 저장을 한다.

추후 error_query.sql 파일을 분석하여 쿼리문을 완성 시켜 재 복원을 하거나… 버리거나.. 할수 있겠다.

테이블 복원중 VARCHAR(255) UTF8 is too long for key, but max length is 1000 bytes 가 나오는 경우가 발생할수 있다.

key로 사용되는 컬럼의 길이가 1000byte를 넘어가면 안된다는 메세지 이다.
(Mysql에서 UTF8의 경우 문자당 3byte 를 사용한다 – utf8mb4 = 4Byte 를 쓴다..)

이경우 key로 사용되는 컬럼의 varchar 값을 최대치인 333이하를 쓰도록 한다.
일반적으로 2개 이상의 컬럼은 하나의 키로 사용하는 부분에서 에러가 난다. 그때는 사용된 컬럼의 합이 333 이하여야 하겠다.

utf8mb4의 경우에는 200 이하로 해야겠고.. 그래서 대략 아래와 같이… 큰 varchar 컬럼을 앞95자까지 인식하도록 하게 한다.

컬럼A varchar(255)

컬럼B varchar(255)

KEY `키명` (`컬럼A`(95),`컬럼B`(95)),

php 에서 dbcon 작성 할때 localhost가 안먹을때.

서버에 APM 이 정상 설치가 되어 있으나

php 로 dbcon 을 할때 127.0.0.1은 먹고 localhost 로는 접속이 불가능할때가 있다.

먼저 mysql로 로그인을 하여 소켓 위치를 파악하자.

[mysql]> SHOW VARIABLES WHERE variable_name = "socket";

+---------------+-----------------+

| Variable_name | Value |

+---------------+-----------------+

| socket | /tmp/mysql.sock |

+---------------+-----------------+

php.ini 파일에서 mysql.default_socket 을 일치시켜준다.

1	mysql.default_socket = /tmp/mysql.sock

그다음 아파치나 php-fpm을 재시작 ‘ㅅ’a

Mysql 5.7 에서 sql 복구중 에러.

mysql 5.7 에서 많은 부분이 바뀐거 같다 ‘ㅅ’a

얘를 들어 ‘can\’t’ 와 같이 싱글 쿼테이션 안에 존재하는 문자열인 ‘ 의 경우 이스케이프 문자열 \’ 으로 표기하는게 일반적이다.

mysql import 과정에서 mysql syntex error 가 발생한다.

공식 메뉴얼에서는 ‘can”t’ 와 같이 실글쿼테이션두개 연속 표기방법으로 바뀌었다.

테스트 해본 결과 “can\’t” 더블 쿼테이션으로 표기를 해도 되는것으로 확인 되었다.

또한 mysql.user 테이블의 Password 컬럼의 이름이 authentication_string 으로 변경 되었다.

GRANT 를 이용한 패스워드 지정명령어까지는 삭제 되지 않았으나 추후 삭제된다는 워닝 메세지를 볼수 있다.

차후 GRANT 문이 없어질 경우 DB패스워드 지정은 명령어는 아래와 같다.

ALTER USER user IDENTIFIED BY ‘auth_string’;

아래 자세한 메뉴얼 이다.

http://dev.mysql.com/doc/refman/5.7/en/string-literals.html