MySQL configuration file my.cnf detailed

MySQL Configuration detailed:

1, error log

--log-error[=file_name] Specifies the location of the mysqld (MySQL server) where the error log file is saved.

2, binary log

--log-bin[=file_name]mysqld writes the SQL command that contains all the updated data to the log file.

Binary logs need to be viewed with the Mysqlbinlog tool, with the following syntax:

#mysqlbinlog Log_file

--binlog-do-db=db_name If the current database is db_name, the record should be updated in the binary log

--binlog-ignore-db=db_name If the current database is db_name, the record should not be updated into the binary log

If you have multiple databases, you can do the following:

--binlog-do-db=db1

--binlog-do-db=db2

Master-Slave synchronization for a single library:

Replicate_do_db=test

replicate_wild_do_table=test.%

Or

Replicate_ignore_db=mysql

Replicate_wild_ignore_table=mysql.%

3, query log

All statements for the client are logged, and the binary log does not contain statements that only query the database

--log[=file_name] When Mysqld is started, the query log begins to be logged, and the default file name is Host_name.log

4, slow query log

Logs that contain all SQL statements set by the execution time over the parameter long_query_time (in seconds) do not count as execution time when the table lock is obtained

--log-slow-queries[=file_name]myqsld, the slow query log starts to be logged, the default file name is Host_name-slow.log

If there are many records in the slow query log, you can use the Mysqldumpslow tool to subtotal the slow query log

#mysqldumpslow ****-slow.log

5, detailed configuration file:

# VI/ETC/MY.CNF

The following lists only the contents of the [MYSQLD] paragraph in the my.cnf file, while the rest of the paragraphs have little impact on MySQL performance, so ignore them.

[Mysqld]

Character-set-server = UTF8

user = MySQL

Port = 3306

Socket =/data/mysql/3306/mysql.sock

Basedir =/usr/local/webserver/mysql

DataDir =/data/mysql/3306/data

Log-error =/data/mysql/3306/mysql_error.log

Pid-file =/data/mysql/3306/mysql.pid

Default-storage-engine = Myisam/innodb

Set the default storage engine

Skip-locking

Avoid the external locking of MySQL and reduce the chance of error to enhance stability.

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!

Skip-networking

Turn on this option to completely turn off the TCP/IP connection for MySQL and do not turn on this option if the CentOS system Web server is accessing the MySQL database server remotely. Otherwise it will not connect properly!

Skip-slave-start

Start MySQL, do not start replication

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. The default value is 50. For Linux systems, the recommended setting is an integer less than 512.

Max_allowed_packet = 16M

Set the maximum package, limit the size of packets accepted by the server, avoid the problem of long SQL execution, the default value is 16M, when the MySQL client or mysqld server receives packets larger than Max_allowed_packet bytes, it will issue a "packet too large" error, and close the connection. For some clients, if the communication packet is too large, you may encounter a "missing connection to the MySQL server" error during query execution.

Key_buffer_size = 256M

Specifies the size of the buffer to use for the index, which increases it to achieve better index processing performance. The parameter can be set to 256M or 384M for a server that has around 4GB. Note: This parameter value setting is too large to be the overall efficiency of the server down!

Sort_buffer_size = 6M

The size of the buffer that can be used when the query is sorted. Note: This parameter corresponds to the allocation of memory per connection exclusive! If there are 100 connections, the actual allocated total sort buffer size is 100X6=600MB. Therefore, the recommended setting for a server that has around 4GB is 6-8m.

Read_buffer_size = 4M

The size of the buffer 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!

Join_buffer_size = 8M

The size of the buffer that the Federated query operation can use, like sort_buffer_size, which allocates memory for each connection alone!

Myisam_sort_buffer_size = 64M

MyISAM table changes when the desired buffer is reordered for repair table.

Thread_stack = 256K

When each connection is created, MySQL allocates the memory to it. This value is generally considered to be applied to most scenarios by default, unless it is necessary to do so.

Thread_concurrency = 8

The parameter value is the number of server logical CPUs x2, in this case, the server has 2 physical CPUs, and each physical CPU supports H.T Hyper-threading, so the actual value is 4x2 = 8

Thread_cache_size = 64

Indicates that the number of threads stored in the cache can be re-used, and if there is room in the cache when disconnected, the client's thread will be placed in the cache, and if the thread is requested again, then the request will be read from the cache, and if the cache is empty or new, the thread will be recreated. If there are many new threads, increasing this value can improve system performance. You can see the effect of this variable by comparing the variables of the connections and threads_created states.

Table_cache = 512

Parameter sets the number of table caches. Each connection comes in with at least one table cache open. Therefore, the size of the Table_cache should be related to the Max_connections setting.

Query_cache_type determines the operating mode of the query cache. The following table shows the mode values that you can use:

Mode meaning

0 do not cache query results or retrieve cached results.

1 cache queries, unless they start with select Sql_no_cache.

2 only those queries that start with select Sql_cache are cached as needed.

Query_cache_size determines the amount of memory allocated to the cache, in bytes.

Query_cache_limit sets the maximum result set size that is cached, and query results that are larger than this value are not cached.

Query_cache_type=1

query_cache_size=16m

Even if the value of Query_cache_type is set to zero, query_cache_size specifies the amount of memory that is allocated. To avoid wasting memory, set the size to greater than 0 only when you want to activate the cache. Also, caching is disabled even if Query_cache_type is not zero, and the size of the query cache is set to zero.

Tmp_table_size = 256M

The default size is 32M. If a temporary table exceeds that size, MySQL produces an error in the form of the table tbl_name is, and if you do many advanced GROUP by queries, increase the tmp_table_size value.

Max_connections = 768

Specifies the maximum number of connection processes allowed by MySQL. If the too many connections error is frequently encountered when accessing the forum, you need to increase the parameter value.

Max_connect_errors = 10000

If a user initiates a connection error that exceeds this number, the user's next connection will be blocked until the administrator executes flush hosts; An order to prevent a hacker

Wait_timeout = 10

Specifies the maximum connection time for a request, which can be set to 5-10 for a server with about 4GB of memory.

This article is from the "Linuxdream" blog, make sure to keep this source http://books.blog.51cto.com/2600359/1550179

MySQL configuration file my.cnf detailed