mysql配置文件详解

╰半橙微兮° 2023-06-01 11:04 109阅读 0赞 
  1. [client]
  2. port = 3306
  3. socket = /home/work/mysql_3306/tmp/mysql.sock
  5. [mysqld]
  6. user    = work
  7. port = 3306
  9. basedir = /home/work/mysql_3306
  10. datadir = /home/work/mysql_3306/data
  11. tmpdir = /home/work/mysql_3306/tmp
  12. socket = /home/work/mysql_3306/tmp/mysql.sock
  13. pid_file = /home/work/mysql_3306/tmp/mysql.pid
  14. log-error = /home/work/mysql_3306/log/mysql.err
  15. general_log = /home/work/mysql_3306/log/mysql.log
  16. slow_query_log_file = /home/work/mysql_3308/log/slow.log
  17. log-bin = /home/work/mysql_3306/log/mysql-bin
  18. plugin_dir = /home/work/mysql/lib/mysql/plugin
  20. default-time-zone = "+08:00"
  21. thread_handling = pool-of-threads
  22. thread_pool_stall_limit = 50
  23. thread_pool_oversubscribe = 20
  24. performance_schema = 1
  25. log_slave_updates
  26. log_timestamps = SYSTEM
  27. log_warnings
  28. slow_query_log
  29. long_query_time = 0.5
  30. lock_wait_timeout    = 120
  31. show_compatibility_56 = on
  32. sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
  33. table_open_cache_instances = 16 
  34. ##################################
  35. enforce_storage_engine    = InnoDB 
  36. optimizer_switch='index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on'
  37. super_read_only = off 
  38. log_slow_slave_statements = on
  39. log_slow_admin_statements = on
  40. log_slow_verbosity = 'full'
  41. slow_query_log_use_global_control = "long_query_time" 
  42. event-scheduler = off 
  43. back_log = 1000 
  44. skip_name_resolve
  45. max_connections = 10240
  46. max_user_connections = 4000
  47. max_connect_errors = 1000 
  48. extra_max_connections = 3
  49. extra_port = 13308
  50. table_open_cache = 8192 
  51. table_definition_cache = 65535
  52. max_allowed_packet = 64M 
  53. expire_logs_days = 7
  54. max_heap_table_size = 1024M 
  55. read_rnd_buffer_size = 512K 
  56. group_concat_max_len = 1024000
  57. sort_buffer_size = 256K 
  58. read_buffer_size = 64K
  59. join_buffer_size = 128K 
  60. thread_cache_size = 256 
  61. ft_min_word_len = 4 
  62. default-storage-engine = INNODB
  63. thread_stack = 192K 
  64. transaction_isolation = REPEATABLE-READ
  65. tmp_table_size = 1024M 
  66. open_files_limit = 65536 
  67. key_buffer_size = 32M 
  68. bulk_insert_buffer_size = 64M 
  69. myisam_sort_buffer_size = 128M 
  70. myisam_max_sort_file_size = 1G 
  71. myisam_repair_threads = 1 
  72. max_allowed_packet = 64M
  73. max_prepared_stmt_count = 1000000
  74. #-----------------------------------------------------------------
  75. #replication
  76. server-id = 4545454545
  77. binlog_rows_query_log_events = on 
  78. log-slave-updates = 1
  79. relay-log = relay-bin
  80. #这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
  81. #auto_increment_offset = 1           
  82. #这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
  83. # auto_increment_increment = 1   
  84. #####################必须集群内互相兼容，建议全都保持一致并为 row
  85. binlog_format = row
  86. binlog_checksum = none 
  87. binlog_cache_size = 16M 
  88. max_binlog_cache_size = 2G
  89. sync_binlog = 1
  90. master_info_repository = TABLE
  91. relay_log_info_repository = TABLE
  92. relay_log_recovery = on
  93. sync_master_info = 10000
  94. sync_relay_log_info = 10000
  95. sync_relay_log = 0
  96. #slave_net_timeout    = 4
  97. #slave-parallel-type = LOGICAL_CLOCK
  98. #slave-parallel-workers = 16
  99. slave_pending_jobs_size_max = 134217728
  100. slave_preserve_commit_order = 0
  101. transaction_write_set_extraction = XXHASH64
  102. binlog_transaction_dependency_tracking = WRITESET
  103. #####################MySQL5.7.7之后，默认改成60秒。该参数定义了从库从主库获取数据等待的秒数，超过这个时间从库会主动退出读取，中断连接，并尝试重连。
  104. #slave_net_timeout=8
  105. ###############mysql版本在5.6之后才有该选项。
  106. gtid_mode = on
  107. ###############mysql版本在5.6之后才有该选项。
  108. enforce-gtid-consistency = on
  109. #
  110. ##############################################################################################半同步复制插件，需要安装。mysql5.7版本之后才有。
  111. ##########################################。安装插件在主库上：install plugin rpl_semi_sync_master soname 'semisync_master.so'; --安装 semisync_master.so插件 
  112. ##########################################。安装插件在从库上：install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; --安装 semisync_slave.so插件
  113. #plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
  114. ################################半同步复制插件，需要安装。mysql5.7版本之后才有。
  115. #rpl_semi_sync_master_enabled = on
  116. ################################半同步复制插件，需要安装。mysql5.7版本之后才有。
  117. #rpl_semi_sync_slave_enabled = on
  118. ################################半同步复制插件，需要安装。mysql5.7版本之后才有。
  119. #rpl_semi_sync_master_wait_for_slave_count = 1
  120. ################################半同步复制插件，需要安装。mysql5.7版本之后才有。
  121. #rpl_semi_sync_master_timeout = 1000
  122. ################################半同步复制插件，需要安装。mysql5.7版本之后才有。
  123. #rpl_semi_sync_master_wait_point = after_sync
  126. #undo
  127. innodb_max_undo_log_size = 1024M 
  128. innodb_undo_log_truncate = on 
  129. innodb_undo_logs = 128
  130. innodb_undo_tablespaces = 3
  131. innodb_purge_rseg_truncate_frequency = 128
  133. #innodb
  134. innodb_autoextend_increment = 64
  135. innodb_concurrency_tickets = 5000
  136. innodb_old_blocks_time = 1000
  137. innodb_purge_batch_size = 300
  138. innodb_stats_on_metadata = off
  139. innodb_thread_sleep_delay = 10000 
  140. innodb_adaptive_max_sleep_delay = 15000 
  141. innodb_buffer_pool_dump_at_shutdown = on
  142. innodb_buffer_pool_load_at_startup = on
  143. innodb_flush_neighbors = 1
  144. ############################nnoDB使用后台线程处理数据页上写 I/O（输入）请求的数量。
  145. innodb_write_io_threads = 8 
  146. ############################InnoDB使用后台线程处理数据页上读 I/O（输出）请求的数量。这里输出是输入的两倍。
  147. innodb_read_io_threads = 16
  148. innodb_print_all_deadlocks = on
  149. innodb_buffer_pool_size = 30G
  150. innodb_buffer_pool_instances = 8 
  151. innodb_data_file_path = ibdata1:100M:autoextend 
  152. innodb_thread_concurrency = 48 
  153. innodb_flush_log_at_trx_commit = 1
  154. innodb_log_buffer_size = 64M
  155. ############################该值表示每个redo log文件大小，该值大小约为该mysql实例一个小时产生的日志大小，这里设置为4G，ib_logfile0和ib_logfile1文件都是4G，逻辑上ib_logfile被当成了一个文件。循环写入。
  156. innodb_log_file_size = 4096M 
  157. #############################配置redo log成多个文件，Redo log文件以ib_logfile[number]命名，日志目录通过参数innodb_log_group_home_dir控制，以顺序的方式写入文件文件，写满时则回溯到第一个文件，进行覆盖写。
  158. innodb_log_files_in_group = 3 
  159. ###############################控制了 Dirty Page 在 Buffer Pool 中所占的比率。
  160. innodb_max_dirty_pages_pct = 75 
  161. innodb_max_dirty_pages_pct_lwm = 10 
  162. innodb_flush_method = O_DIRECT 
  163. innodb_lock_wait_timeout = 10 
  164. innodb_file_per_table = 1
  165. innodb_purge_threads = 4 
  166. #########################该参数表示缓冲区刷新到磁盘时，刷新脏页数量。普通单个SATA可以设置200，对于SSD可以设置3000到5000，而拥有5个磁盘组成的RAID5可以设置成2000。
  167. innodb_io_capacity = 5000
  168. innodb_open_files = 65535 
  169. innodb_online_alter_log_max_size = 5120M
  170. innodb_sort_buffer_size = 4M
  171. innodb_adaptive_hash_index_parts = 8 
  172. innodb_buffer_pool_chunk_size = 128 
  173. innodb_buffer_pool_dump_pct = 80 
  174. innodb_deadlock_detect = on 
  175. innodb_default_row_format = DYNAMIC 
  176. innodb_fill_factor = 100 
  177. innodb_flush_sync = off 
  178. innodb_log_checksums = on 
  179. innodb_log_write_ahead_size = 8192 
  180. innodb_max_undo_log_size = 1073741824 
  181. innodb_page_cleaners = 4 
  182. innodb_purge_rseg_truncate_frequency = 128 
  183. innodb_temp_data_file_path = ibtmp1:12M:autoextend 
  184. innodb_print_lock_wait_timeout_info = on
  187. [myisamchk]
  188. key_buffer_size = 512M
  189. sort_buffer_size = 512M
  190. read_buffer = 8M
  191. write_buffer = 8M
  193. [mysqld_safe]
  194. numa_interleave = 1
  196. [mysql]
  197. no-auto-rehash
  198. prompt = "\\u@\\h (\\d)> "

转载于:https://www.cnblogs.com/igoodful/p/11555230.html

发表评论

表情：
评论列表 （有 0 条评论，109人围观）

还没有评论，来说两句吧...

相关阅读