开发者社区> 科技小能手> 正文

MySQL5.7配置参数

简介:
+关注继续查看

MySQL5.7的参数调整配置,参考例子,某些具体参数的设置还需要根据实际来调整。

[client] 

port                            = 3306

socket                          = /tmp/mysql3306.sock 

default_character_set           = utf8mb4 

 

[mysqld] 

lower_case_table_names   = 1

port                            = 3306

basedir                         = /usr/local/mysql

datadir                         = /home/mysql3306/mysql3306

socket                          = /tmp/mysq3306.sock

pid_file                        = /home/mysql3306/mysql5711.pid

tmpdir                          = /tmp/

slave_load_tmpdir               = /tmp/

# *** dns resolve *** #

skip_name_resolve               = 1

external_locking

explicit_defaults_for_timestamp = true

character_set_client_handshake  = FALSE

character_set_server            = utf8mb4

collation_server                = utf8mb4_unicode_ci

init_connect                    = 'SET NAMES utf8m2b4'

# *** connection *** #

max_connections                 = 512

max_connect_errors              = 100000

back_log                        = 102

interactive_timeout             = 600

wait_timeout                    = 600

table_definition_cache          = 5000

table_open_cache                = 2048

#query_cache_size                = 64M

#query_cache_limit               = 2M

thread_cache_size               = 51

ft_min_word_len                 = 4

#performance_schema              = OFF

##If large data transfer problems,enlarge max_allowed_packet

thread_stack = 192K

# *** global memory parameter *** #

max_allowed_packet              = 16M

tmp_table_size                  = 256M

max_tmp_tables                  = 128

max_heap_table_size             = 96M

#close query cache

#query_cache_type                = 0

# *** session memory parameter *** #

sort_buffer_size                = 4M

read_buffer_size                = 4M

join_buffer_size                = 4M

read_rnd_buffer_size            = 8M

bulk_insert_buffer_size         = 64M

# *** engine *** # 

default_storage_engine          = InnoDB 

#ignore_builtin_innodb

#plugin_load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so

# *** log *** #

log_timestamps                  = system

log_bin                         = /home/mysql3306/logs/mysql-bin

expire_logs_days                = 10

binlog_format                   = mixed

#binlog_gtid_simple_recovery     = 1

#binlog_do_db

#binlog_ignore_db

log_slave_updates =1

relay_log                       = /home/mysql3306/logs/mysql5711_relay_bin

relay_log_index                 = /home/mysql3306/logs/mysql5711_relay_bin.index

relay_log_info_file             = /home/mysql3306/logs/mysql5711_relay_bin.info

relay_log_recovery              = 0

log_error                       = /home/mysql3306/logs/mysql5711.err

slow_query_log                  = 1 

slow_query_log_file             = /home/mysql3306/logs/mysql5711_slow.log

long_query_time                 = 1 

log_output                      = FILE

log_queries_not_using_indexes   = 1

log_slow_admin_statements       = 1

log_slow_slave_statements       = 1

log_throttle_queries_not_using_indexes = 10

min_examined_row_limit          = 100

#general_log                     = off

general_log_file                = /home/mysql3306/logs/mysql5711_general.log

#log_queries_not_using_indexes  = /mysqllog/mysql5711/mysql5711_not_useing_index.log

max_binlog_cache_size           = 8M

max_binlog_size                 = 1024M

binlog_cache_size               = 4M

sync_binlog                     = 1

server_id                       = 28703306

#read_only                       = 0

#slave_skip_errors               = all

# *** MyISAM Specific options *** #

#key_buffer_size                 = 128M

#bulk_insert_buffer_size         = 64M

#myisam_sort_buffer_size         = 128M 

#myisam_max_sort_file_size       = 10G

#myisam_repair_threads           = 1

#myisam_recover_options          = BACKUP,FORCE

# *** INNODB Specific options *** #

innodb_data_home_dir            = /home/mysql3306/mysql3306/

innodb_data_file_path           = ibdata1:1024M:autoextend 

innodb_log_group_home_dir       = /home/mysql3306/mysql3306/

innodb_buffer_pool_instances    = 1

innodb_buffer_pool_size         = 1024MB

innodb_buffer_pool_dump_pct     = 40

innodb_page_cleaners            = 4

innodb_log_file_size            = 256M 

innodb_log_files_in_group       = 2

innodb_log_buffer_size          = 32M  #default 16M 

innodb_flush_log_at_trx_commit  = 1 

innodb_lock_wait_timeout        = 30 

innodb_undo_directory           = /home/mysql3306/mysql3306/

