Shulou(Shulou.com)05/31 Report--

This article mainly explains the "detailed explanation of my.cnf in mysql". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the detailed explanation of my.cnf in mysql".

# * client options related options * *

# the following options will be read by the MySQL client application. Note that only the client application that ships with MySQL guarantees that this can be read. If you want your own MySQL application to get these values. You need to specify these options when the MySQL client library is initialized.

[client]

Port = 3309

Socket = / usr/local/mysql/tmp/mysql.sock

[mysqld]

! include / usr/local/mysql/etc/mysqld.cnf

# include configuration files and store user name and password files separately

Port = 3309

Bind-address = 0.0.0.0

Server-id = 1

# indicates that the serial number of this machine is 1, unique

Socket = / usr/local/mysql/tmp/mysql.sock

Pid-file = / usr/local/mysql/var/mysql.pid

Basedir = / usr/local/mysql/

Datadir = / usr/local/mysql/var/

Tmpdir = / usr/local/mysql/tmp/

This directory is used by MySQL to save temporary files. For example, it is used to handle large disk-based sorting, like internal sorting, and simple temporary tables. If you don't create a very large temporary file, it might be better to put it 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, which will be polled according to the roud-robin method.

Slave-load-tmpdir = / usr/local/mysql/tmp/

# use # * skip options related options * * when slave executes load data infile

Skip-name-resolve

# prohibit MySQL from parsing DNS for external connections. Use this option to eliminate the time for MySQL to parse DNS. It should be noted, however, that if this option is turned on, all remote host connection authorizations must use IP addresses, otherwise MySQL will not be able to handle connection requests properly!

Skip-symbolic-links

# connection files cannot be used, and multiple customers may access the same database, so this prevents external customers from locking down the MySQL server. This option is enabled by default

Skip-external-locking

# do not use system locking, to use myisamchk, you must shut down the server to avoid external locking of MySQL, reduce the probability of error and enhance stability.

Skip-slave-start

# start mysql without starting replication

Skip-networking

# enable this option to completely turn off the TCP/IP connection mode of MySQL. Do not turn on this option if the WEB server accesses the MySQL database server by remote connection. Otherwise, you will not be able to connect properly! If all processes connect to the local mysqld on the same server, this setting will be a way to enhance security

Sysdate-is-now = 1

# programming the SYSDATE function as an alias for NOW

# * system resource related options *

Back_log = 50

# accept the queue, put the request queue without establishing a tcp connection into the cache, the queue size is back_log, restricted with the OS parameter, trying to set the limit of back_log higher than your operating system will be invalid. The default value is 50. It is recommended to set an integer less than 512 for Linux systems. If the system has many connections in a short period of time, you need to increase the value of this parameter.

Max_connections = 1000

# specify the maximum number of connection processes allowed by MySQL. If the "Too Many Connections" error occurs frequently when accessing the database, you need to increase the parameter value.

Max_connect_errors = 10000

# if a user initiates a connection error that exceeds this value, the user's next connection will be blocked until the administrator executes flush hosts; commands or service restarts to prevent hackers, illegal passwords and other errors in the link will increase this value

Open_files_limit = 10240

The file descriptor limit opened by # MySQL is 1024 by default. When open_files_limit is not configured, compare the values of max_connections*5 and ulimit-n, which is the most useful, and when open_file_limit is configured, compare the values of open_files_limit and max_connections*5, which is the most useful.

Connect-timeout = 10

# the maximum number of seconds before the connection timeout, which is also used as the time to wait for the first response from the server on Linux platforms

Wait-timeout = 28800

# time to wait for connection to be closed

Interactive-timeout = 28800

# allow interactive_timeout (instead of wait_timeout) seconds of inactivity before closing the connection. The client's session wait_timeout variable is set to the value of the session interactive_timeout variable. If the front-end program uses a short connection, it is recommended to shorten these two values. If the front-end program uses a long connection, you can comment out these two options directly. The default configuration (8 hours)

Slave-net-timeout = 600,

# the slave server can also handle network connection interruptions. However, a network outage is notified only if the slave server does not receive data from the master server for more than slave_net_timeout seconds.

