MySQL configuration file my.cnf Chinese version configuration Reference document

MySQL configuration file my.cnf Chinese version configuration Reference document

#BEGIN CONFIG INFO
#DESCR: 4GB RAM, using only InnoDB, ACID, a small number of connections, large queue load
#TYPE: SYSTEM
#END CONFIG INFO

#
# This MySQL Tutorial profile example for 4G memory
# Main Use InnoDB
#处理复杂队列并且连接数量较少的mysql服务器
#
# Copy this file to/ETC/MY.CNF as a global setting,
# MYSQL-DATA-DIR/MY.CNF Specify settings as Server
# (@localstatedir @ for this installation) or put
# ~/.my.cnf as user settings.
#
# In this configuration file, you can use the long options supported by all programs.
# If you want to know all the options supported by the program
# Please run the program with the "--help" parameter after the program.
#
# More details about the standalone option can be found in the manual
#

#
# The following options will be read by the MySQL client application.
# Note Only the client application included with MySQL guarantees that this content can be read.
# If you want your own MySQL application to get these values
# You need to specify these options when the MySQL client library is initialized

#
[Client]
#password = [Your_password]
Port = @MYSQL_TCP_PORT @
Socket = @MYSQL_UNIX_ADDR @

# * * * Apply Customization options * * *

#
# MySQL Service Side
#
[Mysqld]

# General configuration Options
Port = @MYSQL_TCP_PORT @
Socket = @MYSQL_UNIX_ADDR @

# Back_log is the number of connections the operating system can hold in the listening queue.
# The queue holds the connection before the MySQL Connection Manager thread is processed.
# If you have a very high connection rate and ' connection refused ' error,
# You should add the value here.
# Check your operating system documentation to get the maximum value for this variable.
# If you set the Back_log to a higher value than your operating system, there will be no effect.
Back_log = 50

# Do not listen on TCP/IP ports.
# If all the processes are connected to the local mysqld on the same server,
# This setting will be the way to enhance security
# All MYSQLD connections are made through UNIX sockets or named pipes.
# Note that if you do not open the Named pipe option on Windows, you only use this item
# (through the "enable-named-pipe" option) will cause the MySQL service to have no effect!
#skip-networking

# The maximum number of simultaneous sessions allowed by the MySQL service
# One of the connections will be reserved by super privileges as an administrator.
# even if the number of connections has reached the upper limit.
max_connections = 100

# The maximum number of error allowed per client connection, if this limit is reached.
# This client will be blocked by the MySQL service until the "FLUSH HOSTS" or service reboot is performed.
# an illegal password and other errors at the time of the link increase this value.
# View the "aborted_connects" status to get the global counter.
Max_connect_errors = 10

# Number of tables opened by all threads.
# Increasing this value increases the number of file descriptors required for mysqld
# so you need to make sure that the "open-files-limit" variable set in [Mysqld_safe] has a minimum number of open files allowed at least 4096
Table_cache = 2048

# Allow external file-level locks. Opening a file lock can adversely affect performance
# so use this option only if you run multiple database tutorial instances on the same file (note that there are still other constraints!)
# or you use some other software dependencies on the file level to lock the MyISAM table
#external-locking

# The maximum size of the request package that the service can handle and the maximum request size that the service can handle (is necessary when working with large BLOB fields)
# The size of each connection is independent. Dynamic Increase in size
Max_allowed_packet = 16M

# Binlog in a transaction to record the cache size held by the SQL state
# If you often use large, multiple-declaration transactions, you can increase this value to achieve greater performance.
# All engaged states will be buffered in Binlog buffers and then written to Binlog once committed
# If the transaction is larger than this value, a temporary file on disk will be used instead.
# This buffer is created when each connected transaction is first updated state
Binlog_cache_size = 1M

# The maximum allowable capacity of a separate memory table.
# This option to prevent accidental creation of an oversized memory table causes all memory resources to be exhausted.
Max_heap_table_size = 64M

# The sort buffer is used to handle the sort that is caused by the order by and the group by queue
# If the sorted data cannot be placed in the sort buffer,
# A disk-based merge classification that is used for substitution
# View the ' sort_merge_passes ' state variable.
# is assigned by each thread when the sort occurs
Sort_buffer_size = 8M

# This buffer is used to optimize the Union (full joins union without indexes).
# Similar unions have very poor performance in most cases,
# but setting this value large can mitigate the performance impact.
# View the number of total unions through the ' Select_full_join ' state variable
# When a full union occurs, it is allocated in each thread
Join_buffer_size = 8M

