MySQL配置文件my.ini详解

小鱼儿 2022-10-15 05:58 304阅读 0赞

文章目录

  • my.ini 是啥玩意？
  • my.ini 在哪放着呢？
  • my.ini的配置内容介绍：
    • 客户端的参数
    • 服务器断参数：
      • InnoDB存储引擎使用的参数：
  • 中文翻译版 my.ini

my.ini 是啥玩意？

my.ini是MySQL数据库中使用的配置文件，修改这个文件可以达到更新配置的目的。

my.ini 在哪放着呢？

my.ini存放在MySql安装的根目录，如图所示：（我比较懒，用的WAMP，大家找自己的安装目录即可）
在这里插入图片描述

my.ini的配置内容介绍：

其实大体可以分为两部分：客户端的参数、服务器参数。其中服务器参数里还包括 InnoDB存储引擎参数。

客户端的参数

下面显示的是客户端的参数，[client]和[mysql]都是客户端，下面是参数简介：

  1. port参数表示的是MySQL数据库的端口，默认的端口是3306，如果你需要更改端口号的话，就可以通过在这里修改。
  2. default-character-set参数是客户端默认的字符集，如果你希望它支持中文，可以设置成gbk或者utf8。

  3. 这里还有一个password参数，在这里设置了password参数的值就可以在登陆时不用输入密码直接进入

    CLIENT SECTION

    ———————————————————————————————————

    #

    The following options will be read by MySQL client applications.

    Note that only client applications shipped by MySQL are guaranteed

    to read this section. If you want your own MySQL client program to

    honor these values, you need to specify it as an option during the

    MySQL client library initialization.

    #
    [client]

    port=3306

    [mysql]

    default-character-set=gb2312

服务器断参数：

以下是参数的介绍：

  1. port参数也是表示数据库的端口。
  2. basedir参数表示MySQL的安装路径。
  3. datadir参数表示MySQL数据文件的存储位置，也是数据库表的存放位置。
  4. default-character-set参数表示默认的字符集，这个字符集是服务器端的。
  5. default-storage-engine参数默认的存储引擎。
    这里有两个引擎 MyISAM 和 InnoDB ，用什么看你需求，详细介绍可以参考下面这篇博文：https://yangyongli.blog.csdn.net/article/details/117213310
  6. sql-mode参数表示SQL模式的参数，通过这个参数可以设置检验SQL语句的严格程度。
  7. max_connections参数表示允许同时访问MySQL服务器的最大连接数，其中一个连接是保留的，留给管理员专用的。
  8. query_cache_size参数表示查询时的缓存大小，缓存中可以存储以前通过select语句查询过的信息，再次查询时就可以直接从缓存中拿出信息。
  9. table_cache参数表示所有进程打开表的总数。
  10. tmp_table_size参数表示内存中临时表的总数。
  11. thread_cache_size参数表示保留客户端线程的缓存。
  12. myisam_max_sort_file_size参数表示MySQL重建索引时所允许的最大临时文件的大小。
  13. myisam_sort_buffer_size参数表示重建索引时的缓存大小。
  14. key_buffer_size参数表示关键词的缓存大小。
  15. read_buffer_size参数表示MyISAM表全表扫描的缓存大小。
  16. read_rnd_buffer_size参数表示将排序好的数据存入该缓存中。

  17. sort_buffer_size参数表示用于排序的缓存大小

    SERVER SECTION

    ———————————————————————————————————

    #

    The following options will be read by the MySQL Server. Make sure that

    you have installed the server correctly (see above) so it reads this

    file.

    #
    [mysqld]

    The TCP/IP Port the MySQL Server will listen on

    port=3306

  1. #Path to installation directory. All paths are usually resolved relative to this.
  2. basedir="E:/Java/Mysql/"
  4. #Path to the database root
  5. datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
  7. # The default character set that will be used when a new schema or table is
  8. # created and no character set is defined
  9. character-set-server=gb2312
  11. # The default storage engine that will be used when create new tables when
  12. default-storage-engine=INNODB
  14. # Set the SQL mode to strict
  15. sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
  17. # The maximum amount of concurrent sessions the MySQL server will
  18. # allow. One of these connections will be reserved for a user with
  19. # SUPER privileges to allow the administrator to login even if the
  20. # connection limit has been reached.
  21. max_connections=100
  23. # Query cache is used to cache SELECT results and later return them
  24. # without actual executing the same query once again. Having the query
  25. # cache enabled may result in significant speed improvements, if your
  26. # have a lot of identical queries and rarely changing tables. See the
  27. # "Qcache_lowmem_prunes" status variable to check if the current value
  28. # is high enough for your load.
  29. # Note: In case your tables change very often or if your queries are
  30. # textually different every time, the query cache may result in a
  31. # slowdown instead of a performance improvement.
  32. query_cache_size=0
  34. # The number of open tables for all threads. Increasing this value
  35. # increases the number of file descriptors that mysqld requires.
  36. # Therefore you have to make sure to set the amount of open files
  37. # allowed to at least 4096 in the variable "open-files-limit" in
  38. # section [mysqld_safe]
  39. table_cache=256
  41. # Maximum size for internal (in-memory) temporary tables. If a table
  42. # grows larger than this value, it is automatically converted to disk
  43. # based table This limitation is for a single table. There can be many
  44. # of them.
  45. tmp_table_size=35M
  48. # How many threads we should keep in a cache for reuse. When a client
  49. # disconnects, the client's threads are put in the cache if there aren't
  50. # more than thread_cache_size threads from before. This greatly reduces
  51. # the amount of thread creations needed if you have a lot of new
  52. # connections. (Normally this doesn't give a notable performance
  53. # improvement if you have a good thread implementation.)
  54. thread_cache_size=8
  56. #*** MyISAM Specific options
  58. # The maximum size of the temporary file MySQL is allowed to use while
  59. # recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.
  60. # If the file-size would be bigger than this, the index will be created
  61. # through the key cache (which is slower).
  62. myisam_max_sort_file_size=100G
  64. # If the temporary file used for fast index creation would be bigger
  65. # than using the key cache by the amount specified here, then prefer the
  66. # key cache method. This is mainly used to force long character keys in
  67. # large tables to use the slower key cache method to create the index.
  68. myisam_sort_buffer_size=69M
  70. # Size of the Key Buffer, used to cache index blocks for MyISAM tables.
  71. # Do not set it larger than 30% of your available memory, as some memory
  72. # is also required by the OS to cache rows. Even if you're not using
  73. # MyISAM tables, you should still set it to 8-64M as it will also be
  74. # used for internal temporary disk tables.
  75. key_buffer_size=55M
  77. # Size of the buffer used for doing full table scans of MyISAM tables.
  78. # Allocated per thread, if a full scan is needed.
  79. read_buffer_size=64K
  80. read_rnd_buffer_size=256K
  82. # This buffer is allocated when MySQL needs to rebuild the index in
  83. # REPAIR, OPTIMZE, ALTER table statements as well as in LOAD DATA INFILE
  84. # into an empty table. It is allocated per thread so be careful with
  85. # large settings.
  86. sort_buffer_size=256K