Net_read_timeout = 30

# timeout for reading information from the server

Net_write_timeout = 60

# timeout for writing information from the server

Net_retry_count = 10

# if the read operation of a communication port is interrupted, retry several times before giving up

Net_buffer_length = 16384

# packet message buffer is initialized to net_buffer_length bytes, but can be increased to max_allowed_packet bytes if needed

Max_allowed_packet = 64m

# the maximum size of the request packet that the service can handle and the maximum request size that the service can handle (necessary when working with large BLOB fields), the independent size of each connection. The size increases dynamically. Set the maximum packet, limit the packet size accepted by server, and avoid the execution of ultra-long SQL. The default value is 16m when MySQL client or mysqld

When the server receives a packet larger than max_allowed_packet bytes, it issues a "packet too large" error and closes the connection. For some clients, if the communication packet is too large, you may encounter a "lost connection to the MySQL server" error during query execution. The default is 16m.

Table_cache = 512

# the number of tables opened by all threads. Increasing this value increases the number of file descriptors required by mysqld, so you need to make sure that the "open-files-limit" variable setting in [mysqld_safe] allows at least 4096 open files.

Thread_stack = 19K

# the heap size used by the thread. This capacity of memory is reserved for each connection. MySQL itself often does not require more than 64K of memory. If you use your own UDF function that requires a lot of heap or your operating system needs more heap for some operations, you may need to set it higher. The default settings are sufficient for most applications

Thread_cache_size = 20

# how many threads do we keep for reuse in cache? When a client disconnects, if there are fewer threads in cache than in thread_cache_size, the client thread is put into cache. This can greatly reduce the overhead of thread creation when you need a large number of new connections (generally speaking, if you have a good thread model

There will be no significant performance improvement.) The server thread cache this value indicates that the number of threads saved in the cache can be reused. If there is space in the cache when disconnected, the client thread will be placed in the cache, and if the thread is re-requested, the request will be read from the cache, and if the cache is empty or a new request, the thread will be recreated.

If there are many new threads, increasing this value can improve system performance. By comparing the variables of Connections and Threads_created states, we can see the role of this variable.

The rules for setting up physical memory are as follows:

1G-> 8

2G-> 16

3G-> 32

Greater than 3G-> 64

Thread_concurrency = 8

This allows the application to give the thread system a prompt for the number of threads that are eager to be run at the same time. The value of this parameter is the number of server logical CPU × 2. In this example, the server has two physical CPU, and each physical CPU supports H.T hyperthreading, so the actual value is 4 × 2 = 8. Set whether the value of thread_concurrency is correct or not

It has a great impact on the performance of mysql. In the case of multiple cpu (or multicore), setting the value of thread_concurrency incorrectly will cause mysql to fail to make full use of multiple cpu (or multicore), and only one cpu (or core) can work at a time. Thread_concurrency should be set to 2 times the number of CPU kernels. For example, there is a dual-core CPU

Then the value of thread_concurrency should be 4; the value of 2 dual-core cpu,thread_concurrency should be 8, which is a key optimization parameter.

# * qcache settings related options * *

Query_cache_limit = 2m

# does not cache results whose query is greater than this value. Only results less than this setting are buffered. This setting is used to protect the query buffer from overwriting all other query results by a large result set.

Query_cache_min_res_unit = 2K

# the minimum block size allocated by query cache. The default is 4KB. Setting a large value is good for big data queries, but if your queries are small data queries, it is easy to cause memory fragmentation and waste.

Query cache fragmentation rate = Qcache_free_blocks / Qcache_total_blocks * 100%

If the query cache fragmentation rate exceeds 20%, you can use FLUSH QUERY CACHE to defragment the cache, or try to reduce query_cache_min_res_unit, if your query is a small amount of data.

Query cache utilization = (query_cache_size-Qcache_free_memory) / query_cache_size * 100%

If the query cache utilization is below 25%, it means that the query_cache_size setting is too large and can be reduced appropriately; if the query cache utilization is above 80% and the Qcache_lowmem_prunes is more than 50, the query_cache_size may be a little small, or there may be too many fragments.

