The good habit is to “like” first, then “watch”

My. ini configuration details

“Transfer”

#*** Client options ***#
The following options will be read by the MySQL client application. Note that only the client application that comes with MySQL is guaranteed to read this. If you want your own MySQL application to get these values. These options need to be specified during MySQL client library initialization.
[client]
port = 3309
socket =  /usr/local/mysql/tmp/mysql.sock [mysqld] ! include /usr/local/mysql/etc/mysqld.cnf 
# include the configuration file, the user name, password file separate store

port = 3309
bind-address = 0.0.0.0
server-id = 1 
# indicates that the serial number of the machine is 1, unique

socket =  /usr/local/mysql/tmp/mysql.sock
pid-file =  /usr/local/mysql/var/mysql.pid
basedir =  /usr/local/mysql/
datadir =  /usr/local/mysql/var/
tmpdir =  /usr/local/mysql/tmp/ 
# this directory is used by MySQL to store temporary files. For example, it is used to handle large disk-based sorts, as well as internal sorts, and simple temporary tables. If you don't want to create very large temporary files, it might be better to put them on the SWapfs/TMPFS file system. Alternatively, you can also place it on a separate disk. You can use ";" To place multiple paths, which are used by polling according to the Roud-Robin method.

slave-load-tmpdir =  /usr/local/mysql/tmp/ 
This function is used when slave performs load data infile


#*** skip options ***#
skip-name-resolve 
MySQL > disable DNS resolution for external connections. Use this option to eliminate DNS resolution time. Note that if this option is enabled, all remote host connections must be authorized using IP addresses. Otherwise, MySQL will not be able to process connection requests properly.

skip-symbolic-links 
# Do not use connection files, multiple clients may access the same database, so this prevents external clients from locking the MySQL server. This option is enabled by default

skip-external-locking 
To use myisamchk, you must shut down the server to avoid external locking of MySQL, reduce error rate and improve stability.

skip-slave-start 
Mysql > start mysql

skip-networking 
This option can be used to disable TCP/IP connections to the MySQL database server. Do not enable this option if the WEB server is accessing the MySQL database server remotely. Otherwise, the connection will not work! If all processes are connected to the local mysqld on the same server, this setting will be a way to enhance security

sysdate-is-now = 1 
Program SYSDATE as an alias for NOW

System resource options ***#
back_log = 50 
Queue size is back_log, restricted by OS parameters. It is invalid to try to set back_log higher than your OS limit. The default value is 50. For Linux, the recommended value is an integer less than 512. If the system has many connections in a short period of time, you need to increase the value of this parameter

max_connections = 1000 
# set the maximum number of connected processes allowed by MySQL. If the "Too Many Connections" error frequently occurs when accessing the database, you need to increase the value of this parameter.

max_connect_errors = 10000 
# If a connection error from a user exceeds this value, the next connection from that user will be blocked until the administrator executes Flush hosts. Command or service restart, in case hackers, illegal passwords, and other errors in linking can increase this value

open_files_limit = 10240 
MySQL > open file descriptor limit, default minimum 1024; When open_files_limit is not configured, compare max_connections*5 with ulimit-n. Compare the values of open_files_limit and max_connections*5, which is more important.

connect-timeout = 10 
# The maximum number of seconds before a connection times out. On Linux, this timeout is also used as the time to wait for the first response from the server

wait-timeout = 28800 
Wait for the time to close the connection

interactive-timeout = 28800 
Allow interactive_timeout (instead of wait_timeout) seconds of inactivity before closing the connection. The session wait_TIMEOUT variable on the client is set to the value of the session interactive_TIMEOUT variable. If the front-end program uses short connection, it is recommended to shorten these two values. If the front-end program uses long connection, you can comment out these two options directly. Default configuration (8 hours)

slave-net-timeout = 600 
The slave server can also handle network connection interruptions. However, a network outage is notified only if the slave server does not receive data from the master server for more than Slave_net_timeout seconds

net_read_timeout = 30 
# timeout for reading information from server

net_write_timeout = 60 
# timeout for writing information from the server

net_retry_count = 10 
If the read operation on a communication port breaks, try again several times before aborting

net_buffer_length = 16384 
The # packet message buffer is initialized to net_BUFFer_LENGTH bytes, but can grow to max_allowed_packet bytes if needed

max_allowed_packet = 64M
The maximum size of request packets that the service can handle and the maximum size of requests that the service can handle (quite necessary when working with large BLOB fields), independent of each connection. The size increases dynamically. Set maximum packet size to limit the size of packets accepted by the server to avoid problems with ultra-long SQL execution default value is 16MB when used by MySQL client or mysqldWhen the server receives packets larger than max_allowed_packet bytes, it issues an "packet too Large" error and closes the connection. For some clients, if the communication packet is too large, you may experience a "lost connection to the MySQL server" error during query execution. The default value is 16 MB. table_cache = 512The number of open tables for all threads. Increasing this value increases the number of file descriptors required by mysqld so you need to make sure that the "open-files-limit" variable in [mysqLD_safe] allows at least 4096 open files

thread_stack = 192K 
# heap size used by threads. Memory of this capacity is reserved for each connection. MySQL itself usually doesn't need more than 64K of memory. If you use your own UDF functions that require a lot of heaps or your operating system needs more heaps for some operations, you may need to set it higher. The default Settings are sufficient for most applications

thread_cache_size = 20 
# How many threads do we keep in cache for reuse? When a client disconnects, if there are fewer threads in the cache than thread_cache_size, the client thread is put into the cache. This can greatly reduce the overhead of thread creation when you need lots of new connections (generally if you have a good threading model,There will be no noticeable performance improvement.) Server thread cache This value represents the number of threads held in the cache that can be reused. If there is space in the cache when the connection is disconnected, the client thread will be put into the cache. If the thread is requested again, the request will be read from the cache. The thread will then be recreated, and if there are many new threads, increasing this value can improve system performance. By comparing the Connections and Threads_created state variables, you can see that the effect of this variable is set based on physical memory as follows: 1G - > 8 2G - > 16 3G - > 32 greater than 3G - > 64 thread_concurrency = 8# This allows the application to give the threading system a hint about the number of threads that are eager to be run at the same time. The value is the number of logical cpus on the server x 2. In this example, the server has two physical cpus, and each physical CPU supports H.T hyperthreading. Therefore, the actual value is 4 x 2 = 8. Set the thread_concurrency value to be correct or not,Setting the thread_concurrency value incorrectly can have a significant impact on mysql performance. If there are multiple cpus (or cores), the mysql server may be unable to take full advantage of the concurrency setting. Only one CPU (or core) is working at a time. Thread_concurrency should be set to 2 times the number of CPU cores. For example, if you have a dual-core CPU, the thread_concurrency file should be 4. For two dual-core cpus, the thread_concurrency value should be 8#*** qcache Settings ***#
query_cache_limit = 2M 
# do not cache results of queries greater than this value. Only results less than this value are buffered. This setting protects the query buffer from a large result set overwriting all other query results.

query_cache_min_res_unit = 2K 
Query the minimum block size allocated by the cache. The default value is 4KB. A large value is good for big data queries, but if your queries are all small data queries, it can cause memory fragmentation and wasteQUERY CACHE fragmentation rate = Qcache_free_blocks/Qcache_total_blocks * 100% If the QUERY CACHE fragmentation rate exceeds 20%, FLUSH QUERY CACHE to defragment the CACHE. Or try reducing query_cache_min_res_unit if your queries are small. Query_cache_size = (query_cache_size - Qcache_free_memory)/query_cache_size *100% Can be appropriately reduced; If the query cache utilization is above 80% and Qcache_lowmem_prunes > 50, query_cache_size may be a bit small or too fragmented. Query cache hit ratio = (Qcache_hits - Qcache_inserts)/Qcache_hits * 100% querY_cache_size = 64MMySQL query buffer size You can observe this by executing the following command on the MySQL console:> SHOW VARIABLES LIKE'%query_cache%';
> SHOW STATUS LIKE 'Qcache%'; If the value of Qcache_lowmem_prunes is very large, it indicates that buffering is often insufficient; If the value of Qcache_hits is very large, it indicates that the query buffer is used very frequently. If the value is too small to be efficient, you can consider not using the query buffer. Qcache_free_blocks, if very large, indicates a lot of fragmentation in the buffer. memlock# If your system supports the memlock() function, you may want to turn on this option to keep mysql running at memory heightIn tight times, data remains locked in memory and may be prevented from being considerations out, this option is beneficial for performance#*** default Settings ***#
default_table_type = InnoDB 
# as the default table type used when creating a new table, this value will be used if there is no specific table type executed during the creation presentation

