Detailed introduction to MySQL configuration parameters

This article brings you a detailed introduction to MySQL configuration parameters. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

# The following options will be read by the MySQL client application.

# Note that only the client application that comes with mysql is guaranteed to be able to read this content.

# If you want your own MySQL application to get these values.

# These options need to be specified when the MySQL client library is initialized.

#[client]

#password = [your_password]

port = @MYSQL_TCP_PORT@

socket = @MYSQL_UNIX_ADDR@

# *** Application Customization Options ***

## MySQL Server

#[mysqld]

# General configuration options

port = @MYSQL_TCP_PORT@

socket = @MYSQL_UNIX_ADDR@

# back_log is what the operating system can maintain in the listening queue Number of connections,

# The queue saves the connections before being processed by the MySQL connection manager thread.

# If you have a very high connection rate and a "connection refused" error occurs,

# You should increase the value here.

# Check your operating system documentation to get the maximum value of this variable.

# If you set back_log to a value higher than your operation Higher system limit values ​​will have no effect

back_log = 300

# Do not listen on the TCP/IP port.

# If all processes are on The same server is connected to the local mysqld.

# This setting will be a way to enhance security

# All mysqld connections are made through Unix Sockets or named pipes.

# Note that if you do not turn on the named pipe option under Windows and just use this option

# (via the "enable-named-pipe" option), the MySQL service will have no effect!

#skip-networking

# The upper limit of the number of simultaneous sessions allowed by the MySQL service

# One of the connections will be reserved with SUPER permissions and logged in as an administrator.

# Even if The upper limit of the number of connections has been reached.

max_connections = 3000

# The maximum number of errors allowed for each client connection, if this limit is reached.

# This client The client will be blocked by the MySQL service until "FLUSH HOSTS" is executed or the service is restarted

# Illegal passwords and other errors during connection will increase this value.

# See "Aborted_connects" Status to get the global counter.

max_connect_errors = 50

# The number of tables opened by all threads.

# Increasing this value increases the file descriptors required by mysqld The number

#So you need to confirm that the "open-files-limit" variable setting in [mysqld_safe] allows the number of open files to be at least equal to the value of table_cache

table_open_cache = 4096

# Allow external file-level locks. Turning on file locks can have a negative impact on performance

# So only use this option if you are running multiple database instances on the same file (note that there will still be other Constraints!)

# Or you use some other software dependencies at the file level to lock the MyISAM table

#external-locking

# The request package that the service can handle The maximum size and the maximum request size that the service can handle (necessary when working with large BLOB fields)

# Independent size for each connection, the size increases dynamically

max_allowed_packet = 32M

# The cache size held by binlog in a transaction to record SQL status

# If you often use large, multi-statement transactions, you can increase this value to obtain Greater performance.

# All status from the transaction will be buffered in the binlog buffer and then written to the binlog once after submission.

# If the transaction is larger than this value , a temporary file on disk will be used instead.

# This buffer is created when each connected transaction first updates its status

binlog_cache_size = 4M

# The maximum capacity allowed by an independent memory table.

# This option is to prevent accidental creation of an overly large memory table that will permanently use up all memory resources.

max_heap_table_size = 128M

# Random read data buffer uses memory (read_rnd_buffer_size): Corresponding to sequential reading,

# When MySQL performs non-sequential reading (random reading) of data blocks, it will use > The buffer temporarily stores the read data

# For example, read the table data based on the index information, join the table based on the sorted result set, etc.

# In general, when When the reading of data blocks needs to meet a certain order, MySQL needs to generate random reads and then use the memory buffer set by the read_rnd_buffer_size parameter

read_rnd_buffer_size = 16M

# The sort buffer is used to handle sorting caused by ORDER BY and GROUP BY queues

# If the sorted data cannot be put into the sort buffer, a user Instead disk-based merge sorting will be used

# See the "Sort_merge_passes" state variable.

# Allocated by each thread when sorting occurs