Query cache hit ratio = (Qcache_hits-Qcache_inserts) / Qcache_hits * 100%

Query_cache_size = 64m

# specify the size of the MySQL query buffer. You can observe by executing the following command in the MySQL console:

Code:

> SHOW VARIABLES LIKE'% query_cache%'

> SHOW STATUS LIKE 'Qcache%'; if the value of Qcache_lowmem_prunes is very large, it indicates that insufficient buffering often occurs.

If the value of Qcache_hits is very large, it indicates that query buffering is used very frequently, and if the value is small and affects efficiency, then consider not query buffering; Qcache_free_blocks, if the value is very large, it indicates that there is a lot of fragmentation in the buffer.

Memlock # if your system supports the memlock () function, you may want to turn on this option to keep the running mysql at memory height

In times of stress, data remains locked in memory and prevents possible swapping out, which is good for performance

# * default settings related options * *

Default_table_type = InnoDB

# this value will be used as the default table type when creating a new table. This value will be used if the creation indicates that there is no special execution table type.

Default-time-zone = system

# Server time zone

Character-set-server = utf8

# server-level character set

Default-storage-engine = InnoDB

# default storage engine

# * tmp & & heap settings related options *

Tmp_table_size = 512m

The maximum size of the temporary table. If this value is exceeded, the result is put to disk. This limit is for a single table, not a sum.

Max_heap_table_size = 512m

# the maximum capacity allowed by stand-alone memory tables. This option prevents accidental creation of an oversized memory table resulting in exhaustion of all memory resources.

# * log settings related options * *

Log-bin = mysql-bin

# turn on the binary log function. In a replication configuration, this must be turned on as the MASTER master server. If you need a point-in-time recovery from your last backup, you also need binary logs. These paths are relative to datadir.

Log_slave_updates = 1

# indicates that slave writes replication events to its own binary log

Log-bin-index = mysql-bin.index

# binary index file name

Relay-log = relay-log

# define the location and name of relay_log. If the value is empty, the default location is in the directory of the data file, and the file name is host_name-relay-bin.nnnnnn (By default, relay log file names have the form host_name-relay-bin.nnnnnn in the data directory)

Relay_log_index = relay-log.index

# Index file name of relay-log

Log-warnings = 1

# print the warning to an error log file. If you have any problems with MySQL, you should open the warning log and carefully review the error log to find out the possible cause.

Log-error = / usr/local/mysql/log/mysql.err

# error log path

Log_output = FILE

# Parameter log_output specifies the format of slow query output, which defaults to FILE. You can set it to TABLE, and then you can query the slow_log table under the mysql schema.

Log_slow_queries

# specify whether to enable slow log (this parameter will be replaced by slow_query_log for compatibility reservation)

Slow_query_log = 1

# specify whether to enable slow query log. Slow queries are queries that consume more time than those defined by "long_query_time". If log_long_format is opened, queries that do not use indexes will also be recorded. If you often add new queries to existing systems. Generally speaking, this is a good idea.

Long-query-time = 1

# set the threshold for slow query, and the SQL that exceeds the second set value will be recorded in the slow query log. The default value is 10s. All queries that use more than this time (in seconds) are considered slow queries. Do not use "1" here, or it will cause all queries and even very fast query pages to be recorded (because the accuracy of MySQL's current time can only reach the level of seconds).

Log_long_format

# record more information in the slow log. In general, this item is best turned on, which records so that queries that do not use indexes are also appended to the slow log as slow queries

Slow_query_log_file = / usr/local/mysql/log/slow.log

# specify the storage location of slow log files, which can be empty, and the system will give a default file host_name-slow.log

Log-queries-not-using-indexes

# if you run a SQL statement that does not use an index, the mysql database will also log the SQL statement to the slow query log file.

Min_examined_row_limit=1000

# record slow queries caused by more than 1000 searches

Long-slow-admin-statements

# record those slow optimize table,analyze table and alter table statements

Log-slow-slave-statements

# record slow queries generated by Slave

General_log = 1

# record all SQL statements that arrive at MySQL Server and turn it off by default

General_log_file = / usr/local/mysql/log/mysql.log

# general_log path

Max_binlog_size = 1G

# if the content written by the binary log exceeds the given value, the log will scroll. You cannot set this variable to be greater than 1GB or less than 4096 bytes. The default value is 1GB. If you are using large transactions, binary logs will also exceed max_binlog_size

Max_relay_log_size = 1G

# Mark the maximum allowed value of relaylog. If the value is 0, the default value is max_binlog_size (1G). If not, max_relay_log_size is the largest relay_log file size.

Relay-log-purge = 1

# whether to automatically empty the relay log is no longer needed. Default is 1 (enabled)

Expire_logs_days = 30

# deletion of binlog over 30 days

Binlog_cache_size = 1m

# the size of the cache held by binlog in order to record the SQL state in a transaction, if you often use large, multi-declared transactions, you can increase this value to achieve greater performance. All states from the transaction will be buffered in the binlog buffer and then written to binlog once committed. If the transaction is larger than this value, a temporary file on disk will be used instead. This buffer is created when the state of each connected transaction is updated for the first time. Session level

Replicate-wild-ignore-table = mysql.%

# ignore databases and tables when replicating

Slave_skip_errors=all

# define the error number that can be skipped automatically from the server during replication. When you encounter a defined error number during replication, you can skip it automatically and execute the following SQL statement directly.

The slave_skip_errors option has four available values, which are: off,all,ErorCode,ddl_exist_errors.

By default, the parameter value is off, and we can list the specific error code, or we can select all,mysql5.6 and MySQL Cluster NDB 7.3.The parameter ddl_exist_errors is added in subsequent versions, which includes a series of error code (1007meme 1008, 1050, 1050, 1051, 1054, 1060, 1061, 1068, 1094, 146).

Some of the errors represented by error code are as follows:

1007: database already exists, failed to create database

1008: database does not exist, failed to delete database

1050: Datasheet already exists, failed to create datasheet

1051: the data table does not exist. Failed to delete the data table.

1054: the field does not exist, or the program file conflicts with the database

1060: duplicate fields, making it impossible to insert

1061: duplicate key name

1068: multiple primary keys are defined

1094: location thread ID

1146: data table is missing, please restore the database

1053: primary server downtime during replication

1062: primary key conflict Duplicate entry'% s' for key% d

# * MyISAM related options * *

Key_buffer_size = 256m

# specifies the size of the buffer used for the index, which can be increased for better index processing performance. If the DB is based on the InnoDB engine, the key_buffer_size dedicated to the MyISAM engine can be set smaller, and the 8MB is sufficient if the MyISAM engine is the main engine, it can be set larger, but not more than 4G. Here, it is strongly recommended not to use the MyISAM engine, the default is to use the InnoDB engine. Note: the excessive setting of this parameter value will reduce the overall efficiency of the server!

Sort_buffer_size = 2m

# the size of the buffer that can be used when sorting queries. Sort buffers are used to handle sorting caused by ORDER BY and GROUP BY queues. An alternative disk-based merge classification will be used. View the "Sort_merge_passes" status variable. When sorting occurs, it is allocated by each thread Note: the allocated memory corresponding to this parameter is exclusive per connection! If there are 100 connections, the actual total sort buffer size allocated is 100x6 = 600MB, so it is recommended to set it to 6-8m for servers with about 4GB memory.

Read_buffer_size = 2m

# the size of the buffer that can be used by read query operations. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection! The buffer size used to do full table scan of MyISAM table. When a full table scan is needed, it is allocated in the corresponding thread.

Join_buffer_size = 8m

# the size of the buffer that can be used in federated query operations. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection! This buffer is used to optimize full federation (full JOINs federation without indexes). Similar combinations have very poor performance in most cases, but setting this value high can mitigate the performance impact. The number of full federations is viewed through the "Select_full_join" state variable, which is allocated in each thread when full federation occurs.

Read_rnd_buffer_size = 8m

# MyISAM scans the buffer size of data by index scan (Random Scan)

Bulk_insert_buffer_size = 64m

