MySQL configuration file my. CNF Chinese Version

Original article: http://www.phpv.net/html/my.cnf_configure.html
From hi! A copy of the configuration copied by the admin. The notes are very good.

# Begin config info
# Descr: 4 gb ram, only InnoDB, acid, a small number of connections, large queue Load
# Type: System
# End config info

#
# This MySQL configuration file example is for 4 GB memory
# Mainly use InnoDB
# MySQL servers that process complex queues and have a small number of connections
#
# Copy the file to/etc/My. CNF as a global setting,
# Mysql-data-DIR/My. CNF as the server to specify settings
# (@ Localstatedir @ for this installation) or put
#~ /. My. CNF is set as the user.
#
# In this configuration file, you can use allProgramSupported long options.
# If you want to know all the options supported by the program
# Add the "-- Help" parameter after the program to run the program.
#
# More details about the independent options can be found in the Manual
#

#
# The following options will be read by the mysql client application.
# Note that only client applications attached to MySQL can read this section.
# If you want your MySQL application to obtain these values
# You need to specify these options when initializing the mysql client library

#
[Client]
# Password = [your_password]
Port = @ mysql_tcp_port @
Socket = @ mysql_unix_addr @

# *** Application customization options ***

#
# MySQL Server
#
[Mysqld]

# General configuration options
Port = @ mysql_tcp_port @
Socket = @ mysql_unix_addr @

# Back_log is the number of connections that the operating system can maintain in the listening queue,
# The queue stores the connection before the MySQL Connection Manager thread processes it.
# If you have a very high connection rate and an "connection refused" error occurs,
# You should add the value here.
# Check your operating system documentation to obtain the maximum value of this variable.
# If you set back_log to a value higher than your operating system limit, it will be ineffective
Back_log = 50

# Do not listen on the TCP/IP Port.
# If all processes are connected to the local mysqld on the same server,
# This setting will enhance security.
# All mysqld connections are performed through UNIX sockets or named pipes.
# Note: In Windows, if you do not enable the named pipe option, you only use this option.
# (Using the "enable-named-pipe" option) will cause the MySQL service to have no effect!
# Skip-networking

# Maximum number of concurrent sessions allowed by the MySQL Service
# One of the connections will be retained as Administrator Logon by the super permission.
# Even if the maximum number of connections has been reached.
Max_connections = 100

# Maximum number of errors allowed for each client connection. If this limit is reached.
# This client will be blocked by the MySQL service until "Flush hosts" is executed or the service is restarted.
# This value is added for invalid passwords and other Link errors.
# View the status of "aborted_connects" to obtain the Global Counter.
Max_connect_errors = 10

# Number of tables opened by all threads.
# Adding this value increases the number of file descriptors required by mysqld.
# In this case, you need to confirm that the "open-files-limit" variable in [mysqld_safe] allows at least 4096 files to be opened.
Table_cache = 2048

# Allow external file-level locks. Opening a file lock will negatively affect the performance.
# This option is used only when you run multiple database instances on the same file (note that there are still other constraints !)
# Alternatively, you can use other software dependencies on the file layer to lock the MyISAM table.
# External-locking

# The maximum size of the request packet that the service can process and the maximum size of the request that the service can process (required when working with a large blob field)
# Independent size of each connection. The size increases dynamically
Max_allowed_packet = 16 m

# In a transaction, BINLOG records the cache size of the SQL state.
# If you often use large, multi-statement transactions, you can add this value to obtain greater performance.
# All the statuses from the transaction will be buffered in the BINLOG buffer and then written to the BINLOG at one time after submission
# If the transaction is larger than this value, it will be replaced by a temporary file on the disk.
# This buffer is created when the transaction status of each connection is updated for the first time.
Binlog_cache_size = 1 m

# Maximum capacity allowed by an independent memory table.
# This option is used to prevent accidental creation of an oversized memory table from permanently consuming all memory resources.
Max_heap_table_size = 64 m

# Sort buffering is used to process sorting caused by order by and group by queues.
# If the sorted data cannot be placed in the SORT buffer,
# A disk-based consolidated category used as an alternative will be used
# View "sort_merge_passes" status variables.
# Allocated by each thread when sorting occurs
Sort_buffer_size = 8 m

