整体架构
mysql
由于 mysql slowlog 与cloudwatch log集成，因此只需要在控制台配置RDS，将slowlog 将log 直接输出到cloudwatch log 中即可
在 cloudwatch logs 控制台 配置 将cloudwatch logs 输出到 elasticsearch，AWS会自动生成相应的lambda函数，通过Lambda
最终将日志传输到elasticsearch中postgresql
pg不支持将slowlog导入到cloudwatch 中，因此需要自己实现Lambda函数，函数的大概逻辑是使用boto3下载slowlog，并输入到
elasticsearh中，最后通过Cloudwatch Event 每小时触发一次lambda函数
#/usr/bin/env python
#coding utf-8
#put pg slowlog to elasticsearch
import re
import os
import boto3
from datetime import datetime,timedelta
from elasticsearch import Elasticsearch,RequestsHttpConnection
from requests_aws4auth import AWS4Auth
endpoint = 'xxx'
# 由于es集群创建的时候没有设置在vpc内，对外暴露的是公网接口，所以请求的时候需要通过access-key进行验证
awsauth = AWS4Auth(os.environ['ACCESS_KEY'], os.environ['SECRET_KEY'], 'us-west-2', 'es')
index_name = 'logstash-pg-slowlog-{0}'.format(datetime.now().strftime('%Y.%m.%d'))
indexDoc = {
"dataRecord" : {
"properties" : {
"timestamp": {
"type" : "date"
},
"message" : {
"type" : "string",
}
}
}
}
def get_pg_slowlog(rds_instance_id):
client = boto3.client('rds')
Marker = '0'
additionalDataPending = True
logfile_prefix = 'error/postgresql.log.'
logfile_name = logfile_prefix+(datetime.now()-timedelta(hours=1)).strftime('%Y-%m-%d-%H')
while additionalDataPending:
response = client.download_db_log_file_portion(
DBInstanceIdentifier = rds_instance_id,
LogFileName = logfile_name,
Marker = Marker,
NumberOfLines = 4000
)
Marker = response['Marker']
additionalDataPending = response['AdditionalDataPending']
p = r'.*duration.*\n(?:(?:\t){1,}.*\n){0,}'
try:
content = re.findall(p,response['LogFileData'])
yield ''.join(content)
except Exception as e:
print(f'no slowlog found in file {logfile} [Error]{e}')
def connect_es(EndPoint):
try:
esclient = Elasticsearch(
hosts=[{'host':EndPoint, 'port':443}],
use_ssl=True,
http_auth=awsauth,
verify_certs=True,
connection_class=RequestsHttpConnection
)
print('connect es ok')
return esclient
except Exception as e:
print(f'Unable to connect to{0esEndPoint:{e}')
exit(1)
def put_slowlog(message):
body = {
'message': message,
'timestamp': datetime.now().isoformat()
}
es_client = connect_es(endpoint)
es_client.indices.create(
index = index_name,
ignore = 400,
body = indexDoc
)
try:
es_client.index(
index = index_name,
doc_type = 'pg-slowlog',
body = body
)
print('put slowlog successed')
except Exception as e:
print(f"put slowlog failed:{e}")
def lambda_handler(event, context):
rds_list = ['webrdsprod','account-prod']:
for rds in rds_list:
slow_log = get_pg_slowlog(rds)
try:
for line in slow_log:
put_slowlog(line)
except Exception as e:
print(f'No Slow Log:{e}')
