• MySQL8--my.cnf配置文件的设置


    原文网址:MySQL8--my.cfg配置文件的设置_IT利刃出鞘的博客-CSDN博客

    简介

    本文介绍MySQL8的my.cnf的配置。

    典型配置

    1. [client]
    2. default-character-set=utf8mb4
    3. [mysql]
    4. default-character-set=utf8mb4
    5. [mysqld]
    6. #服务端口号 默认3306
    7. port=3306
    8. datadir = /work/docker/mysql/data
    9. init_connect='SET NAMES utf8mb4'
    10. character-set-server=utf8mb4
    11. collation-server=utf8mb4_unicode_ci
    12. # 最大连接数
    13. max_connections=200
    14. # 连接失败的最大次数。防止有人从该主机试图攻击数据库系统
    15. max_connect_errors=20
    16. # 创建新表时将使用的默认存储引擎
    17. default-storage-engine=INNODB

    配置大全

    1. ###########################################################################
    2. ##客户端参数配置
    3. ###########################################################################
    4. [client]
    5. port = 3306
    6. socket =/var/lib/mysql/mysqld.sock
    7. [mysql]
    8. #prompt="\u@mysqldb \R:\m:\s [\d]> "
    9. #关闭自动补全sql命令功能
    10. no-auto-rehash
    11. ###########################################################################
    12. ##服务端参数配置
    13. ###########################################################################
    14. [mysqld]
    15. port = 3306
    16. datadir = /var/lib/mysql
    17. socket = /var/lib/mysql/mysqld.sock
    18. log-error = /var/lib/mysql/error.log
    19. pid-file = /var/lib/mysql/mysqld.pid
    20. #只能用IP地址检查客户端的登录,不用主机名
    21. skip_name_resolve = 1
    22. #时区设置
    23. default_time_zone = "+8:00"
    24. #数据库默认字符集, 主流字符集支持一些特殊表情符号(特殊表情符占用4个字节)
    25. character-set-server = utf8mb4
    26. #数据库字符集对应一些排序等规则,注意要和character-set-server对应
    27. collation-server = utf8mb4_general_ci
    28. #设置client连接mysql时的字符集,防止乱码
    29. init_connect='SET NAMES utf8mb4'
    30. #是否对sql语句大小写敏感,1表示不敏感
    31. lower_case_table_names = 1
    32. # 执行sql的模式,规定了sql的安全等级, 暂时屏蔽,my.cnf文件中配置报错
    33. #sql_mode = STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    34. #事务隔离级别,默认为可重复读,mysql默认可重复读级别(此级别下可能参数很多间隙锁,影响性能)
    35. transaction_isolation = READ-COMMITTED
    36. #TIMESTAMP如果没有显示声明NOT NULL,允许NULL值
    37. explicit_defaults_for_timestamp = true
    38. #它控制着mysqld进程能使用的最大文件描述(FD)符数量。
    39. #需要注意的是这个变量的值并不一定是你设定的值,mysqld会在系统允许的情况下尽量获取更多的FD数量
    40. open_files_limit = 65535
    41. #最大连接数
    42. max_connections = 300
    43. #最大错误连接数
    44. max_connect_errors = 600
    45. #在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中
    46. #官方建议 back_log = 50 + (max_connections / 5),封顶数为65535,默认值= max_connections
    47. back_log = 110
    48. # The number of open tables for all threads
    49. # For example, for 200 concurrent running connections, specify a table cache size of at least 200 * N,
    50. # where N is the maximum number of tables per join in any of the queries which you execute.
    51. table_open_cache = 600
    52. # The number of table definitions that can be stored in the definition cache
    53. # MIN(400 + table_open_cache / 2, 2000)
    54. table_definition_cache = 700
    55. # 为了减少会话之间的争用,可以将opentables缓存划分为table_open_cache/table_open_cache_instances个小缓存
    56. table_open_cache_instances = 64
    57. # 每个线程的堆栈大小 如果线程堆栈太小,则会限制执行复杂SQL语句
    58. thread_stack = 512K
    59. # 禁止外部系统锁
    60. external-locking = FALSE
    61. #SQL数据包发送的大小,如果有BLOB对象建议修改成1G
    62. max_allowed_packet = 128M
    63. #order by 或group by 时用到
    64. #建议先调整为4M,后期观察调整
    65. sort_buffer_size = 4M
    66. #inner left right join时用到
    67. #建议先调整为4M,后期观察调整
    68. join_buffer_size = 4M
    69. # How many threads the server should cache for reuse.
    70. # 如果您的服务器每秒达到数百个连接,则通常应将thread_cache_size设置得足够高,以便大多数新连接使用缓存线程
    71. # default value = 8 + ( max_connections / 100) 上限为100
    72. thread_cache_size = 20
    73. #MySQL连接闲置超过一定时间后(单位:秒)将会被强行关闭
    74. #MySQL默认的wait_timeout 值为8个小时, interactive_timeout参数需要同时配置才能生效
    75. interactive_timeout = 1800
    76. wait_timeout = 1800
    77. #Metadata Lock最大时长(秒), 一般用于控制 alter操作的最大时长sine mysql5.6
    78. #执行 DML操作时除了增加innodb事务锁外还增加Metadata Lock,其他alter(DDL)session将阻塞
    79. lock_wait_timeout = 3600
    80. #内部内存临时表的最大值。
    81. #比如大数据量的group by ,order by时可能用到临时表,
    82. #超过了这个值将写入磁盘,系统IO压力增大
    83. tmp_table_size = 64M
    84. max_heap_table_size = 64M
    85. ######################## 慢SQL日志记录 开始 ########################
    86. #是否启用慢查询日志,1为启用,0为禁用
    87. slow_query_log = 1
    88. #记录系统时区
    89. log_timestamps = SYSTEM
    90. #指定慢查询日志文件的路径和名字
    91. slow_query_log_file = /var/lib/mysql/slow.log
    92. #慢查询执行的秒数,必须达到此值可被记录
    93. long_query_time = 5
    94. #将没有使用索引的语句记录到慢查询日志
    95. log_queries_not_using_indexes = 0
    96. #设定每分钟记录到日志的未使用索引的语句数目,超过这个数目后只记录语句数量和花费的总时间
    97. log_throttle_queries_not_using_indexes = 60
    98. #对于查询扫描行数小于此参数的SQL,将不会记录到慢查询日志中
    99. min_examined_row_limit = 5000
    100. #记录执行缓慢的管理SQL,如alter table,analyze table, check table, create index, drop index, optimize table, repair table等。
    101. log_slow_admin_statements = 0
    102. #作为从库时生效, 从库复制中如何有慢sql也将被记录
    103. #对于ROW格式binlog,不管执行时间有没有超过阈值,都不会写入到从库的慢查询日志
    104. log_slow_slave_statements = 1
    105. ######################## 慢SQL日志记录 结束 ########################
    106. ######################## Bin-Log设置 开始 ########################
    107. server-id = 110
    108. #开启bin log 功能
    109. log-bin=mysql-bin
    110. #binlog 记录内容的方式,记录被操作的每一行
    111. binlog_format = ROW
    112. #对于binlog_format = ROW模式时,FULL模式可以用于误操作后的flashBack。
    113. #如果设置为MINIMAL,则会减少记录日志的内容,只记录受影响的列,但对于部分update无法flashBack
    114. binlog_row_image = FULL
    115. #bin log日志保存的天数
    116. #如果 binlog_expire_logs_seconds 选项也存在则 expire_logs_days 选项无效
    117. #expire_logs_days 已经被标注为过期参数
    118. #expire_logs_days = 7
    119. binlog_expire_logs_seconds = 1209600
    120. #master status and connection information输出到表mysql.slave_master_info中
    121. master_info_repository = TABLE
    122. #the slave's position in the relay logs输出到表mysql.slave_relay_log_info中
    123. relay_log_info_repository = TABLE
    124. #作为从库时生效, 想进行级联复制,则需要此参数
    125. log_slave_updates
    126. #作为从库时生效, 中继日志relay-log可以自我修复
    127. relay_log_recovery = 1
    128. #作为从库时生效, 主从复制时忽略的错误
    129. #如果在备份过程中执行ddl操作,从机需要从主机的备份恢复时可能会异常,从而导致从机同步数据失败
    130. #如果对数据完整性要求不是很严格,那么这个选项确实可以减轻维护的成本
    131. slave_skip_errors = ddl_exist_errors
    132. ######################## RedoLog日志 和 binlog日志的写磁盘频率设置 开始 ########################
    133. # RedoLog日志(用于增删改事务操作) + binlog日志(用于归档,主从复制)
    134. # 为什么会有两份日志呢?
    135. # 因为最开始MySQL没有 InnoDB 引擎,自带MyISAM引擎没有 crash-safe能力,binlog日志只用于归档
    136. # InnoDB 引擎是另一个公司以插件形式引入MySQL的,采用RedoLog日志来实现 crash-safe 能力
    137. # redo log 的写入(即事务操作)拆成两阶段提交(2PC):prepare阶段 和 commit阶段
    138. #(事务步骤1) 执行commit命令,InnoDB redo log 写盘,然后告知Mysql执行器:[你可以写binlog了,且一并提交事务],事务进入 prepare 状态
    139. #(事务步骤2) 如果前面 prepare 成功,Mysql执行器生成 binlog 并且将binlog日志写盘
    140. #(事务步骤3) 如果binlog写盘成功,Mysql执行器一并调用InnoDB引擎的提交事务接口,事务进入 commit 状态,操作完成,事务结束
    141. #参数设置成 1,每次事务都直接持久化到磁盘
    142. #参数设置成 0,mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
    143. #参数设置成 2,只有在操作系统崩溃或者系统掉电的情况下,上一秒钟所有事务数据才可能丢失。
    144. #即便都设置为1,服务崩溃或者服务器主机crash,Mysql也可能丢失但最多一个事务
    145. #控制 redolog 写磁盘频率 默认为1
    146. innodb_flush_log_at_trx_commit = 1
    147. #控制 binlog 写磁盘频率
    148. sync_binlog = 1
    149. ######################## RedoLog日志 和 binlog日志的写磁盘频率设置 结束 ########################
    150. #一般数据库中没什么大的事务,设成1~2M,默认32kb
    151. binlog_cache_size = 4M
    152. #binlog 能够使用的最大cache 内存大小
    153. max_binlog_cache_size = 2G
    154. #单个binlog 文件大小 默认值是1GB
    155. max_binlog_size = 1G
    156. #开启GTID复制模式
    157. gtid_mode = on
    158. #强制gtid一致性,开启后对于create table ... select ...或 CREATE TEMPORARY TABLE 将不被支持
    159. enforce_gtid_consistency = 1
    160. #解决部分无主键表导致的从库复制延迟问题
    161. #其基本思路是对于在一个ROWS EVENT中的所有前镜像收集起来,
    162. #然后在一次扫描全表时,判断HASH中的每一条记录进行更新
    163. #该参数已经被标注为过期参数
    164. #slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
    165. # default value is CRC32
    166. #binlog_checksum = 1
    167. # default value is ON
    168. #relay-log-purge = 1
    169. ######################## Bin-Log设置 结束 ########################
    170. ######################## MyISAM性能设置 开始 ########################
    171. #对MyISAM表起作用,但是内部的临时磁盘表是MyISAM表,也要使用该值。
    172. #可以使用检查状态值 created_tmp_disk_tables 得知详情
    173. key_buffer_size = 15M
    174. #对MyISAM表起作用,但是内部的临时磁盘表是MyISAM表,也要使用该值,
    175. #例如大表order by、缓存嵌套查询、大容量插入分区。
    176. read_buffer_size = 8M
    177. #对MyISAM表起作用 读取优化
    178. read_rnd_buffer_size = 4M
    179. #对MyISAM表起作用 插入优化
    180. bulk_insert_buffer_size = 64M
    181. ######################## MyISAM性能设置 结束 ########################
    182. ######################## Innodb性能设置 开始 ########################
    183. # Defines the maximum number of threads permitted inside of InnoDB.
    184. # A value of 0 (the default) is interpreted as infinite concurrency (no limit)
    185. innodb_thread_concurrency = 0
    186. #一般设置物理存储的 60% ~ 70%
    187. innodb_buffer_pool_size = 8G
    188. #当缓冲池大小大于1GB时,将innodb_buffer_pool_instances设置为大于1的值,可以提高繁忙服务器的可伸缩性
    189. innodb_buffer_pool_instances = 4
    190. #默认启用。指定在MySQL服务器启动时,InnoDB缓冲池通过加载之前保存的相同页面自动预热。 通常与innodb_buffer_pool_dump_at_shutdown结合使用
    191. innodb_buffer_pool_load_at_startup = 1
    192. #默认启用。指定在MySQL服务器关闭时是否记录在InnoDB缓冲池中缓存的页面,以便在下次重新启动时缩短预热过程
    193. innodb_buffer_pool_dump_at_shutdown = 1
    194. # Defines the name, size, and attributes of InnoDB system tablespace data files
    195. innodb_data_file_path = ibdata1:1G:autoextend
    196. #InnoDB用于写入磁盘日志文件的缓冲区大小(以字节为单位)。默认值为16MB
    197. innodb_log_buffer_size = 32M
    198. #InnoDB日志文件组数量
    199. innodb_log_files_in_group = 3
    200. #InnoDB日志文件组中每一个文件的大小
    201. innodb_log_file_size = 2G
    202. #是否开启在线回收(收缩)undo log日志文件,支持动态设置,默认开启
    203. innodb_undo_log_truncate = 1
    204. #当超过这个阀值(默认是1G),会触发truncate回收(收缩)动作,truncate后空间缩小到10M
    205. innodb_max_undo_log_size = 4G
    206. #The path where InnoDB creates undo tablespaces
    207. #没有配置则在数据文件目录下
    208. #innodb_undo_directory = /var/lib/mysql/undolog
    209. #用于设定创建的undo表空间的个数
    210. #已经弃用了,只能手动添加undo表空间
    211. #The innodb_undo_tablespaces variable is deprecated and is no longer configurable as of MySQL 8.0.14
    212. #innodb_undo_tablespaces = 95
    213. #提高刷新脏页数量和合并插入数量,改善磁盘I/O处理能力
    214. #根据您的服务器IOPS能力适当调整
    215. #一般配普通SSD盘的话,可以调整到 10000 - 20000
    216. #配置高端PCIe SSD卡的话,则可以调整的更高,比如 50000 - 80000
    217. innodb_io_capacity = 4000
    218. innodb_io_capacity_max = 8000
    219. #如果打开参数innodb_flush_sync, checkpoint时,flush操作将由page cleaner线程来完成,此时page cleaner会忽略io capacity的限制,进入激烈刷脏
    220. innodb_flush_sync = 0
    221. innodb_flush_neighbors = 0
    222. #CPU多核处理能力设置,假设CPU是4颗8核的,设置如下
    223. #读多,写少可以设成 2:6的比例
    224. innodb_write_io_threads = 8
    225. innodb_read_io_threads = 8
    226. innodb_purge_threads = 4
    227. innodb_page_cleaners = 4
    228. innodb_open_files = 65535
    229. innodb_max_dirty_pages_pct = 50
    230. #该参数针对unix、linux,window上直接注释该参数.默认值为 NULL
    231. #O_DIRECT减少操作系统级别VFS的缓存和Innodb本身的buffer缓存之间的冲突
    232. innodb_flush_method = O_DIRECT
    233. innodb_lru_scan_depth = 4000
    234. innodb_checksum_algorithm = crc32
    235. #为了获取被锁定的资源最大等待时间,默认50秒,超过该时间会报如下错误:
    236. # ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
    237. innodb_lock_wait_timeout = 20
    238. #默认OFF,如果事务因为加锁超时,会回滚上一条语句执行的操作。如果设置ON,则整个事务都会回滚
    239. innodb_rollback_on_timeout = 1
    240. #强所有发生的死锁错误信息记录到 error.log中,之前通过命令行只能查看最近一次死锁信息
    241. innodb_print_all_deadlocks = 1
    242. #在创建InnoDB索引时用于指定对数据排序的排序缓冲区的大小
    243. innodb_sort_buffer_size = 67108864
    244. #控制着在向有auto_increment 列的表插入数据时,相关锁的行为,默认为2
    245. #0:traditonal (每次都会产生表锁)
    246. #1:consecutive (mysql的默认模式,会产生一个轻量锁,simple insert会获得批量的锁,保证连续插入)
    247. #2:interleaved (不会锁表,来一个处理一个,并发最高)
    248. innodb_autoinc_lock_mode = 1
    249. #表示每个表都有自已独立的表空间
    250. innodb_file_per_table = 1
    251. #指定Online DDL执行期间产生临时日志文件的最大大小,单位字节,默认大小为128MB。
    252. #日志文件记录的是表在DDL期间的数据插入、更新和删除信息(DML操作),一旦日志文件超过该参数指定值时,
    253. #DDL执行就会失败并回滚所有未提交的当前DML操作,所以,当执行DDL期间有大量DML操作时可以提高该参数值,
    254. #但同时也会增加DDL执行完成时应用日志时锁定表的时间
    255. innodb_online_alter_log_max_size = 4G
    256. ######################## Innodb性能设置 结束 ########################
    257. [mysqldump]
    258. quick
    259. max_allowed_packet = 128M

  • 相关阅读:
    pytorch tensor数据类型转换为python数据
    边缘计算网关在智能工业园区中的的应用及其优势-天拓四方
    【C++模块实现】| 【06】日志模块添加循环覆盖写文件功能
    scrapy针对302请求的处理与重试配置
    基础设施即代码(IAC),Zalando Postgres Operator 简介
    QT---lineEdit相关信号
    利用反射动态构造wrapper条件( 利用反射获取注解值以及实体类值)
    10个Golang 数据库最佳实践
    2022华数杯A题 B题 C题 思路汇总
    数据库新技术【分布式数据库】
  • 原文地址:https://blog.csdn.net/feiying0canglang/article/details/127778371