# This buffer is used to optimize full Union (full joins without indexes ).
# Similar joins have very bad performance in most cases,
# However, setting this value to a large value can reduce the performance impact.
# Use the "select_full_join" status variable to view the total number of conjoins
# When full Union occurs, it is allocated in each thread
Join_buffer_size = 8 m

# How many threads are reserved in the cache for Reuse
# When a client is disconnected, if the cache thread is smaller than thread_cache_size,
# The client thread is put into the cache.
# This can greatly reduce the thread creation overhead when you need a large number of new connections
# (Generally, if you have a good thread model, this will not significantly improve the performance .)
Thread_cache_size = 8

# This allows the application to give the thread system a prompt to give the desired number of threads at the same time.
# This value is only valid for systems that support the thread_concurrency () function (for example, Sun Solaris ).
# You can try to use [number of CPUs] * (2 .. 4) as the value of thread_concurrency
Thread_concurrency = 8

# Query buffering is often used to buffer select results and no direct return results will be executed in the next same query.
# Enabling the Query Buffer can greatly improve the server speed. If you have a large number of identical queries and rarely modify tables.
# Check the "qcache_lowmem_prunes" status variable to check whether the current value is high enough for your load.
# Note: When your table changes frequently or if your original query is different each time,
# Query buffering may cause performance degradation rather than performance improvement.
Query_cache_size = 64 m

# The result is buffered only when it is smaller than this value.
# This setting is used to protect the Query Buffer and prevent a large result set from overwriting all other query results.
Query_cache_limit = 2 m

# The minimum length of the index to be searched in full text.
# You may want to reduce it if you need to search for shorter words.
# Note that after you modify this value,
# You need to recreate your Fulltext Index
Ft_min_word_len = 4

# If your system supports the memlock () function, you may want to enable this option to make the running MySql in high memory tension, keep the data locked in the memory and prevent it from being swapping out.
# This option is useful for performance
# Memlock

# When creating a new table, it is used as the default table type,
# This value is used if the table type is not specially executed during creation.
Default_table_type = MyISAM

# The heap size used by the thread. This capacity is reserved for each connection.
# Mysql itself usually does not require 64 KB of memory
# If you use your own UDF that requires a large number of heap Functions
# Or your operating system needs more heaps for some operations,
# You may need to set it to a higher level.
Thread_stack = 192 K

# Set the default transaction isolation level. The available level is as follows:
# Read-uncommitted, read-committed, REPEATABLE-READ, serializable
Transaction_isolation = REPEATABLE-READ

# Maximum size of temporary internal (in memory) tables
# If a table grows to a value greater than this value, it is automatically converted to a disk-based table.
# This restriction applies to a single table, not the sum.
Tmp_table_size = 64 m

# Enable the binary log function.
# In the replication configuration, this option must be enabled as the master server
# If you need to recover from your last backup based on time points, you also need binary logs.
Log-bin = mysql-bin

# If you are using the chained slave server structure copy mode (a-> B-> C ),
# You need to enable this option on server B.
# This option is used to open the log that has been re-updated on the slave thread,
# And write it into the binary log of the slave server.
# Log_slave_updates

# Open the full query log. All queries received by the server (or even queries with an incorrect syntax)
# Will be recorded. This is very useful for debugging and is often disabled in the production environment.
# Log

# Print the warning to the error log file. If you have any problems with MySQL
# Open the warning log and carefully review the error log to find out the possible causes.
# Log_warnings

# Record slow query. Slow query refers to a query that consumes more time than "long_query_time.
# If log_long_format is enabled, queries without indexes will also be recorded.
# If you often add new queries to existing systems, this is generally a good idea,
Log_slow_queries

# All queries that use more time (in seconds) are considered as slow queries.
# Do not use "1" here; otherwise, all queries may be recorded, or even very fast query pages (because the current MySQL time precision can only reach the second level ).
Long_query_time = 2

# Record more information in slow logs.
# It is recommended to enable this option.
# Enabling this option will record the queries that do not use indexes as slow queries and append them to slow logs.
Log_long_format

# This directory is used by MySQL to save temporary files. For example,
# It is used to process disk-based large sorting, which is the same as internal sorting.
# And a simple temporary table.
# If you do not create a very large temporary file, it may be better to place it on the swapfs/tmpfs file system.
# You can also place it on an independent disk.
# You can use ";" to place multiple paths
# They will be used by polling according to the roud-Robin method.
# Tmpdir =/tmp

# *** Copy related settings

