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