my.cnf (my.ini) important parameter optimization configuration instructions

# The following options will be read by the MySQL client application. 
# Note that only the client application that comes with MySQL is guaranteed to be able to read this section. 
# 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]
port = 3306
socket = /usr/local/mysql/mysql.sock
# MySQL Server [mysqld]
#Default storage engine INNODB
default-storage-engine=INNODB
#GROUP_CONCAT length group_concat_max_len = 99999
#Port number port = 3306
#socket location socket = /usr/local/mysql/mysql.sock 
#pid write file location pid-file = /usr/local/mysql/mysqld.pid
#Database file location datadir = /home/data/mysql/data
user = mysql
#SQL mode, please refer to relevant information sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#When external-locking is in effect, each process needs to access the data table.
#You must wait for the previous process to complete the operation and release the lock. Because the server often needs to wait for unlocking when accessing the data table,
#Therefore, external locking will degrade MySQL performance in a single-server environment.
#Therefore, in the sources of many Linux distributions, skip-external-locking is used by default in the MySQL configuration file to avoid external locking.
skip-external-locking
#Skip DNS reverse resolution skip-name-resolve
#Turn off the default value of TIMESTAMP type explicit_defaults_for_timestamp

#Not affected by the client character set, ensure the server character set skip-character-set-client-handshake
#Initial connection character set UTF8
init-connect='SET NAMES utf8'
#Default database character set character-set-server=utf8

#Query cache 0, 1, 2, representing off, on, and demand respectively
query_cache_type = 1
#Unit: seconds. If the handshake time exceeds connect_timeout, the connection request will be rejected. connect_timeout = 20
#Set the number of seconds after which the slave considers the network has timed out and the slave IO thread will reconnect to the master database if Binary Logs events from the master database are not received.
#The default value of this parameter is 3600s. However, if the time is too long, it will cause database delays or the direct link anomaly between the primary and standby databases cannot be discovered in time.
#Setting slave_net_timeout very short will cause frequent reconnections when the Master has no data updates. Generally, the online setting is 5s 
slave_net_timeout = 30

#This parameter is used to configure whether updates from the server are written to the binary log. This option is not enabled by default.
#However, if this slave server B is the slave server of server A and also serves as the master server of server C, then this option needs to be developed.
#So that its slave server C can obtain its binary log for synchronization operation log-slave-updates=1
#For slave servers, the io thread will write events with the same server id as itself into the log, which conflicts with the log-slave-updates option replicate-same-server-id=0
# By the way, generate a unique server_id. I thought about it. Everyone has a unique IP address, such as 10.112.87.91, just remove the dot and add the number 01 or 02 or 03 at the end (the two-digit number is added in case there is only one physical machine, and the master-slave replication needs server-id to identify it. # Use 10112879101 as server_id.
server_id=10112879101
# Enable binary logging. 
# In a replication configuration, this option must be turned on for the MASTER server. # If you need to do a point-in-time recovery from your last backup, you also need the binary log log-bin =/home/data/mysql/binlog/mysql-bin.log
#relay-log relay-log=mysql-relay-bin
#master-info-repository and relay-log-info-repository are turned on to enable crash-safe binary logging/slave server functionality (storing information in transaction tables instead of flat files)
master-info-repository=TABLE
relay-log-info-repository=TABLE

#Do not write to the database in the binlog binary log binlog-ignore-db=mysql # No sync databases
binlog-ignore-db=test # No sync databases
binlog-ignore-db=information_schema # No sync databases
binlog-ignore-db=performance_schema # No sync databases

#Write to binlog binary log database binlog-do-db=business_db
binlog-do-db=user_db
binlog-do-db=plocc_system

#15 Rolling cleanup binlog
expire-logs-days=15
max_binlog_size = 1073741824 # Bin logs size (1G)

# Make binlog synchronize with hard disk after every 1000 binlog writes sync_binlog = 1000

#Specify which database's data will be copied replicate-do-db=business_db
replicate-do-db=user_db
replicate-do-db=plocc_system

#Open the event scheduler Event Scheduler
event_scheduler=1
#The number of connections that MySQL can temporarily store. This comes into play when the main MySQL thread gets a very large number of connection requests in a short period of time.
#If the number of MySQL connections reaches max_connections, new requests will be stored in the stack to wait for a connection to release resources.
#The number of the stack is back_log. If the number of waiting connections exceeds back_log, no connection resources will be granted back_log = 500

#The maximum number of connections (users) for the entire database max_connections = 6000
#The maximum number of connections for a user max_user_connection=3000 
# The maximum number of errors allowed per client connection, if this limit is reached. 
# This client will be blocked from the MySQL server until "FLUSH HOSTS" is executed or the server is restarted. # Invalid passwords and other errors during connection will increase this value. 
# Check the "Aborted_connects" status to get the global counter max_connect_errors = 1844674407370954751
#Table descriptor cache size, which can reduce the number of file opening/closing times table_open_cache = 2048