# Unique service identification number. The value ranges from 1 to 2 ^ 32-1.
# This value must be set on both master and slave.
# If "Master-host" is not set, the default value is 1. However, if this option is ignored, MySQL will not take effect as the master.
Server-id = 1

# Copy the slave (remove the comment of the master segment to make it take effect)
#
# To configure this host as the replicated slave server, you can select either of the following methods:
#
#1) use the change master to command (which is fully described in our manual )-
# Syntax:
#
# Change master to master_host = # Master_user = <user>, master_password = <password>;
#
# Replace #
# Example:
#
# Change master to master_host = '192. 564.12.1 ', master_port = 125,
# Master_user = 'job', master_password = 'secret ';
#
# Or
#
#2) set the following variables. In any case, when you select this method, and then start replication for the first time (or even if it fails,
# For example, if you enter the wrong password in the master-Password Field and slave cannot be connected ),
# Slave will create a master.info file, and any subsequent changes to parameters contained in this file will be ignored
# It is overwritten by the content in the master.info file, unless you disable the slave service, delete 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 the above) instead.
#
# The unique ID number is located between 2 and 2 ^ 32-1.
# (And different from the master)
# If the master-host is set, the default value is 2.
# It does not take effect if omitted
# Server-id = 2
#
# Master in the replication structure-required
# Master-host = #
# The username used by slave for authentication when connecting to the master node-required
# Master-user = <username>
#
# The password used by slave for authentication when connecting to the master node-required
# Master-Password = <password>
#
# The port listened by the master.
# Optional-the default value is 3306.
# Master-Port = <port>

# Make slave read-only. Only the user has the super permission and can modify data in the preceding slave thread.
# You can use this item to ensure that no application will accidentally modify the slave instead of the data on the master.
# Read_only

# *** MyISAM related options

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

# Buffer size used for full table scan of MyISAM table.
# Allocate it to the corresponding thread when full table scan is required.
Read_buffer_size = 2 m

# When a row is read from a sorted sequence after sorting, the row data is read from this buffer to prevent disk seek.
# If you increase this value, you can improve the performance of many order by statements.
# Allocated by each thread when necessary
Read_rnd_buffer_size = 16 m

# MyISAM uses a special tree-like cache to enable burst Inserts
# (These inserts are: insert... select, insert... values (...), (...),..., and load data.
# Infile) Faster. This variable limits the number of bytes of the buffer tree in each process.
# Setting 0 will disable this optimization.
# For optimization, do not set this value to greater than "key_buffer_size ".
# When burst inserts are detected, the buffer will be allocated.
Bulk_insert_buffer_size = 64 m

# This buffer is allocated when MySQL needs to repair, optimize, alter, and load data infile to an empty table, causing re-indexing.
# This is allocated in each thread. Therefore, be careful when setting the callback.
Myisam_sort_buffer_size = 128 m

# The maximum size of temporary files allowed when MySQL re-creates an index (when repair, alter table, or load data infile ).
# If the file size is greater than this value, the index will be created through the key-value buffer (slower)
Myisam_max_sort_file_size = 10g

# If the temporary file used for faster index creation is greater than the specified value, the key-value buffering method is used.
# This is mainly used to force the long string key in a large table to use the slow key-value buffer method to create an index.
Myisam_max_extra_sort_file_size = 10g

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

# Automatically check and fix MyISAM tables that are not properly closed.
Myisam_recover

# Federated is disabled by default.
Skip-Federated

# *** Bdb related options ***

# If the MySQL service you are running has bdb support but you are not ready to use this option, this will save memory and may accelerate some things.
Skip-bdb

# *** InnoDB related options ***

# If your MySQL service contains InnoDB support but is not intended to be used,
# Using this option will save memory and disk space, and accelerate some parts
# Skip-InnoDB

# The additional memory pool is used by InnoDB to save metadata information.
# If InnoDB requires more memory for this purpose, it will apply for memory from the OS.
# Because this operation is fast enough in most modern operating systems, you generally do not need to modify this value.
# The Show InnoDB status command displays the number of instances that are used first.
Innodb_additional_mem_pool_size = 16 m

# InnoDB uses a buffer pool to store indexes and raw data, unlike MyISAM.
# The larger you set, 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 server's physical memory size
# Do not set too large. Otherwise, the physical memory competition may lead to page changes in the operating system.
# Note that on a 32-bit system, each process may be limited to 2-3.5 GB of user memory,
# Do not set it too high.
Innodb_buffer_pool_size = 2G