# How many threads we keep in the cache for reuse
# When a client disconnects, if the thread in the cache is less than thread_cache_size,
# The client thread is placed in the cache.
# This can greatly reduce the overhead of thread creation when you need a lot of new connections
# (in general, if you have a good threading model, there's no noticeable performance boost.)
Thread_cache_size = 8

# This allows the application to give the thread system a hint at the same time given the number of threads that are eager to be run.
# This value is only meaningful for systems that support the thread_concurrency () function (for example, Sun Solaris).
# you can try using [CPU number]* (2..4) as thread_concurrency value
Thread_concurrency = 8

# query buffering is often used to buffer the results of a SELECT and to no longer perform direct return results the next time the same query is made.
# Open query buffering can greatly improve server speed, if you have a large number of the same query and rarely modify the table.
# Check the "qcache_lowmem_prunes" state variable to see if the current value is high enough for your load.
# Note: When your table changes frequently or if your query text is different each time,
# query buffering may cause performance degradation rather than performance improvement.
Query_cache_size = 64M

# Only a result that is less than this setting will be buffered
# This setting protects the query buffer and prevents a large result set from overwriting all other query results.
Query_cache_limit = 2M

# The smallest word length that is indexed by Full-text retrieval.
# You may want to reduce it if you need to search for shorter words.
# Note that after you modify this value,
# You need to rebuild your fulltext index
Ft_min_word_len = 4

# If your system supports the Memlock () function, you may want to turn on this option to allow the running MySQL to lock in memory and prevent possible swapping out of memory when it is highly stressful.
# This option is good for performance
#memlock

# The table type used as the default when creating new tables.
# This value will be used when creating representations that do not specifically perform table types
Default_table_type = MYISAM

# The heap size used by the thread. Memory for this capacity is reserved for each connection.
# MySQL itself often does not require more than 64K of memory
# If you use your own UDF function that requires a lot of heaps
# or your operating system needs more heaps for some operations,
# You might want to set it up a little bit higher.
Thread_stack = 192K

# Set the default transaction isolation level. The available levels are as follows:
# read-uncommitted, read-committed, Repeatable-read, SERIALIZABLE
Transaction_isolation = Repeatable-read

# The maximum size of an internal (in-memory) temporary table
# If a table grows larger than this, it will automatically be converted to a disk-based table.
# This limit is for a single table, not a sum.
Tmp_table_size = 64M

# turn on the binary log feature.
# In a replication (replication) configuration, you must open this key as the Master master server
# If you need to do a point-in-time restore from your last backup, you also need binary logging.
Log-bin=mysql-bin

# If you're using a chained copy mode from the server structure (A->B->C),
# You need to open this item on Server B.
# This option opens a log of updates that have been redo from the thread.
# and writes it to the binary log from the server.
#log_slave_updates

# Open full query log. All queries received by the server (even for a query with an incorrect syntax)
# will be recorded. This is useful for debugging, which is often turned off in a production environment.
#log

# Prints warnings to the error log file. If you have any questions about MySQL,
# You should turn on the warning log and examine the error log carefully to find out the probable cause.
#log_warnings

# Record slow queries. A slow query refers to a query that consumes more time than the "long_query_time" definition.
# If Log_long_format is opened, queries that do not use the index are also logged.
# If you often add new queries to existing systems. This is generally a good idea,
Log_slow_queries

# All the queries that use more than this time (in seconds) are considered slow queries.
# do not use "1" here, otherwise it will cause all queries, even very fast query pages to be recorded (since MySQL's current time accuracy can only reach the second level).
Long_query_time = 2

# More information is logged in the slow log.
# generally this item is best opened.
# Open this record so that queries that do not use the index are also attached to the slow log as a slow-speed query.
Log_long_format

# This directory is used by MySQL to save temporary files. For example,
# It is used to handle large disks based sorting, as well as internal sorting.
# and a simple temporary table.
# If you don't create very large temporary files, it might be better to put them on the Swapfs/tmpfs file system
# Another option is that you can also place it on a separate disk.
# you can use '; ' To place multiple paths
# They will be polled in accordance with the Roud-robin method.
#tmpdir =/tmp

# * * Copy Related Settings

# Unique service identification number located between 1 and 2^32-1.
# This value needs to be set on both master and slave.
# if "Master-host" is not set, the default is 1, but if this option is omitted, MySQL does not take effect as master.
Server-id = 1

# copied slave (remove the comment from Master section to make it effective)
#
# To configure this host as a replicated slave server, you can choose between two methods:
#
# 1 using the Change MASTER to command (complete description in our manuals)-
# The syntax is as follows:
#
# change MASTER to Master_host=# master_user=<user>, master_password=<password>;
#
# you need to replace #
Example
#
# change MASTER to master_host= ' 125.564.12.1 ', master_port=3306,
# master_user= ' Joe ', master_password= ' secret ';
#
# or
#
# 2) Set the following variables. Anyway, in case you choose this method, and then the first time you start replication (or even unsuccessful,
# For example if you enter the wrong password in the Master-password field and slave cannot connect to it,
# Slave will create a master.info file, and any subsequent changes to the parameters contained within this file will be ignored
# and covered by the contents of the Master.info file, unless you turn off the slave service, delete the master.info, and restart the slave service.
# for this reason, you may not want to touch the configuration (commented out) and use change MASTER to (view above) instead
#
# The unique ID number required is between 2 and 2^32-1
# (and different from master)
# If the master-host is set. The default value is 2
# but if omitted, it will not take effect
#server-id = 2
#
# Master in the copy structure-must be
#master-host = #
# The user name slave is used to authenticate when connecting to master-must be
#master-user = <username>
#
# The password that slave uses to authenticate when connecting to master-must be
#master-password = <password>
#
# Master listens on the port.
# optional-Default is 3306
#master-port = <port>

# makes slave read only. Only the user has the Super privilege and the slave thread on top can modify the data.
# You can use this to ensure that no application will accidentally modify slave instead of data on master
#read_only

#*** MyISAM Related options

# The size of the keyword buffer, generally used to buffer the MyISAM table index block.
# do not set it larger than 30% of your available memory,
# because part of the memory is also used by the OS to buffer row data
# Even if you don't use the MyISAM table, you need to still set up 8-64m memory because it will also be used by the internal temporary disk table.
Key_buffer_size = 32M

# used to do MyISAM table full table scan buffer size.
# When the full table scan is needed, it is allocated in the corresponding thread.
Read_buffer_size = 2M

# When rows are fetched from a sorted sequence, the row data is read from the buffer to prevent disk seek.
# If you increase this value, you can improve the performance of many order by.
# allocated by each thread when needed
Read_rnd_buffer_size = 16M

# MyISAM uses special, tree-like cache to make burst inserts
# (These inserts are, insert ...) SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA
# INFILE) faster. This variable limits the number of bytes in the buffer tree in each process.
# setting to 0 will turn off this optimization.
# to optimize not to set this value greater than ' key_buffer_size '.
# This buffer will be allocated when burst inserts are detected.
Bulk_insert_buffer_size = 64M

# This buffer is allocated when MySQL needs to cause a rebuild index in REPAIR, OPTIMIZE, ALTER, and LOAD DATA INFILE to an empty table.
# This is assigned in each thread, so be careful when setting large values.
Myisam_sort_buffer_size = 128M

# The maximum amount of temporary files allowed when MySQL rebuilds the index (when REPAIR, ALTER TABLE, or LOAD DATA INFILE).
# If the file size is larger than this, the index is created through a key-value buffer (slower)
Myisam_max_sort_file_size = 10G

# Use the key-value buffering method if the temporary file used for indexing the index is faster than the set value.
# This is primarily used to force long string keys in large tables to use slow key-value buffering methods to create indexes.
Myisam_max_extra_sort_file_size = 10G

# If a table has more than one index, MyISAM can fix them by using more than one thread in parallel sorting.
# This is a good choice for users with multiple CPUs and large amounts of memory.
Myisam_repair_threads = 1

# automatically checks and repairs MyISAM tables that are not properly closed.
Myisam_recover

# Default Shutdown Federated
skip-federated

# * * * BDB Related options * * *

# Use this option if you are running a MySQL service that has BDB support but you are not ready to use it. This saves memory and can accelerate something.
Skip-bdb

# * * * INNODB Related options * * *

# If your MySQL service contains INNODB support but is not intended to be used,
# Use this option to save memory and disk space, and to speed up some parts
#skip-innodb

# Additional memory pools are InnoDB used to save metadata information
# If InnoDB needs more memory for this purpose, it will begin to request memory from the OS.
# Since this operation is fast enough on most modern operating systems, you generally do not need to modify this value.
# show INNODB The STATUS command displays the number of the in-use.
Innodb_additional_mem_pool_size = 16M

