mysql性能参数配置案例

深碍√TFBOYSˉ_ 2022-02-28 15:36 142阅读 0赞

现在有一台机器配置是4核CPU，24G内存，1000G硬盘的服务器，搭建mysql服务器的主服务器，在/etc/my.ini中的配置如下：

[mysqld]
    datadir=/opt/mysql/data
    socket=/var/run/mysqld/mysql.sock
    pid-file=/var/run/mysqld/mysql.pid
    default-storage-engine=INNODB
    replicate-ignore-db=mysql
    character_set_server=utf8
    lower_case_table_names=1
    explicit_defaults_for_timestamp=true
    log-bin=/opt/mysql/logs/mysql-bin.log
    log-bin-index=/opt/mysql/logs/mysql-bin.log.index
    relay-log=/opt/mysql/logs/mysql-bin.relay
    relay-log-index=/opt/mysql/logs/mysql-bin.relay.index
    log-error=/opt/mysql/logs/mysql-error.log
    slow_query_log=1
    binlog_format="MIXED"
    expire-logs-days=10
    max-binlog-size=200M
    log-slave-updates=1
    replicate-do-db=mls
    replicate-do-db=ultrax
    relay_log_recovery=0
    port=3306
    user=mysql
    server-id=2
    gtid_mode = off
    enforce-gtid-consistency
    skip-name-resolve
    sync_binlog=0
    auto-increment_increment=2
    auto_increment_offset=2
    replicate-same-server-id=0
    skip-external-locking
    back_log=500
    key_buffer_size=1024M
    max_allowed_packet=20M
    thread_stack=512k
    sort_buffer_size=32M
    read_buffer_size=32M
    thread_cache_size=64
    query_cache_size = 256M
    query_cache_limit=4M
    query_cache_min_res_unit=2K
    table_open_cache = 1024
    tmp_table_size=512M
    max_connections=5000
    wait_timeout=120
    thread_concurrency=4
    innodb_flush_log_at_trx_commit=2
    innodb_log_buffer_size=64M
    innodb_thread_concurrency=8
    innodb_buffer_pool_size=8G
    read_rnd_buffer_size=16M
    read_only
    innodb_force_recovery= 0
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
    [client]
    socket=/var/run/mysqld/mysql.sock