Explanation of MySQL database my. cnf configuration file

We know thatMySQL databaseAfter the installation is completeMy. cnf configuration fileMake proper modifications to make full use of the functions of the MySQL database. However, it seems difficult for beginners to modify the my. cnf configuration file. To solve this problem, we have explained the configuration file for your reference and hope to help you.

The following describes the parameters in the my. cnf configuration file:

 
 

  
  
[Client]
  
  
Port = 3309
  
  
Socket =/home/mysql/tmp/mysql. sock
  
  
[Mysqld]
  
  
! Include/home/mysql/etc/mysqld. cnf # contains the configuration file and stores the user name and password file separately.
  
  
Port = 3309
  
  
Socket =/home/mysql/tmp/mysql. sock
  
  
Pid-file =/longxibendi/mysql/var/mysql. pid
  
  
Basedir =/home/mysql/
  
  
Datadir =/longxibendi/mysql/var/
  
  
# Tmp dir settings
  
  
Tmpdir =/home/mysql/tmp/
  
  
Slave-load-tmpdir =/home/mysql/tmp/
  
  
# Used when slave executes load data infile
  
  
# Language =/home/mysql/share/mysql/english/
  
  
Character-sets-dir =/home/mysql/share/mysql/charsets/
  
  
# Skip options
  
  
When using skip-name-resolve # grant, you must use an ip address instead of a host name.
  
  
Skip-symbolic-links # connection files cannot be used
  
  
Skip-external-locking # Do not use system lock. To use myisamchk, you must disable the server.
  
  
Skip-slave-start # start mysql, do not start Replication
  
  
# Sysdate-is-now
  
  
# Res settings
  
  
Back_log = 50 # accept the queue. If no tcp connection is established, the request queue is placed in the cache. The queue size is back_log, which is restricted by operating system parameters.
  
  
Max_connections = 1000 # maximum number of concurrent connections. To increase this value, you must increase the number of file descriptors that can be opened.
  
  
Max_connect_errors = 10000 # If a user initiates a connection error that exceeds this value, the next connection of the user will be blocked until the Administrator executes the flush hosts command to prevent hackers
  
  
# Open_files_limit = 10240
  
  
Connect-timeout = 10 # maximum number of seconds before connection timeout. on Linux, this timeout is also used to wait for the first response from the server.
  
  
Wait-timeout = 28800 # wait for the connection to be closed
  
  
Interactive-timeout = 28800 # Allow interactive_timeout to replace wait_timeout before closing the connection. The client's session wait_timeout variable is set to the value of the session interactive_timeout variable.
  
  
Slave-net-timeout = 600 # The slave server can also handle network connection interruptions. However, network interruption is notified only when the slave server does not receive data from the master server within seconds after slave_net_timeout.
  
  
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 on a communication port is interrupted, retry multiple times before giving up.
  
  
Net_buffer_length = 16384 # The packet message buffer is initialized to net_buffer_length bytes, but can be increased to max_allowed_packet bytes as needed
  
  
Max_allowed_packet = 64 M #
  
  
# Table_cache = 512 # number of tables opened by all threads. Increasing this value can increase the number of file descriptors required by mysqld.
  
  
Thread_stack = 192 K # the size of each thread's stack
  
  
Thread_cache_size = 20 # thread Cache
  
  
Thread_concurrency = 8 # the data of the Concurrently Running thread should be twice the number of CPUs. Number of CPUs configured on the local machine
  
  
# Qcache settings
  
  
Query_cache_size = 256 M # query cache size
  
  
Query_cache_limit = 2 M # Do not cache query results greater than this value
  
  
Query_cache_min_res_unit = 2 K # query the minimum block size allocated by the cache
  
  
# Default settings
  
  
# Time zone
  
  
Default-time-zone = system # server time zone
  
  
Character-set-server = utf8 # server-level character set
  
  
Default-storage-engine = InnoDB # default storage
  
  
# Tmp & heap
  
  
Tmp_table_size = 512 M # size of the temporary table. If this value is exceeded, the result is stored in the disk.
  
  
Max_heap_table_size = 512 M # This variable sets the maximum size of the MEMORY (HEAP) Table space that can be increased
  
  
Log-bin = mysql-bin # these paths are relative to datadir
  
  
Log-bin-index = mysql-bin.index
  
  
Relayrelay-log = relay-log
  
  
Relayrelay_log_index = relay-log.index
  
  
# Warning & error log
  
  
Log-warnings = 1
  
  
Log-error =/home/mysql/log/mysql. err
  
  
Log_output = FILE # The log_output parameter specifies the slow query output format. The default format is FILE. You can set it to TABLE and then query the slow_log TABLE in mysql architecture.
  
  
# Slow query log
  
  
Slow_query_log = 1
  
  
Long-query-time = 1 # Slow query if the query time exceeds 1 second
  
  
Slow_query_log_file =/home/mysql/log/slow. log
  
  
# Log-queries-not-using-indexes
  
  
# Log-slow-slave-statements
  
  
General_log = 1
  
  
General_log_file =/home/mysql/log/mysql. log
  
  
Max_binlog_size = 1G
  
  
Max_relay_log_size = 1G
  
  
# If use auto-ex, set to 0
  
  
Relay-log-purge = 1 # delete logs without relay. This operation is completed by an SQL thread.
  
  
# Max binlog keeps days
  
  
Expire_logs_days = 30 # delete a binlog that exceeds 30 days
  
  
Binlog_cache_size = 1 M # session level
  
  
# Replication
  
  
Replicate-wild-ignore-table = mysql. % # ignore databases and tables during replication
  
  
Replicate-wild-ignore-table = test. % # ignore databases and tables during replication
  
  
# Slave_skip_errors = all
  
  
Key_buffer_size = 256 M # myisam index buffer, only the key does not have data
  
  
Sort_buffer_size = 2 M # Sort buffer size; Thread level
  
  
Read_buffer_size = 2 M # buffer size of data scanned in full table Scan (Sequential Scan) mode; Thread level
  
  
Join_buffer_size = 8 M # join buffer size; Thread level
  
  
Read_rnd_buffer_size = 8 M # MyISAM uses the index Scan (Random Scan) method to Scan the buffer size of data; Thread level
  
  
Bulk_insert_buffer_size = 64 M # size of the tree buffer used by MyISAM in block insertion optimization. Note: This is a per thread restriction.
  
  
Myisam_sort_buffer_size = 64 M # MyISAM sets the buffer size used to restore the TABLE. The buffer allocated by sorting the MyISAM INDEX during the repair table or create index or alter table Process
  
  
Myisam_max_sort_file_size = 10G # If the temporary file of MyISAM exceeds the index, do not use the Quick Sort index method to create an index. Note: This parameter provides the maximum size of temporary files used by MySQL when re-indexing MyISAM indexes (during repair table, alter table, or load data infile) in bytes. If the file size exceeds this value, it is much slower to use the key value cache to create an index. The unit of the value is byte.
  
  
Myisam_repair_threads = 1 # If the value is greater than 1, create the MyISAM Table index in parallel during the Repair by sorting process (each index is in its own thread)
  
  
Myisam_recover = 64 K # Maximum length of the result of the allowed GROUP_CONCAT () function
  
  
Transaction_isolation = REPEATABLE-READ
  
  
Innodb_file_per_table
  
  
# Innodb_status_file = 1
  
  
# Innodb_open_files = 2048
  
  
Innodb_additional_mem_pool_size = 100 M # The control object of the frame cache must apply for cache from this place. Therefore, this value corresponds to innodb_buffer_pool.
  
  
Innodb_buffer_pool_size = 2G # data page, index page, insert cache, lock information, adaptive hash, data dictionary Information
  
  
Innodb_data_home_dir =/longxibendi/mysql/var/
  
  
# Innodb_data_file_path = ibdata1: 1G: autoextend
  
  
Innodb_data_file_path = ibdata1: 500 M; ibdata2: 2210 M: autoextend # tablespace
  
  
Innodb_file_io_threads = 4 # Number of I/O threads
  
  
Innodb_thread_concurrency = 16 # InnoDB tries to keep the number of operating system threads in InnoDB less than or equal to the limit given by this parameter
  
  
Innodb_flush_log_at_trx_commit = 1 # data in each commit log cache is flushed to the disk.
  
  
Innodb_log_buffer_size = 8 M # transaction log Cache
  
  
Innodb_log_file_size = 500 M # transaction log size
  
  
# Innodb_log_file_size = 100 M
  
  
Innodb_log_files_in_group = 2 # two groups of transaction logs
  
  
Innodb_log_group_home_dir =/longxibendi/mysql/var/# log Group
  
  
Innodb_max_dirty_pages_pct = 90 # the innodb main thread refreshes the data in the cache pool so that the proportion of dirty data is less than 90%.
  
  
Innodb_lock_wait_timeout = 50 # InnoDB transactions can wait for a lock timeout period before being rolled back. InnoDB automatically detects the transaction deadlock and rolls back the transaction in its own locked table. InnoDB uses the lock tables statement to notice the LOCK settings. The default value is 50 seconds.
  
  
# Innodb_flush_method = O_DSYNC
  
  
[Mysqldump]
  
  
Quick
  
  
Max_allowed_packet = 64 M
  
  
[Mysql]
  
  
Disable-auto-rehash # allow prompt by the TAB key
  
  
Default-character-set = utf8
  
  
Connect-timeout = 3
 
 

The explanation of the MySQL database my. cnf configuration file is introduced here. I hope this introduction will be helpful to you!