sort_buffer_size = 16M

# This buffer is used to optimize full joins (FULL JOINS joints without indexes).

# Similar joins have very poor performance in most cases, but will Setting this value large can reduce the performance impact.

# View the number of full joins through the "Select_full_join" state variable

# When a full join occurs, allocate it in each thread

join_buffer_size = 16M

# How many threads do we reserve in the cache for reuse

# When a client disconnects, if the threads in the cache are less than thread_cache_size, the client The end thread is put into the cache.

# This can greatly reduce the overhead of thread creation when you need a large number of new connections

# (Generally speaking, if you have good threads model, this will not result in a significant performance improvement.)

thread_cache_size = 16

# This allows the application to give the threading system a hint as to the number of threads that are eager to be running at the same time.

# This value is only meaningful for systems that support the thread_concurrency() function (such as Sun Solaris).

# You can try using [Number of CPUs]*(2..4) to As the value of thread_concurrency

thread_concurrency = 8

# The query buffer is often used to buffer the results of SELECT and return the results directly without executing the same query next time.

# Turning on the query cache can greatly improve server speed if you have a large number of identical queries and rarely modify the table.

# Check the "Qcache_lowmem_prunes" status variable to check whether the current value is suitable for your load Is it high enough?

# Note: If your table changes frequently or if your query text is different every time,

# Query buffering may cause performance degradation rather than performance improvement.

query_cache_size = 128M

# Only results smaller than this setting will be buffered

# This setting is used to protect the query cache and prevent a huge result set Override all other query results.

query_cache_limit = 4M

# The minimum word length to be indexed by full-text search.

# You may wish to reduce it if you When you need to search for shorter words.

# Note that after you modify this value, you need to rebuild your FULLTEXT index

ft_min_word_len = 8

# If your system Supports the memlock() function. You may want to turn this option on to allow running mysql to keep data locked in memory and prevent it from being swapped out

# This option is good for performance when memory is highly tight Useful

#memlock

# As the table type used by default when creating a new table,

# If there is no special execution of the table type when creating a table, this value will be used

default_table_type = InnoDB

# The heap size used by the thread. This amount of memory is reserved for each connection.

# MySQL itself usually does not need to exceed 64K Memory

# If you use your own UDF functions that require a large amount of heap or your operating system requires more heap for certain operations, you may need to set it higher.

thread_stack = 512K

# Set the default transaction isolation level. The available levels are as follows:

# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

transaction_isolation = REPEATABLE-READ

# Maximum size of internal (in-memory) temporary table

# If a table grows larger than this value, it will automatically be converted to disk-based Table.

# This limit is for a single table, not the total.

tmp_table_size = 128M

# Turn on the binary log function.

# In In the replication configuration, as the MASTER master server, this must be turned on

# If you need to do point-in-time recovery from your last backup, you also need binary logs.

log-bin=mysql-bin

# If you are using the chained replication mode from the server structure (A->B->C),

# You need to Turn this on B.

# This option opens the log of updates redone on the slave thread and writes them to the binary log of the slave server.

#log_slave_updates

# Turn on full query logging. All queries received by the server (even for a query with incorrect syntax)

# will be logged. This is useful for debugging and is often turned off in production environments. This item.

#log

# Print warnings to the error log file. If you have any questions about MySQL

# you should open the warning log and carefully review the error log to find out possible Reason.

#log_warnings

# Log slow queries. Slow queries refer to queries that consume more time than "long_query_time" defined.

# If log_long_format is turned on, queries that do not use indexes will also be logged.

# This is generally a good idea if you often add new queries to an existing system.

log_slow_queries

# All queries that take more than this time (in seconds) will be considered slow queries.

# Do not use "1" here, otherwise it will Causes all queries, even very fast query pages, to be recorded (since MySQL's current time accuracy can only reach the second level).

long_query_time = 6

# in the slow log Record more information.

# It is generally best to turn this item on.

