mysql5.7配置文件详解

[client]
port = 3306
socket = /data/mysql/mysql.sock

[mysql]
prompt = "mysql [\d]> "
no-auto-rehash

[mysqldump]
quick

[mysqld]
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql
socket  = /data/mysql/mysql.sock
pid_file = /data/mysql/mysql.pid
log_error = /var/log/mysql.log
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
default_storage_engine = innodb
default_tmp_storage_engine = innodb
transaction_isolation = READ-COMMITTED
log_timestamps = system
skip_name_resolve = 1
lower_case_table_names = 0 #0表示区分大小写，1表示不区分大小写
max_allowed_packet = 32M
back_log = 1024
thread_stack = 512K
bulk_insert_buffer_size = 64M
max_heap_table_size = 32M
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
max_connections = 512
max_connect_errors = 1000000
open_files_limit = 65535
explicit_defaults_for_timestamp = 1

interactive_timeout = 1800
wait_timeout = 1800
lock_wait_timeout = 1800

table_open_cache = 4000
table_definition_cache = 2000
table_open_cache_instances = 32

read_buffer_size = 8M
read_rnd_buffer_size = 4M
sort_buffer_size = 16M
tmp_table_size = 96M
join_buffer_size = 16M
thread_cache_size = 64
key_buffer_size = 32M
myisam_sort_buffer_size = 32M

# slow log
slow_query_log = 1
slow_query_log_file = /data/mysql/slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
long_query_time = 5

# binlog settings
log_bin = /data/mysql/mybinlog
log_error_verbosity = 2
log_error_verbosity = 2
binlog_cache_size=2M
max_binlog_size = 1G
binlog_checksum = NONE
binlog_format = ROW
binlog_rows_query_log_events = 1
sync_binlog = 1
expire_logs_days = 7

# replication settings
server_id = 101
gtid_mode = on
enforce_gtid_consistency = 1
skip_slave_start = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = 1
slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN'
slave_parallel_type = logical_clock
slave_parallel_workers = 4 #执行relay log的线程数

# innodb settings
# 以下两个参数的值加起来等于cpu核心数
# 也可以根据读写比例来设置对应比例的线程数
innodb_write_io_threads = 4
innodb_read_io_threads = 4

# 根据服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
# 普通磁盘可往下降低
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000

innodb_flush_neighbors = 1 #HDD硬盘设为1、SSD设为0

innodb_file_per_table = 1
innodb_rollback_on_timeout = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_page_size = 16k
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 4
innodb_lru_scan_depth = 256
innodb_lock_wait_timeout = 50
innodb_flush_method = O_DIRECT
innodb_undo_tablespaces = 3
innodb_log_file_size = 1G
innodb_log_files_in_group = 3
innodb_log_buffer_size = 32M
innodb_purge_threads = 4
innodb_flush_log_at_trx_commit = 1
innodb_print_all_deadlocks = 1
innodb_sort_buffer_size = 4M
innodb_online_alter_log_max_size = 1G
innodb_open_files=65535
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_max_undo_log_size = 2G

# perforamnce_schema settings
performance_schema = 1
performance-schema-instrument='memory/%=COUNTED'

验证取值是否合理：
mysql [(none)]> show global status like 'binlog_cache_%';
+-----------------------+----------+
| Variable_name         | Value    |
+-----------------------+----------+
| Binlog_cache_disk_use | 0        |#表示binlog_cache临时文件方式被用上了多少次
| Binlog_cache_use      | 19047819 |#表示binlog_cache内存方式被用上了多少次
+-----------------------+----------+

# 当对应的Binlog_cache_disk_use值比较大的时候 我们可以考虑适当的调高 binlog_cache_size 对应的值

# 取值建议
# 如果没有什么大事务，DML也不是很频繁的情况下可以设置小一点（如1048576即1M）
# 如果事务大而且多，DML操作也频繁，则可以适当的调大一点（2097152~4194304  即2~4M）

# 动态修改配置（注意：动态修改，只能用byte单位）
mysql> set global binlog_cache_size=1048576

# 配置文件里可用其它单位
vi /etc/my.cnf
binlog_cache_size=2M