# InnoDB uses a buffer pool to hold indexes and raw data, unlike MyISAM.
# The larger you set here, the less disk I/O you need to access the data in the table.
# on an independently used database server, you can set this variable to 80% of the size of the server's physical memory
# do not set too large, otherwise, due to the competition of physical memory may cause the operating system of page-changing.
# Note that each process on a 32-bit system may be limited to 2-3.5g user-level memory limits,
# so don't set it too high.
Innodb_buffer_pool_size = 2G

# InnoDB saves data in one or more data files as a tablespace.
# If you only have a single logical drive to save your data, a single self-add file is good enough.
# in other cases. A single file for each device is generally a good choice.
# You can also configure InnoDB to use bare disk partitions-please refer to the manual for more information
Innodb_data_file_path = Ibdata1:10m:autoextend

# Set this option if you want the InnoDB table space file to be saved in another partition.
# The default is saved in the MySQL DataDir.
#innodb_data_home_dir = <directory>

# Number of IO threads used to synchronize IO operations. This value is
# This value is hard-coded to 4 under UNIX, but may perform better in a large number of Windows disk I/O.
Innodb_file_io_threads = 4

# If you find that InnoDB table space is corrupted, setting this value to a value other than 0 may help you to export your table.
# Start with 1 and add this value to know that you can successfully export the table.
#innodb_force_recovery =1

# Number of allowed threads within the InnoDB core.
# The optimal value depends on the application, hardware, and operating system scheduling.
# too high a value can cause a thread to be mutually exclusive bumps.
Innodb_thread_concurrency = 16

# If set to 1, InnoDB will refresh (fsync) The transaction log to disk after each commit.
# This provides complete acid behavior.
# If you're willing to compromise on security and you're running a small food, you can set this value to 0 or both to reduce disk I/O caused by the transaction log
# 0 indicates that log files are written only approximately per second and the log files are flushed to disk.
# 2 represents the log write log file after each commit, but only about every second the log file is flushed to disk.
Innodb_flush_log_at_trx_commit = 1

# accelerates the InnoDB off. This prevents InnoDB from doing full cleanup and inserting a buffer merge at shutdown.
# This can greatly increase the shutdown time, but instead the InnoDB may do so at the next startup.
#innodb_fast_shutdown

# The size of the buffer used to buffer log data.
# When this value is full, InnoDB will have to refresh the data to disk.
# because it's almost always refreshed every second, there's no need to set this value too large (even for long transactions)

Innodb_log_buffer_size = 8M

# The size of each log file in the log group.
# You should set the aggregate size of the log file to the size of your buffer pool 25%~100%
# to avoid unnecessary buffer pool refresh behavior in the log file overwrite.
# However, note that a large log file size increases the time required for the recovery process.
Innodb_log_file_size = 256M

# Total number of files in the log group.
# Generally speaking, 2~3 is better.
Innodb_log_files_in_group = 3

# The location of the log file for InnoDB. The default is MySQL DataDir.
# You can assign it to a standalone hard disk or a RAID1 roll to improve its performance
#innodb_log_group_home_dir

# The ratio of the maximum allowable dirty pages in the InnoDB buffer pool.
# If the limit is reached, InnoDB will start refreshing them to prevent them from interfering with the Clean data page.
# This is a soft limit that is not guaranteed to be absolutely executed.
innodb_max_dirty_pages_pct = 90

# InnoDB The method used to refresh the log.
# table space always uses the double write Refresh method
# The default value is ' Fdatasync ' and the other is ' O_dsync '.
#innodb_flush_method =o_dsync

# before being rolled back, a INNODB transaction should wait for a lock to be approved for how long.
# InnoDB automatically detects a transaction deadlock and rolls back the transaction in its own lock table.
# If you use the LOCK TABLES command, or use a storage engine that is secure in the same transaction as a transaction other than InnoDB
# Then a deadlock can occur and InnoDB cannot notice it.
# In this case this timeout value is very helpful for solving this problem.
Innodb_lock_wait_timeout = 120

[Mysqldump]
# do not cache the entire result in memory before it is written to disk. This item is required when exporting very large tables
Quick

Max_allowed_packet = 16M

[MySQL]
No-auto-rehash

# only UPDATEs and deletes with key values are allowed.
#safe-updates

[Isamchk]
Key_buffer = 512M
Sort_buffer_size = 512M
Read_buffer = 8M
Write_buffer = 8M

[Myisamchk]
Key_buffer = 512M
Sort_buffer_size = 512M
Read_buffer = 8M
Write_buffer = 8M

[Mysqlhotcopy]
Interactive-timeout

[Mysqld_safe]
# to increase the number of open files per process.
# Warning: Make sure you have set the system limit high enough!
# Open a large number of tables you need to set this value high
Open-files-limit = 8192