python自动安装mysql5.7
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/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
createmycnf.sh
#!/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
如有不对的地方，欢迎大家拍砖o(∩_∩)o
本文版权归作者所有，未经作者同意不得转载。
