- 1 #BEGIN CONFIG INFO
- 2 #DESCR: 4GB RAM, InnoDB only, ACID, few connections, heavy queries
- 3 #TYPE: SYSTEM
- 4 #END CONFIG INFO
- 5
- 6 #注:这个用于4G的内存,并且只能用于InnoDB模式,支持事务的ACID,很少的连接数,很大的查询数
- 7
- 8
- 9 #
- 10# This is a MySQL example configfile for systems with 4GB of memory
- 11 # running mostly MySQL using InnoDB only tables and performing complex
- 12 # queries with few connections.
- 13 #
- 14
- 15 #注:这是MySQL的一个样例的配置文件,要求系统有至少4G的内存,运行MySQL使用InnoDB模式运行复杂的连接但是连接数较少
- 16
- 17# MySQL programs lookforoption filesin a set of
- 18# locationswhich depend on the deployment platform.
- 19# You can copy this optionfile to one of those
- 20 # locations. For information about these locations, see:
- 21# http://dev.mysql.com/doc/mysql/en/option-files.html
- 22
- 23 #注:MySQL程序将会寻找参数(配置)文件在一系列的地方,这个依赖MySQL的运行的平台和安装情况,你可以复制这些文件
- 24#到MySQL查找配置的文件的路径。如果你想知道更多的信息,可以参考: http://dev.mysql.com/doc/mysql/en/option-files.html
- 25
- 26# In thisfile, you can use alllong options that a program supports.
- 27# If you want to knowwhich options a program supports, run the program
- 28# with the"--help" option.
- 29 #
- 30
- 31 #注:在这个配置文件中,你可以使用所有被MySQL程序支持的长参数,如果你想知道有那些参数被MySQL支持,你可以使用如下命令
- 32# mysql --help 或者manmysql-5.1.72/support-files/my-innodb-heavy-4G.cnf
- 33
- 34 # More detailed information about the individual options can also be
- 35# foundin the manual.
- 36 #
- 37
- 38 #注:更多详细的信息关于个别的参数可以参考参考手册
- 39
- 40 #
- 41 # The following options will be read by MySQL client applications.
- 42 # Note that only client applications shipped by MySQL are guaranteed
- 43 # to read this section. If you want your own MySQL client program to
- 44 # honor these values, you need to specify it as an option during the
- 45 # MySQL client library initialization.
- 46 #
- 47
- 48 #注:接下来的参数将会被MySQL客户端应用读取。记住只有那些被MySQL服务端授权的客户端才会保证去读取到这些参数
- 49 #如果你想让你自己的MySQL客户端去读取这些参数,你需要指定这个作为一个选项在你的MySQL客户端lib库初始化的时候
- 50
- 51 [client] #客户端参数配置
- 52#password = [your_password] #你自己的密码
- 53port =3306 #数据库的端口为3306
- 54socket = /application/mysql5.1.72/tmp/mysql.sock #指定socket路径,我也不知道什么意思
- 55
- 56# *** Application-specific options follow here *** 57
- 58 #注下面是MySQL服务端参数配置
- 59
- 60 #
- 61 # The MySQL server
- 62 #
- 63 [mysqld]
- 64
- 65 # generic configuration options
- 66port =3306 #数据库服务端端口为3306
- 67socket = /application/mysql5.1.72/tmp/mysql.sock #指定socket路径
- 68
- 69# back_log is the number of connections the operating system can keepin
- 70 # the listen queue, before the MySQL connection manager thread has
- 71 # processed them. If you have a very high connection rate and experience
- 72#"connection refused" errors, you might need to increase this value.
- 73# Check your OS documentationfor the maximum value of this parameter.
- 74 # Attempting to set back_log higher than your operating system limit
- 75 # will have no effect.
- 76back_log =50
- 77
- 78 #注:back_log 是一个数值,在数据库连接管理器处理这些队列之前,操作系统可以保持监听队列的数量。
- 79 #如果你的数据库服务有一个很高的连接数并且有连接拒绝的经历,你可以增大这个值。你需要检查你的操作系统
- 80 #的文件系统对这个参数的最大支持。如果你设定的值超过操作系统的最大值,那么这个值将无效
- 81
- 82
- 83# Don't listen on a TCP/IP port at all. This can be a security
- 84# enhancement,if all processes that need to connect to mysqld run
- 85 # on the same host. All interaction with mysqld must be made via Unix
- 86 # sockets or named pipes.
- 87 # Note that using this option without enabling named pipes on Windows
- 88# (via the"enable-named-pipe"option) will render mysqld useless! 89#skip-networking
- 90
- 91#注:skip-networking 这个参数是一个属性,没有值。把注解去掉即生效。这个参数的用途为:
- 92#不监听TCP/IP的端口。这是一个增加安全的参数,如果所有的连接MySQL的程序都来自相同的主机。
- 93 #这样,所有与MySQL服务器的连接都需要在通道或者管道中进行
- 94 #记住:使用这个参数不能使用管道早Windows中
- 95
- 96
- 97 # The maximum amount of concurrent sessions the MySQL server will
- 98# allow. One of these connections will be reservedfor a user with
- 99# SUPER privileges to allow the administrator tologinevenif the
- 100 # connection limit has been reached.
- 101max_connections =100
- 102
- 103 #注:MySQL允许的最大连接数。在这些连接中,始终会为超级管理员保留一个连接,即使所有的连接已经达到上限
- 104
- 105 # Maximum amount of errors allowed per host. If this limit is reached,
- 106# the host will be blocked from connecting to the MySQL serveruntil
- 107#"FLUSH HOSTS" has been run or the server was restarted. Invalid
- 108# passwords and other errors during the connect phase resultin
- 109# increasing this value. See the"Aborted_connects"status variablefor
- 110 # global counter.
- 111max_connect_errors =10
- 112
- 113 #注:每个客户端运行的最大的错误数。如果错误的数量到达上限,这个客户端将会被阻塞去连接数据库直到刷新客户端
- 114 #或者重新启动服务器。无效的密码或者其他的连接错误都算作错误,会增加这个值。可以参考Aborted_connects的状态
- 115 #来查看全局的计数器
- 116
- 117# The number of open tablesfor all threads. Increasing this value
- 118# increases the number offile descriptors that mysqld requires.
- 119# Therefore you have tomake sure to set the amount of open files
- 120# allowed to at least4096 inthe variable"open-files-limit" in
- 121 # section [mysqld_safe]
- 122table_open_cache =2048
- 123
- 124 #注:这个值代表着所有对外开放的数据表的数量。增加这个值将会增加MySQL所需要的文件描述符
- 125#因此你需要去设置"open-files-limit"的值至少为4096在MySQL安全的配置文件中,你可以看到这个值在本文的最后
- 126
- 127# Enable externalfilelevel locking. Enabledfile locking will have a
- 128# negative impact on performance, so only use itin case you have
- 129 # multiple database instances running on the same files (note some
- 130# restrictions still apply!) orif you use other software relying on
- 131# locking MyISAM tables onfile level.
- 132#external-locking
- 133
- 134#注:external-locking为外部文件锁的意思,可用的外部文件锁。如果使用外部文件锁将会对MySQL的性能有消极影响。
- 135 #因此使用外部文件锁当你有多重的数据库实例运行在相同的文件中(记住一些限制任然是有用的)或者可用使用一些依赖MyISAM的外部锁软件
- 136
- 137
- 138 # The maximum size of a query packet the server can handle as well as
- 139 # maximum query size server can process (Important when working with
- 140# large BLOBs). enlarged dynamically,for each connection.
- 141max_allowed_packet = 16M
- 142
- 143 #注:MySQL查询包的最大值或者是处理查询的最大值,(这个属性非常重要,如果你操作很大的二进制对象的数据的时候)
- 144 #这个值可以对每个线程进行动态的扩大
- 145
- 146# The size of the cache to hold the SQL statementsfor the binary log
- 147# during a transaction. If you often use big, multi-statement
- 148# transactions you can increase this value to getmore performance. All
- 149# statements from transactions are bufferedin the binary log cache and
- 150 # are being written to the binary log at once after the COMMIT. If the
- 151# transaction is larger than this value, temporaryfile on disk is used
- 152 # instead. This buffer is allocated per connection on first update
- 153# statementin transaction
- 154binlog_cache_size = 1M
- 155
- 156 #注:binlog_cache_size 是一个缓存大小的值,这个是用于存放在事务中,用于存储SQL statement的日志
- 157 #如果你经常使用大的、多statement的事务,你可以增加这个值去获取更多的性能。
- 158 #所有和事务相关的statement都会以二进制的形式写入该缓冲区,并且都会立刻写入二进制日志在事务提交以后
- 159 #如果事务的数据量超过这个值,在磁盘上就会建立临时文件来使用。
- 160 #这个缓冲区将会开启在每个连接第一次进行update操作的时候
- 161
- 162
- 163
- 164# Maximum allowed sizefora single HEAP (in memory) table. This option
- 165 # is a protection against the accidential creation of a very large HEAP
- 166# tablewhich could otherwise use up all memory resources.
- 167max_heap_table_size = 64M
- 168
- 169 #注:max_heap_table_size是一个最大限制值,限制一个单独的数据表在内存里面的堆积的最大值
- 170 #有了此限制,就不会让表数据把内存堆满
- 171
- 172# Size of the buffer usedfor doing full table scans.
- 173# Allocated per thread,if a full scan is needed.
- 174read_buffer_size = 2M
- 175
- 176 #read_buffer_size 是一个数据表视图全部显示的缓存大小,允许每个线程去使用此缓存
- 177
- 178# When reading rowsinsorted order after asort, the rows are read
- 179 # through this buffer to avoid disk seeks. You can improve ORDER BY
- 180# performance a lot,if set this to a high value.
- 181 # Allocated per thread, when needed.
- 182read_rnd_buffer_size = 16M
- 183
- 184 #read_rnd_buffer_size 是一个按顺序读取的缓存大小,在读取数据过程中,我们可能先对数据排序,排序好的数据会先放在此缓冲区中。
- 185 #然后在进行读取,避免在一次进行磁盘查找。增加这个值可以提升 ORDER BY 语句的性能。允许每个线程使用
- 186
- 187# Sort buffer is used to perform sortsfor some ORDER BY and GROUP BY
- 188# queries. If sorted data does not fit into thesort buffer, a disk
- 189# based mergesortis used instead - See the"Sort_merge_passes"
- 190# status variable. Allocated per threadif sort is needed.
- 191sort_buffer_size = 8M
- 192
- 193#sort_buffer_size 是分类或者排序缓存的大小,用于去执行分类/排序语句,如 ORDER BY and GROUP BY
- 194 #如果实际的数据过大超过这个缓冲区,那么基于磁盘的分类合并将会被使用。
- 195 #允许每个线程
- 196
- 197# This buffer is usedfor the optimization of full JOINs (JOINs without
- 198# indexes). Such JOINs are very badforperformancein most cases
- 199 # anyway, but setting this variable to a large value reduces the
- 200# performance impact. See the"Select_full_join"status variablefor a
- 201# count of full JOINs. Allocated per threadiffulljoin is found
- 202join_buffer_size = 8M
- 203
- 204 #join_buffer_size 是一个内连接的缓存,用于优化内连接操作。我们知道,内连接对性能是有非常坏的影响的
- 205#但是设置内连接缓存将会减少内连接对性能的消极影响。查看"Select_full_join"可以知道有多少内连接操作
- 206
- 207# How many threads we should keepina cachefor reuse. When a client
- 208# disconnects, the client's threads are put in the cache if there aren't
- 209#more than thread_cache_size threads from before. This greatly reduces
- 210# the amount of thread creations neededif you have a lot of new
- 211# connections. (Normally this doesn't give a notable performance
- 212# improvementif you have a good thread implementation.)
- 213thread_cache_size =8
- 214
- 215 #注:thread_cache_size是数据库服务端的连接线程在线程池中保持活跃的上限。当客户端不连接数据库的时候
- 216#这个客户端的连接线程将会保持在线程池里面如果线程中的总线程数量不超过"thread_cache_size"给定的值
- 217 #这个将会减少线程的创建当有很多新的连接突然来到的时候。(正常情况下,这个参数将不会给数据库性能带来不稳定的提升
- 218 #如果你有一个很好的线程实现)
- 219
- 220# This permits the application to give the threads system a hintfor the
- 221# desired number of threads that should be run at the sametime. This
- 222 # value only makes sense on systems that support the thread_concurrency()
- 223#functioncall (Sun Solaris,for example).
- 224# You should try [number of CPUs]*(2..4)for thread_concurrency
- 225thread_concurrency =8
- 226
- 227 #注:thread_concurrency 是一个线程并发的数量的参考值。这个应用去给线程系统一个暗示关于希望多少个线程可以使用
- 228 #在相同的时间内。这个值只是做一个参考在支持thread_concurrency()在(Sun Solaris 的操作系统中)
- 229 #说白了就是我们CPU的线程数,如2线程,4线程
- 230
- 231 # Query cache is used to cache SELECT results and later return them
- 232 # without actual executing the same query once again. Having the query
- 233# cache enabled may resultinsignificant speed improvements,if your
- 234 # have a lot of identical queries and rarely changing tables. See the
- 235#"Qcache_lowmem_prunes"status variable to checkif the current value
- 236# is high enoughfor your load.
- 237# Note: Incaseyour tables change very often orif your queries are
- 238# textually different everytime, the query cache may resultin a
- 239 # slowdown instead of a performance improvement.
- 240query_cache_size = 64M
- 241
- 242 #注:query_cache_size 是查询缓存的大小。如果你执行一次查询,数据库会把查询的结果放入缓存中
- 243 #如果你以后执行了同以前相同的查询,那么数据库会直接从缓存中返回数据(前提是有以前的缓存数据)
- 244 #如果你经常查询同一张表或者经常查询相同的大数据,这个缓存将会帮你提升性能
- 245 #如果你的查询是多变的,并且每次都不一样,那么这个缓冲区可能会降低你的性能
- 246
- 247 # Only cache result sets that are smaller than this limit. This is to
- 248 # protect the query cache of a very large result set overwriting all
- 249 # other query results.
- 250query_cache_limit = 2M
- 251
- 252 #注:query_cache_limit 查询缓存的最小值,只有当 query_cache_size 的设置小于 query_cache_limit
- 253 #才会生效。设置这个值是为了避免查询缓存过小,而一个大数据的查询会把所有的查询缓存覆盖
- 254
- 255 # Minimum word length to be indexed by the full text search index.
- 256# You might wish to decrease itifyou need to searchfor shorter words.
- 257 # Note that you need to rebuild your FULLTEXT index, after you have
- 258 # modified this value.
- 259ft_min_word_len =4
- 260
- 261 #ft_min_word_len 是被编入数据库索引单词的最小长度。你可能想去减少这个长度如果你想查询更短的单词的时候
- 262 #那你就需要重新编译 FULLTEXT 在你修改这个值之后
- 263
- 264# If your system supports the memlock()function call, you might want to
- 265# enable this optionwhilerunning MySQL to keep it lockedin memory and
- 266# to avoid potential swapping outin case of high memory pressure. Good
- 267#for performance.
- 268 #memlock
- 269
- 270 #memlock 是内存锁的参数。所谓的内存锁就是在运行MySQL的时候指定就在某个内存范围内运行
- 271 #这样就避免高端内存的交换,对性能有提升。但是这是参数需要操作系统的支持,默认关闭此参数
- 272
- 273# Table typewhichis used by default when creating new tables,if not
- 274 # specified differently during the CREATE TABLE statement.
- 275default-storage-engine = MYISAM
- 276
- 277#注: default-storage-engine,默认的存储引擎参数,在MySQL中,mysql中用的最多存储引擎就是innodb和 myisam
- 278 #这里使用的默认存在引擎为myisam
- 279
- 280 # Thread stack size to use. This amount of memory is always reserved at
- 281# connectiontime. MySQL itself usually needs nomore than 64K of
- 282# memory,while if you use your own stack hungry UDF functions or your
- 283# OS requiresmorestackfor some operations, you might need to set this
- 284 # to a higher value.
- 285thread_stack = 192K
- 286
- 287 #注:thread_stack线程堆栈的大小。这个值将会被保留在内存中当在连接的时候,MySQL自身需要的线程
- 288 #堆栈不超过64K,然而当你自己的定义的UDF(User define Function 用户自定义函数)或者是你的操作
- 289 #系统需要更多的堆栈对一些操作,你可能需要去提高这个值
- 290
- 291 # Set the default transaction isolation level. Levels available are:
- 292# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
- 293transaction_isolation = REPEATABLE-READ
- 294
- 295#注:transaction_isolation 设置MySQL事务的隔离级别,这里设置的是REPEATABLE-READ(可重复的读)
- 296#此外MySQL还有额外的事务隔离机制 READ-UNCOMMITTED(脏读) READ-COMMITTED(读已提交) SERIALIZABLE(串行化)
- 297
- 298# Maximum sizeforinternal (in-memory) temporary tables. If a table
- 299 # grows larger than this value, it is automatically converted to disk
- 300# based table This limitation isfor a single table. There can be many
- 301 # of them.
- 302tmp_table_size = 64M
- 303
- 304
- 305 #注 tmp_table_size 是限制一直数据表临时在内存中的最大存储容量,如果超过了这个值,那么将自动的转为磁盘存储
- 306 #这个限制只作用于一个单独的表。
- 307
- 308# Enable binary logging. This is requiredforacting as a MASTERin a
- 309# replication configuration. You also need the binary logif you need
- 310# the ability todopointin time recovery from your latest backup.
- 311log-bin=mysql-bin
- 312
- 313#注:log-bin是二进制的日志文件。二进制的日志文件被需要当管理员去恢复数据库的配置的时候。
- 314 #你会需要二进制日志当你需要恢复数据库从最近的备份
- 315
- 316# binary logging format - mixed recommended
- 317binlog_format=mixed
- 318
- 319 # binlog_format 设置上面二进制的日志格式,mixed 格式被推荐使用
- 320
- 321# If you're using replication with chained slaves (A->B->C), you need to
- 322# enable this option on server B. It enables logging of updatesdone by
- 323# the slave thread into the slave's binary log.
- 324 #log_slave_updates
- 325
- 326#注:log_slave_updates 关系型日志更新,如果你使用这样的关系链如(A->B->C),那么你需要把这个参数打开在B机器上
- 327 #他可以记录关联机器的(A和C)更新的日志
- 328
- 329 # Enable the full query log. Every query (even ones with incorrect
- 330# syntax) that the server receives will be logged. This is usefulfor
- 331# debugging, it is usually disabledin production use.
- 332 #log
- 333
- 334# Print warnings to the error logfile. If you have any problem with
- 335 # MySQL you should enable logging of warnings and examine the error log
- 336#for possible explanations.
- 337 #log_warnings
- 338
- 339 #注:log_warnings 是打印警告对于错误的日志文件,默认被注释掉(关闭的)。如果你任何的问题
- 340 #对于MySQL,你应当开启错误日志警告并且测试错误日志得到相应的解释
- 341
- 342# Log slow queries. Slow queries are querieswhichtakemore than the
- 343# amount oftimedefinedin "long_query_time"orwhich do not use
- 344# indexes well,if log_short_format is not enabled. It is normally good idea
- 345# to have this turned onif you frequently add new queries to the
- 346 # system.
- 347 slow_query_log
- 348
- 349#注:slow_query_log 是开启查询缓慢日志。查询缓慢是那些查询花费的时间超过定义的"long_query_time" 的查询或者是没有使用索引的查询将会被写如日志
- 350#如果"log_short_format" 没有开启,正常情况下是非常好的去打开这个控制如果你频繁的增加新的的查询
- 351
- 352# All queries takingmorethan this amount oftime(in seconds) will be
- 353# trated as slow. Do not use"1"as a value here, as this will resultin
- 354# even very fast queries being logged fromtimetotime (as MySQL
- 355# currently measurestime with second accuracy only).
- 356long_query_time =2
- 357
- 358 #注:long_query_time 是设置查询的最大时间限制,单位:秒
- 359#如果所有的查询时间超过这个时间就会被认为是查询缓慢。不要设置1作为"long_query_time"的值
- 360 #因为这个将会导致系统有时候误认快速查询为慢速查询。(MySQL一般是以秒来作为精确度)
- 361
- 362
- 363# *** Replication related settings #MySQL主从复制原理设置
- 364
- 365
- 366# Unique server identification number between1and2^32-1. This value
- 367# is requiredforboth master and slave hosts. It defaults to1 if
- 368#"master-host"is not set, but will MySQL will notfunction as a master
- 369#if it is omitted.
- 370server-id=1
- 371
- 372#注:server-id是唯一的服务器标识数在1到2^32-1之间。这个值将会被主机(master)和副机(slave hosts)使用
- 373#server-id将默认会被定义成1如果"master-host"(主从复制的主机,这里的称为主机(master),另一个称为副机(slave host))没有指定,
- 374 #那么这个MySQL服务器将不会执行作为主机(master)的功能
- 375
- 376 # Replication Slave (comment out master section to use this)
- 377
- 378 #注:配置副机(slave host)(添加注释的区域是说明和使用的)
- 379 #
- 380 # To configure this host as a replication slave, you can choose between
- 381 # two methods :
- 382 #注:为了去配置这个MySQL作为副机(slave),你可以使用下面两种方案
- 383 #
- 384#1) Use the CHANGE MASTER TO command (fully describedinour manual) -385 # the syntax is:
- 386 #
- 387# CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,
- 388# MASTER_USER=, MASTER_PASSWORD= ;
- 389 #
- 390# where you replace , , by quoted strings and
- 391# by the master's port number (3306 by default).
- 392 #
- 393 # Example:
- 394 #
- 395# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
- 396# MASTER_USER='joe', MASTER_PASSWORD='secret';
- 397 #
- 398
- 399#注:第一种方案:使用"CHANGE MASTER TO"命令(完整的描述在MySQL的使用手册中)
- 400#"CHANGE MASTER TO"使用的语法及样例你可以参考什么的介绍,说的很详细
- 401
- 402
- 403 # OR
- 404 #
- 405#2) Set the variables below. However,in caseyou choose this method,then
- 406# start replicationforthe firsttime(even unsuccessfully,for example
- 407#ifyou mistyped the passwordinmaster-password and the slave fails to
- 408# connect), the slave will create a master.info file, and any later
- 409# changesinthisfile to the variable values below will be ignored and
- 410# overridden by the content of the master.info file, unless you shutdown
- 411# the slave server, delete master.info and restart the slaver server.
- 412 # For that reason, you may want to leave the lines below untouched
- 413 # (commented) and instead use CHANGE MASTER TO (see above)
- 414 #
- 415
- 416 #注:第二种方案:设置如下的变量,可是,如果你使用了这种方法,然后首次开启主从复制(即使你失败了,举个例子:如果你误输入主机(master)
- 417 #的密码,副机(salve)将不会连接),这个副机将会创建一个master.info文件,之后里面的配置变量在下面的都会失效并且会用
- 418 #主机(master)的master.info里面的内容来重写,除非你停止副机(slave),然后删除副机(slave)的master.info文件并且重新启动
- 419#副机(slave)。对于这个理由,你可能立刻想放弃这个方案。使用"CHANGE MASTER TO"命令的方法可以参考什么
- 420
- 421# required uniqueidbetween2and2^32-1
- 422 # (and different from the master)
- 423# defaults to2 ifmaster-host is set
- 424# but will notfunctionas a slaveif omitted
- 425#server-id=2
- 426
- 427#注:需要唯一的标识数id来标识这是一个副机(slave),不同于主机(master)。默认使用2如果主机(master-host)已经设置为1
- 428 #但是如果这个值被遗漏,那么这个副机(slave)就不会执行副机有的功能
- 429
- 430 #
- 431# The replication masterforthis slave - required
- 432#master-host = <hostname>433 #
- 434
- 435 #注:指定主从复制的主机(master)对于副机(slave),这个是必须的
- 436
- 437# The username the slave will usefor authentication when connecting
- 438# to the master - required
- 439#master-user =
- 440
- 441 #注:这个主机(master)的用户名被用于副机(slave)连接主机的验证,这个参数是必须的
- 442
- 443 # The password the slave will authenticate with when connecting to
- 444# the master - required
- 445#master-password =
- 446
- 447 #注:这个主机(master)的密码用于副机(slave)连接主机时候的认证
- 448
- 449 # The port the master is listening on.
- 450# optional - defaults to3306
- 451#master-port =
- 452
- 453 #注:这个主机(master)所在的端口,默认为3306
- 454
- 455# Make the slave read-only. Only users with the SUPER privilege and the
- 456 # replication slave thread will be able to modify data on it. You can
- 457 # use this to ensure that no applications will accidently modify data on
- 458 # the slave instead of the master
- 459 #read_only
- 460
- 461 #注:read_only 数据只读性。使得副机(slave)保持只读的性质。只有拥有超级权限的用户和主从复制的线程才有权限去修改副机(slave)
- 462 #你可以使用这个参数去确保没有应用可以去修改副机上面的数据
- 463
- 464 #以上的这些主从复制参数都被注释掉了,因为这些参数都用于副机(slave),而这里是主机
- 465
- 466#*** MyISAM Specific options #使用MyISAM存储引擎的一些特殊的参数
- 467
- 468
- 469# Size of the Key Buffer, used to cache index blocksfor MyISAM tables.
- 470# Do not set it larger than30% of your available memory, as some memory
- 471# is also required by the OS to cache rows. Evenifyou're not using
- 472# MyISAM tables, you should still set it to8-64M as it will also be
- 473# usedfor internal temporary disk tables.
- 474key_buffer_size = 32M
- 475
- 476 #注:key_buffer_size 是索引缓冲区的大小。用于缓存索引区块对于使用MyISAM储存的数据表
- 477#不要设置他的值超过你内存的30%,因为操作系统也需要内存去去缓存行数据。即使你不使用MyISAM存储引擎
- 478#你也需要设置key_buffer_size值为8-64M去用于存放内部的临时数据表
- 479
- 480# MyISAM uses special tree-like cache tomake bulk inserts (that is,
- 481 # INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA
- 482# INFILE) faster. This variable limits the size of the cache treein
- 483# bytes per thread. Setting it to0 will disable this optimisation. Do
- 484# not set it larger than"key_buffer_size" for optimal performance.
- 485 # This buffer is allocated when a bulk insert is detected.
- 486bulk_insert_buffer_size = 64M
- 487
- 488 #注:bulk_insert_buffer_size 是MyISAM专属的树形缓冲区的大小。这个树形缓冲区主要用于
- 489 #存放待插入的数据。bulk_insert_buffer_size将会限制树形缓冲区的大小对于每个线程,如果bulk_insert_buffer_size
- 490#设置为0,那么这个功能将不可用。设置这个值不要超过"key_buffer_size"是最佳性能
- 491 #这个缓冲区将会被分配当检测到插入数据的时候
- 492
- 493# This buffer is allocated when MySQL needs to rebuild the indexin
- 494# REPAIR, OPTIMIZE, ALTER table statements as well asin LOAD DATA INFILE
- 495 # into an empty table. It is allocated per thread so be careful with
- 496 # large settings.
- 497myisam_sort_buffer_size = 128M
- 498
- 499 # myisam_sort_buffer_size 是一个缓冲区,当MySQL需要重新修建索引在 REPAIR, OPTIMIZE, ALTER 数据表是或者
- 500 #插入数据到一个空表中时,会分配这个缓冲区。这个缓冲区将会被分配到每个线程以至于很小心的对于大的设置
- 501
- 502# The maximum size of the temporaryfileMySQL is allowed to usewhile
- 503 # recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.
- 504# If thefile-size would be bigger than this, the index will be created
- 505# through the key cache (which is slower).
- 506myisam_max_sort_file_size = 10G
- 507
- 508 #注:myisam_max_sort_file_size MySQL最大使用的临时文件大小当重建索引或者修改表或者加载数据时
- 509 #如果文件大小超过这个设定的值,那么索引将会被创建在索引缓冲区中(这个行为会比较慢)
- 510
- 511# If a table hasmorethan one index, MyISAM can usemore than one
- 512# thread to repair them by sortinginparallel. This makes senseif you
- 513 # have multiple CPUs and plenty of memory.
- 514myisam_repair_threads =1
- 515
- 516
- 517 #注: myisam_repair_threads 是MyISAM开启的修理线程数量。当一个表有多个索引,MyISAM就会开启一个或者多个
- 518 #修理线程去修理这些索引使他们并行的排序。如果想让这个变量有意义,那么你的机器需要有一个很复杂的CPU和大量的内存
- 519
- 520 # Automatically check and repair not properly closed MyISAM tables.
- 521 myisam_recover
- 522
- 523 # myisam_recover 是一个控制,控制是否自动的检查和修理不正确关闭的数据表
- 524
- 525# *** INNODB Specific options *** #使用INNODB存储引擎的一些特殊的参数
- 526
- 527# Use this optionif you have a MySQL server with InnoDB support enabled
- 528# but youdo not plan to use it. This will save memory and disk space
- 529 # and speed up some things.
- 530#skip-innodb
- 531
- 532 #注:使用这参数如果你的MySQL服务支持InnoDB,但是如果你不打算使用它,这个将会节约内存和磁盘空间
- 533 #并且加速某些操作
- 534
- 535 # Additional memory pool that is used by InnoDB to store metadata
- 536# information. If InnoDB requiresmorememoryfor this purpose it will
- 537 # start to allocate it from the OS. As this is fast enough on most
- 538# recent operating systems, you normallydo not need to change this
- 539 # value. SHOW INNODB STATUS will display the current amount used.
- 540innodb_additional_mem_pool_size = 16M
- 541
- 542 #注: innodb_additional_mem_pool_size 额外的内存池大小由InnoDB去存储元数据
- 543 #如果InnoDB需要更多的内存,他会直接找操作系统去要,这个操作是足够快的对于操作系统
- 544#因此你正常情况下没必要去改变这个值。使用"SHOW INNODB STATUS"命令可以查看当前的
- 545 #innodb_additional_mem_pool_size的值
- 546
- 547 # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
- 548# row data. The bigger you set this thelessdisk I/O is needed to
- 549# access datain tables. On a dedicated database server you may set this
- 550# parameter up to80% of the machine physical memory size. Do not set it
- 551 # too large, though, because competition of the physical memory may
- 552# cause pagingin the operating system. Note that on 32bit systems you
- 553# might be limited to2-3.5G of user level memory per process, sodo not
- 554 # set it too high.
- 555innodb_buffer_pool_size = 2G
- 556
- 557 #注:innodb_buffer_pool_size 是InnoDB用来存在索引和数据的缓冲区。InnoDB存储引擎和MyISAM不一样
- 558#InnoDB使用缓存池来缓存索引和行数据。这个值你设置的越大,磁盘I/O消耗的就会越少
- 559#在一个专门用于数据存储的服务器中,你可能设置这个参数的值为内存大小的80%。但是不要设置这个值过大
- 560 #因为在和操作系统竞争物理内存的过程中会导致操作系统出现问题。在32位的操作系统中,这个值限制在
- 561#2-3.5G在用户级别的程序中。总之不要设置这个过大
- 562
- 563# InnoDB stores datainone ormore data files forming the tablespace.
- 564# If you have a single logical drivefor your data, a single
- 565# autoextendingfilewould be good enough. In other cases, a singlefile
- 566 # per device is often a good choice. You can configure InnoDB to use raw
- 567# disk partitions as well - please refer to the manualfor more info
- 568 # about this.
- 569innodb_data_file_path = ibdata1:10M:autoextend
- 570
- 571 #注: innodb_data_file_path 指定数据文件的路径。InnoDB存储数据使用的是多个数据文件来形成空间表
- 572 #如果你有一个额外的逻辑分区来存在你的数据,单个的自填充文件是最好不过了。在另一方面
- 573 #一系列简单的文件对于每个驱动来说都是一个好的选择。如果你想知道如何配置InnoDB使用 raw disk partitions
- 574 #请参考使用手册
- 575
- 576# Set this optionif you would like the InnoDB tablespace files to be
- 577# storedin another location. By default this is the MySQL datadir.
- 578#innodb_data_home_dir =
- 579
- 580 #注: innodb_data_home_dir指定InnoDB数据文件的存在位置。你可以修改这个值来指定
- 581 #数据文件的存在路径
- 582
- 583# Number of IO threads to usefor async IO operations. This value is
- 584# hardcoded to4on Unix, but on Windows disk I/O may benefit from a
- 585 # larger number.
- 586innodb_file_io_threads =4
- 587
- 588 #注:innodb_file_io_threads 异步的IO线程数量对于业务的操作。这个值被写死为4在Unix 或者Linux
- 589 #在Windows下面,可以适当增加
- 590
- 591 # If you run into InnoDB tablespace corruption, setting this to a nonzero
- 592# value will likely help you to dump your tables. Start from value1 and
- 593# increase ituntilyou're able to dump the table successfully.
- 594#innodb_force_recovery=1
- 595
- 596 #注:innodb_force_recovery 是InnoDB空间表的转储情况。如果你运行InnoDB空间表出错
- 597 #设置这个值为一个非0的数将会帮助你去转储你的空间表。从1开始增长直到你的数据表转储完成
- 598
- 599 # Number of threads allowed inside the InnoDB kernel. The optimal value
- 600 # depends highly on the application, hardware as well as the OS
- 601 # scheduler properties. A too high value may lead to thread thrashing.
- 602innodb_thread_concurrency =16
- 603
- 604 #注:innodb_thread_concurrency 指定InnoDB的线程并发数量。这个值允许低于InnoDB的内核
- 605 #这个参数的最优值要依赖于具体的应用程序,硬件以及操作系统。太高的设置这个参数值将会导致
- 606 #线程超负荷
- 607
- 608# If set to1, InnoDB will flush (fsync) the transaction logs to the
- 609# disk at each commit,which offers full ACID behavior. If you are
- 610 # willing to compromise this safety, and you are running small
- 611# transactions, you may set this to0or2to reduce disk I/O to the
- 612# logs. Value0means that the log is only written to the logfile and
- 613# the logfileflushed to disk approximately once per second. Value2
- 614# means the log is written to the logfile at each commit, but the log
- 615#file is only flushed to disk approximately once per second.
- 616innodb_flush_log_at_trx_commit =1
- 617
- 618 #注:innodb_flush_log_at_trx_commit 设置事务日志的级别。
- 619#如果设置为1,那么每一次事务提交,就会把事务日志同步到磁盘,这样安全性高,但是占用较多的磁盘I/O
- 620#如果设置为0,日志只会被写入日志文件,并且每隔1s刷新到磁盘,不怎么消耗I/O,但安全性较差
- 621#如果设置为2,每次事务提交就会立刻写入日志文件,但是每隔1s才会同步到磁盘,不怎么消耗I/O,但安全性较差
- 622
- 623# Speed up InnoDB shutdown. This will disable InnoDB todo a full purge
- 624# and insert buffer merge on shutdown. It may increase shutdowntime a
- 625# lot, but InnoDB will have todo it on the next startup instead.
- 626 #innodb_fast_shutdown
- 627
- 628 #注: 加速InnoDB关闭的设置,这个设置将会废除InnoDB做整个数据的检查并且会保存到缓冲区中去
- 629 #这个也可能增加InnoDB的关闭时间。但是InnoDB将会去做整个数据检查在下一次开机的时候
- 630
- 631# The size of the buffer InnoDB usesfor buffering log data. As soon as
- 632 # it is full, InnoDB will have to flush it to disk. As it is flushed
- 633# once per second anyway, it does notmake sense to have it very large
- 634# (even withlong transactions).
- 635innodb_log_buffer_size = 8M
- 636
- 637 #注: innodb_log_buffer_size InnoDB日志缓冲区大小。只要日志缓冲区满了,InnoDB就会刷新日志到磁盘中
- 638 #但是因为InnoDB每秒刷新一次日志到磁盘,所以没必要设置这个值太大(即使是很长的事务)
- 639
- 640# Size of each logfile in a log group. You should set the combined size
- 641# of log files to about25%-100% of your buffer pool size to avoid
- 642# unneeded buffer pool flush activity on logfile overwrite. However,
- 643# note that a larger logfile size will increase thetimeneededfor the
- 644 # recovery process.
- 645innodb_log_file_size = 256M
- 646
- 647 #注: innodb_log_file_size是在一组日志文件中,每个日志文件的大小。你可以综合设置日志文件的大小
- 648#为"innodb_buffer_pool_size"(索引和数据缓冲区)大小的25%-100% 去避免因日志文件满而进行的刷新重新操作。
- 649 #记住如果innodb_log_file_size值过大将会增加恢复进程的处理时间
- 650
- 651# Total number of filesinthe log group. A value of2-3 is usually good
- 652 # enough.
- 653innodb_log_files_in_group =3
- 654
- 655#注: innodb_log_files_in_group是每个日志组里面日志的总数量。2-3个比较合适
- 656
- 657 # Location of the InnoDB log files. Default is the MySQL datadir. You
- 658# may wish to point it to a dedicated hard drive or a RAID1 volumefor
- 659 # improved performance
- 660 #innodb_log_group_home_dir
- 661
- 662 #注:InnoDB的日志存储路径。默认为MySQL的datadir。你可能想指定这个路径到一个专用的RAID1设备
- 663 #去提高MySQL的性能
- 664
- 665# Maximum allowed percentage of dirty pagesin the InnoDB buffer pool.
- 666 # If it is reached, InnoDB will start flushing them out agressively to
- 667 # not run out of clean pages at all. This is a soft limit, not
- 668 # guaranteed to be held.
- 669innodb_max_dirty_pages_pct =90
- 670
- 671 #注: innodb_max_dirty_pages_pct是InnoDB允许在缓存池内脏页(dirty pages)占的最大比例。如果超出了这个比例
- 672 #InnoDB将会去刷新这些脏页(dirty pages)但是不会用光这些干净页(dirty pages).
- 673 #这个一个软的限制,不能保证会执行
- 674
- 675# The flush method InnoDB will usefor Log. The tablespace always uses
- 676# doublewrite flush logic. The default value is"fdatasync", another
- 677# option is"O_DSYNC".
- 678#innodb_flush_method=O_DSYNC
- 679
- 680 #注:InnoDB的日志刷新方式,InnoDB总是使用双向写(doublewrite)的方式来刷新日志。
- 681#默认值是"fdatasync",还有一个值是O_DSYNC。如果想了解更多的日志刷新方式和脏页(dirty pages),
- 682 #可以参考下面的链接
- 683http://www.cnblogs.com/cchust/p/3961260.html
- 684
- 685# Howlongan InnoDB transaction shouldwait for a lock to be granted
- 686 # before being rolled back. InnoDB automatically detects transaction
- 687# deadlocksin its own lock table and rolls back the transaction. If you
- 688# use the LOCK TABLES command, or other transaction-safe storage engines
- 689# than InnoDBinthe same transaction,thena deadlock may arisewhich
- 690 # InnoDB cannot notice. In cases like this the timeout is useful to
- 691 # resolve the situation.
- 692innodb_lock_wait_timeout =120
- 693
- 694 #注: innodb_lock_wait_timeout是InnoDB锁等待的超时时间。InnoDB将会自动的发现死锁在
- 695#他的锁表中并且会回滚这些事务。如果你使用"LOCK TABLES"命令或者其他事务安全存储引擎
- 696 #在同一个事务中,死锁可能使InnoDB连接不敏感,使用这个超时参数会很有用的去解决这个问题
- 697
- 698
- 699 [mysqldump]
- 700# Do not buffer the whole result setin memory before writing it to
- 701#file. Requiredfor dumping very large tables
- 702 quick
- 703
- 704 #注:MySQL数据转储。在把数据转储之前,不会缓存整个结果在内存中。适用于转储数据量很大的表
- 705
- 706max_allowed_packet = 16M
- 707
- 708 #注:参阅141行
- 709
- 710 [mysql]
- 711no-auto-rehash
- 712
- 713 #注:MySQL不自动刷新
- 714
- 715 # Only allow UPDATEs and DELETEs that use keys.
- 716#safe-updates
- 717
- 718 #安全的更新,只允许更新和删除使用索引
- 719
- 720 [myisamchk]
- 721key_buffer_size = 512M #参考474行
- 722sort_buffer_size = 512M #参考191行
- 723read_buffer = 8M #读缓冲区大小
- 724write_buffer = 8M #写缓冲区大小
- 725
- 726 [mysqlhotcopy]
- 727interactive-timeout
- 728
- 729 #注:服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。
- 730 #参数默认值:28800秒(8小时)
- 731
- 732 [mysqld_safe]
- 733 # Increase the amount of open files allowed per process. Warning: Make
- 734# sure you have set the global system limit high enough! The high value
- 735# is requiredfor a large number of opened tables
- 736open-files-limit =8192
- 737
- 738 #注:MySQL的安全性参数,开放文件的数量。允许每个进程去增加开放文件的数量
- 739#警告:确保你已经做了全局系统的限制。这个值需要被增大当有大量的开放表被使用的时候。
来源: