Basic configuration and optimized configuration methods of files in MySQL

[client]

port   = 3306  #The client port number is 3306
socket  =/data/3306/ #

default-character-set = utf8  #Client character set, (control character_set_client, character_set_connection, character_set_results)
 

[mysql]

no-auto-rehash  
# Only updates and deletes with key values ​​are allowed
 

[mysqld]  
The # group includes parameters for starting mysqld service, which involves many aspects, including MySQL directories and files, communications, networks,# Information security, memory management, optimization, query cache area, and MySQL log settings.
user    = mysql  
#mysql_safe script uses MySQL to run users (specified at compile time --user=mysql), and it is recommended to use mysql user.
port    = 3306  
# The port number of the MySQL service runtime.  It is recommended to change the default port, which is vulnerable to attacks by default.
socket  =/data/3306/  #socket files are unique in Linux/Unix environments. Users can directly connect to MySQL using unix sockets without going through TCP/IP networks.
basedir = /application/mysql  
# The path stored in mysql program is often used to store mysql startup, configuration files, logs, etc.
datadir = /data/3306/data  
# MySQL data storage files (extremely important)
character-set-server = utf8  
# The default character set for databases and database tables.  (Utf8 is recommended to avoid garbled code)
log-error=/data/3306/mysql_xuliangwei.err  
# MySQL error log storage path and name (If you start errors, you must look at the error log, and 100% can be solved by inserting error logs.)
pid-file=/data/3306/mysql_xuliangwei.pid  
# The MySQL_pid file records the pid of the current mysqld process, which is the ProcessID.
skip-locking  
# Avoid MySQL's external locking, reduce the chance of errors, and enhance stability.
skip-name-resolv  
# Disable MySQL for DNS resolution on external connections. Use this option to eliminate MySQL's DNS resolution.# However, it should be noted that if this option is enabled, all remote host connection authorizations must use the IP address method.# Otherwise, MySQL will not be able to handle connection requests normally!
skip-networking  
# Turn on this option to completely turn off MySQL's TCP/IP connection method.# If the web server accesses the MySQL database server through a remote connection,# Do not enable this option, otherwise the connection will not be possible!
open_files_limit    = 1024  
# The maximum number of files that MySQLd can open. If too mant openfiles appears, you need to adjust the value.
back_log = 384  
# back_log parameter is a value indicating how many requests can be stored in the stack in a short time before MySQL temporarily stops responding to new requests.# If the system has many connections in a short time, the value of this parameter needs to be increased.# This parameter value specifies the size of the listening queue for the incoming TCP/IP connection.# Different operating systems have their own limitations on the size of this queue.# If you try to set back_log higher than the operating system limit, it will be invalid, and its default value is 50. For Linux systems,# It is recommended to set to an integer less than 512.
max_connections = 800 
# Specify the maximum number of connection processes allowed by MySQL.# If you often see errors about Too Many Connections when visiting a blog, you need to increase the value of this parameter.
max_connect_errors = 6000  
# Set the maximum number of abnormal interrupts for each host's connection request. When this number exceeds,# MySQL server will prohibit the connection request of the host.# Until the MySQL server restarts or clears the relevant information of this host through the flush hosts command.
wait_timeout = 120  
# Specify the maximum connection time for a request. For servers with about 4GB of memory, it can be set to 5~10.
table_cache = 614K  
# table_cache indicates the size of the table cache.  When MySQL accesses a table, if there is still space in the MySQL buffer,# Then this table is opened and put into the table buffer. The advantage of this is that it can access the content in the table more quickly.  Generally speaking,# You can view the status values ​​of the peak time of the database running Open_tables and Open_tables to determine whether it needs to be added.# the value of table_cache, that is, if Open_tables is close to table_cache,# And the value of Opened_tables is gradually increasing, so you need to consider increasing the size of this value.
external-locking = FALSE  
# MySQL option can avoid external locking.  True is enabled.
max_allowed_packet =16M  #The server can handle the maximum query packet value at a time, which is also the largest query that the server program can handle.
sort_buffer_size = 1M  #Set the buffer size that can be used when sorting the query. The default size of the system is 2MB.
Notice：The allocated memory corresponding to this parameter is exclusive to each connection，If there is100Connections，Then the total sort buffer size of the actual allocated is100 x6=600MB。so，For the In-Existence4GBFor the servers on the left and right，It is recommended to set it to6MB~8MB