# Turning this item on will record queries that do not use indexes and will be appended to slow queries as slow queries.

log_long_format

# This directory is used by MySQL to save temporary files. For example,

# It is used to handle large disk-based sorting, and internal sorting Same.

# As well as simple temporary tables.

# If you don't create very large temporary files, it may be better to place them on the swapfs/tmpfs file system

# Another option is that you can also place it on a separate disk.

# You can use ";" to place multiple paths

# They will follow roud-robin The method is polled and used.

#tmpdir = /tmp

# *** Master-slave replication related settings

# The unique service identification number, the value is between 1 and Between 2^32-1.

# This value needs to be set on both the master and slave.

# If "master-host" is not set, it defaults to 1, but if Ignore this option, MySQL will not take effect as the master.

server-id = 1

# Replicated Slave (remove the comments in the master section to make it effective)

## In order to configure this host as a replicated slave server, you can choose two methods:

# 1) Use the CHANGE MASTER TO command (in our manual Full description in ) -

# The syntax is as follows:

## CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,

# MASTER_USER=, MASTER_PASSWORD = ;

# You need to replace , , and other fields surrounded by angle brackets and replace them with the master’s port number (default 3306).

## Example:

## CHANGE MASTER TO MASTER_HOST='125.564.12.1′, MASTER_PORT=3306,

# MASTER_USER='joe', MASTER_PASSWORD ='secret';

## or

# 2) Set the following variables. No matter what, when you choose this method case, and then start replication for the first time (even in the case of unsuccessful,

# For example, if you entered the wrong password in the master-password field and the slave cannot connect),

# slave A master.info file will be created, and any subsequent changes to the parameters contained in this file will be ignored

# and overwritten by the contents of the master.info file, unless you turn off the slave service and delete the master .info and restart the slave service.

# For this reason, you may not want to touch the configuration (commented out) and use CHANGE MASTER TO (see above) instead

## The unique id number required is between 2 and 2^32 – 1

# (and different from master)

# If master-host is set. Then The default value is 2

# but if omitted, it will not take effect

#server-id = 2

## The master in the replication structure – Must

#master-host =

## The username used by slave to authenticate when connecting to the master – Must be

#master -user =

## Password used by slave to authenticate when connecting to master – must

#master-password =

## The port that the master listens on.

# Optional – the default is 3306

#master-port =

# Makes the slave read-only. Only owned by the user SUPER permission and the slave thread on it can modify the data.

# You can use this to ensure that no application will accidentally modify the data on the slave instead of the master

#read_only

#*** MyISAM related options

# The size of the keyword buffer is generally used to buffer the index block of the MyISAM table.

# Do not set it larger than your available memory 30%,

# Because part of the memory is also used by the OS to buffer row data

# Even if you are not using MyISAM tables, you still need to set up 8-64M memory because it will also be used by internal temporary disk tables.

key_buffer_size = 128M

# The buffer size used for full table scan of MyISAM table.

# When a full table scan is required, it is allocated in the corresponding thread.

read_buffer_size = 8M

# When rows are read from an already sorted sequence after sorting, the row data will be read from this buffer to prevent disk seeks.

# If you increase this value, it can be improved a lot Performance of ORDER BY.

# Allocated by each thread when needed

read_rnd_buffer_size = 64M

# MyISAM uses a special tree-like cache to enable burst inserts

# (These inserts are, INSERT … SELECT, INSERT … VALUES (…), (…), …, and LOAD DATA INFILE) faster.

# This variable is limited to each process The number of bytes of the buffer tree.

# Setting it to 0 will turn off this optimization.

# For optimization, do not set this value larger than "key_buffer_size".

# When This buffer will be allocated when a burst insert is detected.

bulk_insert_buffer_size = 256M

# This buffer is caused when MySQL requires REPAIR, OPTIMIZE, ALTER and LOAD DATA INFILE into an empty table Allocated when rebuilding the index.

# This is allocated in each thread. So you need to be careful when setting large values.

