my.cnf 自动生成脚本-蒲公英云

my.cnf 自动生成脚本

青旅半醒 2023-06-11 03:30 83阅读 0赞

利用python 写了一个自动生成my.cnf 的脚本，写的很简单，运行结果如下：

(yuhuashi) [root@yuhuashi opt]# python auto_mycnf.py 
MySQL安装文件路径:/opt/mysql    
MySQL数据文件路径:/database/mysql
MySQL监听端口:3306
日志保留天数:7

脚本代码如下：

#/usr/bin/python
# -*- coding: UTF-8 -*-
import os
import configparser
import socket
import psutil
def get_hostname():
    return socket.gethostname()
def get_meminfo():
    memory = psutil.virtual_memory()
    ##memory_total = memory.total
    #memory_free = memory.free
    return memory.free
def get_cpu(): 
    return psutil.cpu_count(logical=False)
def get_ip():
    info = psutil.net_if_addrs()
    ip_addr='127.0.0.1'
    for k,v in info.items():
        for item in v:
          if item[0] == 2 and not item[1]=='127.0.0.1':
            ip_addr=item[1]
    return ip_addr
def auto_mycnf(mysql_port,mysql_install_dir,datadir,innodb_buffer_pool_size,expire_days,cpu_cores,local_ip):
    config= configparser.ConfigParser()
    config['client']={
        'port':mysql_port,
        'socket': mysql_install_dir+'/mysql.sock'
    }
    config['mysqld']={
        'port':mysql_port,
        'socket':mysql_install_dir+'/mysql.sock',
        'basedir':mysql_install_dir,
        'datadir':datadir,
        'server_id':mysql_port,
        'report_host':local_ip,
        'user':'mysql',
        'federated':'',
        'key_buffer_size':'512M',
        'read_buffer_size':'8M',
        'read_rnd_buffer_size':'8M',
        'join_buffer_size':'8M',
        'sort_buffer_size':'8M',
        'myisam_sort_buffer_size':'64M',
        'max_heap_table_size':'128M',
        'tmp_table_size':'128M',
        'max_allowed_packet':'16M',
        'table_open_cache':'4096',
        'thread_cache_size':'4096',
        'thread_concurrency':'38',
        'back_log':'1024',
        'max_connections':'6000',
        'max_user_connections':'2000',
        'max_connect_errors':'10',
        'wait_timeout':'60',
        'lower_case_table_names':'1',
        'innodb_data_file_path':'ibdata1:2000M;ibdata2:10M:autoextend',
        'innodb_buffer_pool_size':innodb_buffer_pool_size+'G',
        'innodb_additional_mem_pool_size':'10M',
        'innodb_log_file_size':'256M',
        'innodb_log_buffer_size':'16M',
        'innodb_flush_log_at_trx_commit':'1',
        'innodb_lock_wait_timeout':'50',
        'innodb_file_per_table':'1',
        'innodb_log_files_in_group':'10',
        'innodb_flush_method':'O_DIRECT',
        'innodb_open_file':'1024',
        'innodb_purge_threads':'4',
        'innodb_write_io_threads':cpu_cores,
        'innodb_read_io_threads':cpu_cores,
        'log_error':mysql_install_dir+'/data/error-log',
        'pid-file':'mysqld.pid',
        'log_bin':mysql_install_dir+'/data/mysql-bin',
        'max_binlog_size':'1G',
        'max_binlog_cache_size':'2G',
        'binlog_format':'mixed',
        'expire_logs_days':expire_days,
        'general_log_file':mysql_install_dir+'/data/general.log',
        'relay_log':mysql_install_dir+'/data/relay-bin',
        'log_slave_updates':'1',
        'slave_compressed_protocol':'1',
        'slow_query_log':'1',
        'slow_query_log':mysql_install_dir+'/data/slow-query.log',
        'long_query_time':'1',
        'log_queries_not_using_indexes':'0',
        'event_scheduler':'on',
        'log_slave-_updates':'on',
        'skip_external_locking':'ON',
        'skip_name_resolve':'ON',
    }
    with open('my.cnf', 'w') as configfile:
      config.write(configfile)
if __name__=='__main__':
    mysql_install_dir=input('MySQL安装文件路径:')
    while len(mysql_install_dir.strip())==0:
         buffer_pool_size=input('MySQL安装文件路径:')
    datadir=input('MySQL数据文件路径:')
    while len(datadir.strip())==0:
         buffer_pool_size=input('MySQL数据文件路径:')     
    mysql_port=input('MySQL监听端口:')
    while len(mysql_port.strip())==0 or mysql_port.isdigit()==False:
         mysql_port=input('MySQL监听端口:')
    expire_days=input('日志保留天数:')
    while len(expire_days.strip())==0 or expire_days.isdigit()==False:
         expire_days=input('日志保留天数:')
    cpu_cores = get_cpu()
    innodb_buffer_pool_size=get_meminfo()/1024/1024/1024*0.8
    local_ip=get_ip()
    server_id=local_ip+mysql_port
    auto_mycnf(mysql_port,mysql_install_dir,datadir,str(int(innodb_buffer_pool_size)),expire_days,cpu_cores,local_ip)