my.cnf 自动生成脚本

青旅半醒 2023-06-11 03:30 3阅读 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)