Shulou(Shulou.com)06/01 Report--

The most detailed translation of the my.cnf example of the MySQL configuration file:

# BEGIN CONFIG INFO

# DESCR: 4GB RAM, using only InnoDB and ACID, with a small number of connections and heavy queue load

# TYPE: SYSTEM

# END CONFIG INFO

#

# this example of mysql configuration file is for 4G memory.

# mainly using INNODB

# handle complex queues and connect a small number of mysql servers

#

# copy this file to / etc/my.cnf as the global setting

# mysql-data-dir/my.cnf specifies settings as the server

# (@ localstatedir@ for this installation) or put in

# ~ /. My.cnf is set as a user.

#

In this configuration file, you can use the long options supported by all programs.

# if you want to know all the options supported by the program

# Please add the parameter "--help" after the program to run the program.

#

# more details on stand-alone options can be found in the manual

#

#

# the following options will be read by MySQL client applications.

# Note that only the client application included with MySQL guarantees that this can be read.

# if you want your own MySQL application to get these values

# these options need to be specified when the MySQL client library is initialized

#

[client]

# password = [your_password]

Port = @ MYSQL_TCP_PORT@

Socket = @ MYSQL_UNIX_ADDR@

# * * apply customization options *

#

# MySQL server

#

[mysqld]

# General configuration options

Port = @ MYSQL_TCP_PORT@

Socket = @ MYSQL_UNIX_ADDR@

# back_log is the number of connections that the operating system can maintain in the listening queue

The # queue holds connections before the MySQL connection manager thread processing.

# if you have a very high connection rate and have a "connection refused" error

# you should increase the value here.

# check your operating system documentation to get the maximum value of this variable.

# if you set back_log to a higher value than your operating system limit, it will have no effect

Back_log = 50

# do not listen on the TCP/IP port.

# if all processes connect to the local mysqld on the same server

# this setting will be a way to enhance security

All mysqld connections are made through Unix sockets or named pipes.

# Note: if you do not open the named pipe option under windows, you will only use this option.

# (through the "enable-named-pipe" option) will cause the mysql service to have no effect!

# skip-networking

# the maximum number of simultaneous sessions allowed by the MySQL service

# one of the connections will be logged in as an administrator with SUPER permissions.

# even if the upper limit of the number of connections has been reached.

Max_connections = 100

# the maximum number of errors allowed per client connection, if this limit is reached.

# this client will be blocked by the MySQL service until "FLUSH HOSTS" is executed or the service is restarted

Illegal passwords and other errors in linking will increase this value.

# check the "Aborted_connects" status to get the global counter.

Max_connect_errors = 10

# 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 files to be opened

Table_cache = 2048

# allow external file-level locks. Opening a file lock can have a negative impact on performance

# so use this option only if you are running multiple database instances on the same file (note that there will still be other constraints!)

# or you use some other software dependency to lock the MyISAM table at the file level

# external-locking

# 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. Dynamic increase in size

Max_allowed_packet = 16m

# the size of the SQL held by binlog in order to record the cache status in a transaction

If you often use large, multi-declared transactions, you can increase this value for 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 status of each connected transaction is updated for the first time

Binlog_cache_size = 1m

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

Max_heap_table_size = 64m

# sort buffers are used to handle sorting caused by ORDER BY and GROUP BY queues

# if the sorted data cannot be put into the sort buffer

# an alternative disk-based merge classification will be used

# View the "Sort_merge_passes" status variable.

# assigned by each thread when sorting occurs

Sort_buffer_size = 8m

This buffer is used to optimize full federation (full JOINs federation without indexes).

# similar unions have very poor performance in most cases

# but setting this value high can mitigate the performance impact.

# View the number of full federations through the "Select_full_join" status variable

# when full federation occurs, allocate it in each thread

Join_buffer_size = 8m

# 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

# then the client thread is put into the cache.

# this can greatly reduce the overhead of thread creation when you need a lot of new connections

# (generally speaking, if you have a good threading model, there will be no significant performance improvement.)

Thread_cache_size = 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.

This value is meaningful only for systems that support the thread_concurrency () function (for example, Sun Solaris).

# you can try using [number of CPU] * (2.. 4) as the value of thread_concurrency

Thread_concurrency = 8

Query buffers are often used to buffer SELECT results and return results directly without executing the next same query.

Opening query buffering can greatly improve server speed if you have a large number of the same queries and rarely modify tables.

# check the "Qcache_lowmem_prunes" status variable to see if the current value is high enough for your load.

# Note: if your table changes frequently or if your query text is different each time

Query buffering may cause performance degradation rather than performance improvement.

Query_cache_size = 64m

# only results less than this setting will be buffered

This setting is used to protect query buffers from overwriting all other query results by a large result set.

Query_cache_limit = 2m

# the minimum word length indexed by a full-text search.

# you may want to reduce it if you need to search for shorter words.

