mysql配置文件详解

╰半橙微兮° 2023-06-01 11:04 9阅读 0赞

[client]
    port = 3306
    socket = /home/work/mysql_3306/tmp/mysql.sock
    
    [mysqld]
    user    = work
    port = 3306
    
    basedir = /home/work/mysql_3306
    datadir = /home/work/mysql_3306/data
    tmpdir = /home/work/mysql_3306/tmp
    socket = /home/work/mysql_3306/tmp/mysql.sock
    pid_file = /home/work/mysql_3306/tmp/mysql.pid
    log-error = /home/work/mysql_3306/log/mysql.err
    general_log = /home/work/mysql_3306/log/mysql.log
    slow_query_log_file = /home/work/mysql_3308/log/slow.log
    log-bin = /home/work/mysql_3306/log/mysql-bin
    plugin_dir = /home/work/mysql/lib/mysql/plugin
    
    default-time-zone = "+08:00"
    thread_handling = pool-of-threads
    thread_pool_stall_limit = 50
    thread_pool_oversubscribe = 20
    performance_schema = 1
    log_slave_updates
    log_timestamps = SYSTEM
    log_warnings
    slow_query_log
    long_query_time = 0.5
    lock_wait_timeout    = 120
    show_compatibility_56 = on
    sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
    table_open_cache_instances = 16 
    ##################################
    enforce_storage_engine    = InnoDB 
    optimizer_switch='index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on'
    super_read_only = off 
    log_slow_slave_statements = on
    log_slow_admin_statements = on
    log_slow_verbosity = 'full'
    slow_query_log_use_global_control = "long_query_time" 
    event-scheduler = off 
    back_log = 1000 
    skip_name_resolve
    max_connections = 10240
    max_user_connections = 4000
    max_connect_errors = 1000 
    extra_max_connections = 3
    extra_port = 13308
    table_open_cache = 8192 
    table_definition_cache = 65535
    max_allowed_packet = 64M 
    expire_logs_days = 7
    max_heap_table_size = 1024M 
    read_rnd_buffer_size = 512K 
    group_concat_max_len = 1024000
    sort_buffer_size = 256K 
    read_buffer_size = 64K
    join_buffer_size = 128K 
    thread_cache_size = 256 
    ft_min_word_len = 4 
    default-storage-engine = INNODB
    thread_stack = 192K 
    transaction_isolation = REPEATABLE-READ
    tmp_table_size = 1024M 
    open_files_limit = 65536 
    key_buffer_size = 32M 
    bulk_insert_buffer_size = 64M 
    myisam_sort_buffer_size = 128M 
    myisam_max_sort_file_size = 1G 
    myisam_repair_threads = 1 
    max_allowed_packet = 64M
    max_prepared_stmt_count = 1000000
    #-----------------------------------------------------------------
    #replication
    server-id = 4545454545
    binlog_rows_query_log_events = on 
    log-slave-updates = 1
    relay-log = relay-bin
    #这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
    #auto_increment_offset = 1           
    #这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
    # auto_increment_increment = 1   
    #####################必须集群内互相兼容，建议全都保持一致并为 row
    binlog_format = row
    binlog_checksum = none 
    binlog_cache_size = 16M 
    max_binlog_cache_size = 2G
    sync_binlog = 1
    master_info_repository = TABLE
    relay_log_info_repository = TABLE
    relay_log_recovery = on
    sync_master_info = 10000
    sync_relay_log_info = 10000
    sync_relay_log = 0
    #slave_net_timeout    = 4
    #slave-parallel-type = LOGICAL_CLOCK
    #slave-parallel-workers = 16
    slave_pending_jobs_size_max = 134217728
    slave_preserve_commit_order = 0
    transaction_write_set_extraction = XXHASH64
    binlog_transaction_dependency_tracking = WRITESET
    #####################MySQL5.7.7之后，默认改成60秒。该参数定义了从库从主库获取数据等待的秒数，超过这个时间从库会主动退出读取，中断连接，并尝试重连。
    #slave_net_timeout=8
    ###############mysql版本在5.6之后才有该选项。
    gtid_mode = on
    ###############mysql版本在5.6之后才有该选项。
    enforce-gtid-consistency = on
    #
    ##############################################################################################半同步复制插件，需要安装。mysql5.7版本之后才有。
    ##########################################。安装插件在主库上：install plugin rpl_semi_sync_master soname 'semisync_master.so'; --安装 semisync_master.so插件 
    ##########################################。安装插件在从库上：install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; --安装 semisync_slave.so插件
    #plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
    ################################半同步复制插件，需要安装。mysql5.7版本之后才有。
    #rpl_semi_sync_master_enabled = on
    ################################半同步复制插件，需要安装。mysql5.7版本之后才有。
    #rpl_semi_sync_slave_enabled = on
    ################################半同步复制插件，需要安装。mysql5.7版本之后才有。
    #rpl_semi_sync_master_wait_for_slave_count = 1
    ################################半同步复制插件，需要安装。mysql5.7版本之后才有。
    #rpl_semi_sync_master_timeout = 1000
    ################################半同步复制插件，需要安装。mysql5.7版本之后才有。
    #rpl_semi_sync_master_wait_point = after_sync
    
     
    #undo
    innodb_max_undo_log_size = 1024M 
    innodb_undo_log_truncate = on 
    innodb_undo_logs = 128
    innodb_undo_tablespaces = 3
    innodb_purge_rseg_truncate_frequency = 128
    
    #innodb
    innodb_autoextend_increment = 64
    innodb_concurrency_tickets = 5000
    innodb_old_blocks_time = 1000
    innodb_purge_batch_size = 300
    innodb_stats_on_metadata = off
    innodb_thread_sleep_delay = 10000 
    innodb_adaptive_max_sleep_delay = 15000 
    innodb_buffer_pool_dump_at_shutdown = on
    innodb_buffer_pool_load_at_startup = on
    innodb_flush_neighbors = 1
    ############################nnoDB使用后台线程处理数据页上写 I/O（输入）请求的数量。
    innodb_write_io_threads = 8 
    ############################InnoDB使用后台线程处理数据页上读 I/O（输出）请求的数量。这里输出是输入的两倍。
    innodb_read_io_threads = 16
    innodb_print_all_deadlocks = on
    innodb_buffer_pool_size = 30G
    innodb_buffer_pool_instances = 8 
    innodb_data_file_path = ibdata1:100M:autoextend 
    innodb_thread_concurrency = 48 
    innodb_flush_log_at_trx_commit = 1
    innodb_log_buffer_size = 64M
    ############################该值表示每个redo log文件大小，该值大小约为该mysql实例一个小时产生的日志大小，这里设置为4G，ib_logfile0和ib_logfile1文件都是4G，逻辑上ib_logfile被当成了一个文件。循环写入。
    innodb_log_file_size = 4096M 
    #############################配置redo log成多个文件，Redo log文件以ib_logfile[number]命名，日志目录通过参数innodb_log_group_home_dir控制，以顺序的方式写入文件文件，写满时则回溯到第一个文件，进行覆盖写。
    innodb_log_files_in_group = 3 
    ###############################控制了 Dirty Page 在 Buffer Pool 中所占的比率。
    innodb_max_dirty_pages_pct = 75 
    innodb_max_dirty_pages_pct_lwm = 10 
    innodb_flush_method = O_DIRECT 
    innodb_lock_wait_timeout = 10 
    innodb_file_per_table = 1
    innodb_purge_threads = 4 
    #########################该参数表示缓冲区刷新到磁盘时，刷新脏页数量。普通单个SATA可以设置200，对于SSD可以设置3000到5000，而拥有5个磁盘组成的RAID5可以设置成2000。
    innodb_io_capacity = 5000
    innodb_open_files = 65535 
    innodb_online_alter_log_max_size = 5120M
    innodb_sort_buffer_size = 4M
    innodb_adaptive_hash_index_parts = 8 
    innodb_buffer_pool_chunk_size = 128 
    innodb_buffer_pool_dump_pct = 80 
    innodb_deadlock_detect = on 
    innodb_default_row_format = DYNAMIC 
    innodb_fill_factor = 100 
    innodb_flush_sync = off 
    innodb_log_checksums = on 
    innodb_log_write_ahead_size = 8192 
    innodb_max_undo_log_size = 1073741824 
    innodb_page_cleaners = 4 
    innodb_purge_rseg_truncate_frequency = 128 
    innodb_temp_data_file_path = ibtmp1:12M:autoextend 
    innodb_print_lock_wait_timeout_info = on
    
    
    [myisamchk]
    key_buffer_size = 512M
    sort_buffer_size = 512M
    read_buffer = 8M
    write_buffer = 8M
    
    [mysqld_safe]
    numa_interleave = 1
    
    [mysql]
    no-auto-rehash
    prompt = "\\u@\\h (\\d)> "

转载于:https://www.cnblogs.com/igoodful/p/11555230.html