Skip to content

Instantly share code, notes, and snippets.

@wido
Last active September 21, 2017 14:03
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save wido/7e68fc85505269b04d4a5d20db48eb4e to your computer and use it in GitHub Desktop.
Save wido/7e68fc85505269b04d4a5d20db48eb4e to your computer and use it in GitHub Desktop.
Send MariaDB User statistics to InfluxDB
#!/usr/bin/env python
"""
Author: Wido den Hollander <wido@pcextreme.nl>
Resources:
- https://mariadb.com/kb/en/library/user-statistics/
- http://influxdb-python.readthedocs.io/en/latest/
"""
import mysql.connector
import ConfigParser as configparser
import influxdb
import socket
import json
import datetime
INFLUX_HOST = "stats"
INFLUX_DB = "mysql_stats"
def get_mysql_connection():
config = configparser.ConfigParser()
config.read('/root/.my.cnf')
return mysql.connector.connect(password=config.get('client', 'password'), user='root')
def get_mysql_user_statistics():
conn = get_mysql_connection()
try:
cursor = conn.cursor()
cursor.execute("SHOW USER_STATISTICS")
result = list()
for row in cursor:
result.append({'user': row[0], 'total_connections': row[1], 'concurrent_connections': row[2],
'select_commands': row[14], 'update_commands': row[15],
'other_commands': row[16], 'max_statement_time_exceeded': row[17]})
cursor.execute("FLUSH USER_STATISTICS")
cursor.close()
return result
except Exception as exc:
print(exc)
finally:
conn.close()
def send_data_to_influx(body):
client = influxdb.InfluxDBClient(host=INFLUX_HOST, port=8086, database=INFLUX_DB)
client.write_points(body)
def main():
hostname = socket.gethostname()
now = datetime.datetime.utcnow().isoformat()
stats = get_mysql_user_statistics()
body = list()
for row in stats:
for name in ['total_connections', 'concurrent_connections', 'select_commands', 'update_commands', 'other_commands', 'max_statement_time_exceeded']:
if row[name] > 0:
body.append({'measurement': name, 'time': now, 'fields': {'value': row[name]}, 'tags': {'user': row['user'], 'host': hostname}})
send_data_to_influx(body)
if __name__ == '__main__':
main()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment