MySQL my.cnf file (fix 5.7.18 under no my-default.cnf)

The official website says: The my-default.cnf file is not available in the binary package starting from 5.7.18. Reference: https://dev.mysql.com/doc/refman/5.7/en/binary-installation.html

After testing, in version 5.7.18, when using tar.gz installation, that is, the compressed package is extracted to install this, no longer need to my.cnf files can also function properly.

The my.cnf file is configured with the CNF file when you start MySQL on the command line, so you no longer need to add parameters to the command when the download is started.

This my.cnf file can be a custom location, or you can use the default location, as long as it is placed in the default location, MySQL automatic recognition (installed through the Deb or apt source, the initial location in the list below):

file name	Purpose
/etc/my.cnf	Global options
/etc/mysql/my.cnf	Global options
SYSCONFDIR/my.cnf	Global options
$MYSQL_HOME/my.cnf	Server-specific options (server only)
defaults-extra-file	The specified file --defaults-extra-file , if any
~/.my.cnf	User-specific options
~/.mylogin.cnf	User-specific Login path options (client only)

The above detailed explanation can refer to the official explanation: https://dev.mysql.com/doc/refman/5.7/en/option-files.html

In summary, whether using the APT source installation or the Deb package installation, or the binary (compressed package tar.gz) package installed, can be configured through the my.cnf file to achieve MySQL boot configuration and tuning.

Since the binary package no longer contains the sample file My-default.cnf at the beginning of 5.7.18, I extracted the sample from version 5.7.17, but found that there are not too many configurations, MY-DEFAULT.CNF content is as follows:

# forAdvice onHow to change settings# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.HTML# *** Do  notEDIT this FILE.It ' s a template which 'll be copied to the# * * * default location during install, and'll be replacedif You# * * * * Upgrade to a newer version of MySQL.[Mysqld]# Remove Leading # andSetTo the amount of RAM forThe most important data#CacheIn MySQL.StartAt 70% of Total RAM forDedicated server,Else10%.# innodb_buffer_pool_size =128M# Remove Leading # to turn ona very important data integrity Option:logging# Changes to the binary log between backups.#Log_bin# these is commonlySet, remove the # andSetAs required.# Basedir = ... # datadir = ... # port = ..... # server_id = .... # socket = ..... # Remove leading # to.SetOptions mainly useful forReporting servers.# the server defaults is faster forTransactions and fast selects.# Adjust sizes as needed, experiment toFindThe optimal values.# join_buffer_size =128M# sort_buffer_size =2M# read_rnd_buffer_size =2M Sql_mode=no_engine_substitution,strict_trans_tables

In fact, these items are command line parameters, on the official website from this page https://dev.mysql.com/doc/refman/5.7/en/option-files.html as the portal, find the items that need to be configured to configure as needed.

The following is an example of a my.cnf from http://www.fx114.net/qa-220-164752.aspx:

