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
import sys
from optparse import OptionParser
from subprocess import Popen, PIPE
import shlex
import time
import MySQLdb
import re
import shutil
import tarfile
import stat
import logging
import  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)


def opt():
    parser = OptionParser("Usage: %prog -P -f -b -p")
    parser.add_option("-P", "--port",
                      dest="port",
                      action="store",
                      default="3306",
                      help='port 3306')
    parser.add_option("-f", "--tarfile",
                      dest="tarfile",
                      action="store",
                      default="/tmp/mysql-5.6.28-linux-glibc2.5-x86_64.tar.gz",
                      help='file  /tmp/mysql-5.6.28-linux-glibc2.5-x86_64.tar.gz')
    parser.add_option("-b", "--bashfile",
                      dest="myfile",
                      action="store",
                      default="/tmp/createmycnf.sh",
                      help='file  /tmp/createmycnf.sh')
    parser.add_option("-p", "--mysqlpwd",
                      dest="mysqlpwd",
                      action="store",
                      default="123456",
                      help='password 123456')
    options, args = parser.parse_args()
    return options, args


# 设置安装目录和数据目录的权限
def setOwner(mysqlport):
    list=[]
    with open('/etc/passwd', 'r') as fd:
        for line in fd:
            matchmysql = re.search(r'mysql', line, re.I)

    if matchmysql:
        os.system('chown -R mysql:mysql %s' % MYSQL_DATA_DIR)
        os.system('chown -R mysql:mysql %s' % MYSQL_INSTALL_DIR)
    else:
        os.system('useradd  -M  -s /sbin/nologin  mysql')
        os.system('chown -R mysql:mysql %s' % MYSQL_DATA_DIR)
        os.system('chown -R mysql:mysql %s' % MYSQL_INSTALL_DIR)
    #检查安装目录和数据目录权限
    for i in  pwd.getpwnam('mysql'):
        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)
    if not (os.stat(MYSQL_DATA_DIR+'mysql%s/data' %(mysqlport)).st_uid == mysqluid and os.stat(MYSQL_DATA_DIR+'mysql%s/data' %(mysqlport)).st_gid == mysqlgid):
        logger.error('chown mysql datadir or installdir not ok ')
        sys.exit(1)
    if not (os.stat(MYSQL_DATA_DIR+'mysql%s/logs' %(mysqlport)).st_uid == mysqluid and os.stat(MYSQL_DATA_DIR+'mysql%s/data' %(mysqlport)).st_gid == mysqlgid):
        logger.error('chown mysql datadir or installdir not ok ')
        sys.exit(1)
    if not (os.stat(MYSQL_DATA_DIR + 'mysql%s/tmp' % (mysqlport)).st_uid == mysqluid and os.stat(MYSQL_DATA_DIR + 'mysql%s/tmp' % (mysqlport)).st_gid == mysqlgid):
        logger.error('chown mysql datadir or installdir not ok ')
        sys.exit(1)





# 创建必要的目录
def 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('/data/mysql/mysql%s/data' % port)
        os.makedirs('/data/mysql/mysql%s/tmp' % port)
        os.makedirs('/data/mysql/mysql%s/logs' % port)
    except Exception, e:
        logger.error(e)


# 解压二进制安装包
def extract(mysqlfile):
    if 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, 'r:gz')
    t.extractall()  # 解压到当前目录
    t.close()


# 拷贝安装包文件到程序目录
def copyFile(mysqlfile):
    shutil.copytree(mysqlfile.split('.tar.gz')[0], MYSQL_INSTALL_DIR)
    shutil.copy2(MYSQL_INSTALL_DIR + 'support-files/mysql.server', MYSQL_STARTUP_SCRIPT)
    shutil.rmtree(mysqlfile.split('.tar.gz')[0])


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


# 初始化mysql
def mysqlInstall():
    cnf = '/etc/my.cnf'
    if 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()
        if stdout:
            logger.info('install output: %s' % (stdout))
        if stderr:
            logger.error('install error output: %s' % (stderr))

        if p.returncode == 0:
            logger.info('initialize completed')
            logger.info('install returncode: %s' % (p.returncode))
        else:
            logger.info('initialize failed , please check the mysql errror log')
            logger.info('install returncode: %s' % (p.returncode))
            sys.exit(1)
    else:
        logger.error(cnf + ' do not esixts')
        sys.exit(1)


# 设置my.cnf
def mycnfCreate(mybashfile, mysqlport):
    cnf = '/etc/my.cnf'
    cmd = "/bin/bash  %s" % mybashfile
    p = Popen(shlex.split(cmd), stdout=PIPE, stderr=PIPE)
    p.communicate()
    p.returncode
    f1 = open(cnf, "r", )
    f2 = open("%s.bak" % cnf, "w", )
    for line in f1:
        f2.write(re.sub(r'3306', mysqlport, line, count=1))
    f1.close()
    f2.close()
    os.remove(cnf)
    os.rename("%s.bak" % cnf, cnf)


