Mysql_ configuration file Detailed

adjust the MySQL run parameters, modify the/etc/my.cnf file to adjust the MySQL run parameters after restarting MySQL, after the MySQL4 version, a part of the internal variables can be set in the MySQL runtime, but the restart of MySQL is invalid.
mysqld Programs--Directories and files
basedir = path # uses the given directory as the root directory (the installation directory).
datadir = path # reads the database file from the given directory.
pid-file = filename # Specifies a file that holds the process ID for the MYSQLD program (only for Unix/linux systems);

[Mysqld]
socket =/tmp/mysql.sock # Specifies a socket file for local communication between the MySQL client and server (the default is/var/lib/mysql/mysql.sock file under Linux)
Port = 3306 # Specifies the port on which MSSQL listens
key_buffer = 384M # Key_buffer is the buffer size used for the index block, increasing the index (for all read and multiple writes) that it can get better processing.
The index block is buffered and shared by all threads, and the size of the key_buffer depends on the size of the memory.
Table_cache = 512 # Open the number of tables for all threads. Increasing this value can increase the number of file descriptors required by the mysqld. The overhead of frequently opening data tables can be avoided
sort_buffer_size = 2M # Each thread that needs to be sorted allocates a buffer of that size. Increase this value to accelerate the order by or group by operation.
Note: This parameter corresponds to the allocated memory that is per connection exclusive! If there are 100 connections, the actual allocated total sort buffer size is 100X6=600MB
read_buffer_size = 2M # The buffer size that can be used by the read query operation. As with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection.
query_cache_size = 32M # Specifies the size of the MySQL query result buffer
read_rnd_buffer_size = 8M # parameters are randomly read after they are sorted using the row pointer.
myisam_sort_buffer_size =64m # MyISAM table changes when the required buffers are reordered
thread_concurrency = 8 # Maximum number of concurrent threads, value is the number of server logical CPUs x2, if the CPU supports H.T Hyper-threading, then X2
Thread_cache = 8 # #缓存可重用的线程数
skip-locking # Avoid MySQL external lock, reduce the chance of error increase stability.
[mysqldump]
Max_allowed_packet =16m # The maximum possible packets between the server and the client can be sent

[Myisamchk]
Key_buffer = 256M
Sort_buffer = 256M
Read_buffer = 2M
Write_buffer = 2M

Other optional parameters:
Back_log = 384
Specifies the number of possible connections for MySQL.
when the MySQL main thread receives very many connection requests in a very short time, this parameter takes effect and the main thread takes a short time to check the connection and start a new thread.
the value of the Back_log parameter indicates how many requests can be present in the stack for a short period of time before MySQL temporarily stops responding to a new request.
If the system has many connections in a short period of time, you need to increase the value of this parameter, which specifies the size of the listening queue for incoming TCP/IP connections.
attempting to set a limit of back_log above your operating system will be invalid. The default value is 50. For Linux systems, the recommended setting is an integer less than 512.
max_connections = n
The maximum number of database connections that the MySQL server processes concurrently (the default setting is 100). Too Many connections error is reported after the limit is exceeded
key_buffer_size = n
the RMA value used to hold the index chunk (the default setting is 8M), which increases the index (for all read and multiple writes) that it can be better processed
Record_buffer:
each thread that makes a sequential scan allocates a buffer of that size for each table it scans.
If you do a lot of sequential scans, you may want to increase the value. The default value is 131072 (128K)
wait_timeout:
The number of seconds the server waits for an action on a connection before shutting it down.
interactive_timeout:
The number of seconds the server waits for an action on an interactive connection before shutting it down.
an interactive customer is defined as a customer who uses the client_interactive option for Mysql_real_connect ().
The default value is 28800 and you can change it to 3600.
Skip-name-resolve
disable MySQL for DNS resolution of external connections, and Use this option to eliminate the time for DNS resolution for MySQL.
However, it is important to note that if this option is turned on, all remote host connection authorizations will use IP address mode, otherwise MySQL will not be able to handle the connection request properly!
log-slow-queries = Slow.log
log slow queries and then optimize for slow query one by one
Skip-innodb
skip-bdb
Close the unwanted table type, and if you need to, don't add this
          
          
# > SHOW VARIABLES like '%query_cache% ';
# > SHOW STATUS like ' qcache% ';
if the value of the qcache_lowmem_prunes is very large, it indicates that there is often insufficient buffering;
if the value of qcache_hits is very large, it indicates that the query buffer is used very frequently, and if the value is smaller it will affect efficiency, then you can consider not querying the buffer;
if the value of qcache_free_blocks is very large, it indicates a lot of fragmentation in the buffer.


