[root@localhost ～]# cat /home/mysql/conf/my1.cnf
[client]
loose_default-character-set = utf8
port=3306
socket=/home/mysql/data/mysqldata1/sock/mysql.sock
[mysqldump]
quick
max_allowed_packet = 2G
default-character-set = utf8
[mysql]
no-auto-rehash
show-warnings
prompt="\\u@\\h : \\d \\r:\\m:\\s> "
default-character-set = utf8
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
user=mysql
open-files-limit = 65535
[mysqld]
#large-pages
#＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊common parameters＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
default-storage-engine = INNODB
character-set-server=utf8
collation_server = utf8_bin
log_timestamps=SYSTEM
user=mysql
port=3306
socket=/home/mysql/data/mysqldata1/sock/mysql.sock
pid-file=/home/mysql/data/mysqldata1/sock/mysql.pid
datadir=/home/mysql/data/mysqldata1/mydata
tmpdir=/home/mysql/data/mysqldata1/tmpdir
skip-name-resolve
skip_external_locking
lower_case_table_names=1
event_scheduler=0
back_log=512
default-time-zone='+8:00'
max_connections = 3000
max_connect_errors=99999
max_allowed_packet = 64M
slave_pending_jobs_size_max=128M
max_heap_table_size = 8M
max_length_for_sort_data = 16k
wait_timeout=172800
interactive_timeout=172800
net_buffer_length = 8K
read_buffer_size = 2M
read_rnd_buffer_size = 2M
sort_buffer_size = 2M
join_buffer_size = 4M
binlog_cache_size = 2M
table_open_cache = 4096
table_open_cache_instances = 2
table_definition_cache = 4096
thread_cache_size = 512
tmp_table_size = 8M
# QC系统变量在MySQL 8.0.3版本中被移除
query_cache_size=0
query_cache_type=OFF
#＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊  Logs related settings ＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
log-error=/home/mysql/data/mysqldata1/log/error.log
long_query_time = 1
slow_query_log
slow_query_log_file=/home/mysql/data/mysqldata1/slowlog/slow-query.log
log_slow_slave_statements
#log_queries_not_using_indexes
#＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊  Replication related settings ＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
#### For Master
server-id=330614
log-bin=/home/mysql/data/mysqldata1/binlog/mysql-bin
binlog-format=ROW
binlog-checksum=CRC32
binlog-rows-query-log-events=1
binlog_max_flush_queue_time=1000
max_binlog_size = 512M
expire_logs_days=15
sync_binlog=1
master-verify-checksum=1
master-info-repository=TABLE
auto_increment_increment=2
auto_increment_offset=2
# 开启多线程复制之后，如果从库意外挂掉，使用relay_log_recovery=1 crash recovery时会到relay log中查找用于补齐这个gaps的日志信息，如果relay log没有实时落盘，则会导致复制启动可能报ERROR 1872（HY000）:Slave failed to initialize relay log info structure from the repository的错误，可以使用sync_relay_log=1来尽量避免。如果不能调整 sync_relay_log参数为 1，则在报错时需要使用 stop slave; change master to master_auto_position=1; start slave；这几条语句让从库清理掉relay log并重新到主库中找位置
# sync_relay_log=1
#### For Slave
relay-log=/home/mysql/data/mysqldata1/relaylog/mysql-relay-bin
relay-log-info-repository=TABLE
relay-log-recovery=1
#slave-skip-errors=1022,1032,1062,1236
slave-parallel-workers=4
slave-sql-verify-checksum=1
log_bin_trust_function_creators=1
log_slave_updates=1
slave-net-timeout=10
#＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊ MyISAM Specific options ＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
key_buffer_size = 8M
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover_options=force
# ＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊ INNODB Specific options ＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
#### Data options
innodb_data_home_dir = /home/mysql/data/mysqldata1/innodb_ts
innodb_data_file_path = ibdata1:2048M:autoextend
innodb_file_per_table
# 以下三个format系统变量在MySQL 8.0中被移除，内部默认使用barracuda
innodb_file_format = barracuda
innodb_file_format_max = barracuda
innodb_file_format_check = ON
innodb_strict_mode = 1
innodb_flush_method = O_DIRECT
innodb_checksum_algorithm=crc32
innodb_autoinc_lock_mode=2
#### Buffer Pool options
innodb_buffer_pool_size = 6G
innodb_buffer_pool_instances = 4
innodb_max_dirty_pages_pct = 75
innodb_adaptive_flushing = ON
innodb_flush_neighbors = 0
innodb_lru_scan_depth = 4096
innodb_change_buffering = all
innodb_old_blocks_time = 1000
innodb_buffer_pool_dump_at_shutdown=ON
innodb_buffer_pool_load_at_startup=ON
# 在MySQL 8.0中被废弃
# innodb_adaptive_hash_index_partitions=32
#### Redo options
innodb_log_group_home_dir = /home/mysql/data/mysqldata1/innodb_log
innodb_log_buffer_size = 128M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1
innodb_fast_shutdown = 1
# 在MySQL 8.0中被废弃，内部默认开启XA
innodb_support_xa = ON
#### Transaction options
innodb_thread_concurrency = 64
innodb_lock_wait_timeout = 120
innodb_rollback_on_timeout = 1
transaction_isolation = READ-COMMITTED
#### IO options
performance_schema=on
innodb_read_io_threads = 8
innodb_write_io_threads = 16
innodb_io_capacity = 20000
innodb_use_native_aio = 1
#### Undo options
innodb_undo_directory = /home/mysql/data/mysqldata1/undo/
innodb_undo_tablespaces=4
innodb_undo_log_truncate=ON
innodb_purge_threads = 4
innodb_purge_batch_size = 512
innodb_max_purge_lag = 65536
#### MySQL 5.6
#### GTID
gtid-mode=on # GTID only
enforce-gtid-consistency=true # GTID only
optimizer_switch='mrr=on, mrr_cost_based=off, batched_key_access=on'
#### MySQL 5.7
#super_read_only=on
explicit_defaults_for_timestamp=ON
secure_file_priv=null
slave_parallel_type=LOGICAL_CLOCK
slave_rows_search_algorithms='INDEX_SCAN, HASH_SCAN'
innodb_page_cleaners=4