Some configuration optimizations for MySQL

[Mysqld]
Lower_case_table_names=1
Datadir=/var/lib/mysql
Socket=/var/lib/mysql/mysql.sock
User=mysql
# Disabling Symbolic-links is recommended to prevent assorted security risks
Symbolic-links=0
#默认存储引擎
Default-storage-engine=innodb
# # #youhua

#skip-innodb

MySQL Open file descriptor limit, default minimum 1024, when Open_files_limit is not configured, compare the values of max_connections*5 and ulimit-n, which big use which,
# when OPEN_FILE_LIMIT is configured, compare the values of open_files_limit and max_connections*5, whichever is larger.
open_files_limit=10240

#back_log = 100

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
max_connections = 1000

# for the same host, if there is an interrupt error connection that exceeds the number of values for this parameter, the host will be blocked from connecting. Such as
Max_connect_errors = 5000

Table_cache = 100
external-locking = FALSE

#服务器发送和接受的最大包长度
Max_allowed_packet = 16M

# MySQL performs sorting using the buffer size. If you want to increase the speed of the order by, first see if you can let MySQL use the index instead of the extra sort stage.
# If you can't, try increasing the size of the sort_buffer_size variable
Sort_buffer_size = 128K
# The buffer size that can be used by the union query operation, as with Sort_buffer_size, the allocated memory for this parameter is also per connection exclusive
Join_buffer_size = 128K
# This value (default 8) means 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.
# If the thread is requested again, then the request will be read from the cache, if the cache is empty or a new request, then 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. (–> indicates the value to adjust)
# According to the physical memory setting rules are as follows:
# 1g-> 8
# 2g-> 16
# 3g-> 32
# Greater than 3g-> 64
thread_cache_size = 100

Thread_concurrency = 8

#MySQL的查询缓冲大小 (starting with 4.0.1, MySQL provides a query buffering mechanism) using query buffering, MySQL stores the SELECT statement and query results in a buffer,
# Future for the same SELECT statement (case sensitive), the result will be read directly from the buffer. Depending on the MySQL user manual, the use of query buffering can be up to 238% efficient.
# By checking the status value ' qcache_% ', you can know whether the query_cache_size setting is reasonable: If the value of Qcache_lowmem_prunes is very large, it indicates that there is often insufficient buffering,
# If the value of the qcache_hits is also very large, it indicates that the query buffer is used very frequently, the buffer size needs to be increased, and if the value of qcache_hits is small, your query repetition rate is very low.
# In this case, the use of query buffering can affect efficiency, so consider not querying the buffers. Additionally, adding Sql_no_cache in the SELECT statement can make it clear that the query buffer is not used
Query_cache_size = 128M
#指定单个查询能够使用的缓冲区大小, default 1M
Query_cache_limit = 2M

Query_cache_min_res_unit = 2k
Thread_stack = 192K

# MySQL heap (heap) table buffer size. All unions are completed within a DML instruction, and most unions can be done without a temporary table.
Tmp_table_size = 128M

Character-set-server=utf8

Local-infile=0

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

Innodb_additional_mem_pool_size = 8M

# to improve performance, MySQL can write log files to multiple files in a circular fashion. Recommended setting is 3
Innodb_log_files_in_group = 3
# Set: _log_file_size to% of buffer pool size

# This parameter determines the size of the data log file, larger settings can improve performance, but also increases the time required to recover the failed database
Innodb_log_file_size = 32M

Innodb_flush_method=o_direct

# This parameter determines the amount of memory used by the log files, in M. Larger buffers can improve performance, but unexpected failures will cause data loss. MySQL developer recommends setting the 1-8m between
Innodb_log_buffer_size = 10M

# InnoDB The main thread flushes the data in the cache pool so that the dirty data scale is less than 90%
innodb_max_dirty_pages_pct = 50

# 0: If the value of Innodb_flush_log_at_trx_commit is 0,log buffer, the log file will be written to disk every second, and no action is taken when committing the transaction (execution is performed by the MySQL master thread thread.)
# Redo log buffers are written to the disk's redo log file (REDO log) every second in the main thread. Whether the transaction has been committed or not, the default log file is Ib_logfile0,ib_logfile1
# 1: When set to the default value of 1, log buffer will be written to the log every time the transaction is committed.
# 2: If set to 2, each commit transaction will write the log, but will not perform the brush operation. The log file is brushed on a per-second schedule. Note that there is no guarantee that 100% per second will be brushed to disk, depending on the scheduling of the process.
# each time a transaction commits, the data is written to the transaction log, where the write is only called the file system, and the filesystem is cached, so this write does not guarantee that the data has been written to the physical disk
# The default value of 1 is to ensure complete acid. Of course, you can set this configuration to a value other than 1 in exchange for higher performance, but you will lose 1 seconds of data when the system crashes.
# set to 0, when the mysqld process crashes, it loses the last 1 seconds of the transaction. Set to 2, the last 1 seconds of data will be lost only if the operating system crashes or loses power. InnoDB will ignore this value when doing a recovery.
# Summary
# set to 1 is certainly the safest, but the performance page is the worst (as opposed to the other two parameters, but not unacceptable). If the data consistency and integrity requirements are not high, can be set to 2, if only for performance, such as high concurrent write log server, set to zero for higher performance
Innodb_flush_log_at_trx_commit = 2

# InnoDB transactions can wait a locked timeout number of seconds before being rolled back. InnoDB automatically detects the transaction deadlock in its own locking table and rolls back the transaction. InnoDB Use the Lock tables statement to notice the locking settings. The default value is 50 seconds
Innodb_lock_wait_timeout = 50

# InnoDB is a standalone tablespace pattern, and each table in each database generates a data space
# Standalone Table Space benefits:
# 1. Each table has a self-contained table space.
# 2. The data and indexes for each table will exist in the table space themselves.
# 3. You can implement a single table to move through different databases.
# 4. Space can be recycled (except for the drop table operation, the meter is not able to recycle)
Disadvantages
# single table increased too large, such as more than 100G
Conclusion
# shared tablespace has few advantages over insert operations. Others do not have a separate table space to perform well. When enabling a stand-alone tablespace, make reasonable adjustments: Innodb_open_files
innodb_file_per_table = 1
# Limit the data of the table that InnoDB can open, if the table in the library is particularly many cases, please add this. This value is 300 by default.
Innodb_open_files = 100

[Mysqld_safe]
Log-error=/var/log/mysqld.log
Pid-file=/var/run/mysqld/mysqld.pid
[Client]
Default-character-set=utf8
MySQL optimization parameters
There are comments on them.
Can look at the adjustment according to the parameters

Some configuration optimizations for MySQL