MySQL——配置文件（my.ini）

1、配置文件说明信息。

# CLIENT SECTION
# ----------------------------------------------------------------------
[client]
#password =1234

# pipe
# socket=mysql
# 设置mysql客户端连接服务端时默认使用的端口
port=3306

default-character-set=utf8
[mysql]
port=3306
# 设置mysql客户端默认字符集
default-character-set=utf8


# SERVER SECTION
# ----------------------------------------------------------------------
[mysqld]
# mysql服务端默认监听(listen on)的TCP/IP端口
port=3306

# 基准路径，其他路径都相对于这个路径；即MySQL的安装路径
basedir="D:\MySQL"

# mysql数据库文件所在目录
datadir="D:\Mysql\data"

# 服务端使用的字符集默认为8比特编码的utf-8字符集
character-set-server=utf8

# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

# SQL模式为strict模式
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# General and Slow logging.
log-output=NONE
general-log=0
general_log_file="WANGZHANGJIE.log"
slow-query-log=0
slow_query_log_file="WANGZHANGJIE-slow.log"
long_query_time=10

# Error Logging.
log-error="WANGZHANGJIE.err"

# mysql服务器支持的最大并发连接数（用户数）。但总会预留其中的一个连接给管理员使用超级权限登录，
# 即使连接数目达到最大限制。如果设置得过小而用户比较多，会经常出现“Too many connections”错误。
max_connections=100

# 查询缓存大小，用于缓存SELECT查询结果。如果有许多返回相同查询结果的SELECT查询，并且很少改变表，
# 可以设置query_cache_size大于0，可以极大改善查询效率。而如果表数据频繁变化，就不要使用这个，会适得其反
query_cache_size=0

# The number of open tables for all threads. Increasing this value
# increases the number of file descriptors that mysqld requires.
# Therefore you have to make sure to set the amount of open files
# allowed to at least 4096 in the variable "open-files-limit" in
# section [mysqld_safe]
table_open_cache=2000

# 内存中的每个临时表允许的最大大小。如果临时表大小超过该值，临时表将自动转为基于磁盘的表（Disk Based Table）。
tmp_table_size=20M

# 缓存的最大线程数。当客户端连接断开时，如果客户端总连接数小于该值，则处理客户端任务的线程放回缓存。
# 在高并发情况下，如果该值设置得太小，就会有很多线程频繁创建，
# 线程创建的开销会变大，查询效率也会下降。一般来说如果在应用端有良好的多线程处理，这个参数对性能不会有太大的提高。
thread_cache_size=9

# mysql重建索引时允许使用的临时文件最大大小
myisam_max_sort_file_size=100G

# If the temporary file used for fast index creation would be bigger
# than using the key cache by the amount specified here, then prefer the
# key cache method.  This is mainly used to force long character keys in
# large tables to use the slower key cache method to create the index.
myisam_sort_buffer_size=39M


# Key Buffer大小，用于缓存MyISAM表的索引块。决定数据库索引处理的速度（尤其是索引读）
key_buffer_size=8M


# 用于对MyISAM表全表扫描时使用的缓冲区大小。针对每个线程进行分配（前提是进行了全表扫描）。
# 进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，
# 如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，
# 所以应尽量适当设置该值，以避免内存开销过大。
read_buffer_size=64K
read_rnd_buffer_size=256K


# connection级参数（为每个线程配置），500个线程将消耗500*256K的sort_buffer_size。
sort_buffer_size=256K


# InnoDB用于存储元数据信息的内存池大小，一般不需修改
innodb_additional_mem_pool_size=2M


# 事务相关参数，如果值为1，则InnoDB在每次commit都会将事务日志写入磁盘（磁盘IO消耗较大），
# 这样保证了完全的ACID特性。而如果设置为0，则表示事务日志写入内存log和内存log写入磁盘的频率都为1次/秒。
# 如果设为2则表示事务日志在每次commit都写入内存log，但内存log写入磁盘的频率为1次/秒。


innodb_flush_log_at_trx_commit=1


# InnoDB日志数据缓冲大小，如果缓冲满了，就会将缓冲中的日志数据写入磁盘（flush）。
# 由于一般至少都1秒钟会写一次磁盘，所以没必要设置过大，即使是长事务。
innodb_log_buffer_size=1M

# InnoDB使用缓冲池来缓存索引和行数据。该值设置的越大，则磁盘IO越少。一般将该值设为物理内存的80%。
innodb_buffer_pool_size=97M

# 每一个InnoDB事务日志的大小。一般设为innodb_buffer_pool_size的25%到100%
innodb_log_file_size=48M

# InnoDB内核最大并发线程数
innodb_thread_concurrency=9

# The increment size (in MB) for extending the size of an auto-extend InnoDB system tablespace file when it becomes full.
innodb_autoextend_increment=64M

# The number of regions that the InnoDB buffer pool is divided into.
# For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency,
# by reducing contention as different threads read and write to cached pages.
innodb_buffer_pool_instances=8

# Determines the number of threads that can enter InnoDB concurrently.
innodb_concurrency_tickets=5000

# Specifies how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access before
# it can be moved to the new sublist.
innodb_old_blocks_time=1000

# It specifies the maximum number of .ibd files that MySQL can keep open at one time. The minimum value is 10.
innodb_open_files=300

# When this variable is enabled, InnoDB updates statistics during metadata statements.
innodb_stats_on_metadata=0

# When innodb_file_per_table is enabled (the default in 5.6.6 and higher), InnoDB stores the data and indexes for each newly created table
# in a separate .ibd file, rather than in the system tablespace.
innodb_file_per_table=1

# Use the following list of values: 0 for crc32, 1 for strict_crc32, 2 for innodb, 3 for strict_innodb, 4 for none, 5 for strict_none.
innodb_checksum_algorithm=0

# The number of outstanding connection requests MySQL can have.
# This option is useful when the main MySQL thread gets many connection requests in a very short time.
# It then takes some time (although very little) for the main thread to check the connection and start a new thread.
# The back_log value indicates how many requests can be stacked during this short time before MySQL momentarily
# stops answering new requests.
# You need to increase this only if you expect a large number of connections in a short period of time.
back_log=70


# If this is set to a nonzero value, all tables are closed every flush_time seconds to free up resources and
# synchronize unflushed data to disk.
# This option is best used only on systems with minimal resources.
flush_time=0

# The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use
# indexes and thus perform full table scans.
join_buffer_size=256K

# The maximum size of one packet or any generated or intermediate string, or any parameter sent by the
# mysql_stmt_send_long_data() C API function.
max_allowed_packet=4M

# If more than this many successive connection requests from a host are interrupted without a successful connection,
# the server blocks that host from performing further connections.
max_connect_errors=100

# Changes the number of file descriptors available to mysqld.
# You should try increasing the value of this option if mysqld gives you the error "Too many open files".
open_files_limit=4110

# Set the query cache type. 0 for OFF, 1 for ON and 2 for DEMAND.
query_cache_type=0

# If you see many sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the
# sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization
# or improved indexing.
sort_buffer_size=256K

# The number of table definitions (from .frm files) that can be stored in the definition cache.
# If you use a large number of tables, you can create a large table definition cache to speed up opening of tables.
# The table definition cache takes less space and does not use file descriptors, unlike the normal table cache.
# The minimum and default values are both 400.
table_definition_cache=1400

# Specify the maximum size of a row-based binary log event, in bytes.
# Rows are grouped into events smaller than this size if possible. The value should be a multiple of 256.
binlog_row_event_max_size=8K

# If the value of this variable is greater than 0, a replication slave synchronizes its master.info file to disk.
# (using fdatasync()) after every sync_master_info events.
sync_master_info=10000

# If the value of this variable is greater than 0, the MySQL server synchronizes its relay log to disk.
# (using fdatasync()) after every sync_relay_log writes to the relay log.
sync_relay_log=10000

# If the value of this variable is greater than 0, a replication slave synchronizes its relay-log.info file to disk.
# (using fdatasync()) after every sync_relay_log_info transactions.
sync_relay_log_info=10000