default-time-zone = system 
# server time zone

character-set-server = utf8 
# Server level character set

default-storage-engine = InnoDB 
The default storage engine

#*** TMP && heap Settings options ***#
tmp_table_size = 512M 
# Maximum size of temporary tables. If this value is exceeded, the results will be put to disk. This limit is for individual tables, not the total.

max_heap_table_size = 512M 
The maximum capacity allowed by a separate memory table. This option prevents the accidental creation of a large memory table from exhausting all memory resources forever.

#*** log Settings options ***#
log-bin = mysql-bin 
Enable binary logging. In a replication configuration, this must be turned on as the MASTER server. If you need to do a point-in-time restore from your last backup, you will also need binary logs. These paths are relative to datADIr

log_slave_updates = 1 
# indicates that the slave writes the replication event to its binary log

log-bin-index = mysql-bin.index 
Binary index file name

relay-log = relay-log 
# define relay_log's location and name. If the value is null, the default location is in the data file directory. The file name is host_name-relay-bin. NNNNNN (By default, Relay log file names have the form host_name-relay-bin. NNNNN in the data directory);

relay_log_index = relay-log.index  
Relay -log index file name

log-warnings = 1 
Print the warning to the error log file. If you have any problems with MySQL, you should open the warning log and scrutinize the error log for possible causes.

log-error =  /usr/local/mysql/log/mysql.err 
Error log path

log_output = FILE 
The log_output parameter specifies the slow_log output format. The default is FILE. You can set this parameter to TABLE, and then query the slow_log TABLE in mysql

log_slow_queries 
Specifies whether to enable slow query logging (this parameter should be replaced by slow_query_log for compatibility)

slow_query_log = 1 
# Specifies whether to enable slow query logs. Slow queries are queries that consume more time than defined by "long_query_time". If log_long_format is turned on, queries that do not use indexes will also be logged. If you frequently add new queries to an existing system. This is generally a good idea,

long-query-time = 1 
# set the threshold for slow query. SQL exceeding this threshold is logged as slow query. Default value is 10s. All queries that use more than this time (in seconds) are considered slow queries. Do not use "1" here, otherwise all queries, even very fast query pages, will be logged (since MySQL's current time accuracy is only seconds).

log_long_format 
Record more information in the slow log. This option is usually best turned on because it records that queries that do not use indexes are appended to the slow log as slow queries

slow_query_log_file =  /usr/local/mysql/log/slow.log 
The default file host_name-slow.log will be used

log-queries-not-using-indexes 
If you run an SQL statement that does not use an index, the mysql database will also log the SQL statement to the slow query log file.min_examined_row_limit=1000       Log slow queries that are caused by more than 1000 lookupslong-slow-admin-statements       Record slow OPTIMIZE table, Analyze Table and ALTER table statements

log-slow-slave-statements 
Record the slow queries generated by the Slave

general_log = 1 
# log all SQL statements that arrive at MySQL Server

general_log_file =  /usr/local/mysql/log/mysql.log 
# general_log path

max_binlog_size = 1G 
If the binary log writes more than the given value, the log will scroll. You cannot set this variable to be greater than 1GB or less than 4096 bytes. The default value is 1GB. If you are using large transactions, the binary log will also exceed max_binlog_size

max_relay_log_size = 1G 
# flag the maximum number of relaylogs allowed. If this value is 0, the default value is max_binlog_size(1G); If the value is not 0, max_relay_log_size is the maximum relay_log file size.

relay-log-purge = 1 
# Whether to automatically clear trunk logs when they are no longer needed. Default is 1(enabled)

expire_logs_days = 30 
Delete binlogs older than 30 days

binlog_cache_size = 1M 
Binlog Specifies the size of the cache used to record SQL state in a transaction. If you often use large, multi-declared transactions, you can increase this value for better performance. All states from the transaction will be cached in the binlog buffer and written to the binlog once after the commit. If the transaction is larger than this value, temporary files on disk will be used instead. This buffer is created at the first state update for each connected transaction. Session level