# 设置启动脚本
def modifyStartupscript(port):
    isdatadirfind = 0
    isbasedirfind = 0
    f1 = open(MYSQL_STARTUP_SCRIPT, "r", )
    f2 = open("%s.bak" % MYSQL_STARTUP_SCRIPT, "w", )
    for line in f1:
        if line.startswith('datadir=') and not isdatadirfind:
            f2.write(line.replace('datadir=', 'datadir=/data/mysql/mysql%s/data' % port, 1))
            isdatadirfind = 1
        elif line.startswith('basedir=') and not isbasedirfind:
            f2.write(line.replace('basedir=', 'basedir=/usr/local/mysql', 1))
            isbasedirfind = 1
        else:
            f2.write(line)
    f1.close()
    f2.close()
    os.remove(MYSQL_STARTUP_SCRIPT)
    os.rename("%s.bak" % 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)


# 检查安装
def checkInstall(port):
    if not os.path.exists('/data/mysql/mysql%s/data/ibdata1' % port):
        logger.error('mysql not install ')
        sys.exit(1)
    with open('/data/mysql/mysql%s/logs/error.log' % port, 'r') as fd:
        fdlist = [i for i in fd if i]
        fdstr = ''.join(fdlist)
        re_error = re.compile(r'\s\[error\]\s', re.I | re.M)  # 匹配errorlog日志格式
        errorlist = re_error.findall(fdstr)

    if errorlist:
        logger.error('error.log error count:' + str(len(errorlist)))
        logger.error('mysql not install ')
        sys.exit(1)
    else:
        logger.info('install mysql  ok')


def mysqlserviceStart():
    cnf = '/etc/my.cnf'
    cmd = MYSQL_INSTALL_DIR+"bin/mysqld --defaults-file=%s &" %(cnf)
    p = Popen(cmd, stdout=PIPE, stderr=PIPE, shell=True)
    stdout, stderr = p.communicate()
    if stdout:
        logger.info('mysql startup output: %s' % (stdout))
    if stderr:
        logger.error('mysql startup error output: %s' % (stderr))

    if p.returncode == 0:
        logger.info('mysql startup completed')
        logger.info('mysql startup returncode: %s' % (p.returncode))
    else:
        logger.info('mysql startup failed , please check the mysql errror log')
        logger.info('mysql startup returncode: %s' % (p.returncode))
        sys.exit(1)
    time.sleep(4) # 休眠4秒 让mysql完全启动完毕


#连接mysql
def connMysql(mysqlport):
    cnf = '/etc/my.cnf'
    if os.path.exists(cnf):
        host = 'localhost'
        user = 'root'
        dbname = 'mysql'
        usocket = MYSQL_DATA_DIR+'mysql%s/tmp/mysql.sock' % (mysqlport)
        try:
            conn = MySQLdb.connect(host=host, user=user, db=dbname, unix_socket=usocket)
        except Exception, e:
            logger.error(e)
            sys.exit(1)
        cur = conn.cursor()
        return cur

#设置mysql的root的密码
def runSQL(mysqlport, mysqlpwd):
    sql = "alter user root@localhost identified  by '%s' " % (mysqlpwd)
    cur = connMysql(mysqlport)
    cur.execute(sql)


if __name__ == '__main__':
    init_log()

    options, args = opt()
    try:
        cmd = args[0]
    except IndexError:
        print "%s follow a command" % __file__
        print "%s -h" % __file__
        sys.exit(1)

    if (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)) and (
            options.mysqlpwd):
        mysqlport = options.port
        mysqlfile = options.tarfile
        mybashfile = options.myfile
        mysqlpwd = options.mysqlpwd

    else:
        print "%s -h" % __file__
        sys.exit(1)

    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 = ON
#lower_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.115
#report_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=1
#general_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 = 1
#rpl_semi_sync_master_wait_no_slave = 1
#rpl_semi_sync_master_timeout = 1000
#rpl_semi_sync_slave_enabled = 1
#rpl_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


#ignore
#replicate-ignore-db = 'school','school2'
#replicate-do-db = 'school','school2'
#replicate-do-table = 'db1.t1'
#replicate-ignore-table= 'db1.t1'

#Multi-threaded Slave
#slave_parallel_workers=8
#slave-parallel-type=DATABASE(默认)/LOGICAL_CLOCK
#binlog_group_commit_sync_delay=1000 
#binlog_group_commit_sync_no_delay_count =100 
#slave_preserve_commit_order=1 

#replication error
#slave-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=0
#innodb_fast_shutdown=1
#innodb_status_output=1
#innodb_status_output_locks=1
#innodb_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