python自动安装mysql5.7
python版本：python2.6
centos版本：centos6.9
mysql版本：mysql5.7.19
安装目录路径和数据目录路径都是固定，当然也可以自己修改
这个脚本的原理是，通过createmycnf.sh的shell脚本生成my.cnf，buffer pool大小等在shell脚本里已经计算好，然后installmysql.py修改生成好的my.cnf里的端口等变量
并根据my.cnf来初始化mysql，初始化完毕之后启动mysql服务，并设置mysql的root用户密码，整个执行过程的日志会保存在当前目录下的installmysql.log
注意：目前不支持多实例安装，只支持单实例安装
执行脚本前要先安装MySQL-python
yum install -y MySQL-python
installmysql.py
installmysql.py脚本参数
-P：mysql端口号
-f：mysql二进制安装包位置-b：createmycnf.sh文件的位置-p：mysql的root用户密码#!/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/mysqlimport osimport sysfrom optparse import OptionParserfrom subprocess import Popen, PIPEimport shleximport timeimport MySQLdbimport reimport shutilimport tarfileimport statimport loggingimport pwdlogger = NoneMYSQL_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')# 初始化mysqldef 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.cnfdef 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完全启动完毕#连接mysqldef 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
createmycnf.sh
#!/bin/bash# Written by steven# Name: createmycnf.sh# Version: v1.0# Function: 创建my.cnf# Create Date: 2016-08-27port=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$portcat > /etc/my.cnf<< EOF[client]port = $portsocket = /data/mysql/mysql$port/tmp/mysql.sock # The MySQL server[mysqld]#########Basic##################explicit_defaults_for_timestamp=trueport = $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 setcharacter_set_server = utf8mb4open_files_limit = 65535back_log = 500#event_scheduler = ON#lower_case_table_names=0log_timestamps = 1skip-external-lockingskip_name_resolve = 1#skip-networking = 1default-storage-engine = InnoDB#timeoutwait_timeout=1000lock_wait_timeout=3600interactive_timeout=1000connect_timeout = 20server-id =$serverid #ip最后一位+端口号#percona 的--recursion-method slavehost模式#report_host = 10.105.9.115#report_port = 3306#pluginplugin-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 = 1Ginnodb_undo_tablespaces = 8 #undo tablespace的个数innodb_undo_log_truncate = 1innodb_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 = 1long_query_time=1 #0表示记录所有SQLslow_query_log_file = /data/mysql/mysql$port/logs/mysql.slow ############# for replication###################log-bin = /data/mysql/mysql$port/logs/mysql-bin binlog_format = rowmax_binlog_size = 500Mbinlog_cache_size = 5Mmax_binlog_cache_size = 5Mexpire-logs-days = $expirelogsdaysslave-net-timeout=30log-slow-slave-statements =1 log_bin_trust_function_creators = 1log-slave-updates = 1 skip-slave-start = 1#super_read_only =1 #GTIDgtid-mode = onbinlog_gtid_simple_recovery=1enforce_gtid_consistency=1#relay logrelay-log = /data/mysql/mysql$port/logs/mysql-relay relay-log-index=/data/mysql/mysql$port/logs/relay-bin.indexmax-relay-log-size = 500Mrelay_log_purge = $relaylogpurge #MHA里不能清除relaylog#replication crash safesync_master_info = 1sync_relay_log_info = 1sync_relay_log = 1relay_log_recovery = 1master_info_repository = TABLErelay_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=1100max_user_connections=1000max_connect_errors=1000#myisam_recovermax_allowed_packet = 16M#table_cache = 3096table_open_cache = 6144table_definition_cache = 4096table_open_cache_instances = 64 read_buffer_size = 1Mjoin_buffer_size = 4Mread_rnd_buffer_size = 1M#myisamsort_buffer_size = 128Kmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1key_buffer_size = 64Mmyisam_sort_buffer_size = 32Mtmp_table_size = 64Mmax_heap_table_size = 64Mquery_cache_type=0query_cache_size = 0bulk_insert_buffer_size = 32Mthread_cache_size = 64#thread_concurrency = 32thread_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:autoextendinnodb_temp_data_file_path = ibtmp1:12M:autoextendinnodb_buffer_pool_size = $innodbbufferpoolsize innodb_buffer_pool_instances = 8#innodb_additional_mem_pool_size = 16Minnodb_log_file_size = 500Minnodb_log_buffer_size = 16Minnodb_log_files_in_group = 3innodb_flush_log_at_trx_commit = 1sync_binlog = 1innodb_lock_wait_timeout = 10innodb_sync_spin_loops = 40innodb_max_dirty_pages_pct = 80innodb_support_xa = 1innodb_thread_concurrency = 0innodb_thread_sleep_delay = 500innodb_concurrency_tickets = 1000innodb_flush_method = O_DIRECTinnodb_file_per_table = 1innodb_read_io_threads = 16innodb_write_io_threads = 16innodb_io_capacity = $innodbiocapacity innodb_flush_neighbors = 1innodb_purge_threads=$innodbpurgethreads innodb_purge_batch_size = 32innodb_old_blocks_pct=75innodb_change_buffering=allinnodb_stats_on_metadata=OFFinnodb_print_all_deadlocks = 1performance_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]quickmax_allowed_packet = 128M[mysql]no-auto-rehash max_allowed_packet = 128Mprompt = '($environment)\u@\h:\p [\d]> ' default_character_set = utf8[myisamchk]key_buffer_size = 64Msort_buffer_size = 512kread_buffer = 2Mwrite_buffer = 2M[mysqlhotcopy]interactive-timeout[mysqld_safe]#malloc-lib= /usr/local/mysql/lib/mysql/libjemalloc.soEOF
如有不对的地方，欢迎大家拍砖o(∩_∩)o
本文版权归作者所有，未经作者同意不得转载。