replicate-wild-ignore-table = mysql.% 
Ignore database and table when copying
slave_skip_errors=all 
SQL > select error number from server that can be skipped automatically during replication.The slave_skip_errors option has four available values: off, all, ErorCode, and DDl_exist_errors. By default this parameter is off. We can list the specific error code, or select all, mysql5.6, MySQL Cluster NDB 7.3, and later versions of ddl_exist_errors. This parameter contains a list of the error code (1007100 8105 0105 1105 4106 0106 1106 8109 4114 6) some error code on behalf of the error is as follows: 1007: the database has been exist, failed to create a database of 1008: 1050: the data table exists, but the data table creation fails. 1051: the data table does not exist, and the data table deletion fails. 1054: the field does not exist, or the program file conflicts with the database. 1094: location thread ID 1146: data table missing, please restore database 1053: primary server down during replication 1062: primary key conflict Duplicate entry'%s' for key %d
    
    
#*** MyISAM options ***#
key_buffer_size = 256M 
# specifies the size of the buffer used for the index, increasing it for better index processing performance. If InnoDB is the main DB engine, key_buffer_size for MyISAM can be set to a smaller size (8MB is sufficient). If MyISAM is the main DB engine, key_buffer_size can be set to a larger size (4G). In this case, it is highly recommended not to use the MyISAM engine. The default is InnoDB. Note: if this parameter value is set too large, the overall efficiency of the server will decrease.

sort_buffer_size = 2M 
The size of the buffer that can be used for sorting queries. The sort buffer is used to handle sorts like those caused BY ORDER BY and GROUP BY queues. An alternative disk-based merge classification will be used. View the Sort_merge_passes status variable. Note: the allocated memory for this parameter is per-connection exclusive! If there are 100 connections, the actual total sort buffer size allocated is 100 x 6 = 600MB, so 6-8m is recommended for servers with around 4GB of memory.

read_buffer_size = 2M 
The buffer size that can be used by read query operations. Like sort_BUFFer_SIZE, the allocated memory is also per-connection exclusive! Buffer size used to do full table scans of MyISAM tables. When a full table scan is required, it is allocated in the corresponding thread.

join_buffer_size = 8M 
Sort_buffer_size specifies the buffer size that can be used by a joint query operation. This buffer is used to optimize full JOINs (full JOINs without indexes). Such unions have very poor performance in the vast majority of cases, but setting this value high can mitigate the performance impact. View the number of full unions through the "Select_full_join" status variable, which is assigned per thread when full unions occur.

read_rnd_buffer_size = 8M 
#MyISAM Scans the buffer size in Random Scan mode

bulk_insert_buffer_size = 64M 
#MyISAM uses a special treelike cache to make burst inserts (these inserts are, inserts... The SELECT, INSERT... VALUES (...). , (...). ,... And LOAD DATAINFILE) faster. This variable limits the number of bytes in the buffer tree per process. Setting it to 0 turns this optimization off. Do not set this value greater than "key_buffer_SIZE" for optimization. This buffer will be allocated the size of the tree buffer MyISAM uses for block insertion optimization when burst inserts are detected. Note: This is a per thread limitation (bulk bulk). This buffer is allocated when MySQL needs to cause rebuild indexes in REPAIR, OPTIMIZE, ALTER, and LOAD DATA INFILE into an empty table. This is allocated in each thread. So be careful when setting large values.

myisam_sort_buffer_size = 64M 
#MyISAM Set the size of the buffer allocated by MyISAM INDEX when repairing TABLE or creating INDEX with CREATE INDEX or ALTER TABLE

myisam_max_sort_file_size = 10G
# maximum temporary file size allowed by mysql to rebuild index

myisam_repair_threads = 1 
If the value is greater than 1, MyISAM table indexes are created in parallel during Repair by sorting (each index is within its own thread). If a table has more than one index, MyISAM can use more than one thread to fix them by parallel sorting. This is a good choice for users with multiple cpus and a lot of memory.

