yum install -y MysqL-python

#!/usr/bin/env python
# -*- coding:utf-8 -*- @Author : huazai @Time : 2017/5/4 22:04 @File : installMysqL.py @Description : MysqL数据目录路径：/data/MysqL/,MysqL安装目录路径：/usr/local/MysqL


import os
 sys
from optparse  OptionParser
from subprocess  Popen,PIPE
 shlex
 time
 MysqLdb
 re
 shutil
 tarfile
 stat
 logging
  pwd


logger = None
MysqL_DATA_DIR = '/data/MysqL/'
MysqL_INSTALL_DIR = /usr/local/MysqL/
MysqL_CONF_DIR = /etc/
MysqL_BACK_DIR = /data/backup/MysqL/
MysqL_STARTUP_SCRIPT = /etc/init.d/MysqL'


def init_log():
    global logger
    fmt_date = %Y-%m-%d %H:%M:%S.%s
    fmt_file = %(lineno)s %(asctime)s  [%(process)d]: %(levelname)s  %(filename)s  %(message)s

    log_file = installMysqL.log
    logger = logging.getLogger(MysqLinstallloging)
    logger.setLevel(logging.INFO)
    file_handler = logging.FileHandler(log_file,mode=a)
    file_handler.setFormatter(logging.Formatter(fmt_file,fmt_date))
    logger.addHandler(file_handler)


 opt():
    parser = OptionParser("Usage: %prog -P -f -b -p")
    parser.add_option(-P",--port,dest=portstore3306port 3306-f--tarfiletarfile/tmp/MysqL-5.6.28-linux-glibc2.5-x86_64.tar.gzfile  /tmp/MysqL-5.6.28-linux-glibc2.5-x86_64.tar.gz-b--bashfilemyfile/tmp/createmycnf.shfile  /tmp/createmycnf.sh-p--MysqLpwdMysqLpwd123456password 123456)
    options,args = parser.parse_args()
    return options,args


 设置安装目录和数据目录的权限
 setOwner(MysqLport):
    list=[]
    with open(/etc/passwd',1)">r) as fd:
        for line in fd:
            matchMysqL = re.search(rMysqLif matchMysqL:
        os.system(chown -R MysqL:MysqL %s' % MysqL_DATA_DIR)
        os.system( MysqL_INSTALL_DIR)
    else:
        os.system(useradd  -M  -s /sbin/nologin  MysqL)
        os.system(检查安装目录和数据目录权限
    for i in  pwd.getpwnam():
        list.append(i)
    MysqLuid = list[2]
    MysqLgid = list[3]
    stdatadirmode = os.stat(MysqL_DATA_DIR).st_mode
    stinstalldirmode = os.stat(MysqL_INSTALL_DIR).st_mode
    if not (os.stat(MysqL_DATA_DIR).st_uid == MysqLuid and os.stat(MysqL_DATA_DIR).st_gid == MysqLgid):
        logger.error(chown MysqL datadir or installdir not ok )
        sys.exit(1)
    not (os.stat(MysqL_DATA_DIR+MysqL%s/data' %(MysqLport)).st_uid == MysqLuid and os.stat(MysqL_DATA_DIR+' %(MysqLport)).st_gid ==MysqL%s/logsnot (os.stat(MysqL_DATA_DIR + MysqL%s/tmp' % (MysqLport)).st_uid == MysqLuid and os.stat(MysqL_DATA_DIR + ' % (MysqLport)).st_gid ==)





 创建必要的目录
 makeDIR(port):
    if os.path.exists(/data/MysqL/MysqL%s/data port):
        logger.error(MysqL %s already install port)
        sys.exit(1)

    try:
         os.makedirs('/usr/local/MysqL')
        os.makedirs( port)
        os.makedirs(/data/MysqL/MysqL%s/tmp/data/MysqL/MysqL%s/logs port)
    except Exception,e:
        logger.error(e)


 解压二进制安装包
 extract(MysqLfile):
    not os.path.exists(MysqLfile):
        logger.error(%s is not exists MysqLfile)
        sys.exit(1)
    os.chdir(os.path.dirname(MysqLfile))
    t = tarfile.open(MysqLfile,1)">r:gz)
    t.extractall()   解压到当前目录
    t.close()


 拷贝安装包文件到程序目录
 copyFile(MysqLfile):
    shutil.copytree(MysqLfile.split(.tar.gz)[0],MysqL_INSTALL_DIR)
    shutil.copy2(MysqL_INSTALL_DIR + support-files/MysqL.serverMysqL_STARTUP_SCRIPT)
    shutil.rmtree(MysqLfile.split()[0])


 设置环境变量
 setEnv():
    with open(/etc/profile) as fd:
        fd.write(export PATH=$PATH:/usr/local/MysqL/bin' + \n)
    os.system(source /etc/profile)


 初始化MysqL
 MysqLInstall():
    cnf = /etc/my.cnf'
     os.path.exists(cnf):
        cmd = MysqL_INSTALL_DIR + bin/MysqLd --defaults-file=%s  --initialize-insecure" % cnf
        p = Popen(shlex.split(cmd),stdout=PIPE,stderr=PIPE)
        stdout,stderr = p.communicate()
         stdout:
            logger.info(install output: %s (stdout))
         stderr:
            logger.error(install error output: %s (stderr))

        if p.returncode == 0:
            logger.info(initialize completed)
            logger.info(install returncode: %s (p.returncode))
        :
            logger.info(initialize Failed,please check the MysqL errror log (p.returncode))
            sys.exit(1:
        logger.error(cnf +  do not esixts 设置my.cnf
 mycnfCreate(mybashfile,MysqLport):
    cnf = 
    cmd = /bin/bash  %s mybashfile
    p = Popen(shlex.split(cmd),1)">PIPE)
    p.communicate()
    p.returncode
    f1 = open(cnf,)
    f2 = open(%s.bak" % cnf,1)">w f1:
        f2.write(re.sub(rMysqLport,count=1))
    f1.close()
    f2.close()
    os.remove(cnf)
    os.rename( cnf,cnf)


 设置启动脚本
 modifyStartupscript(port):
    isdatadirfind = 0
    isbasedirfind = 0
    f1 = open(MysqL_STARTUP_SCRIPT,1)">" % MysqL_STARTUP_SCRIPT,1)"> f1:
        if line.startswith(datadir=') and  isdatadirfind:
            f2.write(line.replace(datadir=/data/MysqL/MysqL%s/data' % port,1))
            isdatadirfind = 1
        elif line.startswith(basedir= isbasedirfind:
            f2.write(line.replace(basedir=/usr/local/MysqL))
            isbasedirfind = 1
        :
            f2.write(line)
    f1.close()
    f2.close()
    os.remove(MysqL_STARTUP_SCRIPT)
    os.rename( MysqL_STARTUP_SCRIPT,MysqL_STARTUP_SCRIPT)
     设置启动脚本执行权限
    stmode = os.stat(MysqL_STARTUP_SCRIPT).st_mode
    os.chmod(MysqL_STARTUP_SCRIPT,stmode | stat.S_IXOTH | stat.S_IXGRP | stat.S_IXUSR)


 检查安装
 checkInstall(port):
    not os.path.exists(/data/MysqL/MysqL%s/data/ibdata1MysqL not install )
    with open(/data/MysqL/MysqL%s/logs/error.log) as fd:
        fdlist = [i in fd  i]
        fdstr = ''.join(fdlist)
        re_error = re.compile(r\s\[error\]\s 匹配errorlog日志格式
        errorlist = re_error.findall(fdstr)

     errorlist:
        logger.error(error.log error count:' + str(len(errorlist)))
        logger.error(:
        logger.info(install MysqL  ok MysqLserviceStart():
    cnf = 
    cmd = MysqL_INSTALL_DIR+bin/MysqLd --defaults-file=%s &(cnf)
    p = Popen(cmd,stderr=PIPE,shell=True)
    stdout,1)"> p.communicate()
     stdout:
        logger.info(MysqL startup output: %s (stdout))
     stderr:
        logger.error(MysqL startup error output: %s (stderr))

     0:
        logger.info(MysqL startup completed)
        logger.info(MysqL startup returncode: %s (p.returncode))
    MysqL startup Failed,1)"> (p.returncode))
        sys.exit(1)
    time.sleep(4)  休眠4秒 让MysqL完全启动完毕


连接MysqL
 connMysqL(MysqLport):
    cnf =  os.path.exists(cnf):
        host = localhost
        user = root
        dbname = 
        usocket = MysqL_DATA_DIR+MysqL%s/tmp/MysqL.sock (MysqLport)
        :
            conn = MysqLdb.connect(host=host,user=user,db=dbname,unix_socket=usocket)
        )
        cur = conn.cursor()
         cur

设置MysqL的root的密码
 runsql(MysqLport,MysqLpwd):
    sql = alter user root@localhost identified  by '%s'  (MysqLpwd)
    cur = connMysqL(MysqLport)
    cur.execute(sql)


if __name__ == __main__:
    init_log()

    options,1)"> opt()
    :
        cmd = args[0]
     IndexError:
        print %s follow a command" % __file__
        %s -h__file__
        sys.exit(1if (options.port and str.isdigit(options.port)) and (options.tarfile and os.path.isfile(options.tarfile)) and (
                options.myfile and os.path.isfile(options.myfile))  (
            options.MysqLpwd):
        MysqLport = options.port
        MysqLfile = options.tarfile
        mybashfile = options.myfile
        MysqLpwd = options.MysqLpwd

    if cmd == create:
        mycnfCreate(mybashfile,MysqLport)
        logger.info(step1:mycnfCreate completed)

        makeDIR(MysqLport)
        logger.info(step2:makeDIR completed)

        extract(MysqLfile)
        logger.info(step3:extract completed)

        copyFile(MysqLfile)
        logger.info(step4:copyFile completed)

        setOwner(MysqLport)
        logger.info(step5:setOwner completed)

        MysqLInstall()
        logger.info(step6:MysqL_install completed)

        setEnv()
        logger.info(step7:setEnv completed)

        modifyStartupscript(MysqLport)
        logger.info(step8:modify_startupscript completed)

        checkInstall(MysqLport)
        logger.info(step9:checkInstall completed)

        MysqLserviceStart()
        logger.info(step10:MysqLserviceStart completed)

        runsql(MysqLport,MysqLpwd)
        logger.info(step11:runsql completed)

        print  MysqL install finish'

         调用示例
         python /tmp/installMysqL.py  -f /data/download/MysqL-5.7.19-linux-glibc2.12-x86_64.tar.gz -P3306 -p123456 -b /tmp/createmycnf.sh  create

!/bin/bash Written by steven Name:        createmycnf.sh Version:      v1.0 Function:     创建my.cnf Create Date:  2016-08-27




port=3306   端口
expirelogsdays=7   binlog清除时间
relaylogpurge=1  mha环境不能清除
innodbbufferpoolsize=$(printf %1.f\n" `echo $(free -m |grep Mem|awk '{ print $2}')*0.8"|bc`)M   bufferpool大小,物理内存的80%
innodbiocapacity=800     iocapacity大小
innodbpurgethreads=2   清除线程数量
performanceschema=1    是否打开P_S库
environment=product   是否是生产环境


ip=$(ip a|awk -F inet|/"  /inet.*brd/ {print $2}'|awk -F.' {print $4})
serverid=$ip$port




cat > /etc/my.cnf<< EOF
[client]
port            = $port
socket            = /data/MysqL/MysqL$port/tmp/MysqL.sock   
  

 The MysqL server
[MysqLd]
########Basic##################
explicit_defaults_for_timestamp=true

port            = $port  
user            = MysqL   
basedir         = /usr/local/MysqL  
datadir         = /data/MysqL/MysqL$port/data   
tmpdir          = /data/MysqL/MysqL$port/tmp   
pid-file        = /data/MysqL/MysqL$port/tmp/MysqL.pid    
socket            = /data/MysqL/MysqL$port/tmp/MysqL.sock   
skip-grant-tables  

character set
character_set_server = utf8mb4


open_files_limit = 65535
back_log = 500
event_scheduler = ONlower_case_table_names=0
log_timestamps = 1
skip-external-locking
skip_name_resolve = 1
skip-networking = 1
default-storage-engine = InnoDB

timeout
wait_timeout=1000
lock_wait_timeout=3600
interactive_timeout=1000
connect_timeout = 20


server-id       =$serverid  ip最后一位+端口号


percona 的--recursion-method slavehost模式report_host = 10.105.9.115report_port = 3306



plugin
plugin-load=semisync_master.so;semisync_slave.so"



########SSL#############
ssl-ca = /data/MysqL/MysqL$port/data/ca.pem 
ssl-cert = /data/MysqL/MysqL$port/data/server-cert.pem 
ssl-key = /data/MysqL/MysqL$port/data/server-key.pem




########undo#############
innodb_undo_logs  =126  每个tablespace里包含的rollback seg的个数
innodb_undo_directory =/data/MysqL/MysqL$port/logs/
innodb_max_undo_log_size = 1G
innodb_undo_tablespaces = 8   undo tablespace的个数
innodb_undo_log_truncate = 1
innodb_purge_rseg_truncate_frequency = 128


########error log#############
log-error = /data/MysqL/MysqL$port/logs/error.log  
log_error_verbosity  = 3    

########general log#############general_log=1general_log_file=/data/MysqL/MysqL$port/logs/MysqL.log 

########slow log#############
slow_query_log = 1
long_query_time=1  0表示记录所有sql
slow_query_log_file = /data/MysqL/MysqL$port/logs/MysqL.slow   


############ for replication###################
log-bin     = /data/MysqL/MysqL$port/logs/MysqL-bin   
binlog_format = row
max_binlog_size = 500M
binlog_cache_size = 5M
max_binlog_cache_size = 5M
expire-logs-days = $expirelogsdays
slave-net-timeout=30
log-slow-slave-statements =1 


log_bin_trust_function_creators = 1
log-slave-updates = 1   
skip-slave-start = 1
super_read_only =1    

GTID
gtid-mode = on
binlog_gtid_simple_recovery=1
enforce_gtid_consistency=1

relay log
relay-log = /data/MysqL/MysqL$port/logs/MysqL-relay  
relay-log-index=/data/MysqL/MysqL$port/logs/relay-bin.index
max-relay-log-size = 500M
relay_log_purge = $relaylogpurge  MHA里不能清除relaylog


replication crash safe
sync_master_info = 1
sync_relay_log_info = 1
sync_relay_log = 1
relay_log_recovery = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE

semisync   动态开启 主从切换的时候用rpl_semi_sync_master_enabled = 1rpl_semi_sync_master_wait_no_slave = 1rpl_semi_sync_master_timeout = 1000rpl_semi_sync_slave_enabled = 1rpl_semi_sync_master_timeout = 100000000 rpl_semi_sync_master_wait_point = 'after_sync' # after_sync 5.7增强半同步 rpl_semi_sync_master_wait_for_slave_count = 2  等待多少个从库接收到binlog


ignorereplicate-ignore-db = 'school','school2'replicate-do-db = 'school',1)">replicate-do-table = 'db1.t1'replicate-ignore-table= 'db1.t1'

Multi-threaded Slaveslave_parallel_workers=8slave-parallel-type=DATABASE(默认)/LOGICAL_CLOCKbinlog_group_commit_sync_delay=1000 binlog_group_commit_sync_no_delay_count =100 slave_preserve_commit_order=1 

replication errorslave-skip-errors=1007,1051,1062

######per_thread_buffers#####################
max_connections=1100
max_user_connections=1000
max_connect_errors=1000
myisam_recover
max_allowed_packet = 16M
table_cache = 3096
table_open_cache = 6144
table_definition_cache = 4096
table_open_cache_instances = 64 


read_buffer_size = 1M
join_buffer_size = 4M
read_rnd_buffer_size = 1M

myisam
sort_buffer_size = 128K
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
key_buffer_size = 64M


myisam_sort_buffer_size = 32M
tmp_table_size = 64M
max_heap_table_size = 64M
query_cache_type=0
query_cache_size = 0
bulk_insert_buffer_size = 32M

thread_cache_size = 64
thread_concurrency = 32
thread_stack = 192K


##############InnoDB###########################
innodb_data_home_dir = /data/MysqL/MysqL$port/data      
innodb_log_group_home_dir = /data/MysqL/MysqL$port/logs    
innodb_data_file_path = ibdata1:1000M:autoextend
innodb_temp_data_file_path = ibtmp1:12M:autoextend

innodb_buffer_pool_size = $innodbbufferpoolsize  

innodb_buffer_pool_instances    = 8
innodb_additional_mem_pool_size = 16M
innodb_log_file_size = 500M
innodb_log_buffer_size = 16M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_lock_wait_timeout = 10
innodb_sync_spin_loops = 40
innodb_max_dirty_pages_pct = 80
innodb_support_xa = 1
innodb_thread_concurrency = 0
innodb_thread_sleep_delay = 500
innodb_concurrency_tickets = 1000
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = $innodbiocapacity  
innodb_flush_neighbors = 1
innodb_purge_threads=$innodbpurgethreads    
innodb_purge_batch_size = 32
innodb_old_blocks_pct=75
innodb_change_buffering=all
innodb_stats_on_Metadata=OFF
innodb_print_all_deadlocks = 1
performance_schema=$performanceschema   
transaction_isolation = READ-COMMITTED
innodb_force_recovery=0innodb_fast_shutdown=1innodb_status_output=1innodb_status_output_locks=1innodb_status_file = 1  




[MysqLdump]
quick
max_allowed_packet = 128M


[MysqL]
no-auto-rehash  
max_allowed_packet = 128M
prompt                         = ($environment)\u@\h:\p [\d]>     
default_character_set          = utf8


[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 512k
read_buffer = 2M
write_buffer = 2M

[MysqLhotcopy]
interactive-timeout

[MysqLd_safe]
malloc-lib= /usr/local/MysqL/lib/MysqL/libjemalloc.so
EOF