Mysql configuration file example _ MySQL

Mysql configuration file example bitsCN.com

Mysql configuration file example

[Mysqld]

Port = 3306

Socket =/tmp/mysql. sock

# 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

Default-character-set = 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.

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

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

Slow_query_log

# 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.

Myisam_recover

# *** 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

[Client]

Port = 3306

Socket =/tmp/mysql. sock

# Set the character set of the mysql client

Default-character-set = utf8

[Mysqldump]

Quick

Max_allowed_packet = 16 M

[Mysql]

No-auto-rehash

# Only allow UPDATEs and DELETEs that use keys.

# Safe-updates

[WinMySQLAdmin]

# Specify the mysql service startup file

Server = D: // mysql-5.6.14-win32 // bin // mysqld.exe

BitsCN.com