MySQL基本配置文件
# 5.7
# mkdir /data/mysql57/{data,logs,run} -pv
[client]
port = 3306
socket = /data/mysql57/run/mysql57.sock
[mysql]
prompt = "\u@mysql57 \R:\m:\s [\d]> "
[mysqld]
server-id = 33061 # 主从架构下server_id需要不一样
user = mysql
port = 3306
basedir = /usr/local/mysql57
datadir = /data/mysql57/data
socket = /data/mysql57/run/mysql57.sock
pid-file = /data/mysql57/run/mysql57.pid
transaction_isolation = READ-COMMITTED # 自建数据库默认为RR隔离级别，需要与业务确认该参数设置
character-set-server = utf8mb4
skip_name_resolve = 1
log_timestamps=system
## innodb设置，随配置调整
innodb_buffer_pool_size = 6G # 若服务器单独运行MySQL服务，可调整服务器内存60%～80%
innodb_buffer_pool_instances = 4 # 当innodb_buffer_pool_size大于1G时，可适当调整增大该参数
#innodb_buffer_pool_load_at_startup = 1
#innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:12M:autoextend
innodb_temp_data_file_path=ibtmp1:12M:autoextend
innodb_log_buffer_size = 16M
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_max_undo_log_size = 1G
innodb_undo_tablespaces = 2
innodb_undo_log_truncate = 1
# error log
log_timestamps = SYSTEM
log-error = /data/mysql57/logs/error.log
innodb_print_all_deadlocks = 1
## slow log
slow_query_log = 1
slow_query_log_file = /data/mysql57/logs/slow.log
long_query_time = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
#log_queries_not_using_indexes = 1
#log_throttle_queries_not_using_indexes = 60
## binlog log
log-bin = /data/mysql57/logs/binlog
binlog_format = row
binlog_row_image = FULL
max_binlog_size = 1G
expire_logs_days = 0 # 可根据业务需求设置该参数
binlog_cache_size = 2M
binlog_rows_query_log_events=1
binlog_checksum = CRC32
## relay log
relay_log = /data/mysql57/logs/relaylog
relay_log_recovery = 1
relay_log_purge = 1
## 复制设置
gtid_mode = on
enforce_gtid_consistency = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_slave_updates = 1 # 若需要进行级联复制，需要打开该参数
## 多线程并行复制
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8 # 建议设置服务器逻辑CPU个数*2
slave_preserve_commit_order = 1
binlog_order_commits = 1
## 半同步复制
loose-rpl_semi_sync_master_enabled = ON
loose-rpl_semi_sync_master_wait_point = after_sync
loose-rpl_semi_sync_master_wait_for_slave_count = 1
loose-rpl_semi_sync_slave_enabled = ON
## 双一设置
innodb_flush_log_at_trx_commit = 1
sync_binlog=1
## performance设置
max_connections = 1200
max_user_connections = 1000
max_connect_errors = 1000
sort_buffer_size = 262144 # default 262144 , suggest setting 256k~2M
join_buffer_size = 1M # default 262144
innodb_sort_buffer_size = 1048576 # default 1048576
query_cache_size = 0
tmp_table_size = 2M
key_buffer_size = 16M
table_open_cache = 2048
table_open_cache_instances = 16
table_definition_cache = 2048
innodb_open_files = 65535
max_allowed_packet = 32M
## IO能力（服务器存储为SSD时，再考虑IO能力的优化，否则优先默认即可）
innodb_io_capacity_max = 40000
innodb_io_capacity = 2000 # 根据磁盘IOPS能力进行调整，若磁盘为SSD，可设置为20000
innodb_flush_neighbors = 1 # 若磁盘为SSD，该参数可设置为0
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 1024
#performance设置
performance_schema = 1
#performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'
[mysqldump]
quick
max_allowed_packet = 32M # mysqldump限制server接受的数据包大小innodb_log_file_size =
innodb_log_files_in_group = 4
max_connect_errors = 65535
log_error_verbosity = 3
#relay_log_recovery = 1
open_files_limit = 65535
innodb_open_files = 10240
lock
