以下配置需要找到MySQL的my.cnf的配置文件,在文件中修改,增加或者修改以下配置
字符集
character_set_server=utf8
init-connect='SET NAMES utf8'
validate_password=OFF
大小写
lower_case_table_names=1
扩展设置
最大链接数 初始100,链接超过 之后就不能连接
max_connections = 5000
最大错误链接数 初始100 错误链接超过 之后就不能连接
max_connect_errors = 6000
连接过程中握手时长 默认为10秒
connect_timeout = 120
等待时长 初始值是 28800 即8小时
wait_timeout = 864000
mysql在关闭一个交互的连接之前所要等待的秒数 初始值是 28800 即8小时
interactive_timeout = 864000
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 = 256
thread_stack = 512K
ft_min_word_len = 1
skip-external-locking
bulk_insert_buffer_size = 128M
myisam_sort_buffer_size = 32M
net_retry_count = 100
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
慢查询
slow_query_log=1
long_query_time=10
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 = 32G
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
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