join_buffer_size = 8M 
# The buffer size that can be used by the joint query operation is the same as sort_buffer_size.# The allocated memory corresponding to this parameter is also exclusive to each connection.
thread_cache_size = 64 #Set the maximum number of connection threads that can be cached in the Thread Cache pool. It can be set to 0~16384, and the default is 0. This value indicates that the number of threads stored in the cache can be reused. When disconnected, if there is still space in the cache, the client's thread will be put into the cache; if the thread is requested again, the request will be read from the cache. If the cache is empty or a new request, the thread will be recreated. If there are many threads, increasing this value can improve system performance.  By comparing the variables in the Connections and Threads_created states, you can see the role of this variable.  We can set the rules according to the physical memory as follows: 1GB memory we configure as 8, 2GB memory we configure as 16, 3GB we configure as 32, 4GB or above we give this value 64 or more.
thread_concurrency = 8  #The value of this parameter is x 2 for the server logical CPUs. In this example, the server has two physical CPUs, and each physical CPU supports hyperthreading, so the actual value is 4 x 2 = 8.  This is also the configuration of dual quad-core mainstream servers.
query_cache_size = 64M #Specify the size of the MySQL query buffer.  You can observe in the MySQL console that if the value of Qcache_lowmem_prunes is very large, it means that there is often insufficient buffering; if the value of Qcache_hits is very large, it means that the query buffering is used very frequently.  In addition, if the change value is small, it will affect the efficiency, so you can consider not querying buffering.  For Qcache_free_blocks, if the value is very large, it means that there is a lot of fragmentation in the buffer.
query_cache_limit = 2M  #Only results that are less than this setting will be cached
query_cache_min_res_unit = 2k  #Set the minimum unit of memory allocated by query cache. You must set this parameter appropriately to reduce the number of memory fast applications and allocations, but setting too large may cause the memory fragmentation value to rise.  The default value is 4K, and it is recommended to set it to 1K~16K.
default_table_type = InnoDB  #The default table type is InnoDB
thread_stack = 256K  #Set the stack size of each thread of MySQL. The default value is large enough to meet ordinary operations.  The range can be set from 128KB to 4GB, and the default is 192KB.
#transaction_isolation = Level #Database isolation level (READ UNCOMMITTED (read unsubmitted content) READ COMMITTED (read submission content) REPEATABLE READ (rereadable) SERIALIZABLE (serialized))
tmp_table_size = 64M  #Set the maximum value of the temporary table in memory.  If this value is exceeded, a temporary table is written to disk, with a range of 1KB to 4GB.
max_heap_table_size = 64M  #The maximum capacity allowed by a standalone memory table.
table_cache = 614 #The memory allocated to frequently accessed tables, the larger the physical memory, the larger the settings.  By increasing this value, the disk IO can generally be reduced, but it will consume more memory accordingly. This is set to 614.
table_open_cache = 512  #Set the number of table caches.  At least one table cache will be opened whenever each connection comes in.  Therefore, the size of table_cache should be related to the setting of max_connections.  For example, for 200 parallel running connections, the cache of the table should be at least 200 × N, where N is the maximum number of tables in a join in which the application can perform.  In addition, some additional file descriptors need to be reserved for temporary tables and files.
long_query_time = 1  #The upper limit of execution time for slow query, the default setting is 10s, recommended (1s~2s)
log_long_format  #Queers without indexes will also be recorded.  (Recommended, adjusted according to business)
log-slow-queries = /data/3306/  #Slow query log file path (if slow query is enabled, it is recommended to open this log)
log-bin = /data/3306/mysql-bin #Logbin database operation logs, such as update, delete, create, etc., will be stored in binlog logs. Incremental recovery can be achieved through logbin
relay-log = /data/3306/relay-bin #relay-log log records the slave server I/O thread reads the binary log of the master server and records it to the slave server local file. Then the SQL thread will read the content of the relay-log log and apply it to the slave server.
relay-log-info-file = /data/3306/  #The file that is used by the server to record the relay log related information, the default name is in the data directory.
binlog_cache_size = 4M  # In a transaction, binlog records the cache size held by SQL state. If you often use large, multi-declared transactions, you can increase this value to obtain greater performance. All states from transactions are buffered in the binlog buffer, and then submitted and written to the binlog at one time. If the transaction is larger than this value, temporary files on disk will be used instead. This buffer is created when each linked transaction updates the status for the first time.
max_binlog_cache_size = 8M  #Maximum binary cache log buffer size.
max_binlog_size = 1G  #Maximum length of binary log file (default setting 1GB) Before a binary file information exceeds this maximum length, the MySQL server will automatically provide a new binary log file to continue.
expire_logs_days = 7  #Binlog for more than 7 days, the mysql program will be automatically deleted (if the data is important, it is recommended not to enable this option)
key_buffer_size = 256M  #Specify the buffer size used for indexing, increasing it can achieve better index processing performance.  For servers with about 4GB of memory, this parameter can be set to 256MB or 384MB.
Notice：If the parameter value is set too large, the overall efficiency of the server will be reduced.！

