Configuration sample
First provide a sample of the configuration I used
[client] #password=88888888Socket=/data/var/mysql/Mysql.sock [Mysqld_safe] PID-file=/data/var/mysql/mysqld.pid Log-error =/data/local/mysql-5.7. +/log/mysql-error.log [MySQL] socket=/data/var/mysql/Mysql.sock [mysqld] User=MySQL Port=31306DataDir=/data/var/MySQL Socket=/data/var/mysql/Mysql.sock Symbolic-links=0####### #basic settings######## Server-id = One#bind_address=10.166.224.32autocommit=1Character_set_server=utf8mb4 skip_name_resolve=1max_connections= -max_connect_errors= -transaction_isolation= read-COMMITTED Explicit_defaults_for_timestamp=1join_buffer_size=128M tmp_table_size=128M Tmpdir=/dev/SHM Max_allowed_packet=16M Sql_mode="Strict_trans_tables,no_engine_substitution,no_zero_date,no_zero_in_date,error_for_division_by_zero,no_auto _create_user"Interactive_timeout= -Wait_timeout= -read_buffer_size=16M read_rnd_buffer_size=32M sort_buffer_size=32M ####### #log settings######## #log_error=/data/local/mysql-5.7. +/log/mysql-Error.log Slow_query_log=1Slow_query_log_file=/data/local/mysql-5.7. +/log/mysql-Slow.log log_queries_not_using_indexes=1log_slow_admin_statements=1log_slow_slave_statements=1log_throttle_queries_not_using_indexes=Tenexpire_logs_days= -Long_query_time=1Min_examined_row_limit= -####### #replication settings######## #master_info_repository=TABLE #relay_log_info_repository=TABLE Log_bin=/data/local/mysql-5.7. +/log/mysql-bin #sync_binlog=4Gtid_mode=On enforce_gtid_consistency=1#log_slave_updates Binlog_format=Row #relay_log=/data/local/mysql-5.7. +/log/mysql-Relay.log #relay_log_recovery=1#binlog_gtid_simple_recovery=1#slave_skip_errors=ddl_exist_errors ####### #innodb settings######## innodb_page_size=16K innodb_buffer_pool_size=4G #innodb_buffer_pool_instances=8#innodb_buffer_pool_load_at_startup=1#innodb_buffer_pool_dump_at_shutdown=1#innodb_lru_scan_depth= -Innodb_lock_wait_timeout=5#innodb_io_capacity=4000#innodb_io_capacity_max=8000#innodb_flush_method=O_direct #innodb_log_group_home_dir=/data/local/mysql-5.7. +/log/redolog/#innodb_undo_directory=/data/local/mysql-5.7. +/log/undolog/#innodb_undo_logs= -#innodb_undo_tablespaces=0#innodb_flush_neighbors=1#innodb_log_file_size=4G #innodb_log_buffer_size=16M #innodb_purge_threads=4Innodb_large_prefix=1innodb_thread_concurrency= -#innodb_print_all_deadlocks=1#innodb_strict_mode=1innodb_sort_buffer_size=64M ####### #semi sync replication settings######## #plugin_dir=/data/local/mysql-5.7. +/lib/plugin #plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"#loose_rpl_semi_sync_master_enabled=1#loose_rpl_semi_sync_slave_enabled=1#loose_rpl_semi_sync_master_timeout= the[Mysqld-5.7] #innodb_buffer_pool_dump_pct= +innodb_page_cleaners=4#innodb_undo_log_truncate=1#innodb_max_undo_log_size=2G #innodb_purge_rseg_truncate_frequency= -#binlog_gtid_simple_recovery=1Log_timestamps=system #transaction_write_set_extraction=MURMUR32 #show_compatibility_56=on
Explain in detail [client] #password =88888888mysql default password socket=/data/var/mysql/mysql.sockmysql sock file location to run as socket [Mysqld_safe]
Log-error=/var/log/mysqld.logError log location pid-file=/var/run/mysqld/mysqld.pid process ID file [MySQL]
Socket=/data/var/mysql/mysql.sockmysql the sock file location to run as socket [Mysqld]user = Mysqlmysql to what user is running port = 31306mysql the data directory on which port DataDir =/data/var/mysql/mysql is running
Socket=/data/var/mysql/mysql.sockmysql sock file location to run as socket
Symbolic-links=0 Whether a symbolic link is supported, that is, a database or table can be stored in a partition or directory other than datadir specified in my.cnf, 0 does not open ####### #basic settings####### #server-id = 11mysql server assignment ID, must be specified when master-slave and cluster is enabled, each node must be different #bind_address = 10.166.224.32mysql Listening IP address, if it is 127.0.0.1, it means only native access autocommit = 1 Data modification is automatically committed, 0 does not automatically commit the CHARACTER_SET_SERVER=UTF8MB4 server using the character set skip_name_resolve = 1 Disables DNS hostname lookup, Enable later use of the intranet address to the MYSQLSLAP request response faster than half max_connections = 800mysql Maximum number of connections
Max_connect_errors = 1000 One host connection error is equal to Max_connect_errors (default 10), and host ' host_name ' is masked when it tries again. Can effectively counter Dos attacks Transaction_isolation = read-committed database transaction ISOLATION LEVEL 1.read-uncommitted (Read UNCOMMITTED content) level
2. read-committed (read submit content)
3. Repeatable-read (can be reread)
4.SERIERLIZED (Serializable)
The default Level Repeatable-readexplicit_defaults_for_timestamp = 1mysql timestamp type is a little different from other types (without setting the EXPLICIT_DEFAULTS_FOR_ Timestamp=1 case) Join_buffer_size = 128M When our join is a buffer used by All,index,rang or Index_merge. Actually, this join is called full join.
Tmp_table_size = 128M Specifies the maximum value of the internal memory temporary table, which is allocated for each thread. (The actual limiting effect is the minimum value of Tmp_table_size and Max_heap_table_size.) If the memory temp table exceeds the limit, MySQL automatically translates it into a disk-based MyISAM table, stored in the specified tmpdir directory tmpdir =/dev/shm/mysql-tmp/The directory where the temporary files are saved Max_allowed_packet = 16Mmysql Maximum accepted packet size
Sql_mode = "Strict_trans_tables,no_engine_substitution,no_zero_date,no_zero_in_date,error_for_division_by_zero, No_auto_create_user "Sql_mode mode, defines the SQL syntax that you should support for MySQL, checks the data, and so on, limiting some of the so-called ' illegal ' operations interactive_timeout = 60 number of seconds that the server waits for activity before closing the interactive connection. The interactive client is defined as the number of seconds that the Mysql_real_connect () client uses the Client_interactive option in the Wait_timeout = 60 server waits for activity before closing the non-interactive connection, and when the thread starts, based on the global Wait_ Timeout value or global interactive_timeout value initializes the session Wait_timeout value, depending on the client type (defined by the connection option client_interactive for Mysql_real_connect ())
Read_buffer_size = 16M reads the size of the buffer, the request to sequentially scan the table allocates a read-in buffer, and MySQL allocates it a memory buffer read_rnd_buffer_size = 32M Random Read buffer size, When a row is read in any order (a column, such as sort order), is assigned a random read buffer, and when the query is sorted, MySQL first scans the buffer to avoid disk searches and improve query speed
Sort_buffer_size = 32M is a connection-level parameter that allocates a set of memory ####### #log the first time each connection needs to use this buffer settings########
#log_error =/data/local/mysql-5.7.19/log/mysql-error.log Error log location
Slow_query_log = 1 Whether to turn on slow query log collection Slow_query_log_file =/data/local/mysql-5.7.19/log/mysql-slow.log Slow query log location Log_queries_ Not_using_indexes = 1 Whether to log statements that do not use indexes
log_slow_admin_statements = 1 Slow query also records those slow optimize table,analyze table and ALTER TABLE statements
log_slow_slave_statements = 1 record slow query generated by slave
Log_throttle_queries_not_using_indexes = 10 Sets the number of statements that are recorded to the log at an unused index per minute, exceeding this number and only recording the number of statements and the total time spent
Expire_logs_days = 90 Log automatic expiration cleanup days
Long_query_time = 1 setting record slow query timeout time
Min_examined_row_limit = 100 query checks that the SQL that returns less than the specified row of the parameter is not logged to the slow query log ####### #replication settings########
#master_info_repository = table Slave to save the master node information mode, when set to file will generate Master.info and Relay-log.info2 files, set to table, the information will exist Mysql.master_ The Slave_info table. Do not move or edit related files and tables, regardless of the value you set
#relay_log_info_repository = table is used to hold slave read relay log location information, the optional value is "FILE", "table", so that crash resume after restart
Log_bin =/data/local/mysql-5.7.19/log/mysql-binbinlog save location, can not specify a certain file name such as Mysql-bin.log, can only specify the location and prefix, will generate a series of files preceded by the prefix
#sync_binlog = 4 This parameter is critical to the MySQL system, which not only affects the performance loss of Binlog to MySQL, but also affects the integrity of the data in MySQL. The descriptions for the various settings for the "Sync_binlog" parameter are as follows: Sync_binlog=0, after a transaction is committed, MySQL does not do fsync such as disk synchronization instructions to refresh the information in Binlog_cache to disk, And let filesystem decide when to sync, or the cache is full before syncing to disk. Sync_binlog=n, after every n transaction commits, MySQL will perform a disk synchronization instruction such as Fsync to force the data in the Binlog_cache to disk. In MySQL, the system default setting is Sync_binlog=0, that is, do not make any mandatory disk refresh instructions, the performance is the best, but the risk is also the largest. As soon as the system crash, all binlog information in the Binlog_cache is lost. When set to "1", it is the safest and most performance-depleting setting. Because when set to 1, even if the system is crash, a transaction that is not completed in Binlog_cache is lost at most, without any substantial impact on the actual data. From previous experience and related tests, for systems with high concurrency transactions, the system write performance gap of "Sync_binlog" set to 0 and set to 1 could be as much as 5 times times or more. Gtid_mode = on enable Gtid type, otherwise it is normal replication schema
Enforce_gtid_consistency = 1 Mandatory gtid consistency
#log_slave_updatesslave更新是否记入日志, it is important to make a dual-master architecture, which affects whether the dual-master architecture can synchronize with each other Binlog_format = rowbinlog log format, optional value "MIXED", "ROW", " STATEMENT ", Default to" STATEMENT "before version 5.6, and" MIXED "after 5.6, because" STATEMENT "means data inconsistency when dealing with some" indeterminate "methods, we recommend using" MIXED "or" ROW ”
#relay_log =/data/local/mysql-5.7.19/log/mysql-relay.log The location where the synchronization relay log is saved from the machine
#relay_log_recovery = 1 When slave is down from the vault, if the relay-log is damaged, causing a portion of the trunk log to not be processed, all relay-log that are not executed are automatically discarded and the logs are retrieved from master again. This guarantees the integrity of the relay-log.
#binlog_gtid_simple_recovery = 1 This parameter controls how MySQL iterates over the Binlog file when it searches for Gtids when MySQL starts or restarts. This option is set to true and will improve the performance of MySQL performing recovery. Because of this mysql-server start and binlog log cleanup faster
#slave_skip_errors = ddl_exist_errors Skips errors that specify the error no type, set to all to skip all errors ####### #innodb settings########
Innodb_page_size = 16Kinnodb Each data page size, this parameter at the beginning of the initialization will be added to MY.CNF, if you have created a table, and then modify, start MySQL will error
Innodb_buffer_pool_size = 4G Cache InnoDB table index, data, buffer when inserting data, size of dedicated MySQL server setting: OS memory 70%-80% Best
#innodb_buffer_pool_instances = 8 can turn on multiple memory buffers, hash the data that needs to be buffered into different buffer pools, so that parallel memory reads and writes
#innodb_buffer_pool_load_at_startup = 1 is off by default. If this parameter is turned on, MySQL loads the local hot data into the InnoDB buffer pool when the MySQL service is started
#innodb_buffer_pool_dump_at_shutdown = 1 is off by default. If this parameter is turned on, InnoDB saves hot data from the InnoDB buffer pool to the local hard disk when the MySQL service is stopped
#innodb_lru_scan_depth = 2000 According to the official documentation, it affects the number of page cleaner threads per brush dirty pages, which is a thread that loops once every 1 seconds
Innodb_lock_wait_timeout = 5 The maximum time a transaction waits for a resource to wait, the time that is not yet allocated to the resource to return an application failure; the time unit of the parameter is seconds
#innodb_io_capacity = 4000
#innodb_io_capacity_max = 8000 These two settings affect how many operations the INNODB performs in the background per second. Most write Io (except for write InnoDB logs) is background operation. If you have a deep understanding of hardware performance, such as how many IO operations can be performed per second, it is advisable to use these features rather than leave it idle
#innodb_flush_method = O_direct The default value is Fdatasync. If you use a hardware RAID disk controller, you may need to set it to O_direct. This prevents the "double buffering" effect when reading the InnoDB buffer pool, or a 2 copy (copy) is formed between the file system cache and the InnoDB cache. O_direct can cause performance degradation if you do not use a hardware RAID controller or if you are using SAN storage
#innodb_log_group_home_dir =/data/local/mysql-5.7.19/log/redolog/innodb Redo Log Save Directory
#innodb_undo_directory =/data/local/mysql-5.7.19/log/undolog/innodb rollback Log Save Directory
#innodb_undo_logs = number of 128undo rollback segments, at least greater than or equal to 35, default 128
#innodb_undo_tablespaces = 0 is used to set the number of undo table spaces created, which can no longer be changed after mysql_install_db, and the default value is 0, which means that undo tablespace is not set independently. Default logging to Ibdata; otherwise, if you create so many undo files under the Undo directory, such as assuming that the value is set to 4, the undo Tablespace file named Undo001~undo004 is created, with a default size of 10M per file. Modifying this value causes InnoDB to fail the initialization and the database cannot start, but the other two parameters can be modified
#innodb_flush_neighbors = 1InnoDB When the storage engine refreshes a dirty page, it detects all pages in the page's region (extent) and refreshes together if it is a dirty page. The benefit of this is that multiple IO writes can be combined into an IO operation through AIO. It is recommended for traditional mechanical hard drives and can be turned off for solid state drives.
#innodb_log_file_size = 4G This value defines the size of the log file, and the purpose of the InnoDB log file is to save the redo log. The modification of a transaction to a data or index often corresponds to a random location in the tablespace, so when these modifications are flushed to disk, random I/O is generated, and random I/O tends to be more expensive than sequential I/O, because random I/O requires more overhead to navigate to the specified location. InnoDB uses logs to convert random I/O to sequential I/O, and as long as the log file is secure, the transaction is permanent, even though these changes are not yet written to the data file, and if there is a failure of the machine or server, then InnoDB can also recover and commit the transaction through the log file. However, the log file is a certain size, so you have to write the log file changes to the data file, InnoDB for the log file operation is circular, that is, when the journal file is full, the pointer will be moved back to the beginning of the file to re-write, but it does not overwrite the data file is not written to the log , because this is the only record that records transaction persistence if you have a large number of write operations to the INNODB data table, choosing the appropriate Innodb_log_file_size value is important for improving MySQL performance. However, the settings are too large to increase the recovery time, so the MySQL server will take a long time to recover if MySQL crashes or a sudden power outage occurs.
#innodb_log_buffer_size = buffer in the memory of the 16M transaction. Distribution principle: Control in 2-8m. This value doesn't have to be too much. The memory inside of him is usually written to disk once a second.
#innodb_purge_threads = 4 control is used, several separate purge threads are used (clear binary log)
Innodb_large_prefix = 1mysql has been a single-column index limit of 767 before 5.6, resulting from 256x3-1. This 3 is the maximum character space (UTF8). However, after 5.6, the support for the 4-byte Uutf8 began. 255x4>767, so this parameter is added. The default value for this parameter is off. When on, allows the column index to reach maximum 3072innodb_thread_concurrency = 64InnoDB kernel The maximum number of threads concurrently. 1) Minimum set to (Num_disks+num_cpus) * *. 2) You can disable this restriction by setting the 1000来 to
#innodb_print_all_deadlocks = 1 If deadlock-related information is saved to the MySQL error log
#innodb_strict_mode = 1 turn on InnoDB Strict check mode, especially when using the page data compression function, it is best to turn on this feature. When this feature is turned on, when creating a table (create table), changing tables (ALTER TABLE), and creating index statements, if there is an error in writing, there is no warning message, but instead throws the error directly, so that the problem can be killed directly in the cradle
innodb_sort_buffer_size = buffer cache size for 64MORDER by or group by operations
####### #semi Sync replication settings######## #plugin_dir =/data/local/mysql-5.7.19/lib/plugin Specify the plugin directory for MySQL
#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" specifies which plugins to load
#loose_rpl_semi_sync_master_enabled = 1 Controls whether Semisync is turned on on the main library
#loose_rpl_semi_sync_slave_enabled = 1 Control whether the repository is open Semisync
#loose_rpl_semi_sync_master_timeout = 5000 milliseconds to prevent semi-synchronous replication from sending a blockage without receiving confirmation. Master does not receive a confirmation before timing out, reverts to asynchronous replication, and resumes operations that were not performed by the semi-sync [mysqld-5.7]
#innodb_buffer_pool_dump_pct = 40 Indicates the percentage of the hottest page dumped on each BP instance LRU. You can reduce the number of page dumps by setting this parameter
Innodb_page_cleaners = 4 Several page cleaner threads were introduced in the 5.7.4 version for increased extensibility and brush-dirty efficiency. To achieve the effect of parallel brush dirty in this version, Page Cleaner is not bound to buffer pool, its model is a coordinator thread + multiple worker threads, and the reconcile thread itself is also a worker thread. So if Innodb_page_cleaners is set to 8, then it's a coordinated thread, plus 7 worker threads
#innodb_undo_log_truncate = 1 Whether to turn on online recycle (shrink) Undo log log file, support dynamic settings
#innodb_max_undo_log_size = 2G When this threshold is exceeded (by default, 1G), the truncate recovery (contraction) action is triggered and the truncate space is reduced to 10M
#innodb_purge_rseg_truncate_frequency = 128 controls how often the undo log is recycled (shrunk). The Undo log space does not shrink until its rollback segment is released, and if you want to increase the frequency of releasing the rollback interval, you have to lower the setpoint
#binlog_gtid_simple_recovery =1 This parameter controls how MySQL iterates over the Binlog file when it searches for Gtids when MySQL starts or restarts. This option is set to true and will improve the performance of MySQL performing recovery. Because of this mysql-server start and binlog log cleanup faster. When this parameter is true, Mysql-server only need to open the oldest and newest of these 2 binlog files
Log_timestamps=system in MySQL 5.7.2 added log_timestamps This parameter, which is mainly to control the error log, genera log, and so on to log the display time parameters. Changing the parameter to default UTC after 5.7.2 causes the log to be recorded in a slower time than the Chinese side, which makes it inconvenient to view the log. Modify to SYSTEM to solve the problem
#transaction_write_set_extraction =murmur32 this magical parameter 5.7. Version 6 was introduced to define a transaction-logging algorithm that uses a hash identifier to record transactions. If you use MGR, then this hash value needs to be used for distributed conflict detection, in a 64-bit system, the official website recommends setting this parameter using the XXHASH64 algorithm. If this function is not used on the line, it should be set to off
#show_compatibility_56 =on starting from mysql5.7.6 Information_schema.global_status has started to be discarded, in order to compatibility, this time need to open show_compatibility_ 56
Most detailed explanation of MySQL 5.7 configuration items