# InnoDB stores data in one or more data files as tablespaces.
# If you only have a single logic driver to save your data, a single auto-increment file is enough.
# In other cases, a file on each device is generally a good choice.
# You can also configure InnoDB to use the raw disk partition.-refer to the Manual for more information.
Innodb_data_file_path = ibdata1: 10 m: autoextend

# Set this option if you want the InnoDB tablespace file to be saved in another partition.
# It is stored in MySQL datadir by default.
# Innodb_data_home_dir = <directory>

# The number of Io threads used to synchronize Io operations. This value is
# This value is hard coded as 4 in UNIX, But windows disk I/O may perform better in a large value.
Innodb_file_io_threads = 4

# If you find that the InnoDB tablespace is corrupted, setting this value to a non-zero value may help you export your table.
# From 1 and add this value to know that you can successfully export the table.
# Innodb_force_recovery = 1

# Number of threads allowed in the InnoDB core.
# The optimal value depends on the scheduling methods of applications, hardware, and operating systems.
# A high value may cause mutually exclusive thread bumps.
Innodb_thread_concurrency = 16

# If it is set to 1, InnoDB will refresh the (fsync) transaction log to the disk after each commit,
# This provides complete acid behavior.
# If you are willing to compromise transaction security and you are running a small item, you can set this value to 0 or 2 to reduce disk I/O caused by transaction logs
#0 indicates that logs are only written to the log file every second and the log file is refreshed to the disk.
#2 indicates that the log file written to the log is refreshed to the disk only every second after each submission.
Innodb_flush_log_at_trx_commit = 1

# Accelerate InnoDB shutdown. This will prevent InnoDB from being completely cleared when it is closed and insert buffer merging.
# This may greatly increase the shutdown time, But InnoDB may perform these operations at the next startup.
# Innodb_fast_shutdown

# The buffer size used to buffer log data.
# When the value is full, InnoDB must refresh the data to the disk.
# Since refresh is basically performed every second, it is not necessary to set this value too large (even for long transactions)

Innodb_log_buffer_size = 8 m

# The size of each log file in the log group.
# You should set the total size of log files to 25% ~ of the size of your buffer pool ~ 100%
# To avoid overwriting unnecessary buffer pool refreshing behaviors in log files.
# In any case, note that a large log file size will increase the time required to restore the process.
Innodb_log_file_size = 256 m

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

# Location of the InnoDB log file. The default value is MySQL datadir.
# You can specify it to an independent hard disk or a raid1 volume to improve its performance.
# Innodb_log_group_home_dir

# Maximum allowable proportion of dirty pages in the InnoDB buffer pool.
# If the quota is reached, InnoDB will start refreshing them to prevent them from interfering with the clean data page.
# This is a soft limit and is not guaranteed to be executed absolutely.
Innodb_max_dirty_pages_pct = 90

# How InnoDB refreshes logs.
# The tablespace always uses the double write refresh method.
# The default value is "fdatasync", and the other is "o_dsync ".
# Innodb_flush_method = o_dsync

# How long should an InnoDB Transaction wait for a lock to be approved before being rolled back.
# InnoDB automatically detects the transaction deadlock and rolls back the transaction in its lock table.
# If you use the lock tables command or use a transaction-safe storage engine other than InnoDB in the same transaction
# A deadlock may occur and InnoDB cannot notice it.
# In this case, the timeout value is very helpful for solving this problem.
Innodb_lock_wait_timeout = 120

[Mysqldump]
# Do not cache the entire result in the memory before writing it to the disk. This item is required when exporting a very large table
Quick

Max_allowed_packet = 16 m

[MySQL]
No-auto-rehash

# Only key-value updates and deletes are allowed.
# Safe-Updates

[isamchk]
key_buffer = 512 m
sort_buffer_size = 512 m
read_buffer = 8 m
write_buffer = 8 m
[myisamchk]
key_buffer = 512 m
sort_buffer_size = 512 m
read_buffer = 8 m
write_buffer = 8 m
[mysqlhotcopy]
interactive-Timeout

[mysqld_safe]
# increase the number of files that can be opened by each process.
# warning: Make sure you have set a high enough limit for the entire system!
# To open a large number of tables, set this value to a high value
open-files-Limit = 8192