python脚本-mysql for 817
更新mysql8.17的脚本：2019-09-19
# -*- coding: utf-8 -*- import sys reload(sys) import getopt import commands import subprocess import fileinput import os __author__ = 'Lenny' INFO = "\033[1;33;40m%s\033[0m" ERROR = "\033[1;31;40m%s\033[0m" NOTICE = "\033[1;32;40m%s\033[0m" LINE = "#" * 50 LINE_ERROR = "-" * 50 def CheckAndHelp(project_name, option): usage_help = '''Usage: python %s install [OPTION] [arg...]''' % project_name example = '''Example: Simple: python mysql_install.py install --instance-ports=3366 Multiple instances: python mysql_install.py install --instance-ports=3366,3399,4466 If you know enough to the MySQL, you can use configure area: Simple: python %s install --instance-ports=3366 --innodb-buffer-pool-size=1G '''% project_name configure_usage = ''' ''' install_usage = '''install: custom install: --instance-ports default:3306 --mysql-user default:mysql --base-prefix default:/usr/local/mysql --data-prefix default:/data/db ''' if option == "install" or option is None: usage = usage_help + "\n" + example + "\n" + install_usage + "\n" + configure_usage return usage else: usage = usage_help + "\n" + example + "\n" + install_usage + "\n" + configure_usage return usage def CheckArgv(argvs): check_start_message = "Check Argument Start . . ." print LINE print INFO % check_start_message print LINE invalid_list = [] result_dic = {} #print argvs,"???" try: opts, args = getopt.getopt(argvs, "v", ["instance-ports=", "mysql-user=", "base-prefix=", "data-prefix=", "log-prefix=", "max-allowed-packet=", "max-binlog-size=", "binlog-cache-size=", "expire-logs-days=", "slow-query-log=", "long-query-time=", "log-queries-not-using-indexes=", "key-buffer-size=", "innodb-data-file-path=", "innodb-buffer-pool-size=", "innodb-log-file-size=", "innodb-log-buffer-size=", "innodb-thread-concurrency=", "innodb-write-io-threads=", "innodb-read-io-threads=", "innodb_io_capacity=", "max-connections=", "read-buffer-size=", "read-rnd-buffer-size=", "tmp-table-size=", "max-heap-table-size=", "thread-cache-size=", "wait-timeout="]) #print opts, "opts++" #print args, "args--" result_dic = {'result_state': 'true', 'result': opts} except getopt.GetoptError, err: # print err err_msg = " use -h or -help check" result_dic = {'result_state': 'false', 'result': str(err) + "\n\n" + err_msg} # print result_dic return result_dic def CreateConfigurationFiles(configure): # print configure instance_ports = '3306' mysql_user = 'mysql' base_prefix = '/usr/local/mysql' data_prefix = '/data/db' log_prefix = '/data/dblogs' max_allowed_packet = '64M' max_binlog_size = '1024M' binlog_cache_size = '4M' expire_logs_days = '7' slow_query_log = '0' long_query_time = '1' log_queries_not_using_indexes = '0' key_buffer_size = '64M' innodb_data_file_path = 'ibdata1:1G:autoextend' innodb_log_file_size = '256M' innodb_log_buffer_size = '2M' innodb_io_capacity = '200' max_connections = '5000' read_buffer_size = '8M' read_rnd_buffer_size = '16M' tmp_table_size = '128M' max_heap_table_size = '128M' wait_timeout = '600' system_free_memory = commands.getstatusoutput("cat /proc/meminfo | grep MemFree|awk '{print $(NF-1)}'")[1] # system_total_memory = '1048576' system_total_memory = commands.getstatusoutput("cat /proc/meminfo | grep MemTotal|awk '{print $(NF-1)}'")[1] sort_buffer_size = '16M' # innodb_buffer_pool_size = '128M' innodb_buffer_pool_size = '%sM' % int((int(system_free_memory) / 1024 - int(key_buffer_size[:-1]) - int( max_connections) * (int(sort_buffer_size[:-1]) + int(read_buffer_size[:-1]) + int( binlog_cache_size[:-1])) - int(max_connections) - int(max_binlog_size[:-1]) - int(tmp_table_size[:-1])) * 0.95) if int(innodb_buffer_pool_size[:-1]) < 0: innodb_buffer_pool_size = '512M' cpu_core = commands.getstatusoutput("cat /proc/cpuinfo | grep processor | wc -l")[1] # innodb_thread_concurrency = '8' # innodb_write_io_threads = '4' # innodb_read_io_threads = '4' r1 = commands.getstatusoutput(""" ip a|grep 'scope global'|awk -F[:' '/]+ '{print $3}'|grep ^192|tail -1 """)[1].split('.')[2:] server_id = "".join(r1) + instance_ports innodb_thread_concurrency = '%s' % (int(cpu_core) * 2) innodb_write_io_threads = '%s' % (int(cpu_core)) innodb_read_io_threads = '%s' % (int(cpu_core)) thread_cache_size = '%s' % int(int(max_connections) * 0.1) default_argv = {'--server-id': server_id, '--instance-ports': instance_ports, '--mysql-user': mysql_user, '--base-prefix': base_prefix, '--data-prefix': data_prefix, '--log-prefix': log_prefix, '--max-allowed-packet': max_allowed_packet, '--max-binlog-size': max_binlog_size, '--binlog-cache-size': binlog_cache_size, '--expire-logs-days': expire_logs_days, '--slow-query-log': slow_query_log, '--long-query-time': long_query_time, '--log-queries-not-using-indexes': log_queries_not_using_indexes, '--key-buffer-size': key_buffer_size, '--innodb-data-file-path': innodb_data_file_path, '--innodb-buffer-pool-size': innodb_buffer_pool_size, '--innodb-log-file-size': innodb_log_file_size, '--innodb-log-buffer-size': innodb_log_buffer_size, '--innodb-thread-concurrency': innodb_thread_concurrency, '--innodb-write-io-threads': innodb_write_io_threads, '--innodb-read-io-threads': innodb_read_io_threads, '--innodb_io_capacity': innodb_io_capacity, '--max-connections': max_connections, '--read-buffer-size': read_buffer_size, '--read-rnd-buffer-size': read_rnd_buffer_size, '--tmp-table-size': tmp_table_size, '--max-heap-table-size': max_heap_table_size, '--thread-cache-size': thread_cache_size, '--wait-timeout': wait_timeout } for k, v in configure: if k == "--max-allowed-packet": if int(v[:-1]) < 1000: default_argv[k] = v continue else: default_argv[k] = max_allowed_packet msg = "%s The Value Is Unavailable, Change Default Value" % k print LINE print NOTICE % msg print LINE continue elif k == "--max-binlog-size": if int(v[:-1]) < 2000: default_argv[k] = v continue else: default_argv[k] = max_binlog_size msg = "%s The Value Is Unavailable, Change Default Value" % k print LINE print NOTICE % msg print LINE continue elif k == "--binlog-cache-size": if 2 <= int(v[:-1]) <= 4: default_argv[k] = v continue else: default_argv[k] = binlog_cache_size msg = "%s The Value Is Unavailable, Change Default Value" % k print LINE print NOTICE % msg print LINE continue elif k == "--key-buffer-size": if int(v[:-1]) <= int(int(system_total_memory) / 1024 * 0.2): default_argv[k] = v continue else: default_argv[k] = key_buffer_size msg = "%s The Value Is Unavailable, Change Default Value" % k print LINE print NOTICE % msg print LINE continue elif k == "--tmp-table-size": if int(v[:-1]) <= int(int(system_total_memory) / 1024 * 0.1): default_argv[k] = v continue else: default_argv[k] = tmp_table_size msg = "%s The Value Is Unavailable, Change Default Value" % k print LINE print NOTICE % msg print LINE continue elif k == "--max-heap-table-size": if int(v[:-1]) <= int(int(system_total_memory) / 1024 * 0.1): default_argv[k] = v continue else: default_argv[k] = max_heap_table_size msg = "%s The Value Is Unavailable, Change Default Value" % k print LINE print NOTICE % msg print LINE continue else: default_argv[k] = v #default_argv['--server-id'] = "".join(server_id) + instance_ports #print default_argv check_finish_message = "Check Argument Finish" print LINE print INFO % check_finish_message print LINE return default_argv def CheckReplaceFile(configure_dic): check_environment_start_message = "Check Environment Start . . ." print LINE print INFO % check_environment_start_message print LINE data_file_path = "%s/mysql%s" % (configure_dic['--data-prefix'], configure_dic['--instance-ports']) log_file_path = "%s/mysql%s" % (configure_dic['--log-prefix'], configure_dic['--instance-ports']) local_file = 'my.cnf' # if os.path.exists(configure_dic['--base-prefix']): # pass # else: # commands.getstatusoutput("mkdir -p %s" % configure_dic['--base-prefix']) # print "The %s create success" % configure_dic['--base-prefix'] if os.path.exists(configure_dic['--data-prefix']): pass else: commands.getstatusoutput("mkdir -p %s" % configure_dic['--data-prefix']) msg = "The Path: %s Create Success" % configure_dic['--data-prefix'] print LINE print NOTICE % msg print LINE if os.path.exists(configure_dic['--log-prefix']): pass else: commands.getstatusoutput("mkdir -p %s" % configure_dic['--log-prefix']) msg = "The Path: %s Create Success" % configure_dic['--log-prefix'] print LINE print NOTICE % msg print LINE if os.path.exists(data_file_path): msg = "[Error]: The MySQL Instance Port: %s exists, Pleace Change" % configure_dic['--instance-ports'] print LINE_ERROR print ERROR % msg print LINE_ERROR sys.exit() else: commands.getstatusoutput("mkdir -p %s" % data_file_path) msg = "The Path: %s Create Success" % data_file_path print LINE print NOTICE % msg print LINE if os.path.exists(log_file_path): msg = "[Error]: The MySQL Instance Port: %s exists, Pleace Change" % configure_dic['--instance-ports'] print LINE_ERROR print ERROR % msg print LINE_ERROR sys.exit() else: commands.getstatusoutput("mkdir -p %s" % log_file_path) msg = "The Path: %s Create Success" % log_file_path print LINE print NOTICE % msg print LINE if int(commands.getstatusoutput("id %s" % configure_dic['--mysql-user'])[0]) == 0: pass else: commands.getstatusoutput("useradd %s" % configure_dic['--mysql-user']) msg = "Add MySQL User: %s Succsee" % configure_dic['--mysql-user'] print LINE print NOTICE % msg print LINE check_environment_finish_message = "Check Environment Finish" print LINE print INFO % check_environment_finish_message print LINE create_file_start_message = "Create MySQL Configuration File Start . . ." print LINE print INFO % create_file_start_message print LINE cp_file = commands.getstatusoutput("cp -fr %s %s" % (local_file, data_file_path)) if int(cp_file[0]) == 0: pass else: print cp_file[1] mysql_file = os.path.join(data_file_path, local_file) #print configure_dic for k, v in configure_dic.items(): commands.getstatusoutput("sed -i 's#$%s#%s#g' %s" % (k, v, mysql_file)) result_dic = {'result_state': "true", 'result': mysql_file} create_file_finish_message = "Create MySQL Configuration File Finish" print LINE print INFO % create_file_finish_message print LINE return result_dic def InstallMysql(install_cfg, filename): install_start_message = "Install MySQL Start Port: %s. . ." % install_cfg['--instance-ports'] print LINE print INFO % install_start_message print LINE rootpwd = "root" mysql_conf_name = "my.cnf" old_mysql_file = "/etc/my.cnf" data_file_path = "%s/mysql%s" % (install_cfg['--data-prefix'], install_cfg['--instance-ports']) log_file_path = "%s/mysql%s" % (install_cfg['--log-prefix'], install_cfg['--instance-ports']) data_dir = ['data', 'mysqltmp'] log_dir = ['binlogs', 'slowlogs', 'relaylogs'] install_dir = "/usr/local/" mysql_src_tar = filename mysql_src_name = mysql_src_tar.replace(".tar","").strip() mysql_run_script = 'mysql' mysql_safe_file = '%s/bin/mysqld_safe' % install_cfg['--base-prefix'] mysql_conf_file = os.path.join(data_file_path, mysql_conf_name) mysql_run_file = os.path.join(data_file_path, mysql_run_script) sys_run_file = "/etc/init.d/mysql%s" % install_cfg['--instance-ports'] mysql_sock = "%s/mysql%s/mysql%s.sock" % (install_cfg['--data-prefix'], install_cfg['--instance-ports'], install_cfg['--instance-ports']) if os.path.exists(install_cfg['--base-prefix']) and os.path.exists(os.path.join(install_dir, mysql_src_name)): pass else: tar_mysql = commands.getstatusoutput("tar xvf %s -C %s" % (mysql_src_tar, install_dir)) if int(tar_mysql[0]) == 0: commands.getstatusoutput( "ln -s %s %s" % (os.path.join(install_dir, mysql_src_name), install_cfg['--base-prefix'])) print install_dir, mysql_src_name create_environment_start_message = "Create MySQL Environment Start . . ." print LINE print INFO % create_environment_start_message print LINE if os.path.exists('/usr/sbin/lsof'): pass else: lsof_install = commands.getstatusoutput("yum install lsof -y") if int(lsof_install[0]) != 0: msg = "[Error]: Failed To Install Lsof" print LINE_ERROR print ERROR % msg print LINE_ERROR sys.exit() if os.path.exists('/usr/bin/perl'): pass else: perl_install = commands.getstatusoutput("yum install perl -y") if int(perl_install[0]) != 0: msg = "[Error]: Failed To Install Perl" print LINE_ERROR print ERROR % msg print LINE_ERROR sys.exit() if os.path.exists('/lib/libaio.so.1'): pass else: libaio_install = commands.getstatusoutput("yum install libaio-devel libaio -y") if int(libaio_install[0]) != 0: msg = "[Error]: Failed To Install Libaio" print LINE_ERROR print ERROR % msg print LINE_ERROR sys.exit() commands.getstatusoutput("sed -i 's#/usr/local/mysql#'%s'#g' %s" % (install_cfg['--base-prefix'], mysql_safe_file)) if os.path.exists(old_mysql_file): commands.getstatusoutput("mv %s %s.bak" % (old_mysql_file, old_mysql_file)) for data in data_dir: commands.getstatusoutput("mkdir -p %s/%s" % (data_file_path, data)) for log in log_dir: commands.getstatusoutput("mkdir -p %s/%s" % (log_file_path, log)) commands.getstatusoutput("cp -fr %s %s" % (mysql_run_script, data_file_path)) commands.getstatusoutput("chown -R mysql.mysql %s" % data_file_path) commands.getstatusoutput("chown -R mysql.mysql %s" % log_file_path) commands.getstatusoutput("sed -i 's#{MYCNF-DIR}#'%s'#g' %s" % (mysql_conf_file, mysql_run_file)) commands.getstatusoutput("sed -i 's#{BIN-DIR}#'%s/bin'#g' %s" % (install_cfg['--base-prefix'], mysql_run_file)) commands.getstatusoutput( "sed -i 's#{PID-DIR}#'%s/mysql%s.pid'#g' %s" % (log_file_path, install_cfg['--instance-ports'], mysql_run_file)) commands.getstatusoutput("cp -fr %s %s" % (mysql_run_file, sys_run_file)) # commands.getstatusoutput("/bin/rm -f /etc/init.d/mysqld") commands.getstatusoutput("chmod 700 %s" % sys_run_file) commands.getstatusoutput("/sbin/chkconfig add mysql%s" % install_cfg['--base-prefix']) commands.getstatusoutput("/sbin/chkconfig mysql%s on" % install_cfg['--base-prefix']) commands.getstatusoutput("find %s -name mysql -exec chmod 700 {} \;" % data_file_path) mysql_value = commands.getstatusoutput("grep -w mysql%s /etc/profile | wc -l" % install_cfg['--instance-ports']) if int(mysql_value[1]) == 0: commands.getstatusoutput("echo alias mysql%s='\"'%s/bin/mysql --defaults-file=%s -S %s'\"' >> /etc/profile" % ( install_cfg['--instance-ports'], install_cfg['--base-prefix'], mysql_conf_file, mysql_sock)) create_environment_finish_message = "Create MySQL Environment Finish" print LINE print INFO % create_environment_finish_message print LINE initialize_mysql_start_message = "Initialize MySQL . . ." print LINE print INFO % initialize_mysql_start_message print LINE MYSTR = "%s/bin/mysqld --defaults-file=%s --basedir=%s --user=mysql --initialize --explicit_defaults_for_timestamp >/dev/null" % ( install_cfg['--base-prefix'], mysql_conf_file, install_cfg['--base-prefix']) print INFO % MYSTR # mysql_initialize = commands.getstatusoutput() scommand = "%s/bin/mysqld --defaults-file=%s --basedir=%s --user=mysql --initialize --explicit_defaults_for_timestamp>/dev/null" % ( install_cfg['--base-prefix'], mysql_conf_file, install_cfg['--base-prefix']) status = subprocess.call(scommand, shell=True) if status > 0: msg = "[Error]: Failed to initialize MySQL data directory. Port: %s" % install_cfg['--instance-ports'] print LINE_ERROR print ERROR % msg print LINE_ERROR sys.exit() # # # mysql_initialize = commands.getstatusoutput("%s/scripts/mysql_install_db --basedir=%s --defaults-file=%s --user=mysql --explicit_defaults_for_timestamp >/dev/null" % (install_cfg['--base-prefix'], install_cfg['--base-prefix'], mysql_conf_file)) # if mysql_initialize != 0: initialize_mysql_finish_message = "Initialize MySQL Finish" print LINE print INFO % initialize_mysql_finish_message print LINE mysql_start_message = "MySQL Start . . ." print LINE print INFO % mysql_start_message print LINE print "the sys_run_file is %s" % sys_run_file mysql_start = subprocess.call([sys_run_file, 'start']) print "start print mysql_start" if mysql_start> 0: print "MySQL failed to start on %s" % install_cfg['--instance-ports'] sys.exit() mysql_finish_message = "MySQL Start Finish" print LINE print INFO % mysql_finish_message print LINE mysql_privileges_start_message = "Cfg MySQL(%s) Privileges Start . . ." % install_cfg['--instance-ports'] print LINE print INFO % mysql_privileges_start_message print LINE a=""" grep 'temporary password' %s/error.log |sed 's/.*root@localhost: //' """%(log_file_path) password_temp=commands.getstatusoutput(a)[1] #print password_temp scommand=""" /usr/sbin/lsof -i :%s &>/dev/null && %s/bin/mysql --connect-expired-password --socket=%s -u root -p"%s" -e "SET SQL_LOG_BIN=0;alter user 'root'@'localhost' identified by 'root';SET SQL_LOG_BIN=1;" """ % (install_cfg['--instance-ports'], install_cfg['--base-prefix'], mysql_sock,password_temp) print scommand cfg_mysql_privileges=subprocess.call(scommand, shell=True) if cfg_mysql_privileges > 0: msg = "[Error]: Failed to set MySQL root password! Please do it manually." print LINE_ERROR print ERROR % cfg_mysql_privileges print ERROR % msg print LINE_ERROR sys.exit() # cfg_mysql_privileges = commands.getstatusoutput( # "/usr/sbin/lsof -i :%s &>/dev/null && %s/bin/mysqladmin -u root password '%s' -S %s >/dev/null 2>&1" % ( # install_cfg['--instance-ports'], install_cfg['--base-prefix'], rootpwd, mysql_sock)) # /usr/local/mysql/ bin / mysqladmin - uroot - p 'jSh#jXtkC6i8' password 'root' - -socket = / data / db / mysql3306 / mysql3306.sock # if int(cfg_mysql_privileges[0]) != 0: # print cfg_mysql_privileges[1] # msg = "[Error]: Failed to set MySQL root password! Please do it manually." # print LINE_ERROR # print ERROR % cfg_mysql_privileges[1] # print ERROR % msg # print LINE_ERROR # sys.exit() mysql_privileges_finish_message = "Cfg MySQL(%s) Privileges Finish" % install_cfg['--instance-ports'] print LINE print INFO % mysql_privileges_finish_message print LINE check_mysql_start_message = "Check MySQL(%s) Connected to port Start" % install_cfg['--instance-ports'] print LINE print INFO % check_mysql_start_message print LINE check_mysql = commands.getstatusoutput( '''%s/bin/mysql -uroot -p%s -S %s -Bse "SELECT concat(version(),' started in %s port')" ''' % ( install_cfg['--base-prefix'], rootpwd, mysql_sock, install_cfg['--instance-ports'])) if int(check_mysql[0]) != 0: msg = "[Error]: Failed to connect to port %s! You need to create MySQL monitoring user manually." % install_cfg[ '--instance-ports'] print LINE_ERROR print ERROR % check_mysql[1] print ERROR % msg print LINE_ERROR sys.exit() else: msg = "Connected to port %s successful." % install_cfg['--instance-ports'] print LINE print NOTICE % msg print LINE check_mysql_finish_message = "Check MySQL(%s) Connected to port Finish" % install_cfg['--instance-ports'] print LINE print INFO % check_mysql_finish_message print LINE create_user_start_message = "Creating MySQL Monitoring User Start..." print LINE print INFO % create_user_start_message print LINE create_mysql_monitor = commands.getstatusoutput( '''%s/bin/mysql -uroot -p%s -S %s -Bse "GRANT PROCESS,REPLICATION CLIENT ON *.* TO 'monitoring'@'127.0.0.1 identified by '8e3d7855e5681ee463e28394c2bb33043e65dbb9';FLUSH PRIVILEGES;" ''' % ( install_cfg['--base-prefix'], rootpwd, mysql_sock)) create_user_finish_message = "Creating MySQL Monitoring User Finish..." print LINE print INFO % create_user_finish_message print LINE install_rpl_start_message = "Install rpl_semi_sync on %s Start.... " % install_cfg['--instance-ports'] print LINE print INFO % install_rpl_start_message print LINE install_rpl_semi_sync = commands.getstatusoutput( '''%s/bin/mysql -uroot -p%s -S %s -e "INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';" ''' % ( install_cfg['--base-prefix'], rootpwd, mysql_sock)) if int(install_rpl_semi_sync[0]) != 0: print install_rpl_semi_sync[1] msg = "[Error]: Install rpl_semi_sync failed on %s" % install_cfg['--instance-ports'] print LINE_ERROR print ERROR % install_rpl_semi_sync[1] print ERROR % msg print LINE_ERROR sys.exit() install_rpl_finish_message = "Install rpl_semi_sync on %s Finish.... " % install_cfg['--instance-ports'] print LINE print INFO % install_rpl_finish_message print LINE return "true" if __name__ == '__main__': if os.geteuid() != 0: msg = "[Error]: This script must be run as root. Aborting." print LINE_ERROR print ERROR % msg print LINE_ERROR sys.exit() if len(sys.argv) < 3 or sys.argv[1] != "install": configure_help = CheckAndHelp(sys.argv[0], "install") print configure_help sys.exit() check_argv = sys.argv[2:] #print check_argv if "--instance-ports" not in str(check_argv) or "--innodb-buffer-pool-size" not in str(check_argv): print "python %s install --instance-ports=3366 --innodb-buffer-pool-size=1G"%sys.argv[0] sys.exit() filename = 'mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz' filename2 = 'mysql-8.0.17-linux-glibc2.12-x86_64.tar' if not os.path.exists(filename2): if not os.path.exists(filename): msg = "%s not exists!"%filename print LINE_ERROR print ERROR % msg print "wget http://ftp.ntu.edu.tw/MySQL/Downloads/MySQL-8.0/mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz" print LINE_ERROR sys.exit() else: commands.getstatusoutput("xz -d %s" % (filename)) check_result = CheckArgv(check_argv) if check_result['result_state'] == "false": print check_result['result'] else: #print check_result['result'] create_result = CreateConfigurationFiles(check_result['result']) for port in create_result['--instance-ports'].split(','): create_result['--instance-ports'] = port create_file_result = CheckReplaceFile(create_result) #print create_file_result if create_file_result['result_state'] == "true": msg = "Configuration File %s Create Success" % create_file_result['result'] print LINE print INFO % msg print LINE install_result = InstallMysql(create_result, filename2) #install_result = CfgMysql(create_result) if install_result == "true": commands.getstatusoutput('source /etc/profile') install_finish_message = "Install MySQL Finish Port: %s" % port print LINE print INFO % install_finish_message print LINE
my.cnf 配置文件：
# ## my.cnf for MySQL8.17 ## # # [client] port = $--instance-ports socket = $--data-prefix/mysql$--instance-ports/mysql$--instance-ports.sock [mysql] prompt="\u@\h[\d]> " no-auto-rehash [mysqld] user = $--mysql-user port = $--instance-ports basedir = $--base-prefix datadir = $--data-prefix/mysql$--instance-ports/data socket = $--data-prefix/mysql$--instance-ports/mysql$--instance-ports.sock pid_file = $--log-prefix/mysql$--instance-ports/mysql$--instance-ports.pid #--- GLOBAL ---# server_id = $--server-id character_set_server = utf8mb4 collation_server = utf8mb4_unicode_ci # default_authentication_plugin = 'mysql_native_password' tmpdir = $--data-prefix/mysql$--instance-ports/mysqltmp log_timestamps = SYSTEM log_error = $--log-prefix/mysql$--instance-ports/error.log log_error_verbosity = 2 slow_query_log = 1 slow_query_log_file = $--log-prefix/mysql$--instance-ports/slowlogs/mysql_slow.log long_query_time = 1 general_log_file = $--log-prefix/mysql$--instance-ports/mysql_gene.log open_files_limit = 65535 thread_cache_size = 3000 table_open_cache = 1024 table_definition_cache = 1024 table_open_cache_instances = 64 thread_stack = 512K external-locking = FALSE sort_buffer_size = 4M join_buffer_size = 4M thread_cache_size = 5000 tmp_table_size = 32M max_heap_table_size = 32M slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN' key_buffer_size = 32M read_buffer_size = 8M read_rnd_buffer_size = 4M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 lock_wait_timeout = 3600 lower_case_table_names = 1 explicit_defaults_for_timestamp = 1 innodb_thread_concurrency = 0 innodb_sync_spin_loops = 100 innodb_spin_wait_delay = 30 transaction_isolation = READ-COMMITTED #innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = $--innodb-buffer-pool-size innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_data_file_path = ibdata1:1G:autoextend innodb_log_buffer_size = 32M innodb_log_file_size = 2G innodb_log_files_in_group = 2 innodb_max_undo_log_size = 4G #--- REPL ---# log_bin = $--log-prefix/mysql$--instance-ports/binlogs/mysql_bin binlog_rows_query_log_events = ON #for mysql8 binlog_expire_logs_seconds instead of binlog_expire_logs_days expire_logs_days = 30 binlog_checksum = 1 binlog_format = row binlog_cache_size = 4M max_binlog_cache_size = 2G max_binlog_size = 1G min_examined_row_limit = 100 log_slow_admin_statements = 1 log_slow_slave_statements = 1 log_queries_not_using_indexes = 0 log_slave_updates master_info_repository = TABLE relay_log_info_repository = TABLE skip_slave_start #--gtid gtid_mode = ON enforce_gtid_consistency = 1 #--flush disk innodb_flush_log_at_trx_commit = 1 sync_binlog = 1 #--relay_log relay_log = relay-log relay_log_recovery = 1 relay_log_purge = 1 #--- NETWORK ---# max_allowed_packet = 128M back_log = 1024 interactive_timeout = 600 wait_timeout = 600 max_connections = 5000 skip_name_resolve = 1 max_connect_errors = 1000000 #--UNDO #innodb_undo_directory = ./ #innodb_undo_logs = 128 #innodb_undo_log_truncate = ON # The setting INNODB_UNDO_TABLESPACES is deprecated and is no longer used. InnoDB always creates 2 undo tablespaces to start with #innodb_undo_tablespaces = 3 #--并行复制 loose-binlog_transaction_dependency_tracking = WriteSet_Session loose-transaction_write_set_extraction = XXHASH64 slave_parallel_type = LOGICAL_CLOCK slave_parallel_workers = 8 # 根据您的服务器IOPS能力适当调整 # 一般配普通SSD盘的话，可以调整到 10000 - 20000 # 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000 innodb_io_capacity = 4000 innodb_io_capacity_max = 8000 innodb_flush_sync = 0 innodb_flush_neighbors = 0 innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_purge_threads = 4 innodb_page_cleaners = 4 innodb_open_files = 65535 innodb_max_dirty_pages_pct = 50 innodb_flush_method = O_DIRECT innodb_lru_scan_depth = 4000 innodb_checksum_algorithm = crc32 innodb_lock_wait_timeout = 10 innodb_rollback_on_timeout = 1 innodb_print_all_deadlocks = 1 innodb_file_per_table = 1 innodb_online_alter_log_max_size = 4G #for mysql 8 this variables ls unknown ##internal_tmp_disk_storage_engine = InnoDB innodb_stats_on_metadata = 0 # some var for MySQL 5.7 # for mysql8.0 innodb_checksums is a unkown variables #innodb_checksums = 1 #innodb_file_format = Barracuda #innodb_file_format_max = Barracuda innodb_status_file = 1 # 注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快 innodb_status_output = 0 innodb_status_output_locks = 0 #performance_schema performance_schema = 1 performance_schema_instrument = '%=on' #innodb monitor innodb_monitor_enable="module_innodb" innodb_monitor_enable="module_server" innodb_monitor_enable="module_dml" innodb_monitor_enable="module_ddl" innodb_monitor_enable="module_trx" innodb_monitor_enable="module_os" innodb_monitor_enable="module_purge" innodb_monitor_enable="module_log" innodb_monitor_enable="module_lock" innodb_monitor_enable="module_buffer" innodb_monitor_enable="module_index" innodb_monitor_enable="module_ibuf_system" innodb_monitor_enable="module_buffer_page" innodb_monitor_enable="module_adaptive_hash" log_bin_trust_function_creators = 1 # group replication settings #plugin-load = "group_replication.so;validate_password.so;semisync_master.so;semisync_slave.so" #transaction-write-set-extraction = XXHASH64 #report_host = 172.31.92.104 #binlog_checksum = NONE #loose_group_replication = FORCE_PLUS_PERMANENT #loose_group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" #loose_group_replication_compression_threshold = 100 #loose_group_replication_flow_control_mode = 0 #loose_group_replication_single_primary_mode = 1 #loose_group_replication_transaction_size_limit = 10485760 #loose_group_replication_unreachable_majority_timeout = 120 #loose_group_replication_start_on_boot = 0 #loose_group_replication_local_address = '172.31.92.104:6606' #loose_group_replication_group_seeds = '172.31.92.102:6606,172.31.92.103:6606,172.31.92.104:6606' #loose_group_replication_ip_whitelist= '172.31.92.0/20' [mysqldump] quick
守护进程脚本：
#!/bin/sh # Copyright Abandoned 1996 TCX DataKonsult AB & Monty Program KB & Detron HB # This file is public domain and comes with NO WARRANTY of any kind # MySQL daemon start/stop script. # Usually this is put in /etc/init.d (at least on machines SYSV R4 based # systems) and linked to /etc/rc3.d/S99mysql and /etc/rc0.d/K01mysql. # When this is done the mysql server will be started when the machine is # started and shut down when the systems goes down. # Comments to support chkconfig on RedHat Linux # chkconfig: 2345 64 36 # description: A very fast and reliable SQL database engine. # Comments to support LSB init script conventions ### BEGIN INIT INFO # Provides: mysql # Required-Start: $local_fs $network $remote_fs # Should-Start: ypbind nscd ldap ntpd xntpd # Required-Stop: $local_fs $network $remote_fs # Default-Start: 2 3 4 5 # Default-Stop: 0 1 6 # Short-Description: start and stop MySQL # Description: MySQL is a very fast and reliable SQL database engine. ### END INIT INFO # If you install MySQL on some other places than /usr/local/mysql, then you # have to do one of the following things for this script to work: # # - Run this script from within the MySQL installation directory # - Create a /etc/my.cnf file with the following information: # [mysqld] # basedir=<path-to-mysql-installation-directory> # - Add the above to any other configuration file (for example ~/.my.ini) # and copy my_print_defaults to /usr/bin # - Add the path to the mysql-installation-directory to the basedir variable # below. # # If you want to affect other MySQL variables, you should make your changes # in the /etc/my.cnf, ~/.my.cnf or other MySQL configuration files. # If you change base dir, you must also change datadir. These may get # overwritten by settings in the MySQL configuration files. basedir= datadir= # Default value, in seconds, afterwhich the script should timeout waiting # for server start. # Value here is overriden by value in my.cnf. # 0 means don't wait at all # Negative numbers mean to wait indefinitely service_startup_timeout=900 # Lock directory for RedHat / SuSE. lockdir='/var/lock/subsys' lock_file_path="$lockdir/mysql" # The following variables are only set for letting mysql.server find things. # Set some defaults mysqld_pid_file_path= if test -z "$basedir" then basedir=/usr/local/mysql bindir=/usr/local/mysql/bin if test -z "$datadir" then datadir=/usr/local/mysql/data fi sbindir=/usr/local/mysql/bin libexecdir=/usr/local/mysql/bin else bindir="$basedir/bin" if test -z "$datadir" then datadir="$basedir/data" fi sbindir="$basedir/sbin" libexecdir="$basedir/libexec" fi # datadir_set is used to determine if datadir was set (and so should be # *not* set inside of the --basedir= handler.) datadir_set= # # Use LSB init script functions for printing messages, if possible # lsb_functions="/lib/lsb/init-functions" if test -f $lsb_functions ; then . $lsb_functions else log_success_msg() { echo " SUCCESS! $@" } log_failure_msg() { echo " ERROR! $@" } fi PATH="/sbin:/usr/sbin:/bin:/usr/bin:$basedir/bin" export PATH mode=$1 # start or stop [ $# -ge 1 ] && shift other_args="$*" # uncommon, but needed when called from an RPM upgrade action # Expected: "--skip-networking --skip-grant-tables" # They are not checked here, intentionally, as it is the resposibility # of the "spec" file author to give correct arguments only. case `echo "testing\c"`,`echo -n testing` in *c*,-n*) echo_n= echo_c= ;; *c*,*) echo_n=-n echo_c= ;; *) echo_n= echo_c='\c' ;; esac parse_server_arguments() { for arg do case "$arg" in --basedir=*) basedir=`echo "$arg" | sed -e 's/^[^=]*=//'` bindir="$basedir/bin" if test -z "$datadir_set"; then datadir="$basedir/data" fi sbindir="$basedir/sbin" libexecdir="$basedir/libexec" ;; --datadir=*) datadir=`echo "$arg" | sed -e 's/^[^=]*=//'` datadir_set=1 ;; --pid-file=*) mysqld_pid_file_path=`echo "$arg" | sed -e 's/^[^=]*=//'` ;; --service-startup-timeout=*) service_startup_timeout=`echo "$arg" | sed -e 's/^[^=]*=//'` ;; esac done } wait_for_pid () { verb="$1" # created | removed pid="$2" # process ID of the program operating on the pid-file pid_file_path="$3" # path to the PID file. i=0 avoid_race_condition="by checking again" while test $i -ne $service_startup_timeout ; do case "$verb" in 'created') # wait for a PID-file to pop into existence. test -s "$pid_file_path" && i='' && break ;; 'removed') # wait for this PID-file to disappear test ! -s "$pid_file_path" && i='' && break ;; *) echo "wait_for_pid () usage: wait_for_pid created|removed pid pid_file_path" exit 1 ;; esac # if server isn't running, then pid-file will never be updated if test -n "$pid"; then if kill -0 "$pid" 2>/dev/null; then : # the server still runs else # The server may have exited between the last pid-file check and now. if test -n "$avoid_race_condition"; then avoid_race_condition="" continue # Check again. fi # there's nothing that will affect the file. log_failure_msg "The server quit without updating PID file ($pid_file_path)." return 1 # not waiting any more. fi fi echo $echo_n ".$echo_c" i=`expr $i + 1` sleep 1 done if test -z "$i" ; then log_success_msg return 0 else log_failure_msg return 1 fi } # Get arguments from the my.cnf file, # the only group, which is read from now on is [mysqld] if test -x "$bindir/my_print_defaults"; then print_defaults="$bindir/my_print_defaults" else # Try to find basedir in /etc/my.cnf conf="{MYCNF-DIR}" print_defaults= if test -r $conf then subpat='^[^=]*basedir[^=]*=\(.*\)$' dirs=`sed -e "/$subpat/!d" -e 's//\1/' $conf` for d in $dirs do d=`echo $d | sed -e 's/[ ]//g'` if test -x "$d/bin/my_print_defaults" then print_defaults="$d/bin/my_print_defaults" break fi done fi # Hope it's in the PATH ... but I doubt it test -z "$print_defaults" && print_defaults="my_print_defaults" fi # # Read defaults file from 'basedir'. If there is no defaults file there # check if it's in the old (depricated) place (datadir) and read it from there # extra_args="" if test -r "$basedir/my.cnf" then extra_args="-e $basedir/my.cnf" fi parse_server_arguments `$print_defaults $extra_args mysqld server mysql_server mysql.server` # # Set pid file if not given # if test -z "$mysqld_pid_file_path" then mysqld_pid_file_path=$datadir/`hostname`.pid else case "$mysqld_pid_file_path" in /* ) ;; * ) mysqld_pid_file_path="$datadir/$mysqld_pid_file_path" ;; esac fi case "$mode" in 'start') # Start daemon # Safeguard (relative paths, core dumps..) cd $basedir echo $echo_n "Starting MySQL" if test -x $bindir/mysqld_safe then # Give extra arguments to mysqld with the my.cnf file. This script # may be overwritten at next upgrade. #$bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null & #this is mysql original script for start {BIN-DIR}/mysqld_safe --defaults-file="{MYCNF-DIR}" >/dev/null 2>&1 & #wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$? #this is mysql original script for start wait_for_pid created "$!" "{PID-DIR}"; return_value=$? # Make lock for RedHat / SuSE if test -w "$lockdir" then touch "$lock_file_path" fi exit $return_value else log_failure_msg "Couldn't find MySQL server ({BIN-DIR}/mysqld_safe)" fi ;; 'stop') # Stop daemon. We use a signal here to avoid having to know the # root password. mysqld_pid_file_path={PID-DIR} if test -s "$mysqld_pid_file_path" then # signal mysqld_safe that it needs to stop touch "$mysqld_pid_file_path.shutdown" mysqld_pid=`cat "$mysqld_pid_file_path"` if (kill -0 $mysqld_pid 2>/dev/null) then echo $echo_n "Shutting down MySQL" kill $mysqld_pid # mysqld should remove the pid file when it exits, so wait for it. wait_for_pid removed "$mysqld_pid" "$mysqld_pid_file_path"; return_value=$? else log_failure_msg "MySQL server process #$mysqld_pid is not running!" rm "$mysqld_pid_file_path" fi # Delete lock for RedHat / SuSE if test -f "$lock_file_path" then rm -f "$lock_file_path" fi exit $return_value else log_failure_msg "MySQL server PID file could not be found!" fi ;; 'restart') # Stop the service and regardless of whether it was # running or not, start it again. if $0 stop $other_args; then $0 start $other_args else log_failure_msg "Failed to stop running server, so refusing to try to start." exit 1 fi ;; 'reload'|'force-reload') if test -s "$mysqld_pid_file_path" ; then read mysqld_pid < "$mysqld_pid_file_path" kill -HUP $mysqld_pid && log_success_msg "Reloading service MySQL" touch "$mysqld_pid_file_path" else log_failure_msg "MySQL PID file could not be found!" exit 1 fi ;; 'status') # First, check to see if pid file exists mysqld_pid_file_path={PID-DIR} if test -s "$mysqld_pid_file_path" ; then read mysqld_pid < "$mysqld_pid_file_path" if kill -0 $mysqld_pid 2>/dev/null ; then log_success_msg "MySQL running ($mysqld_pid)" exit 0 else log_failure_msg "MySQL is not running, but PID file exists" exit 1 fi else # Try to find appropriate mysqld process mysqld_pid=`pidof $libexecdir/mysqld` # test if multiple pids exist pid_count=`echo $mysqld_pid | wc -w` if test $pid_count -gt 1 ; then log_failure_msg "Multiple MySQL running but PID file could not be found ($mysqld_pid)" exit 5 elif test -z $mysqld_pid ; then if test -f "$lock_file_path" ; then log_failure_msg "MySQL is not running, but lock file ($lock_file_path) exists" exit 2 fi log_failure_msg "MySQL is not running" exit 3 else log_failure_msg "MySQL is running but PID file could not be found" exit 4 fi fi ;; *) # usage basename=`basename "$0"` echo "Usage: $basename {start|stop|restart|reload|force-reload|status} [ MySQL server options ]" exit 1 ;; esac exit 0
monkeybron
