MySQL 8.0.29 最优配置参数（128G 内存，SSD 磁盘，48 核 CPU）

以下是针对上述硬件配置的 MySQL 8.0.29 最优配置参数：

[mysqld]
# 基础配置
port = 3306
socket = '/var/run/mysqld/mysqld.sock'
pid-file = '/var/run/mysqld/mysqld.pid'

# 存储引擎
default-storage-engine = InnoDB

# 线程和连接
max_connections = 2000
thread_cache_size = 256
thread_concurrency = 48
back_log = 1000

# 缓存
key_buffer_size = 64M
innodb_buffer_pool_size = 100G
innodb_buffer_pool_instances = 32
innodb_log_buffer_size = 8M
query_cache_size = 0
query_cache_type = 0
tmp_table_size = 64M
max_heap_table_size = 64M

# 日志
log_error = '/var/log/mysql/error.log'
slow_query_log = 1
slow_query_log_file = '/var/log/mysql/slow.log'
long_query_time = 2

# 其他
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_flush_method = 'O_DIRECT'
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_thread_concurrency = 48
innodb_write_io_threads = 16
innodb_read_io_threads = 16
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_flush_neighbors = 0
innodb_purge_threads = 4
innodb_page_cleaners = 32
innodb_max_dirty_pages_pct = 90
innodb_lru_scan_depth = 4096
innodb_checksum_algorithm = 'crc32'
innodb_checksums = ON
innodb_doublewrite = ON
innodb_adaptive_hash_index = ON
innodb_stats_on_metadata = OFF
innodb_stats_persistent = ON
innodb_stats_auto_recalc = OFF
innodb_open_files = 4000
innodb_print_all_deadlocks = ON
innodb_sort_buffer_size = 4M
innodb_file_format = 'Barracuda'
innodb_file_format_max = 'Barracuda'

# 安全
skip_name_resolve = 1
secure_file_priv = ''

# 性能调优
innodb_adaptive_flushing_lwm = 10
innodb_adaptive_flushing_lwm_ratio = 0.9
innodb_adaptive_hash_index_parts = 16
innodb_adaptive_max_sleep_delay = 100000
innodb_background_scrub_data_check_interval = 3600
innodb_background_scrub_data_compressed = OFF
innodb_background_scrub_data_interval = 604800
innodb_background_scrub_data_uncompressed = OFF
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_dump_now = OFF
innodb_buffer_pool_load_abort = OFF
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_load_now = OFF
innodb_change_buffer_max_size = 25
innodb_change_buffering = 'all'
innodb_checksum_algorithm = 'crc32'
innodb_cleaner_lsn_age_factor = 'high_checkpoint'
innodb_cmp_per_index_enabled = OFF
innodb_commit_concurrency = 48
innodb_compression_algorithm = 'zlib'
innodb_compression_default = OFF
innodb_compression_level = 6
innodb_compression_pad_pct_max = 50
innodb_concurrency_tickets = 5000
innodb_data_file_path = 'ibdata1:100M:autoextend'
innodb_data_home_dir = '/var/lib/mysql'
innodb_deadlock_detect_interval = 10000
innodb_dedicated_server = ON
innodb_defragment = OFF
innodb_defragment_fill_factor = 0.9
innodb_defragment_frequency = 40
innodb_defragment_n_pages = 7
innodb_defragment_stats_accuracy = 0
innodb_disable_sort_file_cache = OFF
innodb_doublewrite_batch_size = 0
innodb_fast_shutdown = 1
innodb_file_format_check = ON
innodb_file_format_max_check = ON
innodb_file_per_table_check = ON
innodb_flush_log_at_timeout = 1
innodb_flush_method = 'O_DIRECT_NO_FSYNC'
innodb_flush_neighbors = 0
innodb_flush_sync = ON
innodb_flushing_avg_loops = 30
innodb_force_load_corrupted = OFF
innodb_force_recovery = 0
innodb_ft_aux_table = ''
innodb_ft_cache_size = 8000000
innodb_ft_enable_diag_print = OFF
innodb_ft_enable_stopword = ON
innodb_ft_max_token_size = 84
innodb_ft_min_token_size = 3
innodb_ft_num_word_optimize = 2000
innodb_ft_result_cache_limit = 2000000000
innodb_ft_server_stopword_table = ''
innodb_ft_sort_pll_degree = 2
innodb_ft_total_cache_size = 640000000
innodb_ft_user_stopword_table = ''
innodb_idle_flush_pct = 100
innodb_immediate_scrub_data_uncompressed = OFF
innodb_instrument_semaphores = OFF
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_kill_idle_transaction = 0
innodb_large_prefix = OFF
innodb_lock_wait_timeout = 120
innodb_locks_unsafe_for_binlog = OFF
innodb_log_buffer_size = 8M
innodb_log_compressed_pages = ON
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_log_group_home_dir = '/var/lib/mysql'
innodb_lru_scan_depth = 4096
innodb_max_dirty_pages_pct = 90
innodb_max_dirty_pages_pct_lwm = 10
innodb_max_purge_lag = 0
innodb_max_purge_lag_delay = 0
innodb_max_undo_log_size = 2G
innodb_monitor_disable = ''
innodb_monitor_enable = ''
innodb_monitor_reset = ''
innodb_monitor_reset_all = ''
innodb_numa_interleave = OFF
innodb_old_blocks_pct = 37
innodb_old_blocks_time = 1000
innodb_online_alter_log_max_size = 128M
innodb_open_files = 4000
innodb_optimize_fulltext_only = OFF
innodb_page_cleaners = 32
innodb_page_size = 16384
innodb_print_all_deadlocks = ON
innodb_purge_batch_size = 300
innodb_purge_rseg_truncate_frequency = 128
innodb_purge_threads = 4
innodb_random_read_ahead = OFF
innodb_read_ahead_threshold = 56
innodb_read_io_threads = 16
innodb_recovery_stats = OFF
innodb_recovery_update_relay_log = OFF
innodb_replication_delay = 0
innodb_rollback_on_timeout = OFF
innodb_rollback_segments = 128
innodb_sort_buffer_size = 4M
innodb_spin_wait_delay = 6
innodb_stats_auto_recalc = OFF
innodb_stats_include_delete_marked = OFF
innodb_stats_method = 'nulls_equal'
innodb_stats_on_metadata = OFF
innodb_stats_persistent = ON
innodb_stats_persistent_sample_pages = 20
innodb_stats_sample_pages = 8
innodb_stats_transient_sample_pages = 8
innodb_status_output = OFF
innodb_status_output_locks = OFF
innodb_strict_mode = ON
innodb_support_xa = ON
innodb_sync_array_size = 1
innodb_sync_spin_loops = 30
innodb_table_locks = ON
innodb_thread_concurrency = 48
innodb_thread_sleep_delay = 10000
innodb_tmpdir = ''
innodb_undo_directory = '.'
innodb_undo_log_truncate = OFF
innodb_undo_logs = 128
innodb_undo_tablespaces = 0
innodb_use_native_aio = ON
innodb_use_sys_malloc = ON
innodb_version = 8.0.29
innodb_write_io_threads = 16