read_buffer_size = 4M  #Read the buffer size that can be used by query operations.  Like sort_buffer_size, the allocated memory corresponding to this parameter is also exclusive to each connection.
read_rnd_buffer_size = 16M #Set the buffer used when performing random reading.  This parameter is opposite to the Buffer set by read_buffer_size. One is used when reading sequentially, and the other is used when reading randomly.  But both are for the setup with threads, each thread can produce any of the two types of buffers.  The default value is 256KB and the maximum value is 4GB.
bulk_insert_buffer_size = 8M  #If you often need to use special statements for batch insertion to insert data, you can adjust the parameters appropriately to 16MB~32MB, and 8MB is recommended.
#myisam_sort_buffer_size = 8M #Set the buffer size allocated to sort the index during the REPAIR Table or Create Index or Alter Table. You can set the range 4Bytes to 4GB, and the default is 8MB
lower_case_table_names = 1  #Implement MySQL does not distinguish between sizes.  (Development requirements - recommended to enable)
slave-skip-errors = 1032,1062 #The error numeric value that can be skipped from the library (mysql error is feedback with numeric code, a complete collection of mysql error codes will be published to the blog in the future).
replicate-ignore-db=mysql  #In the case of master-slave, set up libraries that do not need to be synchronized.
server-id = 1  # indicates that the serial number of the machine is 1. If it is a master or slave, or multiple instances, the serverid must not be the same.
myisam_sort_buffer_size = 128M  #When it is necessary to re-index for executing REPAIR, OPTIMIZE, ALTER statements, MySQL will allocate this cache, and LOAD DATA INFILE will load into a new table, which will carefully allocate each thread according to the maximum configuration.
myisam_max_sort_file_size = 10G #MySQL is allowed to use the maximum value of the temporary file when re-index (REPAIR, ALTER, TABLE, or LOAD, DATA, TNFILE).
myisam_repair_threads = 1 #If a table has more than one index, MyISAM can fix them using more than one thread through parallel sorting.
myisam_recover #Automatically check and repair MyISAM tables that are not properly closed.
innodb_additional_mem_pool_size = 4M # Used to set the memory pool size of InnoDB stored data directory information and other internal data structures.  The more tables you have in your application, the more memory you need to allocate.  For a relatively stable application, the size of this parameter is also relatively stable, and there is no need to reserve very large values.  If InnoDB uses memory in this pool, InnoDB starts allocating memory from the operating system and writes warning messages to the MySQL error log.  The default is 1MB. When you find that there are already relevant warning information in the error log, the size of this parameter should be appropriately increased.
innodb_buffer_pool_size = 64M #InnoDB uses a buffer pool to store indexes and raw data. The larger the settings, the less disk I/O is required when accessing data in the table.  It is strongly recommended not to arbitrarily configure the Buffer Pool value of InnoDB to 50%~80% of physical memory, which should be determined according to the specific environment.
innodb_data_file_path = ibdata1:128M:autoextend  # Set to configure a separate file with an extensible size of 128MB, named ibdata1. The location of the file is not given, so the default is in the MySQL data directory.
innodb_file_io_threads = 4  #File I/O thread in InnoDB.  Usually set to 4. If it is Windows, you can set a larger value to increase disk I/O.
innodb_thread_concurrency = 8 #If your server has several CPUs, it is recommended to use the default settings, generally set to 8.
innodb_flush_log_at_trx_commit = 1 #Setting to 0 means that the innodb_log_buffer_size queue is stored in a unified manner after it is full. The default is 1, which is also the safest setting.
innodb_log_buffer_size = 2M  #The default is 1MB, and usually set to 8~16MB is enough.
innodb_log_file_size = 32M  #Determine the size of the log file. Larger settings can improve performance, but also increase the time to recover the database.
innodb_log_files_in_group = 3 #To improve performance, MySQL can write log files to multiple files in a loop.  Recommended setting is 3.
innodb_max_dirty_pages_pct = 90 #InnoDB main thread refreshes data in the cache pool.
innodb_lock_wait_timeout = 120 #InnoDB transactions can wait for a locked timeout number before they are rolled back.  InnoDB automatically detects transaction deadlocks and rolls back transactions in its own lock table.  InnoDB notices the lock settings using the locak tables statement.  The default value is 50 seconds.
innodb_file_per_table = 0  #InnoDB is an independent tablespace schema, and each table in each database generates a data space.  0 off, 1 on.
[mysqldump]

quick

max_allowed_packet = 2M  #Set the maximum value of the number of messages transmitted in network transmission。The system default value is1MB，The maximum value is1GB，Must be set to1024multiples of。Units are bytes。