myisam_sort_buffer_size = 256M

# MySQL Rebuilding the Index The maximum temporary file size allowed at the time (when REPAIR, ALTER TABLE or LOAD DATA INFILE).

# If the file size is larger than this value, the index will be created through key-value buffering (slower)

myisam_max_sort_file_size = 10G

# If the temporary file used to create the index faster is larger than the specified value, then use the key value buffer method.

# This is mainly Used to force long string keys in large tables to use the slow key-value buffering method to create indexes.

myisam_max_extra_sort_file_size = 10G

# If a table has more than one index, MyISAM can Use more than one thread to repair them by parallel sorting.

# This is a good choice for users with multiple CPUs and large amounts of memory.

myisam_repair_threads = 1

# Automatically check and repair MyISAM tables that were not closed properly.

myisam_recover

# Closed by default Federated

skip-federated

# ** * BDB related options ***

# Use this option if the MySQL service you are running has BDB support but you are not going to use it. This will save memory and may speed up some things.

skip-bdb

# *** INNODB related options ***

# If your MySQL service includes InnoDB support but you do not plan to use it,

# Use this Options will save memory and disk space, and speed up some parts

#skip-innodb

# The additional memory pool is used by InnoDB to save metadata information (no longer recommended in 5.6)

# If InnoDB needs more memory for this purpose, it will start requesting memory from the OS.

# Since this operation is fast enough on most modern operating systems, you generally do not This value needs to be modified.

# The SHOW INNODB STATUS command will display the currently used amount.

innodb_additional_mem_pool_size = 64M

# InnoDB uses a buffer pool to save indexes and original data , unlike MyISAM.

# The larger you set here, this can ensure that you use memory instead of hard disk for most read operations. The disk I required when accessing data in the table /O The less.

# On a stand-alone database server, you can set this variable to 80% of the server’s physical memory size

# Do not set it too large, otherwise, due to physical Memory competition may cause paging thrashing in the operating system.

# Note that on a 32-bit system you may be limited to 2-3.5G user-level memory limit per process,

# So Don’t set it too high.

innodb_buffer_pool_size = 6G

# InnoDB saves data in one or more data files as table spaces.

# If you only have a single logic For the driver to save your data, a single auto-increment file is good enough.

# In other cases, one file per device is generally a good choice.

# You can also Configure InnoDB to use bare disk partitions – please refer to the manual for more information

innodb_data_file_path = ibdata1:10M:autoextend

# Set this option if you want InnoDB tablespace files to be saved in Other partitions.

# are saved in MySQL’s datadir by default.

#innodb_data_home_dir =

# The number of IO threads used to synchronize IO operations.

# This value is hard-coded to 8 under Unix, but on Windows disk I/O is possible Performs better with a larger value.

innodb_file_io_threads = 8

# If you find that the InnoDB tablespace is corrupted, setting this value to a non-zero value may help you export your table.

# Start with 1 and increase this value until you can successfully export the table.

#innodb_force_recovery=1

# The number of threads allowed within the InnoDb core.

# The optimal value depends on the application, hardware, and operating system scheduling methods.

# Too high a value may cause thread mutual exclusion thrashing.

innodb_thread_concurrency = 16

# If set to 1, InnoDB will flush (fsync) the transaction log to disk after each commit,

# This provides complete ACID behavior.

# If you are willing to compromise transaction safety, and you are running a small transaction, you can set this value to 0 or 2 to reduce the disk I/O caused by the transaction log

# 0 means the log will only log approximately every Seconds are written to the log file and the log file is flushed to disk.

# 2 means that the log is written to the log file after each commit, but the log file is only flushed to disk approximately every second.

innodb_flush_log_at_trx_commit = 2

