[Prometheus - 원하는 데이터 뽑기] part 3 use. mysql_exporter
MySQL_exporter (모니터링 대상 서버에서 진행 mysql server)
MySQL 서버에 SQL을 질의한 결과를 Prometheus에 쌓은 뒤 이를 Grafana에서 대시보드로 구성하기 위해 mysql_exporter를 구성 MySQL에 적재되어 있는 테이블 데이터에 SELECT SQL을 질의한 결과를 Prometheus에 적재할 수 있다면, Grafana의 다양한 그래프를 이용해서 실시간 데이터 변경 추이를 시각적으로 조회 할 수 있습니다.
원하는 데이터를 뽑아 적재하여 grafana로 가시화 할거며, 원하는 값을 뽑기 위해서는 select SQL쿼리를 가지고 프로메테우스로 뽑아 올 예정입니다.
Prometheus MySQL Exporter를 설치하기 위해서는 다음과 같은 Python 관련 패키지를 미리 설치 해야 합니다.
- Python 3.X 설치 : Python으로 개발한 프로그램을 설치하기 위해 필요.
- mysqlclient-python 설치 : Python용 MySQL 클라이언트로 MySQL에 접속하기 위해 필요.
- Prometheus Python Client 설치 : Prometheus 연동을 위한 Python 클라이언트
prometheus-mysql-exporter 를 설치하기 위해서는 Python 3 버전이 미리 설치되어 있어야 합니다.
하지만 RHEL, CentOS 등의 범용 리눅스 6.X 버전에서는 Python 2.X 버전이 설치되어 있을 가능성이 있습니다. 그러므로 Python 3.X 버전을 추가로 설치해야 하며, 이때 2.X 버전은 삭제하지 않고 3.X 버전을 추가로 설치합니다.(2.X버전을 3.X 버전으로 대체한다면 기존의 2.X 버전을 연동하는 프로그램에 영향이 있을 수 있기 때문)
[root@prometheus ~]# git clone https://github.com/braedon/prometheus-mysql-exporter.git
[root@prometheus prometheus-mysql-exporter]# cd /root/prometheus-mysql-exporter/prometheus_mysql_exporter
## select 결과 한글깨짐을 방지하기 위해 char set 추가
[root@prometheus prometheus_mysql_exporter]# vi __init__.py
...(생략)
mysql_kwargs = dict(host=mysql_host,
port=mysql_port,
user=mysql_username,
password=mysql_password,
charset='utf8',
# Use autocommit mode to avoid keeping the same transaction across query
# runs when the connection is reused. Using the same transaction would
# prevent changes from being reflected in results, and therefore metrics.
# Note: Queries could theoretically change data...
autocommit=True)
if mysql_timezone:
mysql_kwargs['init_command'] = "SET time_zone = '{}'".format(mysql_timezone)
mysql_client = PersistentDB(creator=pymysql, **mysql_kwargs)
if queries:
for query_name, (interval, cron, cron_tz,
db_name, query, value_columns,
on_error, on_missing) in queries.items():
schedule_job(scheduler, interval, cron, cron_tz,
run_query, mysql_client, query_name,
db_name, query, value_columns, on_error, on_missing)
else:
log.warning('No queries found in config file(s)')
REGISTRY.register(QueryMetricCollector())
log.info('Starting server...')
start_http_server(port)
log.info('Server started on port %(port)s', {'port': port})
scheduler.run()
...(생략)
[root@mysql prometheus-mysql-exporter]# cd ..
[root@prometheus prometheus-mysql-exporter-master]# python3.6 setup.py install
running install
running bdist_egg
running egg_info
creating prometheus_mysql_exporter.egg-info
writing prometheus_mysql_exporter.egg-info/PKG-INFO
writing dependency_links to prometheus_mysql_exporter.egg-info/dependency_links.txt
writing entry points to prometheus_mysql_exporter.egg-info/entry_points.txt
writing requirements to prometheus_mysql_exporter.egg-info/requires.txt
writing top-level names to prometheus_mysql_exporter.egg-info/top_level.txt
writing manifest file 'prometheus_mysql_exporter.egg-info/SOURCES.txt'
reading manifest file 'prometheus_mysql_exporter.egg-info/SOURCES.txt'
reading manifest template 'MANIFEST.in'
writing manifest file 'prometheus_mysql_exporter.egg-info/SOURCES.txt'
installing library code to build/bdist.linux-x86_64/egg
running install_lib
running build_py
creating build
creating build/lib
creating build/lib/prometheus_mysql_exporter
copying prometheus_mysql_exporter/__main__.py -> build/lib/prometheus_mysql_exporter
copying prometheus_mysql_exporter/metrics.py -> build/lib/prometheus_mysql_exporter
copying prometheus_mysql_exporter/parser.py -> build/lib/prometheus_mysql_exporter
copying prometheus_mysql_exporter/scheduler.py -> build/lib/prometheus_mysql_exporter
copying prometheus_mysql_exporter/utils.py -> build/lib/prometheus_mysql_exporter
copying prometheus_mysql_exporter/__init__.py -> build/lib/prometheus_mysql_exporter
creating build/bdist.linux-x86_64
creating build/bdist.linux-x86_64/egg
creating build/bdist.linux-x86_64/egg/prometheus_mysql_exporter
copying build/lib/prometheus_mysql_exporter/__main__.py -> build/bdist.linux-x86_64/egg/prometheus_mysql_exporter
copying build/lib/prometheus_mysql_exporter/metrics.py -> build/bdist.linux-x86_64/egg/prometheus_mysql_exporter
copying build/lib/prometheus_mysql_exporter/parser.py -> build/bdist.linux-x86_64/egg/prometheus_mysql_exporter
copying build/lib/prometheus_mysql_exporter/scheduler.py -> build/bdist.linux-x86_64/egg/prometheus_mysql_exporter
copying build/lib/prometheus_mysql_exporter/utils.py -> build/bdist.linux-x86_64/egg/prometheus_mysql_exporter
copying build/lib/prometheus_mysql_exporter/__init__.py -> build/bdist.linux-x86_64/egg/prometheus_mysql_exporter
byte-compiling build/bdist.linux-x86_64/egg/prometheus_mysql_exporter/__main__.py to __main__.cpython-36.pyc
byte-compiling build/bdist.linux-x86_64/egg/prometheus_mysql_exporter/metrics.py to metrics.cpython-36.pyc
byte-compiling build/bdist.linux-x86_64/egg/prometheus_mysql_exporter/parser.py to parser.cpython-36.pyc
byte-compiling build/bdist.linux-x86_64/egg/prometheus_mysql_exporter/scheduler.py to scheduler.cpython-36.pyc
byte-compiling build/bdist.linux-x86_64/egg/prometheus_mysql_exporter/utils.py to utils.cpython-36.pyc
byte-compiling build/bdist.linux-x86_64/egg/prometheus_mysql_exporter/__init__.py to __init__.cpython-36.pyc
creating build/bdist.linux-x86_64/egg/EGG-INFO
copying prometheus_mysql_exporter.egg-info/PKG-INFO -> build/bdist.linux-x86_64/egg/EGG-INFO
copying prometheus_mysql_exporter.egg-info/SOURCES.txt -> build/bdist.linux-x86_64/egg/EGG-INFO
copying prometheus_mysql_exporter.egg-info/dependency_links.txt -> build/bdist.linux-x86_64/egg/EGG-INFO
copying prometheus_mysql_exporter.egg-info/entry_points.txt -> build/bdist.linux-x86_64/egg/EGG-INFO
copying prometheus_mysql_exporter.egg-info/requires.txt -> build/bdist.linux-x86_64/egg/EGG-INFO
copying prometheus_mysql_exporter.egg-info/top_level.txt -> build/bdist.linux-x86_64/egg/EGG-INFO
zip_safe flag not set; analyzing archive contents...
creating dist
creating 'dist/prometheus_mysql_exporter-0.5.0-py3.6.egg' and adding 'build/bdist.linux-x86_64/egg' to it
removing 'build/bdist.linux-x86_64/egg' (and everything under it)
Processing prometheus_mysql_exporter-0.5.0-py3.6.egg
Copying prometheus_mysql_exporter-0.5.0-py3.6.egg to /usr/local/lib/python3.6/site-packages
Adding prometheus-mysql-exporter 0.5.0 to easy-install.pth file
Installing prometheus-mysql-exporter script to /usr/local/bin
...(생략)
Installed /usr/local/lib/python3.6/site-packages/zipp-3.11.0-py3.6.egg
Searching for typing-extensions>=3.6.4
Reading https://pypi.org/simple/typing-extensions/
Downloading https://files.pythonhosted.org/packages/0b/8e/f1a0a5a76cfef77e1eb6004cb49e5f8d72634da638420b9ea492ce8305e8/typing_extensions-4.4.0-py3-none-any.whl#sha256=16fa4864408f655d35ec496218b85f79b3437c829e93320c7c9215ccfd92489e
Best match: typing-extensions 4.4.0
Processing typing_extensions-4.4.0-py3-none-any.whl
Installing typing_extensions-4.4.0-py3-none-any.whl to /usr/local/lib/python3.6/site-packages
Adding typing-extensions 4.4.0 to easy-install.pth file
Installed /usr/local/lib/python3.6/site-packages/typing_extensions-4.4.0-py3.6.egg
Searching for prometheus-client==0.15.0
Best match: prometheus-client 0.15.0
Adding prometheus-client 0.15.0 to easy-install.pth file
Using /usr/local/lib/python3.6/site-packages
Searching for six==1.14.0
Best match: six 1.14.0
Adding six 1.14.0 to easy-install.pth file
Using /usr/lib/python3.6/site-packages
Finished processing dependencies for prometheus-mysql-exporter==0.5.0
--============================================================================
-- 다음과 같이 에러가 발생한다면,
-- /etc/ld.so.conf 에 MySQL 라이브러리가 등록되었는지 확인하여 추가해준다.
--============================================================================
[root@pmmclient mysqlclient-1.3.9-py3.5-linux-x86_64.egg]# prometheus-mysql-exporter --help
Traceback (most recent call last):
File "/usr/local/bin/prometheus-mysql-exporter", line 9, in <module>
load_entry_point('prometheus-mysql-exporter==0.2.0.dev1', 'console_scripts', 'prometheus-mysql-exporter')()
File "/usr/local/lib/python3.5/site-packages/pkg_resources/__init__.py", line 542, in load_entry_point
return get_distribution(dist).load_entry_point(group, name)
File "/usr/local/lib/python3.5/site-packages/pkg_resources/__init__.py", line 2569, in load_entry_point
return ep.load()
File "/usr/local/lib/python3.5/site-packages/pkg_resources/__init__.py", line 2229, in load
return self.resolve()
File "/usr/local/lib/python3.5/site-packages/pkg_resources/__init__.py", line 2235, in resolve
module = __import__(self.module_name, fromlist=['__name__'], level=0)
File "/usr/local/lib/python3.5/site-packages/prometheus_mysql_exporter-0.2.0.dev1-py3.5.egg/prometheus_mysql_exporter/__init__.py", line 7, in <module>
File "/usr/local/lib/python3.5/site-packages/mysqlclient-1.3.9-py3.5-linux-x86_64.egg/MySQLdb/__init__.py", line 19, in <module>
import _mysql
ImportError: libmysqlclient.so.18: cannot open shared object file: No such file or directory
[root@pmmclient constants]# cat /etc/ld.so.conf
include ld.so.conf.d/*.conf
/usr/lib64
[root@pmmclient constants]# echo /home/mysql/MySQL/lib >> /etc/ld.so.conf && ldconfig
[root@pmmclient constants]# cat /etc/ld.so.conf
include ld.so.conf.d/*.conf
/usr/lib64
/home/mysql/MySQL/lib
■ 모니터링 테스트 데이터 생성
create database kim;
use kim;
create table vote_candidate
(
candidate_id int not null auto_increment
, candidate_name varchar(100) not null
, votes_cnt bigint not null
, primary key(candidate_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='후보';
create table vote_hist
(
vote_hist_seq bigint not null auto_increment
, candidate_id int not null
, vote_date datetime not null
, primary key(vote_hist_seq)
, key vote_hist_idx01(vote_date, candidate_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='투표이력';
insert into vote_candidate(candidate_name, votes_cnt) values('A후보',12);
insert into vote_candidate(candidate_name, votes_cnt) values('B후보',13);
insert into vote_candidate(candidate_name, votes_cnt) values('C후보',14);
insert into vote_candidate(candidate_name, votes_cnt) values('D후보',15);
insert into vote_candidate(candidate_name, votes_cnt) values('E후보',16);
mysql> select * from vote_candidate order by candidate_id;
+--------------+----------------+-----------+
| candidate_id | candidate_name | votes_cnt |
+--------------+----------------+-----------+
| 1 | A후보 | 12 |
| 2 | B후보 | 13 |
| 3 | C후보 | 14 |
| 4 | D후보 | 15 |
| 5 | E후보 | 16 |
+--------------+----------------+-----------+
5 rows in set (0.00 sec)
## 최근 5초간 후보별 득표수 조회
select
vc.candidate_id, vc.candidate_name, ifnull(taba.cnt,0) as cnt
from kim.vote_candidate vc
left join
(
select candidate_id, count(*) as cnt
from kim.vote_hist
where vote_date between date_add(now(), interval -5 second) and now()
group by candidate_id
) taba on vc.candidate_id = taba.candidate_id;
+--------------+----------------+-----+
| candidate_id | candidate_name | cnt |
+--------------+----------------+-----+
| 1 | A후보 | 0 |
| 2 | B후보 | 0 |
| 3 | C후보 | 0 |
| 4 | D후보 | 0 |
| 5 | E후보 | 0 |
+--------------+----------------+-----+
■ Prometheus MySQL Exporter 설정 및 기동
Prometheus MySQL Exporter에서 사용하는 MySQL 데이터 추출용 SQL은 별도의 쿼리 설정 파일로 설정합니다.
이 파일을 “exporter.cfg”로 명명하고 별도의 디렉토리에 생성하여 다음과 같이 추출 SQL을 등록합니다.
Prometheus MySQL Exporter 기본 포트 : 8090
[query_vote_total] : 쿼리헤더. 반드시 “query_”로 시작
QueryIntervalSecs = 5 : 쿼리 실행 주기(초)
QueryStatement = SELECT candidate_id, candidate_name, votes_cnt as cnt FROM votedb.vote_candidate; : 데이터 추출용 쿼리, Like 연산자 등에서 ‘%’글자를 사용한다면 이스케이프 문자를 포함해야 합니다.
QueryValueColumns = cnt : 쿼리 결과값에 대한 컬럼명
[root@prometheus ~]# cd /root/prometheus-mysql-exporter-master
[root@prometheus prometheus-mysql-exporter-master]# vi exporter.cfg
(다 지우고)
[query_vote_total]
QueryIntervalSecs = 5
QueryStatement = SELECT candidate_id, candidate_name, votes_cnt as cnt FROM kim.vote_candidate;
QueryValueColumns = cnt
querydatabase=kim
[query_vote_5second]
QueryIntervalSecs = 5
QueryStatement = select vc.candidate_id, vc.candidate_name, ifnull(taba.cnt,0) as cnt from kim.vote_candidate vc left join (select candidate_id, count(*) as cnt from kim.vote_hist where vote_date between date_add(now(), interval -5 second) and now() group by candidate_id) taba on vc.candidate_id = taba.candidate_id;
QueryValueColumns = cnt
querydatabase=kim
## MySQL Exporter 기동
exporter.cfg에 기재된 쿼리가 문법적으로 맞지 않을때 에러 발생
[root@prometheus prometheus-mysql-exporter-master]# nohup prometheus-mysql-exporter --port=8090 --mysql-server=101.101.208.34:3306 --mysql-user=prouser --mysql-password=prouser --config-file=/root/prometheus-mysql-exporter-master/exporter.cfg > /root/prometheus-mysql-exporter-master/prometheus-mysql-exporter.log 2>&1 &
■ Prometheus 설정 파일 변경 (클라이언트 추가)
[root@prometheus ~]# cd prometheus-2.40.5.linux-amd64/
[root@prometheus prometheus-2.40.5.linux-amd64]# vi prometheus.yml
...(생략)
- job_name: "prometheus"
# metrics_path defaults to '/metrics'
# scheme defaults to 'http'.
static_configs:
- targets: ["localhost:9090"]
##OS MONITOR
- job_name: linux
static_configs:
- targets: ['192.168.100.85:9100']
##DB MONITOR
- job_name: mysql
static_configs:
- targets: ['192.168.100.85:9104']
##Query MONITOR
- job_name: 'mysqlqueryresult'
scrape_interval: 5s
static_configs:
- targets: ['192.168.100.85:8090']
labels:
alias: "kim:3306"
