mysql設定檔集錦

標籤：wait   cat   tran   ase   pen   blog   work   method   update   

1，4核8G記憶體 主讀

[client] port    = 3306 socket    = /tmp/mysql.sockdefault-character-set = utf8 #設定用戶端的字元編碼[mysqld]#basic settings#default-time_zone=‘+8:00‘character-set-server=utf8collation-server=utf8_general_cibasedir = /usr/local/mysqldatadir=/usr/local/mysql/data/datasocket=/tmp/mysql.sock#pid-file=/var/run/mysqld/mysqld.pidport = 3306server-id = 2symbolic-links=0max_allowed_packet = 100MBslave_pending_jobs_size_max = 200MBtransaction-isolation = READ-COMMITTED#主從配置#master-host=192.168.108.167#master-user=flowtrade#master-password=123456#master-port=3306#master-connect-retry=60#replicate-do-db =flow_trade#replicate-ignore-db=mysql#connection#interactive_timeout = 28800wait_timeout = 28800lock_wait_timeout = 1200skip_name_resolve = 1max_connections = 2000max_connect_errors = 1000000#log setting#log-bin=mysql-bin-log#log-error=/usr/local/mysql/data/mysql-error.loglong_query_time = 5slow_query_log = 1slow_query_log_file = /usr/local/mysql/data/slow.logexpire_logs_days = 90log_slave_updates = 1relay_log = mysql-relay-binrelay_log_recovery=1log_timestamps = SYSTEMmaster_info_repository=TABLErelay_log_info_repository=TABLE#innodb settings#innodb_flush_log_at_trx_commit = 1sync_binlog = 1innodb_file_per_table = 1innodb_buffer_pool_size = 4Ginnodb_doublewrite = ONinnodb_thread_concurrency = 64innodb_purge_threads = 6innodb_read_io_threads = 8innodb_write_io_threads = 8innodb_buffer_pool_instances = 8innodb_log_file_size = 1GBinnodb_log_files_in_group = 3innodb_log_buffer_size = 50Minnodb_io_capacity = 200innodb_io_capacity_max = 400innodb_flush_method=O_DIRECTinnodb_flush_neighbors = 0innodb_print_all_deadlocks = 1#session memory setting#join_buffer_size = 6Mquery_cache_type = 1thread_cache_size = 64query_cache_size = 128Mtable_open_cache = 2000sort_buffer_size = 6Mread_buffer_size = 6Mread_rnd_buffer_size = 6M#replication setting#slave-parallel-type = LOGICAL_CLOCK             slave-parallel-workers = 4              slave_skip_errors = ddl_exist_errors

2，4核16G 主寫

[client] port    = 3306 socket    = /tmp/mysql.sockdefault-character-set = utf8 #設定用戶端的字元編碼[mysqld]#basic settings#default-time_zone = ‘+8:00‘character-set-server = utf8collation-server = utf8_general_cibasedir = /usr/local/mysqldatadir = /usr/local/mysql/data/datasocket = /tmp/mysql.sock#pid-file = /var/run/mysqld/mysqld.pidbind_address = 0.0.0.0port = 3306server-id = 1max_allowed_packet = 100MBtransaction-isolation = READ-COMMITTED#主從複製資料庫設定binlog-do-db=flow_tradebinlog-ignore-db=mysql#connection#interactive_timeout = 28800wait_timeout = 28800lock_wait_timeout = 1200skip_name_resolve = 1max_connections = 3000  ##最大串連數，需根據業務實際使用者連結設定max_connect_errors = 1000000#log setting##log-error = /usr/local/mysql/data/datamysql-error.loglong_query_time = 5slow_query_log = 1slow_query_log_file = /usr/local/mysql/data/data/slow.logexpire_logs_days = 90log_slave_updates = 1log-bin=/usr/local/mysql/data/data/mysql-bin-loglog_timestamps = SYSTEM#innodb settings#innodb_flush_log_at_trx_commit = 1sync_binlog = 1innodb_file_per_table = 1innodb_buffer_pool_size = 10G           innodb_doublewrite = ONinnodb_thread_concurrency = 64innodb_purge_threads = 6innodb_read_io_threads = 6innodb_write_io_threads = 6innodb_buffer_pool_instances = 6innodb_log_file_size = 1GBinnodb_log_files_in_group = 3innodb_log_buffer_size = 50Minnodb_io_capacity = 200        ##根據磁碟的實際IO能力設定innodb_io_capacity_max = 400innodb_flush_method=O_DIRECTinnodb_flush_neighbors = 0innodb_print_all_deadlocks = 1#session memory setting#join_buffer_size = 6Mthread_cache_size = 256query_cache_type = 1query_cache_size = 128Mtable_open_cache = 2000sort_buffer_size = 6Mread_buffer_size = 6Mread_rnd_buffer_size = 6M

 

mysql設定檔集錦