InnoDB存储引擎使用的参数：

以下是参数的简介：

  1. innodb_additional_mem_pool_size参数表示附加的内存池，用来存储InnoDB表的内容。
  2. innodb_flush_log_at_trx_commit参数是设置提交日志的时机，若设置为1，InnoDB会在每次提交后将事务日志写到磁盘上。
  3. innodb_log_buffer_size参数表示用来存储日志数据的缓存区的大小。
  4. innodb_buffer_pool_size参数表示缓存的大小，InnoDB使用一个缓冲池类保存索引和原始数据。
  5. innodb_log_file_size参数表示日志文件的大小。

  6. innodb_thread_concurrency参数表示在InnoDB存储引擎允许的线程最大数。

    INNODB Specific options 

  1. # Use this option if you have a MySQL server with InnoDB support enabled
  2. # but you do not plan to use it. This will save memory and disk space
  3. # and speed up some things.
  4. #skip-innodb
  6. # Additional memory pool that is used by InnoDB to store metadata
  7. # information. If InnoDB requires more memory for this purpose it will
  8. # start to allocate it from the OS. As this is fast enough on most
  9. # recent operating systems, you normally do not need to change this
  10. # value. SHOW INNODB STATUS will display the current amount used.
  11. innodb_additional_mem_pool_size=3M
  13. # If set to 1, InnoDB will flush (fsync) the transaction logs to the
  14. # disk at each commit, which offers full ACID behavior. If you are
  15. # willing to compromise this safety, and you are running small
  16. # transactions, you may set this to 0 or 2 to reduce disk I/O to the
  17. # logs. Value 0 means that the log is only written to the log file and
  18. # the log file flushed to disk approximately once per second. Value 2
  19. # means the log is written to the log file at each commit, but the log
  20. # file is only flushed to disk approximately once per second.
  21. innodb_flush_log_at_trx_commit=1
  23. # The size of the buffer InnoDB uses for buffering log data. As soon as
  24. # it is full, InnoDB will have to flush it to disk. As it is flushed
  25. # once per second anyway, it does not make sense to have it very large
  26. # (even with long transactions).
  27. innodb_log_buffer_size=2M
  29. # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
  30. # row data. The bigger you set this the less disk I/O is needed to
  31. # access data in tables. On a dedicated database server you may set this
  32. # parameter up to 80% of the machine physical memory size. Do not set it
  33. # too large, though, because competition of the physical memory may
  34. # cause paging in the operating system. Note that on 32bit systems you
  35. # might be limited to 2-3.5G of user level memory per process, so do not
  36. # set it too high.
  37. innodb_buffer_pool_size=107M
  39. # Size of each log file in a log group. You should set the combined size
  40. # of log files to about 25%-100% of your buffer pool size to avoid
  41. # unneeded buffer pool flush activity on log file overwrite. However,
  42. # note that a larger logfile size will increase the time needed for the
  43. # recovery process.
  44. innodb_log_file_size=54M
  46. # Number of threads allowed inside the InnoDB kernel. The optimal value
  47. # depends highly on the application, hardware as well as the OS
  48. # scheduler properties. A too high value may lead to thread thrashing.
  49. innodb_thread_concurrency=18