[Client]port= 3306Socket=/tmp/mysql.Sock[mysqld]############################## #基础设置 ######################################unique number of MySQL service each MySQL service ID needs to be unique Server-id= #Service port number default 3306port= 3306#MySQL installation root directory basedir=/opt/MySQL#MySQL data file location DataDir=/opt/mysql/Data#temporary directories such as load data infile will use Tmpdir= /tmp#set the directory where the Socke file is located socket=/tmp/mysql.sock#主要用于MyISAM存储引擎,If multiple servers connect to a database, it is recommended to comment the following skip-external-locking#only the IP address can be used to check the client's login, without the host name Skip_name_resolve= #transaction ISOLATION level, the default is repeatable read, MySQL default repeatable read level (this level may be a lot of parameters gap lock, affect performance) Transaction_isolation=read-committed#数据库默认字符集,The mainstream character set supports some special emoji (special emoji take 4 bytes) character-Set-server =UTF8MB4#数据库字符集对应一些排序等规则, be careful with character-.Set-server Correspondence Collation-server=Utf8mb4_general_ci#设置client连接mysql时的字符集,Prevent garbled Init_connect=SETNAMES utf8mb4 '#whether the SQL statement is case-sensitive, 1 means insensitive lower_case_table_names= #Maximum number of connections Max_connections= 400#Maximum number of error connections max_connect_errors= 1000#timestamp null value is allowed if the declaration is not null Explicit_defaults_for_timestamp=true#the size of the SQL packet sent if a BLOB object is proposed to be modified into 1gmax_allowed_packet=128M#MySQL连接闲置超过一定时间后 (units: seconds)will be forcibly closed .The #MySQL默认的wait_timeout value is 8 hours,The interactive_timeout parameter needs to be configured simultaneously to take effect interactive_timeout= 1800Wait_timeout= 1800#the maximum value of the internal memory temporary table, set to 128M. #比如大数据量的group by,A temporary table may be used when order by.#exceeding this value will be written to disk, system IO pressure increased tmp_table_size= 134217728max_heap_table_size= 134217728#Disable the Cache query result set feature for MySQL#later, according to the business situation test decide whether to open#The following two items are closed in most cases query_cache_size= 0Query_cache_type= 0#################### #用户进程分配到的内存设置BEGIN ###############################each session will allocate the memory size of the parameter settings#for sequential scanning of tables, the readout data is staged in read_buffer_size, and when the buff is full or read, the data is returned to the upper caller#一般在128kb ~ 256kb,for MyISAM#read_buffer_size = 131072#used for random reading of tables, which is used when sorting by a non-indexed field,#一般在128kb ~ 256kb,for MyISAM#read_rnd_buffer_size = 262144#order BY or GROUP by#It is recommended to adjust to 2M, post-observation adjustment sort_buffer_size= 2097152#There is no large transaction in the general database, set to 1~2m, default 32kbbinlog_cache_size= 524288####################### #用户进程分配到的内存设置END #############################how many requests can be present in the stack in a short period of time before MySQL temporarily stops responding to a new request#官方建议back_log = + (MAX_CONNECTIONS/5),cap number is 900back_log= 130########################### #日志设置 ###########################################Database error log file Log_error= error.Log#slow query SQL log settings Slow_query_log= 1Slow_query_log_file= Slow.Log#Check for sqllog_queries_not_using_indexes that are not used to the index= #for log_queries_not_using_indexes, record the frequency of slow SQL and the number of records per minute log_throttle_queries_not_using_indexes= AA as effective from the library,How to have slow SQL from library copy will also be logged log_slow_slave_statements= #The number of seconds that slow query execution must reach this value can be logged Long_query_time= 8#the number of rows retrieved must reach this value to be remembered as a slow query min_examined_row_limit= 100#mysql binlog log file save expiration time, automatically delete after expiration expire_logs_days= 5########################### #主从复制设置 ######################################turn on MySQL binlog function Log-bin=Mysql-bin#Binlog The way the contents are recorded, each line of the action is recorded Binlog_format=ROW#对于binlog_format =row mode, reduce the contents of the logging log, only the affected columns are logged Binlog_row_image=Minimal#master Status and connection information output to table MySQL.Slave_master_info in Master_info_repository=TABLE#the slave ' s position in the relay logs output to table MySQL.Slave_relay_log_info in Relay_log_info_repository=TABLE#作为从库时生效,if you want to cascade replication, this parameter is required log_slave_updates#作为从库时生效,The trunk log relay-log can be self-healing relay_log_recovery= # as effective from the library,error slave_skip_errors when master-slave replication is ignored=ddl_exist_errors#################### #redo Log and Binlog relationship settings begin########################## (step 1)prepare DML-related SQL operations and then persist the cache in the Redo log buff to disk# (Step 2)If the previous prepare succeeds, then continue persisting the transaction log to Binlog# (Step 3)if it succeeds, write a commit record in redo log.#when Innodb_flush_log_at_trx_commit and Sync_binlog are 1 o'clock are the safest,#in the case of a mysqld service crash or a server host crash, binary log can only lose a maximum of one statement or transaction. #However, setting it to 1 causes frequent IO operations, so this mode is also the slowest way. #when Innodb_flush_log_at_trx_commit is set to the 0,MYSQLD process, a crash causes all transactional data to be lost in the last second. #when the Innodb_flush_log_at_trx_commit is set to 2, all transactional data may be lost in the last second only if the operating system crashes or the system loses power. #commit事务时,Control Redo Log Buff persistent disk mode defaults to 1innodb_flush_log_at_trx_commit= When a transaction is 2#commit,The default mode for controlling writes to the MySQL Binlog log is 0#Innodb_flush_log_at_trx_commit and Sync_binlog are 1 o'clock, MySQL is the safest but the performance get stress-is also the largest sync_binlog= 1################### #redo log and Binlog relationship settings end####################################################### #Innodb设置 # # ####################################Data Block Unit 8k, the default is 16K,16KCPU pressure is slightly smaller, 8k on the high throughput of select#innodb_page_size parameter values also affect the maximum index length, 8k is smaller than the maximum index length of 16k#innodb_page_size = 8192# General set 60% ~ 70 of physical storage%innodb_buffer_pool_size=1G#5.7.6 after default 16M#innodb_log_buffer_size = 16777216# This parameter is directly annotated on UNIX, Linux,window.default value is null#O_direct reduces the conflict between the OS-level VFS cache and the buffer cache of the InnoDB itself Innodb_flush_method=O_direct#此格式支持压缩, 5.7.after 7 is the default value Innodb_file_format=Barracuda#CPU Multi-core processing capability setting, assuming that the CPU is 2 4 cores, set the following#read more, write less can be set to 2:6 proportion innodb_write_io_threads= 4innodb_read_io_threads= 4# improves the number of dirty pages flushed and merges the number of inserts, improving the disk i/o processing capacity#Default value 200 (unit: page)#This value can be determined based on the disk's recent IOPS innodb_io_capacity= 500#in order to obtain the maximum waiting time for a locked resource, the default is 50 seconds, and the following error is reported when the time is exceeded:# ERROR 1205 (HY000): Lock wait timeout exceeded;Try restarting Transactioninnodb_lock_wait_timeout= 30# Adjusts the percentage of the most recently used page read and dump in the buffer pool,you can reduce the number of page dumps by setting this parameter innodb_buffer_pool_dump_pct= 40# Sets the directory where the Redolog file is located,Redolog record Transaction specific operation content Innodb_log_group_home_dir=/opt/mysql/redolog/#设置undoLog文件所在目录,Undolog for transaction rollback operations Innodb_undo_directory=/opt/mysql/undolog/#在innodb_log_group_home_dir中的redoLog文件数,the contents of the Redolog file are circular overwrite writes. Innodb_log_files_in_group= 3#mysql5.7 Official recommendations try to set a larger size that can be approached Innodb_buffer_pool_size#Setting this value earlier may cause MySQL downtime to recover too long, and recovery is now much faster.#This value reduces the frequency of dirty data being flushed to disk#最大值innodb_log_file_size * Innodb_log_files_in_group <= 512GB, single file <=256gbinnodb_log_file_size=1024M#setting up the Undolog file takes up space that can be recycled#5.MySQL Undolog files before 7 are constantly growing and cannot be recycled innodb_undo_log_truncate= 1innodb_undo_tablespaces= 3Innodb_undo_logs= 128#5.7.7 default on this parameter control single-column index length max up to 3072#innodb_large_prefix = 1#5.7.8 default is 4,inodb number of threads for background cleanup work#innodb_purge_threads = 4#limit the number of concurrent threads by setting configuration parameter Innodb_thread_concurrency .#Once the number of execution threads reaches this limit, the extra threads will sleep for microseconds before being placed in the queue .#You can configure the sleep time by setting the parameter Innodb_thread_sleep_delay#该值默认为0,On the official doc, there are some suggestions for the use of innodb_thread_concurrency:# (1) If the number of concurrent user threads in a workload is less than 64, it is recommended to set innodb_thread_concurrency=0;# (2) If the workload has been more severe even occasionally peaking, it is recommended to first set innodb_thread_concurrency=128,## #并通过不断的降低这个参数, 96, 80,64 Wait until you find the number of threads that provide the best performance#innodb_thread_concurrency = 0# Strong all occurrences of the deadlock error message are logged to error.log, you can only view the last deadlock information from the command line Innodb_print_all_deadlocks= 1########################### #其他设置 ########################################[Mysqldump]quickmax_allowed_packet=128m[mysql]no-auto-rehash[myisamchk]key_buffer_size=20msort_buffer_size=256kread_buffer=2mwrite_buffer=2m[mysqlhotcopy]interactive-timeout[mysqld_safe]#increase the number of open files per process open-Files-limit = 28192

Tip: The above files are not recommended for all copies, and it is best to copy each one when it is needed, because each server needs to be different.

Note: The official recommendation binary file is to prevent under/usr/local, my.cnf of course can be placed in the default location after, can also be directly specified in the binary folder medium. Then now the new version of Linux is recommended to put the third-party software under the/OPT, I feel can, recommended according to the official website to configure the best, easy to troubleshoot problems.

MySQL my.cnf file (fix 5.7.18 under no my-default.cnf)