MySql configuration file template and mysql configuration file

MySql configuration file template and mysql configuration file
This is the mysql configuration file my. cnf is commonly used in my current environment, including basic configurations and some optimizations. It has been recorded in my notes and I have never used to write a blog, recently I started to register a blog and posted these things. I want to use and learn from the supply and demand.

[Client]
Port = 3306
Socket =/data/mysqldata/mysql. sock
[Mysql] default-character-set = utf8 [mysqld] user = mysqlport = 3306character-set-server = utf8
Basedir =/usr/local/mysqldatadir =/data/mysqldataSocket =/data/mysqldata/mysql. socklog-error =/usr/local/mysql/log/error. log
# Whether to enable slow query logs. The default status is enable, 1, and 0.
Slow_query_log
# Slow query log timeout time: long_query_time = 3
# Whether to record query records without indexing (will be written into slow query logs)
Log_queries_not_using_indexes = 0
# The Log Path for slow query logs must be used with the preceding parameters.
Log-slow-queries =/usr/local/mysql/log/log-slow-queries.logpid-file =/data/mysqldata/mysql. pid
# Set default database engine default-storage-engine = INNODB
# Set the SQL mode: Prohibit grant from creating a user with an empty password. If the required storage engine is disabled or not compiled, an error is thrown, which is a strict select query GROUP BY operation, for details, refer to the network description SQL-mode = "NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION, ONLY_FULL_GROUP_BY"
# Max_connections = 300
# Specify the cache size of the query result query_cache_size = 32 M
# Specify the high-speed cache of the table. Each time a table is opened, the table is placed here to accelerate access. For the value setting, see open_tables. If the two are equal, this value should increase by table_open_cache = 512.
# Set the number of thread caches. If this value is small, MySQL frequently creates threads and consumes resources. For the configuration values, see 1G 8, 2G 16, and 3G 32... Thread_cache_size = 38
# If the temporary file will exceed the index, do not use the Quick Sort index method to create an index myisam_max_sort_file_size = 1G# MyISAM sets the buffer used when the TABLE is restored. repair table or the buffer allocated by sorting MyISAM indexes during the create index or alter table ProcessMyisam_sort_buffer_size = 64 M
# Set the buffer size of the index Block
Key_buffer_size = 290 M
# Buffer size for read query operations
Read_buffer_size = 1 M
# Set the random read buffer size
Read_rnd_buffer_size = 8 M
# Set the size of MySQL used for sorting
Sort_buffer_size = 1 M
# Annotations
Innodb_flush_log_at_trx_commit = 2
# Innodb log buffer size, recommended to be 1-8 M
Innodb_log_buffer_size = 4 M
# Innodb buffer size. A larger value can reduce disk I/O, which is generally set to 80% of memory size.
Innodb_buffer_pool_size = 2G
# Set the log file size of innodb. If it is too large, data recovery will be slow in the future.
Innodb_log_file_size = 512 M
# Concurrency limit. If it is set to 0, no concurrency limit is set.
Innodb_thread_concurrency = 18
# Set innodb to the independent tablespace mode, that is, each table uses a single tablespace, which is easy to maintain,
Innodb_file_per_table = 1
# Setting the innodb File Format
Innodb_file_format = Barracuda
# Interactive connection timeout, in seconds. The default value is 8 hours.
Interactive_timeout =86400
# Non-interactive connection timeout
Wait_timeout = 2147482
# Maximum allowed package size,
Max_allowed_packet = 12 M
# Do not use DNS to parse the connection
Skip_name_resolve
# The following two options are used to set the number of read/write I/O threads based on the number of CPU cores.
Innodb_write_io_threads = 4
Innodb_read_io_threads = 4 # binloglog-bin =/data/mysqldata/mysql-client-binserver-id = 1 [mysqldump] max_allowed_packet = 512 M

Note:

Innodb_flush_log_at_trx_commit:

#0: If the innodb_flush_log_at_trx_commit value is 0, the log buffer will be written to the disk every second, no operation is performed when the transaction is committed (the execution is performed by the mysql master thread.
# The redo log buffer is written to the redo log file of the disk every second in the main thread. Whether the transaction has been committed or not) the default log file is ib_logfile0 and ib_logfile1.
#1: when it is set to the default value of 1, the log buffer will be flushed to the log each time a transaction is committed.
#2: if it is set to 2, logs are written every time a transaction is committed, but the fl operation is not performed. The logs are flushed to every second. Note that it is not guaranteed that the disk will be flushed every 100% seconds, which depends on the process scheduling.
# Data is written to the transaction log every time a transaction is committed, and the write here only calls the write operation of the file system, and the file system has a cache, therefore, this write operation does not guarantee that the data has been written to the physical disk.
# The default value 1 is used to ensure the complete ACID. Of course, you can set this configuration item to a value other than 1 in exchange for higher performance, but when the system crashes, you will lose 1 second of data.
# If it is set to 0, the transaction in the last second will be lost when the mysqld process crashes. If this parameter is set to 2, data of the last second will be lost only when the operating system crashes or the power is down. InnoDB ignores this value during restoration.
# Conclusion
# Setting 1 is of course the safest, but the performance page is the worst (not acceptable to the other two parameters ). If you do not have high requirements on data consistency and integrity, you can set it to 2. If you only want performance, such as a log server with high concurrent writes, set it to 0 to achieve higher performance.