Mysql configuration file example

Source: Internet
Author: User

Port = 3306
# Set the installation directory of mysql
Basedir = D: \ mysql-5.6.14-win32
# Set the data storage directory of the mysql database, which must be data
Datadir = D: \ mysql-5.6.14-win32 \ data
# Innodb_log_arch_dir default datadir
# Innodb_log_group_home_dir default datadir
# Set the character set of the mysql server, default encoding
Character_set_server = utf8

# Number of operating system listening queues for the number of connections. You can add this value if the "reject connection" error occurs frequently.
Back_log = 50
# Do not use the method of answering TCP/IP ports. mysqld uses the named pipe to connect
# Skip-networking
# Maximum number of connections
Max_connections = 100
# The maximum number of threads to open a table is 4096, unless you use mysqld_safe to open the limit
Table_open_cache = 2048
# MySql Service receives the maximum query package size for each process
Max_allowed_packet = 16 M
# Apply to the memory cache used by a single SQL query process. If the binary data of an operation exceeds the limited size, it will open up space for processing on the disk, which is generally set to 1-2 M. The default value is 1 M.
Binlog_cache_size = 2 M
# Maximum value limit for a single memory table
Max_heap_table_size = 64 M
# Sort buffer size allocated for each thread
Sort_buffer_size = 8 M
# The buffer size for join table operations. The default value is 8 Mb.
Join_buffer_size = 32 M
# Operation on the cache of the number of disconnected threads
Thread_cache_size = 8
# Number of concurrent threads. The default value is 8, which can be increased to less than 2 times. If multiple CPUs exist, you can multiply the number of CPUs. The dual-core CPU can be multiplied by the current maximum number of cores and then multiplied by 70%-85%
Thread_concurrency = 16
# It is dedicated to the cache of specific SQL statements. If the submitted query is the same as a query that exists in the query cache, the results in the cache are directly returned.
Query_cache_size = 64 M
# Corresponding to the previous setting, when the query result exceeds the size set below, it will not be interesting to the cache area set above, avoiding a large result occupying a large amount of cache.
Query_cache_limit = 2 M
# Set the Minimum word length in full-text search.
# Ft_min_word_len = 4
# The default TABLE Type of the create table statement. If the type is not specified, the downstream type is used.
Default-storage-engine = InnoDB
# The thread stack size. mysql says its own stack size cannot exceed 64 KB. This value can be appropriately set to a higher value (in my projects, it is shared with the same database connection). The default value is 192 KB.
Thread_stack = 800 K
# Set the level of transaction processing, the default REPEATABLE-READ, generally use it, the following two rows correspond in order,
# You can read and write uncommitted data, create uncommitted data copies for read and write, read and write before submission, and only allow serial sequence Zhaoxing transactions.
Transaction_isolation = REPEATABLE-READ
# The size of a single memory temporary table in the memory. If this value is exceeded, the table is automatically converted to the disk.
Tmp_table_size = 64 M
# Enable the binary log function to restore the latest backup at a time point
# Log-bin = mysql-bin
# Binary log format, followed by the previous one.-mixed format is recommended.
# Binlog_format = mixed
# Convert query to slow Query
# Corresponds to the previous query. If a query exceeds the time set for the next query, the previous query is executed.
Long_query_time = 2
# Custom host ID Identifier for master-slave or multi-server identification. It is an int type.
Server-id = 1
# It is generally used to cache the primary key of the MyISAM table and also used to cache the primary key of the temporary disk table. The temporary disk table appears many times above, so it is best to set a large value for it even if MyISAM is not used, default 32 M
Key_buffer_size = 64 M
# Cache for full table scan of MyISAM table. Each thread has the downstream size.
Read_buffer_size = 2 M
# Cache between the sorting operation and the disk to each thread. The default value is 16 Mb.
Read_rnd_buffer_size = 24 M
# MyISAM uses a special tree to cache batch inserts, such as insert... values (..)(..)(..)
Bulk_insert_buffer_size = 64 M
# Maximum limit of MyISAM index file,
Myisam_max_sort_file_size = 10G
# If a myisam table has more than one index, MyISAM can use more than one thread to sort and parallel them. Hardware resources are high. If your environment is good, you can add this value.
Myisam_repair_threads = 2
# The MyISAM table cannot be properly closed after automatic check and repair.
# *** INNODB Specific options ***
# Enabling the next entry will disable INNODB
# Skip-innodb
# InnoDB generally does not need to be set or set up, and InnoDB will interact with the operating system to manage the size of InnoDB Storage data used by its additional memory pool
Innodb_additional_mem_pool_size = 16 M
# The overall innodb buffer pool size should not be too large. It is suitable to set it to 50%-75% of the local memory. You can set a smaller value during local development, for example, 200 M.
Innodb_buffer_pool_size = 200 M
# InnoDB data is stored in a tablespace composed of one or more data files
Innodb_data_file_path = ibdata1: 10 M: autoextend
# Number of threads used for asynchronous IO operations. The default value is 4, which can be appropriately increased.
Innodb_file_io_threads = 8
# The InnoDB kernel allowed within the thread count should not be too high
Innodb_thread_concurrency = 16
# InnoDB's transaction logs are saved fast. The default value is 1. 0 can reduce disk I/0 operations and 2
Innodb_flush_log_at_trx_commit = 1
# InnoDB buffer log data size
Innodb_log_buffer_size = 8 M
# Log file, which can be set to the overall cache size of 25%-90%. The default value is 256 MB. to modify this parameter, delete datadir \ ib_logfileXXX.
Innodb_log_file_size = 256 M
# Number of log groups. The default value is 3.
Innodb_log_files_in_group = 3
# InnoDB log file location. The default value is MySQL datadir.
# Innodb_log_group_home_dir
# The maximum allowable percentage of dirty page buffer pool in InnoDB. The default value is 90.
Innodb_max_dirty_pages_pct = 80
# Transaction deadlock timeout settings
Innodb_lock_wait_timeout = 120

Port = 3306
# Set the character set of the mysql client
# Default-character-set = utf8

Max_allowed_packet = 16 M


# Only allow UPDATEs and DELETEs that use keys.
# Safe-updates

# Specify the mysql Service Startup File
Server = D: \ mysql-5.6.14-win32 \ bin \ mysqld.exe

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

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: and provide relevant evidence. A staff member will contact you within 5 working days.