MySQL运行参数值变更巡检

import datetime
import difflib
import filecmp
import json
import os
import sys
from pathlib import Path

import func_timeout
import requests
from dingtalkchatbot.chatbot import DingtalkChatbot
from requests.auth import HTTPBasicAuth

project_path = os.path.abspath("../../..")
sys.path.append(project_path)
os.environ["DJANGO_SETTINGS_MODULE"] = "dba_platform.settings"

import django

django.setup()

from dba_platform import configs
from utils import db_conn
from utils.zmail_send import SendEmail


mydb = db_conn.conn_db(
            host='192.168.31.181',
            port=3306,
            user='dts',
            passwd='123456',
        )
mycursor = mydb.cursor(dictionary=True)

get_sql="""
	show variables where variable_name in (
	'binlog_format',
	'binlog_row_image',
	'binlog_row_metadata',
	'gtid_mode',
	'enforce_gtid_consistency',
	'innodb_doublewrite',
	'log_bin',
	'master_info_repository',
	'sync_binlog',
	'binlog_expire_logs_seconds',
	'binlog_group_commit_sync_delay',
	'binlog_order_commits',
	'binlog_rows_query_log_events',
	'binlog_expire_logs_seconds',
	'binlog_stmt_cache_size',
	'binlog_transaction_dependency_history_size',
	'binlog_transaction_dependency_tracking',
	'character_set_server',
	'core_file',
	'event_scheduler',
	'expire_logs_days',
	'explicit_defaults_for_timestamp',
	'general_log',
	'group_concat_max_len',
	'innodb_adaptive_hash_index',
	'innodb_autoinc_lock_mode',
	'innodb_buffer_pool_chunk_size',
	'innodb_buffer_pool_instances',
	'innodb_buffer_pool_size',
	'innodb_concurrency_tickets',
	'innodb_deadlock_detect',
	'innodb_flush_log_at_trx_commit',
	'innodb_io_capacity',
	'innodb_io_capacity_max',
	'innodb_lock_wait_timeout',
	'lock_wait_timeout',
	'local_infile',
	'join_buffer_size',
	'interactive_timeout',
	'innodb_thread_concurrency',
	'interactive_timeout',
	'innodb_write_io_threads',
	'innodb_strict_mode',
	'innodb_status_output',
	'innodb_stats_persistent_sample_pages',
	'innodb_redo_log_capacity',
	'innodb_sort_buffer_size',
	'innodb_log_buffer_size',
	'innodb_log_file_size',
	'innodb_log_files_in_group',
	'innodb_lru_scan_depth',
	'innodb_max_dirty_pages_pct',
	'innodb_max_dirty_pages_pct_lwm',
	'innodb_max_undo_log_size',
	'innodb_monitor_enable',
	'innodb_online_alter_log_max_size',
	'innodb_open_files',
	'innodb_page_cleaners',
	'innodb_print_all_deadlocks',
	'innodb_print_ddl_logs',
	'innodb_print_lock_wait_timeout_info',
	'innodb_purge_threads',
	'innodb_read_io_threads',
	'innodb_read_only',
	'net_read_timeout',
	'net_write_timeout',
	'open_files_limit',
	'table_open_cache',
	'table_open_cache_instances',
	'table_definition_cache',
	'system_time_zone',
	'max_connections',
	'long_query_time',
	'log_timestamps',
	'lower_case_file_system',
	'lower_case_table_names',
	'master_info_repository',
	'max_allowed_packet',
	'max_binlog_cache_size',
	'max_binlog_size',
	'log_slow_slave_statements',
	'log_slow_admin_statements',
	'log_slave_updates',
	'log_queries_not_using_indexes',
	'log_error_verbosity',
	'log_bin_trust_function_creators',
	'thread_handling',
	'time_zone',
	'sql_require_primary_key',
	'sql_mode',
	'sql_generate_invisible_primary_key',
	'slow_query_log_always_write_time',
	'slow_query_log',
	'slave_type_conversions',
	'slave_rows_search_algorithms',
	'slave_preserve_commit_order',
	'slave_exec_mode',
	'slave_parallel_type',
	'slave_parallel_workers',
	'rpl_semi_sync_master_enabled',
	'rpl_semi_sync_slave_enabled',
	'rpl_semi_sync_master_timeout',
	'rpl_semi_sync_master_wait_for_slave_count',
	'rpl_semi_sync_master_wait_point',
	'secure_file_priv',
	'sort_buffer_size',
	'relay_log_purge',
	'relay_log_recovery',
	'relay_log_info_repository',
	'read_buffer_size',
	'optimizer_switch',
	'optimizer_trace',
	'performance_schema',
	'transaction_isolation',
	'transaction_write_set_extraction',
	'group_replication_consistency',
	'group_replication_bootstrap_group',
	'group_replication_autorejoin_tries',
	'group_replication_compression_threshold',
	'group_replication_consistency',
	'group_replication_enforce_update_everywhere_checks',
	'group_replication_exit_state_action',
	'group_replication_flow_control_applier_threshold',
	'group_replication_flow_control_certifier_threshold',
	'group_replication_group_seeds',
	'group_replication_member_weight',
	'group_replication_message_cache_size',
	'group_replication_single_primary_mode',
	'group_replication_transaction_size_limit',
	'group_replication_start_on_boot'
	) ;
"""

mycursor.execute(get_sql)
res = mycursor.fetchall()
content = ""
for i in res:
	content += str(i) + "\n"


# 确保文件存放的目录是存在的
base_path = Path(/tmp/dba_platform/' )
try:
	base_path.mkdir(parents=True)
except Exception as e:
	print(str(e))

# 文件名以日期命名
current_file=str(base_path) + '/' + str(datetime.datetime.now().strftime("%Y%m%d")) + '.txt'
last_file = str(base_path) + '/' +  str((datetime.datetime.now() + datetime.timedelta(days=-1)).strftime("%Y%m%d")) + '.txt'

with open(current_file,'w') as f:
	f.write(content)

# 如果读取前一天的文件失败，则跳过本次
try:
	last_version = open(last_file , "r").readlines()
except Exception as e:
	print(str(e))
	continue

current_version = open(current_file, "r").readlines()

# 如果2个文件相同，则不再进行比对
if filecmp.cmp(last_file, current_file, shallow=False):
	print("文件相同，不再进行difflib检测")
	sys.exit(0)

else:
	difference = difflib.HtmlDiff(tabsize=2)
	with open('/tmp/result.html', "w") as fp:
		html = difference.make_file(
			fromlines=last_version,
			tolines=current_version,
			fromdesc="last_version",
			todesc="current_version",
			context=True,
			numlines=0,
		)
		fp.write(html)
		# 最好加个邮件明细告警（或者IM概要告警）