myisam_recover = 64K
# Maximum allowed length of GROUP_CONCAT() result
transaction_isolation = REPEATABLE-READ Set the default transaction isolation level. The available levels are as follows: read-uncommitted, read-committed, REPEATABLE-READ,SERIALIZABLEREAD UNCOMMITTED- READ UNCOMMITTED 2.READ COMMITTE- READ committed 3.REPEATABLE READ - READ REPEATABLE 4# *** INNODB related options ***#
skip-innodb 
# If your MySQL service includes InnoDB support but does not intend to use it, using this option will save memory and disk space and speed up some parts

innodb_file_per_table = 1 
InnoDB is an independent table space schema, each table in each database will generate a data spaceAdvantages of an independent tablespace: 1. Each table has its own independent table space. 2. The data and indexes for each table are stored in its own table space. 3. You can move a single table across different databases. 4. Space can be reclaimed (except for drop table operation, empty table cannot be reclaimed) disadvantages: 1. Conclusion: Shared table Spaces have little advantage in Insert operations. None of the others perform as well as the standalone tablespace. If independent tablespaces are enabled, set innodb_open_files innodb_status_file = 1# Enable InnoDB status file for administrators to view and monitor

innodb_open_files = 2048 
# limit the number of tables Innodb can open. If there are too many tables in the database, please add this. The default value is 300

innodb_additional_mem_pool_size = 100M 
InnoDB storage engine is used to store data dictionary information and some internal data structures, so when we have a large number of database objects in MySQL Instance, we need to adjust the size of this parameter to ensure that all data can be stored in memory to improve access efficiency.

innodb_buffer_pool_size = 2G 
Include data page, index page, insert cache, lock information, adaptive hashing, data dictionary information. InnoDB uses a buffer pool to hold indexes and raw data, unlike MyISAM. The larger your Settings, 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 this variable to too large, otherwise, the operating system may be page bumping due to physical memory competition. Note that on 32-bit systems you may be limited to 2-3.5GB of user-level memory per process, so don't set it too high.

innodb_write_io_threads = 4
innodb_read_io_threads = 4
Innodb uses background threads to handle read/write I/O requests on data pages, depending on the number of CPU cores you have, the default is 4
MySQL/mysql.cnf/mysql.cnf/mysql.cnf/mysql.cnf/mysql.cnf/mysql.cnf/mysql.cnf/mysql.cnf/mysql.cnf

innodb_data_home_dir =  /usr/local/mysql/var/ 
# Set this option if you want InnoDB tablespace files to be saved in other partitions. By default, it is stored in MySQL datadir.innodb_data_file_path = ibdata1:500M; ibdata2:2210M:autoextendInnoDB stores data in one or more data files as tablespaces. If you only have a single logical driver to hold your data, a single increment file is good enough. In other cases, one file per device is generally a good choice. You can also configure InnoDB to use raw disk partitions - please refer to the manual for more information

innodb_file_io_threads = 4 
The number of IO threads used to synchronize IO operations. This value is hardcoded to 4 under Unix, but may perform better under large values on Windows disk I/O.

innodb_thread_concurrency = 16
# Number of threads allowed in The InnoDb core. InnoDb tries to keep the number of operating system threads in InnoDb below or equal to the limit given by this parameter. The optimal value depends on how the application, hardware and operating system are scheduled. Too high a value can cause threads to be mutex jolted. The default value is 0, indicating that the number of concurrent requests is not limited. You are advised to set the value to 0 to maximize the CPU's multi-core processing capability and increase the number of concurrent requests

innodb_flush_log_at_trx_commit = 1 
# If set to 1,InnoDB will flush (fsync) the transaction log to disk after each commit, which provides complete ACID behavior. If you are willing to compromise transaction security and you are running a small food, you can set this value to 0 or 2 to reduce disk I/O caused by transaction loggingLog files are written to and flushed to disk only about every second. Innodb_log_buffer_size = 8M indicates that the log file is written to the log file after each commit, but the log file is flushed to disk only about every secondSize of the buffer used to buffer log data. When this value is near full InnoDB will have to flush data to disk. There is no need to set this value too high (even for long transactions) since it is refreshed almost every second

innodb_log_file_size = 500M 
# transaction log size. For the size of each log file in the log group, you should set the total log file size to 5% to 100% of your buffer pool size to avoid writing unnecessary buffer pool flush behavior over log files. However, note that a large log file size increases the time required to recover the process.

innodb_log_files_in_group = 2 
The total number of files in the log group. Usually two or three is good.

