MySQL Common configuration and optimization

[Client]
# content in this directory is commonly used for localhost login, generally do not need to modify
Port = 3306# Port number
Socket =/var/lib/mysql/mysql.sock# socket file (localhost login will be generated automatically)

[MySQL]
# contains some configuration of the client MySQL command line
No-auto-rehash # Default does not auto-complete Auto-rehash Auto Completion

[Mysqld]
# mysql optimized configuration directory, in addition to hardware and environment configuration, all optimized in this configuration,
# General Server installation only this configuration directory
user = mysql# default startup users, generally do not need to modify, may appear unsuccessful startup
Port = 3306# Port number
Socket =/var/lib/mysql/mysql.sock# Socket file (socket mode login is faster than TCP/IP connection)
Character-set-server = utf8# Set database server default encoding Utf-8
Basedir =/usr/local/mysql# Database installation directory--Specify this parameter to resolve problems with relative paths
DataDir =/data/mysql# database directory, need to use when database directory is switched
Pid-file =/var/run/mysqld/mysqld.pid#mysql process file, you can specify your own process files
external-locking = false# External Lock (non-multi-server does not set this option, default skip-external-locking)
skip-external-locking# Skip External Lock (avoid external locking--lock mechanism in multi-process environment)
skip-name-resolve# Skip host name resolution, direct IP access, can improve access speed
Log-error =/data/log/mysqld.log# error log file
log-warnings# defaults to 1, which means logging of warning messages is enabled, no 0 is required, and more than 1 indicates error or failed connection log
Open_files_limit = 10240# Global read-only variable, file descriptor limit Note: The upper limit is actually the OS file descriptor limit, which is less than the OS limit available lsof view restrictions and modify the configuration

#以下配置较为重要
Back_log = #在MYSQL暂时停止响应新请求之前, how many requests in a short period of time can be present in the stack. No more than TCP/IP listening queue, 512 times times the recommended number
#如果系统在短时间内有很多连接, you need to increase the value of this parameter to specify the size of the listening queue for incoming TCP/IP connections. The default value is 50.
Max_connections = 5000#mysql allows the maximum number of process connections, this value needs to be increased if too many connections error is frequently present
Max_connect_errors = 6000# Sets the maximum number of times each host's connection request is interrupted, and when this number is exceeded, the MySQL server disables the host connection request.
#直到mysql服务器重启或通过flush the hosts command to clear information about this host
Max_allowed_packet = 32m# limits the size of the received packet, and the insertion or update fails when a single data exceeds the value.
#show VARIABLES like '%max_allowed_packet% '; View current limit size
Sort_buffer_size = 8m# per connection exclusive, for optimization of group and order that cannot be optimized by SQL or index, set the value should be moderate
#比如: 500 connections, 500*8 = 4G, consumes 4G of memory
Join_buffer_size = 8m# for the size of the associated cache between tables, each connection is exclusive
#thread_concurrency = 16# should be set to twice times the number of CPU cores.
Thread_cache_size = 600# Thread cache variable, reduce connection creation, general settings rules
#1G->8;2g->16;3g->32; According to the actual situation can be appropriately increased (personal: 2^ (n+2))
Thread_stack = 1024k# Set the stack size of MySQL per thread, can be set to 128K to 4GB, default is large enough, generally do not modify
Query_cache_size = 128m# query cache, settings should not be too large, mainly because the larger the cache, the longer the set failure time. If high concurrency is written, you can disable the feature
Query_cache_limit = 2m# Specifies the buffer size that can be used by a single query, which defaults to 1M
Query_cache_min_res_unit = 4k# Big Data query 4k, small data query 2k, the purpose is to improve the cache hit ratio

#数据库引擎相关参数
Default-storage-engine = innodb# default Database engine
Innodb_data_file_path = ibdata1:1024m:autoextend# specified data file, initial size, specified extension size note consistent with the initial file size in the database
#格式: Size-allocation[:autoextend[:max-size-allocation]]
innodb_read_io_threads = 16#read io thread, set according to the number of CPU core threads
innodb_write_io_threads = 16#write io thread, set according to the number of CPU core threads
Innodb_thread_concurrency = 48# server has several CPUs set to a few, the recommended default setting, typically 8
Innodb_lock_wait_timeout = 120# transaction Gets the maximum wait time for the lock and the timeout request fails
Innodb_buffer_pool_size = 80g# an appropriate increase in key_pool_size similar to MyISAM increases the hit rate, and the dedicated server can be set to 70-80%
#innodb_flush_log_at_trx_commit = The default value of 1 per commit logging Disk 2nd log write system cache 0 not committed nor logged, unsafe, not recommended
Innodb_flush_method = o_direct# Control innodb data file and redo log open, brush write mode
#fdatasync (default), call Fsync () to swipe the data file with the buffer of the redo log
#O_DSYNC, InnoDB will use O_sync to open and write redo log, and use Fsync () to brush and write data files
#O_DIRECT, InnoDB use O_direct to open the data file, use the Fsync () to write the data file with redo log
#fsync () Effect: The number effect is flush when the buffer brush written to disk is associated with the file referred to in the FD file descriptor
Innodb_log_files_in_group = * Writes log files to multiple files in a circular fashion, default 2
innodb_log_file_size = 4g# Data log file size, large to improve performance,
Innodb_log_buffer_size = The amount of memory used by the 512m# log file, in M. Larger buffers can improve performance, but unexpected failures will lose data (developer recommended 1-8m---don't know why)
#innodb_file_per_table = # Independent tablespace innodb default table space
#
Innodb_autoinc_lock_mode = The main function of the self-increment column
#0 This represents the tradition tradition of getting the lock of a statement level, with continuity and repeatability, but affecting concurrent insertions
#1 This indicates that consecutive continuously generates successive values based on the number of disposable inserts,
#auto_inc锁不需要一直保持到语句的结束, the statement gets the corresponding value so that the lock can be released early
#2 this represents interleaved interleaving without auto_inc locks, auto_incremant values may not be contiguous

