MySQL5.7配置文件 my.cnf

 如果没有 mysqld.cnf 请注释第一行

[mysqld]
!include                          /home/work/zyh/etc/mysqld.cnf

#=============== [SQL_MODE options] ===============================================================
sql_mode                                = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#=============== [basic options] ===============================================================
core_file
skip_name_resolve                       = 1
default_storage_engine                  = InnoDB
basedir                                 = /home/work/zyh
datadir                                 = /home/work/zyh/var
tmpdir                                  = /home/work/zyh/tmp
plugin_dir                              = /home/work/zyh/lib/plugin
lc_messages_dir                         = /home/work/zyh/share
pid_file                                = /home/work/zyh/var/mysql.pid
port                                    = 3306
socket                                  = /home/work/zyh/tmp/mysql.sock
lower_case_table_names                  = 1
explicit_defaults_for_timestamp         = 0

#=============== [error/general/slow log options] ==============================================
log_error_verbosity                     = 3
log_error                               = /home/work/zyh/log/mysql.err
general_log                             = OFF
general_log_file                        = /home/work/zyh/log/mysql.log
slow_query_log                          = ON
slow_query_log_file                     = /home/work/zyh/log/slow.log
long_query_time                         = 1
log_queries_not_using_indexes           = 0


#=============== [file/table options] ==========================================================
open_files_limit                        = 102400
table_open_cache                        = 10000


#=============== [thread options] ==============================================================
thread_cache_size                       = 200
slow_launch_time                        = 1


#=============== [connection options] ==========================================================
connect_timeout                         = 5
max_allowed_packet                      = 64M
max_connections                         = 4000
max_connect_errors                      = 10000
group_concat_max_len                    = 65536
wait_timeout                            = 3600
interactive_timeout                     = 3600


#=============== [charset options] =============================================================
character_sets_dir                      = /home/work/zyh/share/charsets
character_set_server                    = utf8


#=============== [replication options] =========================================================

#__________________> (1) master/slave <_________________________________________________________
read_only                               = ON

#__________________> (2) binlog <_______________________________________________________________
log_timestamps                          = SYSTEM
log_bin                                 = mysql-bin
log_bin_index                           = mysql-bin.index
sync_binlog                             = 1000
binlog_format                           = ROW
binlog_cache_size                       = 1M
max_binlog_size                         = 1G
expire_logs_days                        = 7

#__________________> (3) slave and relay log <__________________________________________________
skip_slave_start                        = 1
log_slave_updates                       = ON
slave_exec_mode                         = STRICT
slave_load_tmpdir                       = /home/work/zyh/tmp
relay_log                               = relay_log
relay_log_index                         = relay_log.index
max_relay_log_size                      = 1G
relay_log_purge                         = 1
sync_relay_log                          = 1000
master_info_repository                  = FILE    # or TABLE
relay_log_info_repository               = FILE    # or TABLE
sync_master_info                        = 10000
sync_relay_log_info                     = 10000
#replicate_wild_ignore_table             = mysql.%
#replicate_wild_ignore_table             = test.%
slave_parallel_workers=4
slave_parallel_type=LOGICAL_CLOCK
slave_preserve_commit_order=1
relay_log_recovery=ON

#__________________> (4) gtid <_________________________________________________________________
enforce_gtid_consistency                = ON
gtid_mode                               = ON

#__________________> (5) semi_sync <____________________________________________________________
#> semi_sync plugins must be installed before uncoment these options
rpl_semi_sync_master_enabled            = ON
rpl_semi_sync_master_timeout            = 7200000
rpl_semi_sync_master_wait_no_slave      = ON
rpl_semi_sync_master_wait_point         = AFTER_SYNC
#rpl_semi_sync_slave_enabled             = ON


#=============== [innodb options] ==============================================================
innodb_file_per_table                   = ON
innodb_buffer_pool_size                 = 65G                          #占用70%运行内存
innodb_data_home_dir                    = /home/work/zyh/var
innodb_data_file_path                   = ibdata1:1G:autoextend
innodb_checksum_algorithm               = crc32
innodb_thread_concurrency               = 256                          #控制InnoDB存储引擎的线程并发性
innodb_concurrency_tickets              = 5000
innodb_flush_log_at_trx_commit          = 1
#参数说明 1表示每次事务提交时，InnoDB都会将事务日志写入磁盘，并等待确认. (最严格的设置,确保了事务的持久性对性能产生一定的开销，因为需要频繁地刷新日志到磁盘) 
#         0表示InnoDB不会在每次事务提交时立即将事务日志写入磁盘，而是将日志缓冲起来,后台周期性写入磁盘.(降低了磁盘IO的负担，提高了性能，但在发生故障时可能会导致事务的部分损失)
#         2表示InnoDB会在每次事务提交时将事务日志写入磁盘，但不等待确认.(某些情况下可以提高性能，同时保持了事务的持久性，但在发生故障时可能会导致少量的数据丢失)
innodb_flush_method                     = O_DIRECT
innodb_io_capacity                      = 1000
innodb_lock_wait_timeout                = 50
innodb_log_buffer_size                  = 8M
innodb_log_file_size                    = 2G
innodb_log_files_in_group               = 2
innodb_log_group_home_dir               = /home/work/zyh/var
innodb_max_dirty_pages_pct              = 75
innodb_print_all_deadlocks              = ON
innodb_purge_threads                    = 2
innodb_strict_mode                      = ON
innodb_flush_neighbors                  = 0
innodb_use_native_aio                   = ON
innodb_temp_data_file_path              = ibtmp1:12M:autoextend:max:8G
innodb_undo_tablespaces                 = 3
innodb_max_undo_log_size                = 2G
innodb_undo_log_truncate                = 1
innodb_buffer_pool_instances            = 8                            #配置缓冲池实例(每个缓冲池资源隔离)
innodb_read_io_threads                  = 20
innodb_thread_concurrency               = 32
innodb_write_io_threads                 = 20
#=============== [miscellaneous options] =======================================================
key_buffer_size                         = 256M
sort_buffer_size                        = 2M
read_buffer_size                        = 2M
join_buffer_size                        = 8M
read_rnd_buffer_size                    = 8M
bulk_insert_buffer_size                 = 64M
myisam_sort_buffer_size                 = 64M
max_heap_table_size                     = 512M
tmp_table_size                          = 512M



#=============== [DBA options] =================================================================
#> This must be the last part in the [mysqld] section, and you can add other options
#> or override above options here.
#>
#> If you wanna override some options above, you donn't need to change their
#> values there, instead, you can put them here to make the overriding take
#> effect. On the other hand, if you override some options at the very begining
#> of the [mysqld] section, the overriding won't take effect.


#______________ [other sections] ______________________________________________________________
[client]
port                                    = 3306
socket                                  = /home/work/zyh/tmp/mysql.sock

[mysql]
disable_auto_rehash
default_character_set                   = utf8
connect_timeout                         = 3

[mysqldump]
quick
max_allowed_packet                      = 64M