MySQL configuration file my. cnf learning notes

The mysql configuration file is my. cnf, which is located according to the settings during installation. When the mysqld service is started, the configuration file is read in a certain order by default.

The mysql configuration file is my. cnf, which is located according to the settings during installation. When the mysqld service is started, the configuration file is read in a certain order by default.

The mysql configuration file is my. cnf, which is located according to the settings during installation.
When the mysqld service is started, the configuration file is read in a certain order by default.

[Root @ zhu2 ~] #/Opt/mysql/libexec/mysqld -- verbose -- help
Default options are read from the following files in the given order:
/Etc/my. cnf/etc/mysql/my. cnf/opt/mysql/etc/my. cnf ~ /. My. cnf

You can find that the system defaults to/etc/my. cnf -----/etc/mysql/my. cnf ----/usr/local/mysql/my. cnf reads configuration files sequentially. When multiple configuration files exist, mysql will take the parameters in the last configuration file as the standard.

Common configuration parameters include:

Port = 3306

The port number when the mysqld service is running. The default value is 3306.

Socket =/tmp/mysql. sock

The socket file is exclusive to linux/unix systems. In this environment, the client connection can be directly connected through the socket file instead of through the TCP/IP network.

Back_log = 300

This value sets the number of requests that can exist in the stack before mysql temporarily stops responding to new requests. If the system has many connections in a short time, this value should be increased. It is best to set an integer smaller than 512.

Skip-networking

It does not listen on the TCP/IP Port. All connections are connected through the local socket file, which improves security and makes sure that the database cannot be connected through the network.

Skip-locking

Avoid mysql external lock and enhance stability
Skip-name-resolve

Avoid using DNS resolution for external connections in mysql. If you use this setting, you can only use ip addresses for remote host connection, but not domain names.

Max_connections = 3000

The maximum number of connection processes allowed by the mysql service,

Max_connect_errorrs = 1000

The number of times that a host connection can be abnormally interrupted. When the number of times exceeds this limit, the mysql server will disable connection requests from the host until the mysql service is restarted or the flushhosts command clears host information.

Table_cache = 614 k

The size of the table's high-speed buffer. when mysql accesses a table, if there is still space in the mysql table buffer, the table will be opened and placed in the high-speed buffer, the advantage is that you can access the table content more quickly.
If the values of open_tables and opened_tables are close to this value, the size of this value is increased for a long time.

Max_allowed_packet = 4 M

Sets the maximum value of a message that can be transmitted during network transmission. The default value is 1 MB, and the maximum value is 1 GB.

Sort_buffer_size = 16 M

The sort buffer is used to process sorting caused by orderby and groupby queues. The default size is 2 MB. The memory allocated for this parameter is exclusive to each connection. If there are 100 connections, the size of the actually allocated sort buffer is 6*100. We recommend that you set it to 6 M-8 M.

Join_buffer_size 8 M

The size of the buffer used by the Joint query operation.

Thread_cache_size = 64

Sets the maximum number of threads that can be cached in the threadcache pool. The default value is 0. This value indicates that the number of threads stored in the cache can be reused. If there is space in the cache when the connection is disconnected, the client thread will be put into the cache. If the thread is requested again, the request will be read from the cache. If the cache is empty or a new request, the thread will be re-created. The setting rule is: 1 GB memory is set to 8, 2 GB memory is set to 16, and 4 GB memory is set to 64

Query_cache_size = 64 M

Specify the size of the mysql Query Buffer to buffer the select results, and then return the results directly without executing the query in the next same query. Based on the Qcache_lowmem_prunes size, to check whether the current load is high enough

Query_cache_limit = 4 M

Only results smaller than this value are buffered. place a large result to overwrite all other query results.
Tmp_table_size 256 M

The size of the temporary memory table. If this value is exceeded, the temporary table is written to the disk.

Default_storage_engine = MYISAM

Storage engine used by default when creating a table

Log-bin = mysql-bin

Enable the binary log function

Key_buffer_size = 384 M

Specify the size of the index buffer. The memory is set to 384 Mb or MB at 4 GB.

Read_buffer_size = 8 M

Buffer size used for full table scan of MYISAM table
.........

Related reading:

Description of configuration items in the my. cnf file of MySQL

Comment on the MySQL my. cnf configuration file

MySQL my. cnf parameter configuration optimization

Linux MySQL 4G memory my. cnf configuration table

For more details, please continue to read the highlights on the next page: