datablogs: Python
Showing posts with label Python. Show all posts
Showing posts with label Python. Show all posts

Wednesday, December 29, 2021

Capture Basic MySQLRemoteServerDetails

Basic Requirement to get the below details from MySQL Remote Host Using Python

  1. OS Version
  2. Find Physical host / VMWare
  3. MySQL Port
  4. Total RAM / Total CPU and Cores
  5. How many Database and Size ?
  6. Disk space

https://github.com/selvackp/MySQLRemoteServerDetails.git

# Required libraries for program
import paramiko
from mysql.connector import connect, Error

# Required libraries to connect
client = paramiko.SSHClient()
client.set_missing_host_key_policy(paramiko.AutoAddPolicy())

# Remote server details to enter
print('Enter the RemoteHost Login Details')
print('----------------------------------')
print('\t')
re_host_IP = input("RemoteHost IP: ")  # If any IP works , pass that to RemoteHost IP
re_host_user = input("RemoteHost Username: ")
re_host_password = input("RemoteHost Password: ")  # If no password for AWS machine just enter to fill the next details
re_host_key_file = input(
    "RemoteHost keyfile name: ")  # If no keys just enter to move next step . We have tested AWS machine , so its required to pass pem key file to connect .
print('\t')

try:
    # Remote Host Server Connectivity
    client.connect(re_host_IP, username=re_host_user, password=re_host_password, key_filename=re_host_key_file)
    stdin, stdout, stderr = client.exec_command('cat /proc/version')
    print('Remote Server OS and Version')
    print('----------------------------')
    print(stdout.readlines()[0])
    print('\t')
    print('Remote Server Total CPU and Cores')
    print('---------------------------------')
    stdin, stdout, stderr = client.exec_command('lscpu')
    print('Total CPU/Core count for Remote server: ', stdout.readlines()[3])
    print('\t')
    print('Connected Server Total RAM in GB')
    print('--------------------------------')
    stdin, stdout, stderr = client.exec_command('vmstat -s')
    print('Total RAM for Remote server: ', stdout.readlines()[0])
    print('\t')
    print('Connected Server Total Disk Spaces')
    print('----------------------------------')
    stdin, stdout, stderr = client.exec_command('df -h --total')
    print('Total Disk for Remote server: ', stdout.read().decode())
    print('\t')

    # MySQL Basic Details code below
    with connect(
            host=re_host_IP,
            user=input("Enter Remote MySQL Username: "),
            password=input("Enter Remote MySQL Password: "),
    ) as connection:
        print('\t')
        get_database_host = "select @@hostname"
        with connection.cursor() as cursor:
            cursor.execute(get_database_host)
            database_host = cursor.fetchone()
            print("Successfully connected Remote MySQL Server :", database_host[0])
            print('\t')
        print(database_host[0], 'Database Name and Size in MB')
        print('----------------------------------------------')
        get_database_size = "SELECT table_schema AS 'Database', ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)' FROM information_schema.TABLES GROUP BY table_schema;"
        with connection.cursor() as cursor:
            cursor.execute(get_database_size)
            database_size = cursor.fetchall()
            for row in database_size:
                dbname, dbsize = row
                print('{} - {}'.format(dbname, dbsize))
        select_port = 'SELECT @@port'
        with connection.cursor() as cursor:
            cursor.execute(select_port)
            database_port = cursor.fetchone()
            print('\t')
            print('Database Port :')
            print('---------------')
            print(database_host[0], 'Server MySQL Port is :', database_port[0])
        client.close()

except Error as e:
    print(e)
    exit()