# MyISAM uses a special tree-like cache to make burst inserts (these inserts are, INSERT... SELECT, INSERT... VALUES (…) , () ,... , and LOAD DATAINFILE) faster. This variable limits the number of bytes in the buffer tree per process. Setting to 0 turns off this optimization. Do not set this value greater than "key_buffer_size" for optimization. When burst inserts are detected, this buffer will be allocated the size of the tree buffer that MyISAM uses in block insertion optimization. Note: this is a per thread limitation (a large number of bulk). This buffer is allocated when MySQL needs to re-index REPAIR, OPTIMIZE, ALTER, and LOAD DATA INFILE into an empty table. This is allocated in each thread. So you need to be careful when setting large values.

Myisam_sort_buffer_size = 64m

# MyISAM sets the size of the buffer used when recovering the table, when sorting the buffer allocated by the MyISAM index during REPAIR TABLE or creating indexes with CREATE INDEX or ALTER TABLE

Myisam_max_sort_file_size = 10G

# maximum temporary file size allowed for mysql re-indexing

Myisam_repair_threads = 1

# if the value is greater than 1, create MyISAM indexes in parallel during Repair by sorting (each index is in its own thread). If a table has more than one index, MyISAM can fix them with more than one thread through parallel sorting. This is a good choice for users with multiple CPU and a lot of memory.

Myisam_recover = 64K

# maximum allowed length of the result of the GROUP_CONCAT () function

Transaction_isolation = REPEATABLE-READ # sets the default transaction isolation level. The following levels are available: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ,SERIALIZABLE

1.READ UNCOMMITTED- read unsubmitted 2.READ COMMITTE- read submitted 3.REPEATABLE READ-repeatable 4.SERIALIZABLE-Serial # * INNODB related options * *

Skip-innodb

# if your MySQL service includes InnoDB support but does not plan to use it, using this option will save memory and disk space, and speed up some parts

Innodb_file_per_table = 1

# InnoDB is an independent tablespace schema, and each table in each database generates a data space.

Advantages of independent tablespaces:

1. Each table has its own independent tablespace.

2. The data and indexes of each table will be stored in its own tablespace.

3. It is possible to move a single table in different databases.

4. Space can be recycled (table emptiness cannot be recycled by itself except for drop table operation)

Disadvantages:

1. Single table increases too much, such as more than 100G

Conclusion:

Shared tablespaces have few advantages in Insert operations. Nothing else performs as well as independent tablespaces. When enabling independent tablespaces, adjust reasonably: innodb_open_files

Innodb_status_file = 1

# enable status file of InnoDB for easy viewing and monitoring by administrators

Innodb_open_files = 2048

# limit the data of tables that Innodb can open. If there are a lot of tables in the library, please add this. This value defaults to 300

Innodb_additional_mem_pool_size = 100m

# set the memory space used by the InnoDB storage engine to store data dictionary information and some internal data structures, so when we have a large number of database objects in a MySQL Instance, we need to properly adjust the size of this parameter to ensure that all data can be stored in memory to improve access efficiency.

Innodb_buffer_pool_size = 2G

# including data pages, index pages, insert cache, lock information, adaptive hash, data dictionary information. InnoDB uses a buffer pool to hold indexes and raw data, unlike MyISAM. The larger the setting here, the less disk Ihandle O you need to access the data in the table. On a stand-alone database server, you can set this variable to 80% of the server's physical memory size, and do not set it too large, otherwise, the page change of the operating system may be caused by the competition of physical memory. Note that on 32-bit systems, each process may be limited to 2-3.5 gigabytes of user-level memory, so don't set it too high.

Innodb_write_io_threads = 4

Innodb_read_io_threads = 4

# innodb uses background threads to handle read and write I / O (input / output) requests on data pages, which can be changed according to your CPU core count. The default is 4.

# Note: these two parameters do not support dynamic change. You need to add this parameter to the my.cnf, and restart the MySQL service after modification. The allowed values range from 1 to 64.

Innodb_data_home_dir = / usr/local/mysql/var/

# set this option if you want InnoDB tablespace files to be saved in other partitions. It is saved in the datadir of MySQL by default.

Innodb_data_file_path = ibdata1:500M;ibdata2:2210M:autoextend