innodb_undo_logs                = 128  #must >=35.default 128

innodb_undo_tablespaces         = 3    #must >=2

innodb_undo_log_truncate        = 1

innodb_max_undo_log_size        = 256M

innodb_purge_rseg_truncate_frequency = 128

innodb_print_all_deadlocks      = 1    

innodb_sort_buffer_size         = 64M

disabled_storage_engines =MyISAM

innodb_buffer_pool_load_at_startup = 1

innodb_buffer_pool_dump_at_shutdown = 1

innodb_file_format_max =Antelope

innodb_file_format =Antelope

sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

innodb_open_files               = 4000

innodb_file_per_table           = 1

innodb_status_file              = 1

innodb_lock_wait_timeout        = 30

innodb_read_io_threads          = 8

innodb_write_io_threads         = 8

innodb_io_capacity              = 200

innodb_thread_concurrency       = 32

innodb_commit_concurrency       = 0

#innodb_flush_neighbors          = 0  #for ssd

#innodb_flush_neighbors          = 1  #for other

innodb_purge_batch_size         = 1000

innodb_flush_log_at_trx_commit  = 0

innodb_flush_method             = O_DIRECT

innodb_purge_threads            = 4

#innodb_file_format              = barracuda

innodb_strict_mode              = 1

innodb_support_xa               = 1

skip_innodb_adaptive_hash_index 

innodb_force_recovery           =0

#innodb_fast_shutdown

innodb_change_buffering         = all

#innodb_use_native_aio          = 1

innodb_max_dirty_pages_pct      =50

##replication

#replicate_do_db

#replicate_ignore_db             = mysql

#replicate_ignore_db             = test

#replicate_do_table

#replicate_ignore_table

#replicate_wild_do_table

#replicate_wild_ignore_table

#gtid_mode                       = on

#enforce_gtid_consistency        = true

#open mts

master_info_repository          = TABLE

relay_log_info_repository       = TABLE

sync_master_info                = 1

slave-parallel-type =LOGICAL_CLOCK #default databases,group rep

slave_parallel_workers          = 16

relay_log_recovery =ON

binlog_checksum                 = CRC32 #default crc32 

master_verify_checksum          = 1     #default off

slave_sql_verify_checksum       = 1

#binlog_rows_query_log_events    = 1    #

##semi sync replication settings

#plugin_dir=/usr/local/mysql57/lib/plugin

#plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

#loose_rpl_semi_sync_master_enabled = 1

#loose_rpl_semi_sync_slave_enabled  = 1

#loose_rpl_semi_sync_master_timeout = 10000 # 10 second

##transaction

transaction_isolation          = REPEATABLE-READ 

#transaction_write_set_extraction= MURMUR32 #open --single-tra no use

[mysqldump] 

quick 

max_allowed_packet              = 16M 

[mysql] 

no_auto_rehash 

default_character_set           = utf8mb4

[myisamchk] 

#key_buffer_size                 = 20M 

#sort_buffer_size                = 4M 

#read_buffer                     = 4M 

#write_buffer                    = 2M 

[mysqlhotcopy] 

interactive_timeout 

[mysqld_safe]

open_files_limit                = 8192


总结:参数的调整,能对RW的性能有帮助,调整不好也是个坑......spacer.gif 



本文转自 DBAspace 51CTO博客,原文链接:http://blog.51cto.com/dbaspace/1884341

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
CentOS7 64位下MySQL5.7安装与配置(YUM)
CentOS7 64位下MySQL5.7安装与配置(YUM)http://www.bieryun.com/3277.html 1、配置YUM源 在MySQL官网中下载YUM源rpm安装包:http://dev.
1289 0
Windows下 MySQL-5.7.11 安装与配置
MySQL-5.7.11 安装与配置 1、下载mysql                                               > ...
677 0
CentOS7 64位下MySQL5.7安装与配置(YUM)
CentOS7 64位下MySQL5.7安装与配置(YUM) 1、配置YUM源 在MySQL官网中下载YUM源rpm安装包:http://dev.mysql.com/downloads/repo/yum/ # 下载mysql源安装包 shell> wget http://dev.
1696 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
16606 0
centos7环境下mysql5.7的安装与配置
centos7环境下mysql5.7的安装与配置 最近无事闲来折腾虚拟机,以前都是折腾云服务器,现在自己捣捣。看到mysql的教程蛮好的,准备做个笔记。原文来自mysql5.7的安装与配置(centos7环境) 第一步:下载mysql http://mirrors.
2176 0
Win10配置免安装版MySQL5.7
Win10配置免安装版MySQL5.7
36 0
23704
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载