innodb_log_group_home_dir =  /usr/local/mysql/var/
# InnoDB log file location. Innodb_max_dirty_pages_pct = 90 # InnoDB main thread flusher data in the cache pool. Make the dirty data percentage less than 90%, which is a soft limit and is not guaranteed to be strictly enforced.

innodb_lock_wait_timeout = 50 
Number of timeout seconds InnoDB transactions can wait to be locked before being rolled back. InnoDB automatically detects transaction deadlocks in its own locked tables and rolls back transactions. InnoDB takes note of the locking setting with the LOCK TABLES statement. The default is 50 seconds

innodb_flush_method = O_DSYNC 
InnoDB is used to refresh logs. Table Spaces always use a dual write refresh method. The default value is "fdatasync" and the other is "O_DSYNC".

innodb_force_recovery=1
# If you find that InnoDB table space is corrupt, setting this value to a non-zero value may help you export your table. Start at 1 and increase this value until you can successfully export the table.

innodb_fast_shutdown 
# Speed up InnoDB shutdown. This prevents InnoDB from doing full cleanup and insert buffer merges on shutdown. This can greatly increase the shutdown time, but instead InnoDB may do this at the next startup.



# *** Other related options ***#
[mysqldump]
quick 
Large database dumps are supported. This is required when exporting very large tables. It is safe to increase the value of this variable because additional memory is allocated only when needed. For example, mysqld allocates more memory only if you issue long queries or if mysqld must return large result rows. The small default value for this variable is a precaution to catch error packets between the client and server and to ensure that memory is not spilled by accidental use of large packets. If you're using large BLOB values, and you don't grant mysqld access to enough memory for processing queries, you can also run into strange problems with large packets. If you suspect this is the case, try adding ulimit -d 256000 at the beginning of the mysqLD_Safe script and restarting mysqld.

[mysql]
auto-rehash 
Allow prompt by TAB key

default-character-set = utf8 
# database character set

connect-timeout = 3
[mysqld_safe]

open-files-limit = 8192 
# Increase the number of open files per process. Make sure you set the system-wide limit high enough! Opening a large number of tables requires this value to be large
Copy the code

Binlog Three modes

1. Statement level mode

Every SQL that modifies data is logged in the master’s bin-log. When the slave is replicated, the SQL process parses the same SQL as the original SQL executed by the master and executes it again. Advantages: The advantages of the statement level are as follows: The statement level eliminates the disadvantages of the row level. It does not need to record the changes of each row, reducing the amount of bin-log logs, saving I/O, and improving performance. Because he only needs to record the details of the statement being executed on the master, and information about the context in which the statement was executed. Faults: since it is recording the execution of the statement, so in order to make these statements on the slave side can also be executed correctly, then he must also record each statement at the time of execution of some relevant information, namely the context information, to ensure that all statements on the slave side is executed to be implemented and in the master when the same results. In addition, due to the rapid development of mysql, a lot of new functions are added, which makes the replication of mysql encounter no small challenge. The more complex the content involved in natural replication, the more likely bugs will appear. At the statement level, there are many cases of mysql replication problems, mainly caused by the use of certain functions or functions when modifying data. For example, sleep() cannot be copied correctly in some versions.

2. Rowlevel mode

The modified data of each row is recorded in the log, and the same data is modified on the slave side. Advantages: Bin-log does not record the context-related information of the EXECUTED SQL statement, but only records which record is modified and how it is modified. So the contents of the row level log clearly record the details of each row. And there are no specific cases where stored procedures, or functions, or trigger calls and triggers cannot be copied correctly. Disadvantages: At row level, all executed statements are logged as changes per row, which may generate a large amount of log content, such as the update statement: Update product set member_id=’d’ where owner_member_id=’a’; The log does not record the events that correspond to the update statement (mysql records bin-log logs as events), but the changes to each record that is updated by the update statement, thus recording many records as many events that are updated. Naturally, the volume of bin-log logs is large.

3, Mixed mode

In mixed mode, mysql will choose between statement and row according to the specific SQL statement it executes. Statement level is the same as before and only records statements executed. Not all changes are recorded in row level mode. For example, when a table structure changes, the statement mode is recorded in statement mode. If the SQL statement is an update or delete statement, the row level mode is optimized. All row changes will still be logged.