(Note: If it is a game server, it is recommended to set this value to 2; if it is an application with extremely high data security requirements, it is recommended to set it to 1; setting it to 0 has the highest performance, but if In the event of a failure, data may be lost! The default value of 1 means that every transaction commit or instruction outside the transaction needs to write the log to the hard disk (flush), which is very time-consuming. Especially when using battery-powered cache ( Battery backed up cache). Set to 2 for many applications, especially those transferred from MyISAM tables. It means that it is not written to the hard disk but written to the system cache. The log will still be flushed to the hard disk every second. So you generally won't lose updates that take more than 1-2 seconds. Setting it to 0 will be faster, but it is less secure. Even if MySQL hangs, you may lose transaction data. A value of 2 will only hang the entire operating system. Data may be lost only when it is closed.)

# Accelerate the shutdown of InnoDB. This will prevent InnoDB from doing full clearing and insert buffer merging when shutting down.

# This may greatly increase the shutdown time. But instead InnoDB may do these operations the next time it starts.

#innodb_fast_shutdown

# The size of the buffer used to buffer log data.

# When this value When almost full, InnoDB will have to flush data to disk.

# Since it will basically be flushed every second, there is no need to set this value too high (even for long transactions)

innodb_log_buffer_size = 16M

# The size of each log file in the log group.

# You should set the total log file size to 25%~100% of your buffer pool size

# to avoid unnecessary buffer pool flushing on log file overwrites.

# However, please note that a large log file size will increase the time required for the recovery process.

innodb_log_file_size = 512M

# The total number of files in the log group.

# Generally speaking, 2~3 is better.

innodb_log_files_in_group = 3

# The location of InnoDB’s log file. The default is MySQL’s datadir.

# You can specify it on a separate hard disk or a RAID1 volume to improve its performance

#innodb_log_group_home_dir

# The maximum allowed proportion of dirty pages in the InnoDB buffer pool.

# If the limit is reached, InnoDB will start flushing them to prevent them from interfering with clean data pages.

# This is a soft limit and is not guaranteed to be executed absolutely.

innodb_max_dirty_pages_pct = 90

# The method used by InnoDB to refresh the log.

# Table Spaces always use the dual write flush method

# The default value is "fdatasync", the other is "O_DSYNC".

# Generally speaking, if you have a hardware RAID controller, and Its independent cache uses the write-back mechanism and has battery power-off protection, so it should be set to O_DIRECT

# Otherwise, in most cases it should be set to fdatasync

#innodb_flush_method= fdatasync

# How long an InnoDB transaction should wait for a lock to be granted before being rolled back.

# InnoDB automatically detects transaction deadlocks in the lock table it owns and rolls back the transaction .

# If you use the LOCK TABLES directive, or use a transaction-safe storage engine other than InnoDB in the same transaction

#, then a deadlock may occur without InnoDB noticing.

# In this case, this timeout value is very helpful to solve this problem.

innodb_lock_wait_timeout = 120

# This setting tells InnoDB whether it needs to store the data and indexes of all tables in a shared table space (innodb_file_per_table = OFF) or place the data of each table in a separate .ibd file (innodb_file_per_table = ON)

# One file per table allows you to reclaim disk space when dropping, truncate or rebuilding tables

# This is also necessary for some advanced features, such as data compression, but it does not bring Any performance gain

innodb_file_per_table = on

[mysqldump]

# Do not cache the entire result in memory before writing it to disk. Required when exporting very huge tables This item

quick

max_allowed_packet = 32M

[mysql]

no-auto-rehash

# Only allows the use of key values of UPDATEs and DELETEs .

#safe-updates

[myisamchk]

key_buffer = 16M

sort_buffer_size = 16M

read_buffer = 8M

write_buffer = 8M

[mysqlhotcopy]

interactive-timeout

[mysqld_safe]

# Increase the number of times each process can Number of open files.

# Warning: Make sure you have set the system-wide limit high enough!

# You need to set this value high to open a large number of tables

open- files-limit = 8192

The above is the detailed content of Detailed introduction to MySQL configuration parameters. For more information, please follow other related articles on the PHP Chinese website!