# InnoDB saves data in one or more data files as tablespaces. If you only have a single logical driver to save your data, a single self-adding 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_file_io_threads = 4

# the number of IO threads used to synchronize IO operations. This value is hard-coded to 4 in Unix, but it may perform better at a large value on Windows disk.

Innodb_thread_concurrency = 16

# the number of threads allowed in the InnoDb core, InnoDB tries to keep the number of operating system threads in InnoDB less than or equal to the limit given by this parameter, and the optimal value depends on the application, hardware, and operating system scheduling. Excessively high values can cause thread mutex bumps. The default setting is 0, which means there is no limit on the number of concurrency. It is recommended to set it to 0 to better exert the multi-core processing capacity of CPU and improve the concurrency.

Innodb_flush_log_at_trx_commit = 1

If set to 1, InnoDB will fsync the transaction log to disk after each commit, which provides complete ACID behavior. If you are willing to compromise on transaction security, and you are running a small food, you can set this value to 0 or 2 to reduce the disk Imax O caused by the transaction log.

0 means that the log is only written to the log file about every second 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 about every second.

Innodb_log_buffer_size = 8m

# the size of the buffer used to buffer log data. When this value is almost full, InnoDB will have to refresh the data to disk. Since it is basically refreshed every second, it is not necessary to set this value too high (even for long transactions)

Innodb_log_file_size = 500m

# Log size of things. For the size of each log file in the log group, you should set the total log file size to 5% '100% of your buffer pool size to avoid unnecessary buffer pool refresh on log file overwriting. In any case, note that a large log file size increases the time required for the recovery process.

Innodb_log_files_in_group = 2

# Total number of files in the log group. Generally speaking, 2-3 is better.

Innodb_log_group_home_dir = / usr/local/mysql/var/

The location of the log file for # InnoDB. The default is MySQL's datadir. You can assign it to a separate hard disk or a RAID1 volume to improve its performance. Innodb_max_dirty_pages_pct = 90 # innodb main thread refreshes the data in the cache pool so that the proportion of dirty data is less than 90%. This is a soft limit and is not guaranteed to be executed absolutely.

Innodb_lock_wait_timeout = 50

# the number of timeout seconds that an InnoDB transaction can wait for a lock before being rolled back. InnoDB automatically detects transaction deadlocks and rolls back transactions in its own locking table. InnoDB notices the lock setting with the LOCK TABLES statement. The default value is 50 seconds

Innodb_flush_method = O_DSYNC

# the method used by InnoDB to refresh the log. Tablespaces always use double write refresh methods. The default is "fdatasync" and the other is "O_DSYNC".

Innodb_force_recovery=1

If you find that the InnoDB tablespace is corrupted, setting this value to a non-zero value may help you export your table. Start at 1 and increase this value until you can successfully export the table.

Innodb_fast_shutdown

# accelerate the shutdown of InnoDB. This prevents InnoDB from doing full cleanup and inserting buffer merging when closed. This may greatly increase shutdown time, but instead, InnoDB may do this at the next startup.

# other related options *

[mysqldump]

Quick

# support the dump of large databases, which is required when exporting very large tables. It is safe to increase the value of this variable because additional memory is allocated only when needed. For example, mysqld allocates more memory only if you issue a long query or if mysqld must return a large result row. The smaller default value for this variable is a precaution to capture error packets between the client and server and to ensure that memory spills are not caused by accidental use of large packets. If you are using a large blob value and do not grant mysqld access to enough memory to process queries, you will also encounter strange problems with large packets. If you suspect this is happening, try adding ulimit-d 256000 at the beginning of the mysqld_safe script and restart mysqld.

[mysql]

Auto-rehash

# allow prompt via TAB key

Default-character-set = utf8

# Database character set

Connect-timeout = 3

[mysqld_safe]

Open-files-limit = 8192

# increase the number of files that can be opened per process. Make sure you have set the system-wide limit high enough! To open a large number of tables, you need to set this value to large.

Thank you for your reading, the above is the content of "detailed explanation of my.cnf in mysql". After the study of this article, I believe you have a deeper understanding of the detailed explanation of my.cnf in mysql, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.