python自动安装MysqL5.7
python版本:python2.6
centos版本:centos6.9
安装目录路径和数据目录路径都是固定,当然也可以自己修改
这个脚本的原理是,通过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
- 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
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 = 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
如有不对的地方,欢迎大家拍砖o(∩_∩)o
本文版权归作者所有,未经作者同意不得转载。