Explanation of MySQL database my. cnf configuration file

Source: Internet
Author: User

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:

 
 
  1. [Client]
  2. Port = 3309
  3. Socket =/home/mysql/tmp/mysql. sock
  4. [Mysqld]
  5. ! Include/home/mysql/etc/mysqld. cnf # contains the configuration file and stores the user name and password file separately.
  6. Port = 3309
  7. Socket =/home/mysql/tmp/mysql. sock
  8. Pid-file =/longxibendi/mysql/var/mysql. pid
  9. Basedir =/home/mysql/
  10. Datadir =/longxibendi/mysql/var/
  11. # Tmp dir settings
  12. Tmpdir =/home/mysql/tmp/
  13. Slave-load-tmpdir =/home/mysql/tmp/
  14. # Used when slave executes load data infile
  15. # Language =/home/mysql/share/mysql/english/
  16. Character-sets-dir =/home/mysql/share/mysql/charsets/
  17. # Skip options
  18. When using skip-name-resolve # grant, you must use an ip address instead of a host name.
  19. Skip-symbolic-links # connection files cannot be used
  20. Skip-external-locking # Do not use system lock. To use myisamchk, you must disable the server.
  21. Skip-slave-start # start mysql, do not start Replication
  22. # Sysdate-is-now
  23. # Res settings
  24. 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.
  25. Max_connections = 1000 # maximum number of concurrent connections. To increase this value, you must increase the number of file descriptors that can be opened.
  26. 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
  27. # Open_files_limit = 10240
  28. 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.
  29. Wait-timeout = 28800 # wait for the connection to be closed
  30. 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.
  31. 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.
  32. Net_read_timeout = 30 # timeout for reading information from the server
  33. Net_write_timeout = 60 # timeout for writing information from the server
  34. Net_retry_count = 10 # If the read operation on a communication port is interrupted, retry multiple times before giving up.
  35. 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
  36. Max_allowed_packet = 64 M #
  37. # Table_cache = 512 # number of tables opened by all threads. Increasing this value can increase the number of file descriptors required by mysqld.
  38. Thread_stack = 192 K # the size of each thread's stack
  39. Thread_cache_size = 20 # thread Cache
  40. 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
  41. # Qcache settings
  42. Query_cache_size = 256 M # query cache size
  43. Query_cache_limit = 2 M # Do not cache query results greater than this value
  44. Query_cache_min_res_unit = 2 K # query the minimum block size allocated by the cache
  45. # Default settings
  46. # Time zone
  47. Default-time-zone = system # server time zone
  48. Character-set-server = utf8 # server-level character set
  49. Default-storage-engine = InnoDB # default storage
  50. # Tmp & heap
  51. Tmp_table_size = 512 M # size of the temporary table. If this value is exceeded, the result is stored in the disk.
  52. Max_heap_table_size = 512 M # This variable sets the maximum size of the MEMORY (HEAP) Table space that can be increased
  53. Log-bin = mysql-bin # these paths are relative to datadir
  54. Log-bin-index = mysql-bin.index
  55. Relayrelay-log = relay-log
  56. Relayrelay_log_index = relay-log.index
  57. # Warning & error log
  58. Log-warnings = 1
  59. Log-error =/home/mysql/log/mysql. err
  60. 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.
  61. # Slow query log
  62. Slow_query_log = 1
  63. Long-query-time = 1 # Slow query if the query time exceeds 1 second
  64. Slow_query_log_file =/home/mysql/log/slow. log
  65. # Log-queries-not-using-indexes
  66. # Log-slow-slave-statements
  67. General_log = 1
  68. General_log_file =/home/mysql/log/mysql. log
  69. Max_binlog_size = 1G
  70. Max_relay_log_size = 1G
  71. # If use auto-ex, set to 0
  72. Relay-log-purge = 1 # delete logs without relay. This operation is completed by an SQL thread.
  73. # Max binlog keeps days
  74. Expire_logs_days = 30 # delete a binlog that exceeds 30 days
  75. Binlog_cache_size = 1 M # session level
  76. # Replication
  77. Replicate-wild-ignore-table = mysql. % # ignore databases and tables during replication
  78. Replicate-wild-ignore-table = test. % # ignore databases and tables during replication
  79. # Slave_skip_errors = all
  80. Key_buffer_size = 256 M # myisam index buffer, only the key does not have data
  81. Sort_buffer_size = 2 M # Sort buffer size; Thread level
  82. Read_buffer_size = 2 M # buffer size of data scanned in full table Scan (Sequential Scan) mode; Thread level
  83. Join_buffer_size = 8 M # join buffer size; Thread level
  84. Read_rnd_buffer_size = 8 M # MyISAM uses the index Scan (Random Scan) method to Scan the buffer size of data; Thread level
  85. 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.
  86. 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
  87. 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.
  88. 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)
  89. Myisam_recover = 64 K # Maximum length of the result of the allowed GROUP_CONCAT () function
  90. Transaction_isolation = REPEATABLE-READ
  91. Innodb_file_per_table
  92. # Innodb_status_file = 1
  93. # Innodb_open_files = 2048
  94. 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.
  95. Innodb_buffer_pool_size = 2G # data page, index page, insert cache, lock information, adaptive hash, data dictionary Information
  96. Innodb_data_home_dir =/longxibendi/mysql/var/
  97. # Innodb_data_file_path = ibdata1: 1G: autoextend
  98. Innodb_data_file_path = ibdata1: 500 M; ibdata2: 2210 M: autoextend # tablespace
  99. Innodb_file_io_threads = 4 # Number of I/O threads
  100. 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
  101. Innodb_flush_log_at_trx_commit = 1 # data in each commit log cache is flushed to the disk.
  102. Innodb_log_buffer_size = 8 M # transaction log Cache
  103. Innodb_log_file_size = 500 M # transaction log size
  104. # Innodb_log_file_size = 100 M
  105. Innodb_log_files_in_group = 2 # two groups of transaction logs
  106. Innodb_log_group_home_dir =/longxibendi/mysql/var/# log Group
  107. 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%.
  108. 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.
  109. # Innodb_flush_method = O_DSYNC
  110. [Mysqldump]
  111. Quick
  112. Max_allowed_packet = 64 M
  113. [Mysql]
  114. Disable-auto-rehash # allow prompt by the TAB key
  115. Default-character-set = utf8
  116. 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!

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.