python自动安装mysql5.7

前端之家收集整理的这篇文章主要介绍了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

  1. yum install -y MysqL-python

 

 

installMysqL.py

installMysqL.py脚本参数

-P:MysqL端口号
-f:MysqL二进制安装包位置
-b:createmycnf.sh文件的位置
-p:MysqL的root用户密码

  1. #!/usr/bin/env python
  2. # -*- coding:utf-8 -*- @Author : huazai @Time : 2017/5/4 22:04 @File : installMysqL.py @Description : MysqL数据目录路径:/data/MysqL/,MysqL安装目录路径:/usr/local/MysqL
  3.  
  4.  
  5. import os
  6. sys
  7. from optparse OptionParser
  8. from subprocess Popen,PIPE
  9. shlex
  10. time
  11. MysqLdb
  12. re
  13. shutil
  14. tarfile
  15. stat
  16. logging
  17. pwd
  18. logger = None
  19. MysqL_DATA_DIR = '/data/MysqL/'
  20. MysqL_INSTALL_DIR = /usr/local/MysqL/
  21. MysqL_CONF_DIR = /etc/
  22. MysqL_BACK_DIR = /data/backup/MysqL/
  23. MysqL_STARTUP_SCRIPT = /etc/init.d/MysqL'
  24.  
  25.  
  26. def init_log():
  27. global logger
  28. fmt_date = %Y-%m-%d %H:%M:%S.%s
  29. fmt_file = %(lineno)s %(asctime)s [%(process)d]: %(levelname)s %(filename)s %(message)s
  30. log_file = installMysqL.log
  31. logger = logging.getLogger(MysqLinstallloging)
  32. logger.setLevel(logging.INFO)
  33. file_handler = logging.FileHandler(log_file,mode=a)
  34. file_handler.setFormatter(logging.Formatter(fmt_file,fmt_date))
  35. logger.addHandler(file_handler)
  36. opt():
  37. parser = OptionParser("Usage: %prog -P -f -b -p")
  38. 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)
  39. options,args = parser.parse_args()
  40. return options,args
  41. 设置安装目录和数据目录的权限
  42. setOwner(MysqLport):
  43. list=[]
  44. with open(/etc/passwd',1)">r) as fd:
  45. for line in fd:
  46. matchMysqL = re.search(rMysqLif matchMysqL:
  47. os.system(chown -R MysqL:MysqL %s' % MysqL_DATA_DIR)
  48. os.system( MysqL_INSTALL_DIR)
  49. else:
  50. os.system(useradd -M -s /sbin/nologin MysqL)
  51. os.system(检查安装目录和数据目录权限
  52. for i in pwd.getpwnam():
  53. list.append(i)
  54. MysqLuid = list[2]
  55. MysqLgid = list[3]
  56. stdatadirmode = os.stat(MysqL_DATA_DIR).st_mode
  57. stinstalldirmode = os.stat(MysqL_INSTALL_DIR).st_mode
  58. if not (os.stat(MysqL_DATA_DIR).st_uid == MysqLuid and os.stat(MysqL_DATA_DIR).st_gid == MysqLgid):
  59. logger.error(chown MysqL datadir or installdir not ok )
  60. sys.exit(1)
  61. 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 ==)
  62. 创建必要的目录
  63. makeDIR(port):
  64. if os.path.exists(/data/MysqL/MysqL%s/data port):
  65. logger.error(MysqL %s already install port)
  66. sys.exit(1)
  67. try:
  68. os.makedirs('/usr/local/MysqL')
  69. os.makedirs( port)
  70. os.makedirs(/data/MysqL/MysqL%s/tmp/data/MysqL/MysqL%s/logs port)
  71. except Exception,e:
  72. logger.error(e)
  73. 解压二进制安装包
  74. extract(MysqLfile):
  75. not os.path.exists(MysqLfile):
  76. logger.error(%s is not exists MysqLfile)
  77. sys.exit(1)
  78. os.chdir(os.path.dirname(MysqLfile))
  79. t = tarfile.open(MysqLfile,1)">r:gz)
  80. t.extractall() 解压到当前目录
  81. t.close()
  82. 拷贝安装包文件到程序目录
  83. copyFile(MysqLfile):
  84. shutil.copytree(MysqLfile.split(.tar.gz)[0],MysqL_INSTALL_DIR)
  85. shutil.copy2(MysqL_INSTALL_DIR + support-files/MysqL.serverMysqL_STARTUP_SCRIPT)
  86. shutil.rmtree(MysqLfile.split()[0])
  87. 设置环境变量
  88. setEnv():
  89. with open(/etc/profile) as fd:
  90. fd.write(export PATH=$PATH:/usr/local/MysqL/bin' + \n)
  91. os.system(source /etc/profile)
  92. 初始化MysqL
  93. MysqLInstall():
  94. cnf = /etc/my.cnf'
  95. os.path.exists(cnf):
  96. cmd = MysqL_INSTALL_DIR + bin/MysqLd --defaults-file=%s --initialize-insecure" % cnf
  97. p = Popen(shlex.split(cmd),stdout=PIPE,stderr=PIPE)
  98. stdout,stderr = p.communicate()
  99. stdout:
  100. logger.info(install output: %s (stdout))
  101. stderr:
  102. logger.error(install error output: %s (stderr))
  103. if p.returncode == 0:
  104. logger.info(initialize completed)
  105. logger.info(install returncode: %s (p.returncode))
  106. :
  107. logger.info(initialize Failed,please check the MysqL errror log (p.returncode))
  108. sys.exit(1:
  109. logger.error(cnf + do not esixts 设置my.cnf
  110. mycnfCreate(mybashfile,MysqLport):
  111. cnf =
  112. cmd = /bin/bash %s mybashfile
  113. p = Popen(shlex.split(cmd),1)">PIPE)
  114. p.communicate()
  115. p.returncode
  116. f1 = open(cnf,)
  117. f2 = open(%s.bak" % cnf,1)">w f1:
  118. f2.write(re.sub(rMysqLport,count=1))
  119. f1.close()
  120. f2.close()
  121. os.remove(cnf)
  122. os.rename( cnf,cnf)
  123. 设置启动脚本
  124. modifyStartupscript(port):
  125. isdatadirfind = 0
  126. isbasedirfind = 0
  127. f1 = open(MysqL_STARTUP_SCRIPT,1)">" % MysqL_STARTUP_SCRIPT,1)"> f1:
  128. if line.startswith(datadir=') and isdatadirfind:
  129. f2.write(line.replace(datadir=/data/MysqL/MysqL%s/data' % port,1))
  130. isdatadirfind = 1
  131. elif line.startswith(basedir= isbasedirfind:
  132. f2.write(line.replace(basedir=/usr/local/MysqL))
  133. isbasedirfind = 1
  134. :
  135. f2.write(line)
  136. f1.close()
  137. f2.close()
  138. os.remove(MysqL_STARTUP_SCRIPT)
  139. os.rename( MysqL_STARTUP_SCRIPT,MysqL_STARTUP_SCRIPT)
  140. 设置启动脚本执行权限
  141. stmode = os.stat(MysqL_STARTUP_SCRIPT).st_mode
  142. os.chmod(MysqL_STARTUP_SCRIPT,stmode | stat.S_IXOTH | stat.S_IXGRP | stat.S_IXUSR)
  143. 检查安装
  144. checkInstall(port):
  145. not os.path.exists(/data/MysqL/MysqL%s/data/ibdata1MysqL not install )
  146. with open(/data/MysqL/MysqL%s/logs/error.log) as fd:
  147. fdlist = [i in fd i]
  148. fdstr = ''.join(fdlist)
  149. re_error = re.compile(r\s\[error\]\s 匹配errorlog日志格式
  150. errorlist = re_error.findall(fdstr)
  151. errorlist:
  152. logger.error(error.log error count:' + str(len(errorlist)))
  153. logger.error(:
  154. logger.info(install MysqL ok MysqLserviceStart():
  155. cnf =
  156. cmd = MysqL_INSTALL_DIR+bin/MysqLd --defaults-file=%s &(cnf)
  157. p = Popen(cmd,stderr=PIPE,shell=True)
  158. stdout,1)"> p.communicate()
  159. stdout:
  160. logger.info(MysqL startup output: %s (stdout))
  161. stderr:
  162. logger.error(MysqL startup error output: %s (stderr))
  163. 0:
  164. logger.info(MysqL startup completed)
  165. logger.info(MysqL startup returncode: %s (p.returncode))
  166. MysqL startup Failed,1)"> (p.returncode))
  167. sys.exit(1)
  168. time.sleep(4) 休眠4秒 让MysqL完全启动完毕
  169.  
  170.  
  171. 连接MysqL
  172. connMysqL(MysqLport):
  173. cnf = os.path.exists(cnf):
  174. host = localhost
  175. user = root
  176. dbname =
  177. usocket = MysqL_DATA_DIR+MysqL%s/tmp/MysqL.sock (MysqLport)
  178. :
  179. conn = MysqLdb.connect(host=host,user=user,db=dbname,unix_socket=usocket)
  180. )
  181. cur = conn.cursor()
  182. cur
  183. 设置MysqL的root的密码
  184. runsql(MysqLport,MysqLpwd):
  185. sql = alter user root@localhost identified by '%s' (MysqLpwd)
  186. cur = connMysqL(MysqLport)
  187. cur.execute(sql)
  188. if __name__ == __main__:
  189. init_log()
  190. options,1)"> opt()
  191. :
  192. cmd = args[0]
  193. IndexError:
  194. print %s follow a command" % __file__
  195. %s -h__file__
  196. sys.exit(1if (options.port and str.isdigit(options.port)) and (options.tarfile and os.path.isfile(options.tarfile)) and (
  197. options.myfile and os.path.isfile(options.myfile)) (
  198. options.MysqLpwd):
  199. MysqLport = options.port
  200. MysqLfile = options.tarfile
  201. mybashfile = options.myfile
  202. MysqLpwd = options.MysqLpwd
  203. if cmd == create:
  204. mycnfCreate(mybashfile,MysqLport)
  205. logger.info(step1:mycnfCreate completed)
  206. makeDIR(MysqLport)
  207. logger.info(step2:makeDIR completed)
  208. extract(MysqLfile)
  209. logger.info(step3:extract completed)
  210. copyFile(MysqLfile)
  211. logger.info(step4:copyFile completed)
  212. setOwner(MysqLport)
  213. logger.info(step5:setOwner completed)
  214. MysqLInstall()
  215. logger.info(step6:MysqL_install completed)
  216. setEnv()
  217. logger.info(step7:setEnv completed)
  218. modifyStartupscript(MysqLport)
  219. logger.info(step8:modify_startupscript completed)
  220. checkInstall(MysqLport)
  221. logger.info(step9:checkInstall completed)
  222. MysqLserviceStart()
  223. logger.info(step10:MysqLserviceStart completed)
  224. runsql(MysqLport,MysqLpwd)
  225. logger.info(step11:runsql completed)
  226. print MysqL install finish'
  227.  
  228. 调用示例
  229. 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

  1. !/bin/bash Written by steven Name: createmycnf.sh Version: v1.0 Function: 创建my.cnf Create Date: 2016-08-27
  2. port=3306 端口
  3. expirelogsdays=7 binlog清除时间
  4. relaylogpurge=1 mha环境不能清除
  5. innodbbufferpoolsize=$(printf %1.f\n" `echo $(free -m |grep Mem|awk '{ print $2}')*0.8"|bc`)M bufferpool大小,物理内存的80%
  6. innodbiocapacity=800 iocapacity大小
  7. innodbpurgethreads=2 清除线程数量
  8. performanceschema=1 是否打开P_S库
  9. environment=product 是否是生产环境
  10. ip=$(ip a|awk -F inet|/" /inet.*brd/ {print $2}'|awk -F.' {print $4})
  11. serverid=$ip$port
  12. cat > /etc/my.cnf<< EOF
  13. [client]
  14. port = $port
  15. socket = /data/MysqL/MysqL$port/tmp/MysqL.sock
  16. The MysqL server
  17. [MysqLd]
  18. ########Basic##################
  19. explicit_defaults_for_timestamp=true
  20. port = $port
  21. user = MysqL
  22. basedir = /usr/local/MysqL
  23. datadir = /data/MysqL/MysqL$port/data
  24. tmpdir = /data/MysqL/MysqL$port/tmp
  25. pid-file = /data/MysqL/MysqL$port/tmp/MysqL.pid
  26. socket = /data/MysqL/MysqL$port/tmp/MysqL.sock
  27. skip-grant-tables
  28.  
  29. character set
  30. character_set_server = utf8mb4
  31. open_files_limit = 65535
  32. back_log = 500
  33. event_scheduler = ONlower_case_table_names=0
  34. log_timestamps = 1
  35. skip-external-locking
  36. skip_name_resolve = 1
  37. skip-networking = 1
  38. default-storage-engine = InnoDB
  39. timeout
  40. wait_timeout=1000
  41. lock_wait_timeout=3600
  42. interactive_timeout=1000
  43. connect_timeout = 20
  44. server-id =$serverid ip最后一位+端口号
  45.  
  46.  
  47. percona 的--recursion-method slavehost模式report_host = 10.105.9.115report_port = 3306
  48.  
  49.  
  50.  
  51. plugin
  52. plugin-load=semisync_master.so;semisync_slave.so"
  53.  
  54.  
  55.  
  56. ########SSL#############
  57. ssl-ca = /data/MysqL/MysqL$port/data/ca.pem
  58. ssl-cert = /data/MysqL/MysqL$port/data/server-cert.pem
  59. ssl-key = /data/MysqL/MysqL$port/data/server-key.pem
  60. ########undo#############
  61. innodb_undo_logs =126 每个tablespace里包含的rollback seg的个数
  62. innodb_undo_directory =/data/MysqL/MysqL$port/logs/
  63. innodb_max_undo_log_size = 1G
  64. innodb_undo_tablespaces = 8 undo tablespace的个数
  65. innodb_undo_log_truncate = 1
  66. innodb_purge_rseg_truncate_frequency = 128
  67.  
  68.  
  69. ########error log#############
  70. log-error = /data/MysqL/MysqL$port/logs/error.log
  71. log_error_verbosity = 3
  72.  
  73. ########general log#############general_log=1general_log_file=/data/MysqL/MysqL$port/logs/MysqL.log
  74.  
  75. ########slow log#############
  76. slow_query_log = 1
  77. long_query_time=1 0表示记录所有sql
  78. slow_query_log_file = /data/MysqL/MysqL$port/logs/MysqL.slow
  79. ############ for replication###################
  80. log-bin = /data/MysqL/MysqL$port/logs/MysqL-bin
  81. binlog_format = row
  82. max_binlog_size = 500M
  83. binlog_cache_size = 5M
  84. max_binlog_cache_size = 5M
  85. expire-logs-days = $expirelogsdays
  86. slave-net-timeout=30
  87. log-slow-slave-statements =1
  88. log_bin_trust_function_creators = 1
  89. log-slave-updates = 1
  90. skip-slave-start = 1
  91. super_read_only =1
  92.  
  93. GTID
  94. gtid-mode = on
  95. binlog_gtid_simple_recovery=1
  96. enforce_gtid_consistency=1
  97.  
  98. relay log
  99. relay-log = /data/MysqL/MysqL$port/logs/MysqL-relay
  100. relay-log-index=/data/MysqL/MysqL$port/logs/relay-bin.index
  101. max-relay-log-size = 500M
  102. relay_log_purge = $relaylogpurge MHA里不能清除relaylog
  103.  
  104.  
  105. replication crash safe
  106. sync_master_info = 1
  107. sync_relay_log_info = 1
  108. sync_relay_log = 1
  109. relay_log_recovery = 1
  110. master_info_repository = TABLE
  111. relay_log_info_repository = TABLE
  112. 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
  113.  
  114.  
  115. ignorereplicate-ignore-db = 'school','school2'replicate-do-db = 'school',1)">replicate-do-table = 'db1.t1'replicate-ignore-table= 'db1.t1'
  116.  
  117. 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
  118.  
  119. replication errorslave-skip-errors=1007,1051,1062
  120.  
  121. ######per_thread_buffers#####################
  122. max_connections=1100
  123. max_user_connections=1000
  124. max_connect_errors=1000
  125. myisam_recover
  126. max_allowed_packet = 16M
  127. table_cache = 3096
  128. table_open_cache = 6144
  129. table_definition_cache = 4096
  130. table_open_cache_instances = 64
  131. read_buffer_size = 1M
  132. join_buffer_size = 4M
  133. read_rnd_buffer_size = 1M
  134. myisam
  135. sort_buffer_size = 128K
  136. myisam_max_sort_file_size = 10G
  137. myisam_repair_threads = 1
  138. key_buffer_size = 64M
  139. myisam_sort_buffer_size = 32M
  140. tmp_table_size = 64M
  141. max_heap_table_size = 64M
  142. query_cache_type=0
  143. query_cache_size = 0
  144. bulk_insert_buffer_size = 32M
  145. thread_cache_size = 64
  146. thread_concurrency = 32
  147. thread_stack = 192K
  148. ##############InnoDB###########################
  149. innodb_data_home_dir = /data/MysqL/MysqL$port/data
  150. innodb_log_group_home_dir = /data/MysqL/MysqL$port/logs
  151. innodb_data_file_path = ibdata1:1000M:autoextend
  152. innodb_temp_data_file_path = ibtmp1:12M:autoextend
  153. innodb_buffer_pool_size = $innodbbufferpoolsize
  154. innodb_buffer_pool_instances = 8
  155. innodb_additional_mem_pool_size = 16M
  156. innodb_log_file_size = 500M
  157. innodb_log_buffer_size = 16M
  158. innodb_log_files_in_group = 3
  159. innodb_flush_log_at_trx_commit = 1
  160. sync_binlog = 1
  161. innodb_lock_wait_timeout = 10
  162. innodb_sync_spin_loops = 40
  163. innodb_max_dirty_pages_pct = 80
  164. innodb_support_xa = 1
  165. innodb_thread_concurrency = 0
  166. innodb_thread_sleep_delay = 500
  167. innodb_concurrency_tickets = 1000
  168. innodb_flush_method = O_DIRECT
  169. innodb_file_per_table = 1
  170. innodb_read_io_threads = 16
  171. innodb_write_io_threads = 16
  172. innodb_io_capacity = $innodbiocapacity
  173. innodb_flush_neighbors = 1
  174. innodb_purge_threads=$innodbpurgethreads
  175. innodb_purge_batch_size = 32
  176. innodb_old_blocks_pct=75
  177. innodb_change_buffering=all
  178. innodb_stats_on_Metadata=OFF
  179. innodb_print_all_deadlocks = 1
  180. performance_schema=$performanceschema
  181. transaction_isolation = READ-COMMITTED
  182. innodb_force_recovery=0innodb_fast_shutdown=1innodb_status_output=1innodb_status_output_locks=1innodb_status_file = 1
  183. [MysqLdump]
  184. quick
  185. max_allowed_packet = 128M
  186. [MysqL]
  187. no-auto-rehash
  188. max_allowed_packet = 128M
  189. prompt = ($environment)\u@\h:\p [\d]>
  190. default_character_set = utf8
  191. [myisamchk]
  192. key_buffer_size = 64M
  193. sort_buffer_size = 512k
  194. read_buffer = 2M
  195. write_buffer = 2M
  196. [MysqLhotcopy]
  197. interactive-timeout
  198. [MysqLd_safe]
  199. malloc-lib= /usr/local/MysqL/lib/MysqL/libjemalloc.so
  200. EOF

 

 

 

如有不对的地方,欢迎大家拍砖o(∩_∩)o 

本文版权归作者所有,未经作者同意不得转载。

猜你在找的Python相关文章