# The maximum size of a request packet that the service can handle and the maximum size of a request that the service can handle (essential when working with large BLOB fields) 
# Each connection has its own independent size. The size increases dynamically max_allowed_packet = 64M
# The size of the cache that binlog holds to record SQL status in a transaction. # If you often use large, multi-statement transactions, you can increase this value to get greater performance. 
# All states from transactions will be buffered in the binlog buffer and then written to the 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 each connection's transaction updates its state for the first time binlog_cache_size = 1M
# The maximum size allowed for a standalone memory table. 
# This option is to prevent accidentally creating an oversized memory table that uses up all memory resources.
max_heap_table_size = 1342177280
# The sort buffer is 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 sort is used. # See the "Sort_merge_passes" status variable. 
# sort_buffer_size = 8M allocated by each thread when sorting occurs
# This buffer is used to optimize full JOINs (joins without indexes). 
# Similar joins have very bad performance in most cases, 
# But setting this value higher can reduce the performance impact. 
# Check the number of full joins through the "Select_full_join" status variable # When a full join occurs, allocate join_buffer_size = 8M in each thread
# How many threads do we keep in the cache for reuse # When a client disconnects, if there are fewer than thread_cache_size threads in the cache, 
# The client thread is put into the cache. 
# This can greatly reduce thread creation overhead when you need a lot of new connections # (generally speaking if you have a good threading model this won't be a noticeable performance improvement.)
thread_cache_size = 128
# This allows an application to give the thread system a hint as to how many threads it wants to run at the same time. 
# This value only makes sense on systems that support the thread_concurrency() function (such as Sun Solaris). 
# You can try to use [CPU number]*(2..4) as the thread_concurrency value thread_concurrency = 8
# Query buffering is often used to cache SELECT results and return results directly without executing the same query next time. 
# Enabling query caching can greatly increase server speed if you have a lot of identical queries and rarely modify tables. 
# Check the "Qcache_lowmem_prunes" status variable to check 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 caching may cause performance degradation rather than improvement.
query_cache_size = 64M
# Only results smaller than this setting will be cached # This setting is used to protect the query cache, preventing a very large result set from overwriting all other query results query_cache_limit = 2M
# The minimum length of a word to be indexed by full-text search. 
# You may want to reduce this if you need to search for shorter words. 
# Note that after you modify this value, 
# You need to rebuild your FULLTEXT index ft_min_word_len = 4
# The heap size used by the thread. This amount of memory is reserved for each connection. 
# MySQL itself usually doesn't need more than 64K of memory. # If you use your own UDFs that require large amounts of heap, # or your operating system requires more heap for certain operations, 
# You may want to set this higher. 
thread_stack = 192K
# Set the default transaction isolation level. The available levels are as follows: 
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
transaction_isolation = READ-COMMITTED
# Maximum size of internal (in-memory) temporary tables # If a table grows larger than this value, it will automatically be converted to a disk-based table. 
# This limit is for individual tables, not the total.
tmp_table_size = 1342177280

#binlog log type -- mixed binlog_format=mixed
#Open the slow query log slow_query_log
#File format log_output = FILE
# All queries that take longer than this time (in seconds) are considered slow queries. 
# Do not use "0" here, otherwise all queries, even very fast ones, will be recorded (since MySQL currently only has time accuracy of seconds). 
long_query_time = 0.5
#Slow query log location slow_query_log_file=/usr/local/mysql/mysqld_slow.log
#Specify the size of the index buffer, which determines the speed of index processing, especially the speed of index reading#******************** MyISAM related options********************************
# The size of the keyword buffer, generally used to buffer the index blocks of MyISAM tables. 
# Do not set it larger than 30% of your available memory, 
# Because part of the memory is also used by the OS to cache row data # Even if you don't use MyISAM tables, you still need to set 8-64M memory because it will also be used by internal temporary disk tables.
key_buffer_size = 32M
# Buffer size used for full table scans of MyISAM tables. 
# When a full table scan is needed, it is allocated in the corresponding thread.
read_buffer_size = 2M
# When reading rows from an already sorted sequence after sorting, the row data will be read from this buffer to avoid disk seeks. 
# If you increase this value, you can improve the performance of ORDER BY a lot. 
# Allocate read_rnd_buffer_size = 8M by each thread when needed
# 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 of the cache tree per process. 
# Setting this to 0 will disable this optimization. 
# For optimization, do not set this value larger than "key_buffer_size". 
# This buffer will be allocated when burst insertion is detected.
bulk_insert_buffer_size = 16M
# This buffer is allocated when MySQL needs to rebuild indexes during REPAIR, OPTIMIZE, ALTER, and LOAD DATA INFILE to an empty table. 
# This is allocated per thread. So be careful when setting large values.
myisam_sort_buffer_size = 128M
# The maximum size of the temporary file that MySQL allows when rebuilding an index (when REPAIR, ALTER TABLE or LOAD DATA INFILE). 
# If the file size is larger than this value, the index will be created through the key value buffer (slower) 
myisam_max_sort_file_size = 1G
# If a table has more than one index, MyISAM can repair them by sorting in parallel using more than one thread. 
# This is a good choice for users with multiple CPUs and a lot of memory.
myisam_repair_threads = 1
# Automatically check and repair MyISAM tables that were not closed properly. 
myisam_recover

