mysql配置优化 64g_Mysql配置文件 16c64g优化

[TOC]

## 一.说明

以下配置适合16核64G及以上的配置，会让性能稍微提高1/3左右。

## 二.配置

my.cnf

```python

[client]

port = 3306

socket = /usr/local/mysql/mysql.sock

[mysqld]

#基础设置

port = 3306

bind-address = 0.0.0.0

lower_case_table_names=1

character-set-server=utf8mb4

default-storage-engine=innoDB

basedir=/usr/local/mysql

datadir=/usr/local/mysql/data

socket=/usr/local/mysql/mysql.sock

log-error=/var/log/mysql/mysql.log

pid-file=/usr/local/mysql/mysql.pid

#扩展设置

max_connections = 5000

max_connect_errors = 6000

connect_timeout=10

wait_timeout = 300

interactive_timeout = 300

back_log = 300

open_files_limit = 65535

table_open_cache = 16000

max_allowed_packet = 500M

max_heap_table_size = 64M

tmp_table_size = 256M

read_buffer_size = 8M

read_rnd_buffer_size = 8M

sort_buffer_size = 8M

join_buffer_size = 8M

key_buffer_size = 256M

thread_cache_size = 64

thread_stack = 512K

ft_min_word_len = 1

skip-external-locking

bulk_insert_buffer_size = 8M

myisam_sort_buffer_size = 32M

net_retry_count = 100

auto_increment_increment=0

auto_increment_offset=0

explicit_defaults_for_timestamp=false

log_bin_trust_function_creators=1

performance_schema= 0

transaction-isolation = REPEATABLE-READ

query_cache_size = 0

query_cache_type = 0

#binlog日志

log-bin=mysql-bin

log-bin-index=mysql-bin.index

server-id=1

max_binlog_size = 512M

binlog_format = MIXED

log_slave_updates = 0

expire_logs_days = 7

max_relay_log_size = 512M

binlog-ignore-db = mysql

binlog-ignore-db = test

binlog-ignore-db = information_schema

binlog-ignore-db = performance_schema

replicate-ignore-db = mysql

replicate-ignore-db = test

replicate-ignore-db = information_schema

replicate-ignore-db = performance_schema

binlog_cache_size = 20M

max_binlog_cache_size = 15M

#慢查询

slow_query_log=1

long_query_time=2

log-queries-not-using-indexes = TRUE

log_throttle_queries_not_using_indexes=1000

min_examined_row_limit=1000

log-slow-admin-statements = TRUE

log-slow-admin-statements = TRUE

#innodb引擎

innodb_file_per_table = 1

innodb_open_files = 1000

innodb_buffer_pool_size = 48G

innodb_thread_concurrency = 0

innodb_purge_threads = 1

innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 128M

innodb_log_file_size = 128M

innodb_max_dirty_pages_pct = 85

innodb_lock_wait_timeout = 120

innodb_flush_method=O_DIRECT

innodb_data_file_path = ibdata1:10M:autoextend

innodb_autoinc_lock_mode = 2

innodb_buffer_pool_dump_at_shutdown = 1

innodb_buffer_pool_load_at_startup = 1

innodb_support_xa = 0

innodb_status_file = 1

```