#Percona XtraDB Cluster 5.7. Multi-master MySQL cluster solution based on synchronous replication, supporting only the InnoDB engine
#需要安装wsrep打过补丁的版本
#:https://www.percona.com/downloads/

Pxc_strict_mode = Enforcing
wsrep_cluster_address = gcomm://192.168.66.242,192.168.66.241 #集群中的节点地址, host name or IP can be used
wsrep_node_address = 192.168.66.241# Native node address, can use host name or IP
Wsrep_provider =/usr/lib64/galera3/libgalera_smm.so
Wsrep_sst_method = xtrabackup-v2# Specifies the SST mode, supports rsync (fastest, requires lock table),
#mysqldump和xtrabackup, support Xtrabackup-v2 from 5.5.33-23.7.6
Wsrep_slave_threads = number of 8# threads
Wsrep_cluster_name = cluster# Cluster name, must be unified
Wsrep_node_name = node name in node1# cluster, must be unique
Wsrep_sst_auth = "Sstuser:8juthgnaufwefj9" #xtrabackup使用的用户名密码, created when the first node is started, see installation documentation
#wsrep_sst_donor = ' Node3, ' #一个逗号分割的节点串作为状态转移源,
#比如wsrep_sst_donor =node5,node3, if NODE5 is available, use NODE5, not used with NODE3,
#如果node3不可用, the last comma indicates that the provider chooses an optimal one.

Transaction_isolation = read-committed# Transaction ISOLATION LEVEL
# 1.read-uncommitted-READ UNCOMMITTED-dirty read (not recommended for development and production)
# 2.read-committe-Read COMMITTED--non-default
# 3.repeatable-read-REPEATABLE READ-phantom rows may appear (INNODB and Falcon solve the phantom Read problem by concurrency)
# 4.SERIALIZABLE-Serial-high levels of time-out and lock contention may occur
Tmp_table_size = 10g# the size of the temporary table generated by the query exceeds this value when a MyISAM table is generated on the hard disk and can be resized if there are a large number of statements such as GROUP by
max_heap_table_size = 5g# Memory table maximum space limit table on disk, data in memory
explicit_defaults_for_timestamp=1# is primarily for timestamp columns, does not specify that the default is automatically added to the Notnull property, and the first column automatically adds the current timestamp
Table-definition-cache = 4096# table Definition related information The cache actually holds the mapping relationship between frm and memory
Table-open-cache = 4096# Open Table cache holds open Table handle
#同时设置生效
Interactive_timeout = 120#mysql number of seconds to wait before closing an interactive connection (client connection)
Wait_timeout = 864000#mysql The number of seconds to wait before closing a non-interactive connection by default of 8 hours (application call),
#根据应用实际情况决定 show Processlist; The sleep process is correspondingly smaller
Long_query_time = * Slow query timeout setting, default 10 seconds, record statements that exceed query time
slow-query-log=1# Note that the previous version before version 5.6 for log-slow-query is logging slow query log---as a database analysis
slow-query-log-file=/data/log/query-slow.log# slow query log record file
#相关二进制文件设置
log_bin# database operation binary record, database backup, replication required
Binlog_format = Row#row (Row-based replication--security, but note concurrency) STATEMENT (SQL statement-based replication), MIXED (mixed-mode replication)
binlog_cache_size = 4m# binary log cache for improved Log-bin recording efficiency
log_bin_trust_function_creators = # master-slave replication is important to note, in order to ensure that the master-slave replication is fully consistent, need to turn on this option, the master-slave default block function
max_binlog_size = 1g# binary log file size default 1G requires greater than 4096 less than 1G
Expire_logs_days = 7# Purge expired logs
#binlog_do_db # This parameter indicates that only the binary log of the specified database is logged
#binlog_ignore_db # ignoring a database record

Key_buffer_size = 2048m# Increase the query hit rate, preferably not more than 4G, according to the cache hit rate appropriate adjustment show global status like ' key_read% ';
Read_buffer_size = 16m# Sequential read cache, exclusive for each connection
Read_rnd_buffer_size = 64m# Random Read cache, per connection exclusive
Bulk_insert_buffer_size = 1g# BULK INSERT and only use this cache when adding data to a non-empty table, only for MyISAM tables.

#主从复制相关--must be turned on Log-bin
Server-id = 19911216# Master-slave replication must be, and each server has uniqueness
log_slave_updates# Configure whether updates from the server are written to the binary log, which is not turned on by default
REPLICATE-IGNORE-DB = mysql# default ignored database for master-slave replication, "," to separate or use multiple records
#replicate-do-db=qrs,login# Master-slave replication Specifies the database, "," number separated or using multiple records

[Mysqldump] #数据库全量备份
quick# forcing mysqldump to retrieve rows from the server one row at a time
Max_allowed_packet = 32m# can receive packet size

[Isamchk] #在mysqld服务器不使用的情况下修复表或在崩溃状态下恢复表
Key_buffer = 1024M
Sort_buff_size =1024m
Read_buffer = 16M
Write_buffer = 16M

[Myisamchk] #在mysqld服务器不使用的情况下修复表或在崩溃状态下恢复表
Key_buffer = 1024M
Sort_buff_size = 1024M
Read_buffer = 16M
Write_buffer = 16M

[Mysqld_safe] #safe方式启动数据库, compared to mysqld, will continue to monitor service status after service startup, the dead machine restarts
Open-files-limit = 8192

MySQL Common configuration and optimization