中文翻译版 my.ini

  1. [client]  
  2. port=3306  
  4. [mysql]  
  5. default-character-set=gbk  
  7. [mysqld]
  8. port = 3306
  9. socket = /tmp/mysql.sock
  10. # 设置mysql的安装目录
  11. basedir=F:\\Hzq Soft\\MySql Server 51GA
  12. # 设置mysql数据库的数据的存放目录，必须是data，或者是\\xxx-data
  13. datadir=F:\\Hzq Soft\\MySql Server 51GA\\data
  14. #innodb_log_arch_dir 默认datadir
  15. #innodb_log_group_home_dir 默认datadir
  16. # 设置mysql服务器的字符集，默认编码
  17. default-character-set=utf8
  19. #连接数的操作系统监听队列数量，如果经常出现“拒绝连接”错误可适当增加此值
  20. back_log = 50
  21. #不使用接听TCP / IP端口方法，mysqld通过命名管道连接
  22. #skip-networking
  23. # 最大连接数量
  24. max_connections = 90
  25. #打开表的线程数量限定，最大4096，除非用mysqld_safe打开限制
  26. table_open_cache = 2048
  27. #MySql 服务接收针对每个进程最大查询包大小
  28. max_allowed_packet = 16M
  29. #作用于SQL查询单笔处理使用的内存缓存，如果一笔操作的二进制数据超过了限定大小，将会在磁盘上开辟空间处理，一般设为 1-2M即可，默认1M
  30. binlog_cache_size = 2M
  31. #单个内存表的最大值限定
  32. max_heap_table_size = 64M
  33. #为每个线程分配的排序缓冲大小
  34. sort_buffer_size = 8M
  35. #join 连表操作的缓冲大小,根据实际业务来设置，默认8M
  36. join_buffer_size = 32M
  37. #操作多少个离开连接的线程的缓存
  38. thread_cache_size = 8
  39. #并发线程数量，默认为8，可适当增加到2倍以内。如果有多个CPU可以乘 上CPU的数量。双核CPU可以乘 上当前最核数再乘 上70%-85%
  40. thread_concurrency = 16
  41. #专用于具体SQL的缓存，如果提交的查询与几次中的某查询相同，并且在query缓存中存在，则直接返回缓存中的结果。
  42. query_cache_size = 64M
  43. #对应上一条设置，当查询的结果超过下面设置的大小时，将不会趣入到上面设置的缓存区中，避免了一个大的结果占据大量缓存。
  44. query_cache_limit = 2M
  45. #设置加全文检索中的最小单词长度。
  46. #ft_min_word_len = 4
  47. #CREATE TABLE 语句的默认表类型，如果不自己指定类型，则使用下行的类型
  48. default-storage-engine = InnoDB
  49. #线程堆栈大小，mysql说它自己用的堆栈大小不超过64K。这个值可适当设高一点（在RCA的项目中都是共用同一个数据库连接的），默认192K
  50. thread_stack = 800K
  51. #设置事务处理的级别,默认 REPEATABLE-READ，一般用它就即可，以下二行按顺序对应，
  52. #可读写未提交的数据，创建未提交的数据副本读写，未提交之前可读不可写，只允许串行序列招行事务。
  53. # READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
  54. transaction_isolation = REPEATABLE-READ
  55. #单一内存临时表在内存中的大小，超过此值自动转换到磁盘操作
  56. tmp_table_size = 64M
  57. #启动二进制日志功能，可通过它实现时间点恢复最新的备份
  58. #log-bin=mysql-bin
  59. #二进制日志格式，对就上一条，-建议混合格式
  60. #binlog_format=mixed
  61. #转换查询为缓慢查询
  62. slow_query_log
  63. #对应上一条，如果一个查询超过了下条设定的时间则执行上一条。
  64. long_query_time = 2
  65. #自定义主机ID识别符，用于主从或多服务器之间识别，为 一个 int 类型
  66. server-id = 1
  67. #一般用来缓存MyISAM表的主键，也用于临时的磁盘表缓存主键，上面多次出现临时磁盘表，所以就算不用MyISAM也最好为其设置一个不小的值，默认32M
  68. key_buffer_size = 56M
  69. #全表扫描MyISAM表时的缓存，每个线程拥有下行的大小。
  70. read_buffer_size = 2M
  71. #排序操作时与磁盘之间的缓存，分到每个线程，默认16M
  72. read_rnd_buffer_size = 16M
  73. #MyISAM使用特殊树形进行批量插入时的缓存，如insert ... values(..)(..)(..)
  74. bulk_insert_buffer_size = 64M
  75. #MyISAM索引文件的最大限定，
  76. myisam_max_sort_file_size = 12G
  77. #如果一个myisam表有一个以上的索引， MyISAM可以使用一个以上线程来排序并行它们。较耗硬件资源，如果你的环境不错，可以增加此值。
  78. myisam_repair_threads = 2
  79. #自动检查和修复无法正确关闭MyISAM表。
  80. myisam_recover
  81. # *** INNODB Specific options ***
  82. #开启下条将会禁用 INNODB
  83. #skip-innodb
  84. #一般不用设置或者说设了也没多大用，InnoDB会自己与操作系统交互管理其附加内存池所使用InnoDB的存储数据的大小
  85. innodb_additional_mem_pool_size = 16M
  86. #innodb整体缓冲池大小，不宜过大，设为本地内存的 50%-75% 比较合适,在本机开发过程中可以设得较小一点如 64M,256M
  87. innodb_buffer_pool_size = 256M
  88. #InnoDB的数据存储在一个或多个数据文件组成的表空间
  89. innodb_data_file_path = ibdata1:10M:autoextend
  90. #用于异步IO操作的线程数量，默认为 4 ，可适当提高
  91. innodb_file_io_threads = 8
  92. #线程数内允许的InnoDB内核,不宜太高
  93. innodb_thread_concurrency = 14
  94. #InnoDB的事务日志快存行为,默认为 1，为0可减轻磁盘I/0操作，还有以为2
  95. innodb_flush_log_at_trx_commit = 1
  96. #InnoDB的用于的缓冲日志数据的大小
  97. innodb_log_buffer_size = 16M
  98. #日志文件，可设置为25%-90%的总体缓存大小，默认 256M. 修改此项要先删除datadir\ib_logfileXXX
  99. innodb_log_file_size = 256M
  100. #日志组数量，默认为3
  101. innodb_log_files_in_group = 3
  102. #InnoDB的日志文件位置。默认是MySQL的datadir
  103. #innodb_log_group_home_dir
  104. #InnoDB最大允许的脏页缓冲池的百分比，默认90
  105. innodb_max_dirty_pages_pct = 90
  106. #事务死锁超时设定
  107. innodb_lock_wait_timeout = 120
  109. [client]
  110. port = 3306
  111. socket = /tmp/mysql.sock
  112. # 设置mysql客户端的字符集
  113. default-character-set=utf8
  115. [mysqldump]
  116. quick
  117. max_allowed_packet = 16M
  119. [mysql]
  120. no-auto-rehash
  122. # Only allow UPDATEs and DELETEs that use keys.
  123. #safe-updates
  125. [WinMySQLAdmin]
  126. # 指定mysql服务启动启动的文件
  127. Server=F:\\myweb\\MySql Server\\bin\\mysqld.exe

发表评论

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

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

相关阅读

    相关 mysql8myini

    mysql my.ini在哪里 linux系统下使用下面命令可以看到。Windows下面类似。用：mysql --help | grep my.ini➜ ~ mysql

    傷城~傷城~/ 0 赞/ 116 阅读

    相关 mysql8026myini

    如何开单机征途 安装MYSQL4.1数据库 1.安装向导欢迎界面 2、选择安装类型 Typical(典型)、Complete(完全)、Custom(自定义),选择“

    迷南。迷南。/ 0 赞/ 186 阅读