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