# notice that after you modify this value

# you need to rebuild your FULLTEXT index

Ft_min_word_len = 4

# if your system supports the memlock () function, you may want to turn on this option to keep data locked in memory and prevent possible swapping out when running mysql is highly strained.

# this option is good for performance

# memlock

# the type of table used by default when creating a new table

# this value will be used if the creation indicates that there is no special execution table type

Default_table_type = MYISAM

# the heap size used by the thread. The memory of this capacity 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 heaps

# or your operating system needs more heaps for certain operations

# you may need to set it a little higher.

Thread_stack = 19K

# set the default transaction isolation level. The levels available are as follows:

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

Transaction_isolation = REPEATABLE-READ

# maximum size of internal (in-memory) temporary tables

# if a table grows to more than this value, it will be automatically converted to a disk-based table.

# this restriction is for a single table, not a sum.

Tmp_table_size = 64m

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

Log-bin=mysql-bin

# if you are using chained replication mode from the server structure (A-> B-> C)

# you need to open this item on server B.

# this option opens the log of updates that have been redone from the thread

# and write it to the binary log of the slave server.

# log_slave_updates

# Open the full query log. All queries received by the server (even for a query with incorrect syntax)

# will be recorded. This is useful for debugging and is often turned off in production environments.

# log

# print the warning to an error log file. If you have any questions about MySQL

You should open the warning log and carefully review the error log to find out the possible cause.

# log_warnings

# record slow query. 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.

Log_slow_queries

All queries that use more than this time (in seconds) are considered slow queries.

# do not use "1" here, otherwise it will cause all queries, even very fast query pages to be recorded (because the accuracy of MySQL's current time can only reach the level of seconds).

Long_query_time = 2

# record more information in the slow log.

In general, this item had better be turned on.

# turning on this item will record so that queries that do not use indexes are also appended to slow logs as slow queries

Log_long_format

This directory is used by MySQL to save temporary files. For example

It is used to handle large disk-based sorting, just like internal sorting.

# and simple temporary tables.

# if you don't create a very large temporary file, it may be better to put it on the swapfs/tmpfs file system

Another option is that you can also put it on a separate disk.

# you can use ";" to place multiple paths

# they will be polled according to the roud-robin method.

# tmpdir = / tmp

# * copy related settings

# unique service identification number with a value between 1 and 2 ^ 32-1.

This value needs to be set on both master and slave.

If master-host is not set, the default is 1, but if you omit this option, MySQL will not take effect as master.

Server-id = 1

# copied Slave (uncomment the master segment to make it effective)

#

# to configure this host as a replicated slave server, you can choose two methods:

#

# 1) use the CHANGE MASTER TO command (fully described in our manual)-

# the syntax is as follows:

#

# CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=

# MASTER_USER=, MASTER_PASSWORD=

#

# you need to replace, and other fields surrounded by angle brackets and use the port number of master (default 3306).

#

# example:

#

# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306

# MASTER_USER='joe', MASTER_PASSWORD='secret'

#

# or

#

# 2) set the following variables. In any case, if you choose this method, and then start replication for the first time (even if it is unsuccessful)

# for example, if you enter the wrong password in the master-password field and slave cannot connect)

# slave creates a master.info file, and any subsequent changes to the parameters contained in this file are ignored

# and overridden by the contents of the master.info file, unless you close the slave service, delete the master.info and restart the slave service.

# for this reason, you may not want to touch the configuration (commented out) and use CHANGE MASTER TO (see above) instead

#

# the unique id number required is between 2 and 2 ^ 32-1

# (and different from master)

# if master-host is set. Then the default value is 2

# but if omitted, it will not take effect

# server-id = 2

#

# copy master in the structure-must

# master-host =

#

# user name used by slave to authenticate when connecting to master-must

# master-user =

#

# password used by slave to authenticate when connecting to master-must

# master-password =

#

# the port on which master listens.

# optional-default is 3306

# master-port =

# make slave read-only. Only the user has SUPER permission and the slave thread on it can modify the data.

# you can use this to ensure that no application will accidentally modify the data on slave instead of master

# read_only

# * * MyISAM related options

# keyword buffer size, which is generally used to buffer the index block of the MyISAM table.

# do not set it greater than 30% of your available memory

# because part of the memory is also used by OS to buffer row data

Even if you are not using MyISAM tables, you still need to set up 8-64m memory because it will also be used by internal temporary disk tables.

Key_buffer_size = 32m

# buffer size used for full table scan of MyISAM table.

# when a full table scan is needed, it is allocated in the corresponding thread.

Read_buffer_size = 2m

When rows are read from an already sorted sequence after sorting, the row data is read from this buffer to prevent disk seek.

If you increase this value, you can improve the performance of a lot of ORDER BY.

# allocated by each thread when needed

Read_rnd_buffer_size = 16m

# MyISAM uses a special tree-like cache to make burst inserts