解释：

1. 基础配置：设置 MySQL 的端口、Socket 文件和 PID 文件的路径。
2. 存储引擎：设置默认的存储引擎为 InnoDB。
3. 线程和连接：设置最大连接数为 2000，线程缓存大小为 256，线程并发数为 48，back_log 为 1000。
4. 缓存：设置 key_buffer_size 为 64M，innodb_buffer_pool_size 为 100G，innodb_buffer_pool_instances 为 32，innodb_log_buffer_size 为 8M，query_cache_size 为 0，query_cache_type 为 0，tmp_table_size 为 64M，max_heap_table_size 为 64M。
5. 日志：设置错误日志和慢查询日志的路径，慢查询时间为 2 秒。
6. 其他：设置 innodb_flush_log_at_trx_commit 为 1，innodb_file_per_table 为 1，innodb_flush_method 为 'O_DIRECT'，innodb_log_file_size 为 2G，innodb_log_files_in_group 为 2，innodb_thread_concurrency 为 48，innodb_write_io_threads 为 16，innodb_read_io_threads 为 16，innodb_io_capacity 为 2000，innodb_io_capacity_max 为 4000，innodb_flush_neighbors 为 0，innodb_purge_threads 为 4，innodb_page_cleaners 为 32，innodb_max_dirty_pages_pct 为 90，innodb_lru_scan_depth 为 4096，innodb_checksum_algorithm 为 'crc32'，innodb_checksums 为 ON，innodb_doublewrite 为 ON，innodb_adaptive_hash_index 为 ON，innodb_stats_on_metadata 为 OFF，innodb_stats_persistent 为 ON，innodb_stats_auto_recalc 为 OFF，innodb_open_files 为 4000，innodb_print_all_deadlocks 为 ON，innodb_sort_buffer_size 为 4M，innodb_file_format 为 'Barracuda'，innodb_file_format_max 为 'Barracuda'。
7. 安全：设置 skip_name_resolve 为 1，secure_file_priv 为空。
8. 性能调优：设置 innodb_adaptive_flushing_lwm 为 10，innodb_adaptive_flushing_lwm_ratio 为 0.9，innodb_adaptive_hash_index_parts 为 16，innodb_adaptive_max_sleep_delay 为 100000，innodb_background_scrub_data_check_interval 为 3600，innodb_background_scrub_data_compressed 为 OFF，innodb_background_scrub_data_interval 为 604800，innodb_background_scrub_data_uncompressed 为 OFF，innodb_buffer_pool_dump_at_shutdown 为 ON，innodb_buffer_pool_dump_now 为 OFF，innodb_buffer_pool_load_abort 为 OFF，innodb_buffer_pool_load_at_startup 为 ON，innodb_buffer_pool_load_now 为 OFF，innodb_change_buffer_max_size 为 25，innodb_change_buffering 为 'all'，innodb_checksum_algorithm 为 'crc32'，innodb_cleaner_lsn_age_factor 为 'high_checkpoint'，innodb_cmp_per_index_enabled 为 OFF，innodb_commit_concurrency 为 48，innodb_compression_algorithm 为 'zlib'，innodb_compression_default 为 OFF，innodb_compression_level 为 6，innodb_compression_pad_pct_max 为 50，innodb_concurrency_tickets 为 5000，innodb_data_file_path 为 'ibdata1:100M:autoextend'，innodb_data_home_dir 为 '/var/lib/mysql'，innodb_deadlock_detect_interval 为 10000，innodb_dedicated_server 为 ON，innodb_defragment 为 OFF，innodb_defragment_fill_factor 为 0.9，innodb_defragment_frequency 为 40，innodb_defragment_n_pages 为 7，innodb_defragment_stats_accuracy 为 0，innodb_disable_sort_file_cache 为 OFF，innodb_doublewrite_batch_size 为 0，innodb_fast_shutdown 为 1，innodb_file_format_check 为 ON，innodb_file_format_max_check 为 ON，innodb_file_per_table_check 为 ON，innodb_flush_log_at_timeout 为 1，innodb_flush_method 为 'O_DIRECT_NO_FSYNC'，innodb_flush_neighbors 为 0，innodb_flush_sync 为 ON，innodb_flushing_avg_loops 为 30，innodb_force_load_corrupted 为 OFF，innodb_force_recovery 为 0，innodb_ft_aux_table 为 ''，innodb_ft_cache_size 为 8000000，innodb_ft_enable_diag_print 为 OFF，innodb_ft_enable_stopword 为 ON，innodb_ft_max_token_size 为 84，innodb_ft_min_token_size 为 3，innodb_ft_num_word_optimize 为 2000，innodb_ft_result_cache_limit 为 2000000000，innodb_ft_server_stopword_table 为 ''，innodb_ft_sort_pll_degree 为 2，innodb_ft_total_cache_size 为 640000000，innodb_ft_user_stopword_table 为 ''，innodb_idle_flush_pct 为 100，innodb_immediate_scrub_data_uncompressed 为 OFF，innodb_instrument_semaphores 为 OFF，innodb_io_capacity 为 2000，innodb_io_capacity_max 为 4000，innodb_kill_idle_transaction 为 0，innodb_large_prefix 为 OFF，innodb_lock_wait_timeout 为 120，innodb_locks_unsafe_for_binlog 为 OFF，innodb_log_buffer_size 为 8M，innodb_log_compressed_pages 为 ON，innodb_log_file_size 为 2G，innodb_log_files_in_group 为 2，innodb_log_group_home_dir 为 '/var/lib/mysql'，innodb_lru_scan_depth 为 4096，innodb_max_dirty_pages_pct 为 90，innodb_max_dirty_pages_pct_lwm 为 10，innodb_max_purge_lag 为 0，innodb_max_purge_lag_delay 为 0，innodb_max_undo_log_size 为 2G，innodb_monitor_disable 为 ''，innodb_monitor_enable 为 ''，innodb_monitor_reset 为 ''，innodb_monitor_reset_all 为 ''，innodb_numa_interleave 为 OFF，innodb_old_blocks_pct 为 37，innodb_old_blocks_time 为 1000，innodb_online_alter_log_max_size 为 128M，innodb_open_files 为 4000，innodb_optimize_fulltext_only 为 OFF，innodb_page_cleaners 为 32，innodb_page_size 为 16384，innodb_print_all_deadlocks 为 ON，innodb_purge_batch_size 为 300，innodb_purge_rseg_truncate_frequency 为 128，innodb_purge_threads 为 4，innodb_random_read_ahead 为 OFF，innodb_read_ahead_threshold 为 56，innodb_read_io_threads 为 16，innodb_recovery_stats 为 OFF，innodb_recovery_update_relay_log 为 OFF，innodb_replication_delay 为 0，innodb_rollback_on_timeout 为 OFF，innodb_rollback_segments 为 128，innodb_sort_buffer_size 为 4M，innodb_spin_wait_delay 为 6，innodb_stats_auto_recalc 为 OFF，innodb_stats_include_delete_marked 为 OFF，innodb_stats_method 为 'nulls_equal'，innodb_stats_on_metadata 为 OFF，innodb_stats_persistent 为 ON，innodb_stats_persistent_sample_pages 为 20，innodb_stats_sample_pages 为 8，innodb_stats_transient_sample_pages 为 8，innodb_status_output 为 OFF，innodb_status_output_locks 为 OFF，innodb_strict_mode 为 ON，innodb_support_xa 为 ON，innodb_sync_array_size 为 1，innodb_sync_spin_loops 为 30，innodb_table_locks 为 ON，innodb_thread_concurrency 为 48，innodb_thread_sleep_delay 为 10000，innodb_tmpdir 为 ''，innodb_undo_directory 为 '.'，innodb_undo_log_truncate 为 OFF，innodb_undo_logs 为 128，innodb_undo_tablespaces 为 0，innodb_use_native_aio 为 ON，innodb_use_sys_malloc 为 ON，innodb_version = 8.0.29，innodb_write_io_threads 为 16。

这些参数的设置是基于硬件配置和 MySQL 版本等因素综合考虑的，旨在优化 MySQL 的性能和稳定性。具体参数的含义和作用可以参考 MySQL 官方文档。