Explanation of MySQL configuration information (My. CNF)

I have been working for almost two months since I changed my job in the next year, so I am very busy, so I have less and less time to write my blog.

In the past, MySQL was used as a student. It was the "Next" step from the beginning of installation. You can set the user, port, and encoding in the setup wizard. Later I worked on Oracle for the company, but it was commonProgramMembers cannot access it either. Now I have the opportunity to get in touch with the database (MySQL) and try my best to understand how the MySQL source code is compiled, how it is configured, and the differences between storage engines.

This blog post records the configurations in my. CNF (My. ini on the window platform) That I understand. Should these configurations be explained on the official website ?! It may be that I have not found any good resources.

My. CNF does not exist by default. You can see the following files in the support-Files directory of the MySQL installation directory:

Can be my-huge.cnf, my-innodb-heavy-4G.cnf, my-large.cnf, my-medium.cnf, my-small.cnf in the appropriate file copy your machine named my. CNF.

The following describes the configuration files in detail.

My-huge.cnf (huge, huge)

# example MySQL config file for very large systems.
# This is a large system configuration example
# this is for a large system with memory of 1g-2g where the system runs mainly
# MySQL.
# one with 1 to 2 GB memory, it is mainly used to run MySQL systems
# MySQL programs look for Option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. for information about these locations, see:
# http://dev.mysql.com/doc/mysql/en/option-files.html
# in this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# With the "-- Help" option.

# The following options will be passed to all MySQL clients
[Client]
# Password = your_password
Port = 3306Port Number
Socket =/tmp/MySQL. SockThe MySQL. Sock file isUNIX socket file used by the server to communicate with the local client

# Here follows entries for some specific programs

# The MySQL Server
[Mysqld]
Port = 3306Port Number
Socket =/tmp/MySQL. SockThe MySQL. Sock file isUNIX socket file used by the server to communicate with the local client
Skip-lockingAvoid external locks of MySQL and reduce the chance of errors to enhance stability.
Key_buffer_size = 384 mSpecifies the size of the index buffer, which determines the index processing speed, especially the index read speed.

Check the status values key_read_requests and key_reads to check whether the key_buffer_size setting is reasonable. Ratio key_reads/key_read_requests should be as low as possible, at least, is better (the above status values can be obtained using show status like 'key _ read %)

Max_allowed_packet = 1 m Set the maximum package to limit the size of data packets received by the server to avoid execution problems of ultra-long SQL statements (When the mysql client or mysqld server receives an information packet greater than the value of max_allowed_packet, the "information packet is too large" error is reported and the connection is closed.)
Table_open_cache = 512 Each time MySQL opens a table, it reads some data into table_open_cache. when MySQL cannot find the corresponding information in this cache, it will directly read the data from the disk.
Sort_buffer_size = 2 m The buffer size that can be used to query sorting. Note: The allocated memory corresponding to this parameter is exclusive to each connection! If there are 100 connections, the total size of the actually allocated sort buffer is 100 × 2 = 200 MB. Therefore, we recommend that you set the size of a server with around 4 GB to 6-8 Mb.
Read_buffer_size = 2 mThe buffer size that can be used by the read query operation. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection! Read_buffer_size only appliesThe MyISAM table is valid.
Read_rnd_buffer_size = 8 m Tables of all storage engines are limited.
Myisam_sort_buffer_size = 64 m  The maximum temporary file size allowed by MySQL during index reconstruction (when repair, alter table, or load data infile)
Thread_cache_size = 8 The value cached by the server thread indicates that the number of threads stored in the cache can be reused. If there is space in the cache when the connection is disconnected, the client thread will be placed in the cache, if the thread is re-requested, the request will be read from the cache. If the cache is empty or a new request, the thread will be re-created. If there are many new threads, adding this value can improve system performance.
Query_cache_size = 32 mHow query_cache_size works: After a SELECT query is executed in a database, the database caches the statement. When the same SQL statement is called in the database again, the DB returns the result from the cache to the client without changing the table. Here is a reference point, that is, when dB uses query_cache to work, it is required that the table involved in this statement not be changed during this period. What if the data in query_cache is processed when the table is changed? First, set all the query_cache statements related to the table to invalid, and then write the updates. If query_cache is very large, the query structure of the table is large, and the query statement becomes invalid, an update or insert operation will be slow, in this way, we can see how the update or insert operation is so slow. This parameter is not suitable for systems with a large number of database writes or updates. This function is disabled for systems with high concurrency and large write volumes.
# Try Number of CPU's * 2 for thread_concurrency
Thread_concurrency = 8 The correct value of thread_concurrency has a great impact on MySQL performance. When multiple CPUs (or multiple cores) are used, the value of thread_concurrency is incorrectly set, as a result, MySQL cannot make full use of multiple CPUs (or multiple cores), and only one CPU (or core) can work at the same time. Thread_concurrency should be set to 2 times the number of CPU cores. For example, if there is a dual-core CPU, thread_concurrency should be 4; if there are two dual-core CPUs, the value of thread_concurrency should be 8

# Don't listen on a TCP/IP Port at all. This can be a security enhancement,
# If all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via UNIX sockets or named pipes.
# Note that using this option without enabling Named Pipes on Windows
# (Via the "enable-named-pipe" option) will render mysqld useless!
#
# Skip-networkingEnabling this option can completely disable the MySQL TCP/IP connection mode. If the Web server accesses the MySQL database server remotely, do not enable this option! Otherwise, the connection will fail!

# Replication master server (default)
# Binary logging is required for replication
Log-bin = mysql-binThe binary log contains all the statements for updating data. It is used to restore the data to the final state as much as possible when restoring the database. In addition, if you perform replication, you also need to use binary logs to transmit modifications. To enable binary log, you must set the log-bin parameter. Log_bin specifies the log file. If no file name is provided, MySQL generates its own default file name.

# Required unique ID between 1 and 2 ^ 32-1
# Defaults to 1 if master-host is not set
# But will not function as a master if omitted
Server-id = 1

# Replication slave (comment out Master section to use this)
#
# To configure this host as a replication slave, you can choose
# Two methods:
#
#1) use the change master to command (fully described in our manual )-
# The syntax is:
#
# Change master to master_host = # Master_user = <user>, master_password = <password>;
#
# Where you replace # <Port> by the master's port number (3306 by default ).
#
# Example:
#
# Change master to master_host = '192. 564.12.1 ', master_port = 125,
# Master_user = 'job', master_password = 'secret ';
#
# Or
#
#2) set the variables below. However, in case you choose this method, then
# Start replication for the first time (even unsuccessfully, for example
# If you mistyped the password in master-password and the slave fails
# Connect), the slave will create a master.info file, and any later
# Change in this file to the variables 'values below will be ignored and
# Overridden by the content of the master.info file, unless you shutdown
# The slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (Commented) and instead use change master to (see above)
#
# Required unique ID between 2 and 2 ^ 32-1
# (And different from the master)
# Defaults to 2 if master-host is set
# But will not function as a slave if omitted
# Server-id = 2
#
# The replication master for this slave-required
# Master-host = #
# The username the slave will use for authentication when connecting
# To the master-required
# Master-user = <username>
#
# The password the slave will authenticate with when connecting
# The Master-required
# Master-Password = <password>
#
# The port the master is listening on.
# Optional-defaults to 3306
# Master-Port = <port>
#
# Binary logging-not required for slaves, but recommended
# Log-bin = mysql-bin
#
# Binary logging format-mixed recommended
# Binlog_format = mixed

# Uncomment the following if you are using InnoDB tables use the InnoDB Configuration
# Innodb_data_home_dir =/usr/local/MySQL/Data InnoDB data storage directory
# Innodb_data_file_path = ibdata1: 2000 m; ibdata2: 10 m: autoextend Specifies the storage space for table data and indexes. It can be one or more files. The last data file must be automatically expanded, and only the last file can be automatically expanded. In this way, when the space is used up, the automatic expansion of data files will automatically increase
# Innodb_log_group_home_dir =/usr/local/MySQL/Data Log location
# You can set .. _ buffer_pool_size up to 50-80%
# Of Ram but beware of setting memory usage too high
# Innodb_buffer_pool_size = 384 m Defines the maximum memory buffer size for table data and index data of the InnoDB Storage engine. Unlike the MyISAM storage engine, MyISAM's key_buffer_size can only cache index keys, while innodb_buffer_pool_size can cache data blocks and index keys. Appropriately increasing the size of this parameter can effectively reduce the disk I/O of InnoDB tables. On an InnoDB-based dedicated database server, you can set this parameter to 60%-80% of the physical memory size.
# Innodb_additional_mem_pool_size = 20 m This parameter is used to set the data directory information stored in InnoDB and the memory pool size of other internal data structures. The more tables there are in the application, the more memory you need to allocate here. For a relatively stable application, the parameter size is also relatively stable, and there is no need to reserve a very large value. If InnoDB uses up the memory in this pool, InnoDB allocates memory from the operating system and writes warning information to the MySQL error log. The default value is 1 MB. When the error log contains related warning information, the parameter size should be appropriately increased.
# Set .. _ log_file_size to 25% of buffer pool size
# Innodb_log_file_size = 100 m
# Innodb_log_buffer_size = 8 m
# Innodb_flush_log_at_trx_commit = 1 If set to 1, InnoDB fl transaction logs on each committed disk, which provides complete acid behavior. If you want to be secure and are performing some small operations, you can set it to 0 or 2 to reduce disk I/O logs.

# Innodb_lock_wait_timeout = 50 InnoDB has its built-in Deadlock Detection Mechanism, which can cause unfinished transaction rollback. However, if InnoDB is used with the lock tables Statement of MyISAM or a third-party transaction engine, InnoDB cannot identify the deadlock. To eliminate this possibility, you can set innodb_lock_wait_timeout to an integer indicating how long MySQL will wait before allowing other transactions to modify the data that is eventually rolled back by the transaction (in seconds)

[Mysqldump]
QuickIf the -- quick or -- opt option is not specified, the entire result set is stored in the memory. Problems may occur if you export a large database.
Max_allowed_packet = 16 m

[MySQL]
No-auto-rehash
# Remove the next comment character if you are not familiar with SQL
# Safe-Updates

[Myisamchk]
Key_buffer_size = 256 m
Sort_buffer_size = 256 m
Read_buffer = 2 m
Write_buffer = 2 m

[Mysqlhotcopy]
Interactive-TimeoutThe number of seconds before the server closes the interactive connection. The Interactive Client is defined to be used in mysql_real_connect ().

 

Other files: my-large.cnf my-medium.cnf my-small.cnf is configured for different machine performance. My-innodb-heavy-4G.cnfThis is an example of a MySQL configuration file for a 4G memory system (mainly running MySQL with only InnoDB tables and performing complex queries using several connections.

 

You can modify the configuration of my. CNF to optimize MySQL performance. The storage location of my. CNF files affects the configuration validity.