mysql5.7配置文件详解

8核心32G独立mysql服务器的配置文件如下：

[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'

secure_file_priv=/var/lib/mysql-files/

默认值为：/var/lib/mysql-files/ 该参数是用来限制LOAD DATA、SELECT ... OUTFILE、LOAD_FILE() 传到哪个指定目录的。seure_file_priv 的值为null 则表示限制mysql不允许导入导出；secure_file_priv的值为/tmp/ 则表示限制mysql的导入导出只能发生在/tmp/目录下；当secure_file_priv的值没有具体值时，表示不对mysqld 的导入导出做限制。secure_file_priv 参数是只读参数，不能使用set global命令修改，只能修改my.cnf配置文件并重启mysql生效

binlog_cache_size=32768

该参数是用来存储事务的二进制日志的缓存，默认值32768 即32k，一个事务在没有提交（uncommitted）的时候，产生的日志，记录到cache中；等到事务提交（committed）的时候，则把日志持久化到磁盘。这个参数不是全局的，而是session级别的，就是每启动一个线程，mysql服务器就对这个线程分配一个size大小的内存，所以这个size不能太大，设置过大可能会耗尽服务器内存。设置太小的话，如果用户提交一个长事务（long_transaction）比如：批量导入数据，那么该事务必然会产生很多binlog，这样cache可能不够用，不够用的时候mysql会把 uncommitted 的部分写入临时文件（临时文件cache的效率必然没有内存cache高），等到committed的时候才会写入正式的持久化日志文件

验证取值是否合理：
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

binlog_checksum=CRC32

binlog_checksum 默认值 CRC32，此设置下写 binlog 时会为每个 event 写入一个校验值。当binlog_checksum=NONE，则通过检查每个 event 的长度来验证 binlog event 的完整性。在值为 CRC32 时，slave io 线程会检查通过网络接收到的 binlog event 的校验值是否正确，如果检查到损坏的 event，将停止复制并报错
注意：搭建MGR集群时，需要设置 binlog_checksum=NONE

后期将持续更新一些参数的详细用法

参考：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html

https://blog.csdn.net/l10283868