# *************** INNODB related options************************ 
# If your MySQL server includes InnoDB support but you do not plan to use it, 
# Using this option will save memory and disk space, and speed up some parts #skip-innodb

# #####[Key items]
# InnoDB uses a buffer pool to store indexes and raw data, unlike MyISAM. 
# The larger you set it, the less disk I/O you need to access the data in the table. 
# On a standalone database server, you can set this variable to 80% of the server's physical memory size. 
# Do not set it too large, otherwise, the competition for physical memory may cause paging jitter in the operating system. 
# Note that on 32-bit systems you may be limited to 2-3.5G user-level memory per process, 
# So don't set it too high.
innodb_buffer_pool_size = 700m #1G
# InnoDB stores data in one or more data files as tablespaces. 
# If you only have a single logical drive to hold your data, a single auto-incrementing file is good enough. 
# In other cases, one file per device is usually a good choice. 
# You can also configure InnoDB to use raw partitions - see the manual for more information innodb_data_file_path = IBdata1:1024M;IBdata2:1024M:autoextend
# Set this option if you want InnoDB tablespace files to be stored on another partition. 
# Saved in MySQL datadir by default. 
#innodb_data_home_dir = 
# The number of IO threads used to synchronize IO operations. This value is 
# This value is hardcoded to 4 on Unix, but on Windows disk I/O may perform better with a larger value. 
innodb_file_io_threads = 4
# Number of threads allowed in the InnoDb core. 
# The optimal value depends on the application, hardware, and operating system scheduling. 
# Too high a value may cause thread mutex thrashing.
innodb_thread_concurrency = 16
# #####[Key items]
# If set to 1, InnoDB will flush (fsync) the transaction log to disk after each commit. 
# This provides full ACID behavior. 
# If you are willing to compromise transaction safety, and you are running small transactions, you can set this value to 0 or 2 to reduce the disk I/O caused by the transaction log. 
# 0 means that the log is only written to the log file approximately 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 approximately every second. 
# --------------------
# (Note: If it is a game server, it is recommended to set this value to 2; if it is an application with extremely high data security requirements, it is recommended to set it to 1;
# Setting it to 0 gives the best performance, but if a failure occurs, data may be lost!
# The default value 1 means that every transaction commit or instruction outside of a transaction needs to flush the log to the hard disk, which is very time-consuming.
# Especially when using a battery backed up cache. Setting it to 2 is fine for many applications, especially when transferring from MyISAM tables.
# It means not writing to hard disk but writing to system cache. The log is still flushed to disk every second, so you generally won't lose more than 1-2 seconds of updates.
# Setting it to 0 will be faster, but less secure. Even if MySQL crashes, transaction data may be lost. A value of 2 may only cause data loss if the entire operating system crashes.)
innodb_flush_log_at_trx_commit = 2

# The size of the buffer used to buffer log data. 
# When this value is almost full, InnoDB will have to flush data to disk. 
# Since it is refreshed almost every second, there is no need to set this value too large (even for long transactions) 
innodb_log_buffer_size = 16M

# The size of each log file in the log group. 
# You should set the combined log file size to 25%~100% of your buffer pool size 
# to avoid unnecessary buffer pool flushes on log file overwrites. 
# However, please note that a large log file size will increase the time required for the recovery process innodb_log_file_size = 1024M

# Total number of files in the log group. 
# Generally speaking, 2~3 is better. 
innodb_log_files_in_group = 3

# The location of InnoDB log files. The default is MySQL datadir. 
# You can assign it to a separate hard disk or a RAID1 volume to improve performance #innodb_log_group_home_dir

# Maximum allowed ratio of dirty pages in the InnoDB buffer pool. 
# If the limit is reached, InnoDB will start flushing them to prevent them from interfering with clean data pages. 
# This is a soft limit and is not guaranteed to be enforced. 
innodb_max_dirty_pages_pct = 90

# The method InnoDB uses to flush logs. 
# Tablespace always uses dual write flush method # The default value is "fdatasync", the other is "O_DSYNC". 
innodb_flush_method=O_DSYNC

# How long an InnoDB transaction should wait for a lock to be granted before being rolled back. 
# InnoDB automatically detects transaction deadlocks in its own lock table and rolls back the transaction. 
# If you use the LOCK TABLES directive, 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, this timeout value is very helpful in solving this problem. 
innodb_lock_wait_timeout = 30

[mysqldump]
# Don't cache the entire result in memory before writing it to disk. This is needed when exporting very large tables.

max_allowed_packet = 64M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
# Increase the number of open files per process. 
# WARNING: MAKE SURE YOU HAVE SET YOUR SYSTEM-WIDE LIMITS HIGH ENOUGH! 
# To open a large number of tables, you need to set this value to a larger value open-files-limit = 8192
log-error=/usr/local/mysql/mysqld.log
pid-file=/usr/local/mysql/mysqld.pid