##########################################
###### Max_allowed_packet ######
##########################################
A communication packet is a single SQL statement sent to the MySQL server, or a single line sent to the client.
The maximum possible packet that can be sent between the MySQL 5.1 server and the client is 1GB.
when a MySQL client or mysqld server receives packets that are larger than max_allowed_packet bytes, it issues a "packet too large" error and closes the connection. For some clients, if the traffic packet is too large, during the execution of the query, the "lost connection to the MySQL server" error can be encountered.
both the client and the server have their own max_allowed_packet variables, so if you intend to handle large packets, you must increase the variables on both the client and the server.
If you are using a MySQL client program, the default value for its max_allowed_packet variable is 16MB. To set a larger value, you can start MySQL in the following ways:
mysql> MySQL--max_allowed_packet=32m
It sets the size of the packet to 32MB.
the default Max_allowed_packet value for the server is 1MB. If the server needs to handle large queries, increase the value (for example, if you are preparing to handle large BLOB columns). For example, to set this to 16MB, you can start the server in the following way:
mysql> mysqld--max_allowed_packet=16m

You can also use the options file to set the Max_allowed_packet. To set this variable of the server to 16MB, add the following line to the options file:
[Mysqld]
max_allowed_packet=16m
it is safe to increase the value of this variable because additional memory is allocated only when needed. For example, MYSQLD will allocate more memory only if you issue a long query or if mysqld must return a large result row. This variable takes a smaller default value as a precaution to capture the error message packets between the client and the server and to ensure that memory overflows are not caused by accidental use of large packets.
If you are using a large BLOB value and do not grant mysqld access to enough memory for processing queries, you will also encounter strange problems with large packets. If you suspect that this is happening, try adding ulimit-d 256000 to the Mysqld_safe script and restarting Mysqld.
##########################################
##### MySQL How to open and close database tables #####
##########################################
Table_cache, Max_connections, and max_tmp_tables affect the maximum number of files that the server keeps open. If you add one or two of these values, you can encounter restrictions imposed on the number of open file descriptors for each process of your operating system. However, you can increase the limit on many systems. Ask your OS documentation to find out how to do this, because the methods of changing the limits vary greatly from one system to another.
Table_cache is concerned with max_connections. For example, for 200 open connections, you should have a table with a buffer of at least ten * n, where n is the maximum number of tables in a join.

The cache of open tables can be increased to a maximum value of Table_cache (default is 64; This can be used with mysqld-o table_cache= #选项来改变). A table is never closed unless the cache is full and another thread tries to open a table or if you use mysqladmin Refresh or mysqladmin flush-tables.
When the table cache is full, the server uses the following procedure to locate a cache entry to use:
Tables that are not currently being used are freed up to least recently used (LRU) order.
If the cache is full and no tables can be freed, but a new table needs to be opened, the cache must be temporarily expanded.
If the cache is in a temporary widening state and a table is removed from the in-use state, it is closed and freed from the cache.
Open a table for each concurrent access. This means that if you have 2 threads accessing the same table or accessing the table two times in the same query (with AS), the table needs to be opened two times. The first opening of any table accounts for 2 file descriptors, and each additional use of the table occupies only one file descriptor. For the first opening of an additional descriptor for the index file; This descriptor is shared across all threads

Mysql_ configuration file Detailed