# (these inserts are, INSERT SELECT, INSERT VALUES (), (), and LOAD DATA

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

# this buffer will be allocated when burst inserts are detected.

Bulk_insert_buffer_size = 64m

This buffer is allocated when MySQL needs to cause re-indexing in 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 = 128m

# the maximum temporary file size allowed by MySQL when re-indexing (when REPAIR, ALTER TABLE or LOAD DATA INFILE).

# if the file size is larger than this value, the index is created through key-value buffering (slower)

Myisam_max_sort_file_size = 10G

If the temporary file used for faster indexing is larger than the specified value, then use the key-value buffering method.

This is mainly used to force long string keys in large tables to use slow key value buffering to create indexes.

Myisam_max_extra_sort_file_size = 10G

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_repair_threads = 1

# automatically check and repair MyISAM tables that are not properly closed.

Myisam_recover

# disable Federated by default

Skip-federated

# * * BDB related options *

# use this option if the MySQL service you are running has BDB support but you are not going to use it. This will save memory and may speed things up.

Skip-bdb

# * * INNODB related options *

# if your MySQL service includes InnoDB support but does not intend to use it

# using this option will save memory and disk space, and speed up some parts

# skip-innodb

# additional memory pools are used by InnoDB to hold metadata information

If InnoDB needs more memory for this purpose, it will start to request memory from OS.

Since this operation is fast enough on most modern operating systems, you generally don't need to modify this value.

The # SHOW INNODB STATUS command displays the quantity that should be used first.

Innodb_additional_mem_pool_size = 16m

# InnoDB uses a buffer pool to hold indexes and raw data, unlike MyISAM.

The larger the setting here, the less disk Imax 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

# do not set too large, otherwise, the page change of the operating system may be bumpy due to the competition of physical memory.

# Note that on a 32-bit system, each process may be limited to 2-3.5G user-level memory limits.

# so don't set it too high.

Innodb_buffer_pool_size = 2G

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

# 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_data_file_path = ibdata1:10M:autoextend

# 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_home_dir =

# the number of IO threads used to synchronize IO operations. This value is

# this value is hard-coded to 4 in Unix, but it may perform better at a large value on Windows disk.

Innodb_file_io_threads = 4

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_force_recovery=1

# the number of threads allowed within the InnoDb core.

The optimal value depends on the scheduling of the application, hardware and operating system.

An excessively high value may cause thread mutex bumps.

Innodb_thread_concurrency = 16

# if set to 1, InnoDB will fsync the transaction log to disk after each commit

This 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_flush_log_at_trx_commit = 1

# 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 these operations the next time it starts.

# innodb_fast_shutdown

# 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_buffer_size = 8m

# the size of each log file in the log group.

# you should set the total log file size to 25% / 100% of your buffer pool size

# to avoid unnecessary buffer pool refresh behavior on log file overwrites.

In any case, please note that a large log file size increases the time required for the recovery process.

Innodb_log_file_size = 256m

# Total number of files in the log group.

# generally speaking, 2-3 is better.

Innodb_log_files_in_group = 3

The location of the log file for # InnoDB. The default is MySQL's datadir.

# you can assign it to a separate hard drive or a RAID1 volume to improve its performance

# innodb_log_group_home_dir

# the maximum percentage of dirty pages allowed in the InnoDB buffer pool.

If the limit is reached, InnoDB will start refreshing them to prevent them from interfering with clean data pages.

This is a soft limit and is not guaranteed to be absolutely enforced.

Innodb_max_dirty_pages_pct = 90

# the method used by InnoDB to refresh the log.

# tablespaces always use double write refresh method

# the default value is "fdatasync" and the other is "O_DSYNC".

# innodb_flush_method=O_DSYNC

# how long should an InnoDB transaction wait for a lock to be approved before being rolled back.

# InnoDB automatically detects transaction deadlocks and rolls back transactions in its own lock table.

# if you use the LOCK TABLES instruction, or use a transaction-safe storage engine other than InnoDB in the same transaction

# then a deadlock may occur without InnoDB noticing.

In this case, the timeout value is very helpful to solve this problem.

Innodb_lock_wait_timeout = 120

[mysqldump]

# do not cache the entire result in memory before writing it to disk. This is required when exporting very large tables

Quick

Max_allowed_packet = 16m

[mysql]

No-auto-rehash

# only UPDATEs and DELETEs with key values are allowed

# safe-updates

[isamchk]

Key_buffer = 512m

Sort_buffer_size = 512m

Read_buffer = 8m

Write_buffer = 8m

[myisamchk]

Key_buffer = 512m

Sort_buffer_size = 512m

Read_buffer = 8m

Write_buffer = 8m

[mysqlhotcopy]

Interactive-timeout

[mysqld_safe]

# increase the number of files that can be opened per process.

# warning: 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 b

Open-files-limit = 8192

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.