1#@!#!123s

: / opt / tier2c /
Filename : check_mysql
back
#!/opt/support/venv/bin/python3
"""MySQL RADS tool - T2C version"""

import argparse
import configparser
import subprocess
from collections import Counter
import sys
import pymysql
from pymysql.optionfile import Parser as PyMySQLParser


def parse_args():
    """Parse commandline arguments"""
    parser = argparse.ArgumentParser(
        description='MySQL RADS tool - T2C version'
    )
    group = parser.add_mutually_exclusive_group(required=True)
    group.add_argument(
        '--sockets',
        action='store_true',
        help='Show number of open connections vs max connections',
    )
    group.add_argument(
        '--active',
        action='store_true',
        help='Display databases with the most currently running queries',
    )
    args = parser.parse_args()
    return args


def get_max_connections() -> int:
    """Read mysqld:max_connections from /etc/my.cnf"""
    try:
        parser = PyMySQLParser(strict=False)
        if not parser.read('/etc/my.cnf'):
            return 100  # default
        return parser.get('mysqld', 'max_connections')
    except configparser.Error:
        return 100  # default


def count_mysql_conns() -> None:
    """Count used/available mysql connections
    using netstat in case the connections are exhausted"""
    try:
        # stderr will go to tty
        conns = subprocess.check_output(
            ['netstat', '-an'], encoding='utf-8'
        ).splitlines()
    except subprocess.CalledProcessError:
        sys.exit("netstat -an failed")
    conns = [x for x in conns if 'ESTABLISHED' in x or 'CONNECTED' in x]
    conns = [x for x in conns if ':3306' in x or 'mysql' in x]
    max_conns = get_max_connections()
    print(f'MySQL Connections: {len(conns)} / {max_conns}')


def show_active() -> None:
    """Show a count of active connections for each mysql user"""
    try:
        with pymysql.connect(
            host='localhost',
            database='INFORMATION_SCHEMA',
            read_default_file="/root/.my.cnf",
        ) as conn:
            with conn.cursor() as cur:
                cur.execute("SELECT USER FROM PROCESSLIST")
                active = Counter([x[0] for x in cur.fetchall()])
    except pymysql.Error as exc:
        sys.exit(str(exc))
    sorted_active = sorted(iter(active.items()), key=lambda x: x[1])
    for user, count in sorted_active:
        print(user, count, sep=': ')


def main():
    """Runs either count_mysql_conns() or show_active()"""
    args = parse_args()
    if args.sockets:
        return count_mysql_conns()
    if args.active:
        return show_active()
    raise NotImplementedError


if __name__ == '__main__':
    main()