MySql教程

Mysql配置文件

本文主要是介绍Mysql配置文件,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

linux中的my.cnf,文件位置在/etc/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#skip-grant-tables
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
#skip-grant-tables
#innodb_force_recovery=6
port=3815
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin=/var/lib/mysql/binlog
server-id = 10
#跳过DNS解析
skip-name-resolve = 1
#binlog-do-db=ambient_monitor
#binlog-do-db=anbao_cabinet
#binlog-do-db=customer_center
#binlog-do-db=information_schema 
#binlog-do-db=monitor
#binlog-do-db=mysql
#binlog-do-db=performance_schema
#binlog-do-db=sys 
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
character-set-server=utf8
local-infile=0
skip_symbolic_links=yes

#开启sql日志
general_log=1
general_log_file=/var/log/general_sql.log

#连接数配置
#设置并发数
innodb_thread_concurrency=128
#连接错误数量
max_connect_errors = 30000
#客户端并发数量
max_connections = 2000
max_user_connections = 2000

#mysql函数参数默认值修改
group_concat_max_len=102400

#max_open_files: 11000
#table_open_cache: 4200

#官方建议 back_log = 50 + (max_connections / 5),封顶数为65535,默认值= max_connections
#back_log = 450
#并发情况下打开缓存表
#table_open_cache = 600
#最小缓存表
#table_definition_cache = 700
#为了减少会话之间的争用,可以将opentables缓存划分为table_open_cache/table_open_cache_instances个小缓存
#table_open_cache_instances = 64
#每个线程的堆栈大小 如果线程堆栈太小,则会限制执行复杂SQL语句
#thread_stack = 1M
#order by 或group by 时用到
#sort_buffer_size = 4M
#inner left right join时用到
#join_buffer_size = 4M
#缓存启用的线程数
#thread_cache_size = 60
#MySQL连接闲置超过一定时间后(单位:秒)将会被强行关闭
#MySQL默认的wait_timeout  值为8个小时, interactive_timeout参数需要同时配置才能生效
#interactive_timeout = 1800
#wait_timeout = 180
#Metadata Lock最大时长(秒), 一般用于控制 alter操作的最大时长sine mysql5.6
#执行 DML操作时除了增加innodb事务锁外还增加Metadata Lock,其他alter(DDL)session将阻塞
#lock_wait_timeout = 3600
#内存内部临时表
#tmp_table_size = 32M
#max_heap_table_size = 32M

#MyISAM 设置
#对MyISAM表起作用,但是内部的临时磁盘表是MyISAM表,也要使用该值。
##可以使用检查状态值 created_tmp_disk_tables 得知详情
#key_buffer_size = 15M
#read_buffer_size = 8M
#read_rnd_buffer_size = 4M
#bulk_insert_buffer_size = 64M

#INNODB 设置
#innodb最大线程数 0=无限制
#innodb_thread_concurrency = 0
#一般设置物理存储的 60% ~ 70%
#innodb_buffer_pool_size = 8G
#当缓冲池大小大于1GB时,将innodb_buffer_pool_instances设置为大于1的值,可以提高繁忙服务器的可伸缩性
#innodb_buffer_pool_instances = 4
#默认启用。指定在MySQL服务器启动时,InnoDB缓冲池通过加载之前保存的相同页面自动预热。 通常与innodb_buffer_pool_dump_at_shutdown结合使用
#innodb_buffer_pool_load_at_startup = 1
#innodb_buffer_pool_dump_at_shutdown = 1
#InnoDB用于写入磁盘日志文件的缓冲区大小(以字节为单位)。默认值为16MB
#innodb_log_buffer_size = 32M
#InnoDB日志文件组中每一个文件的大小
#innodb_log_file_size = 2G
#是否开启在线回收(收缩)undo log日志文件,支持动态设置,默认开启
#innodb_undo_log_truncate = 1
#当超过这个阀值(默认是1G),会触发truncate回收(收缩)动作,truncate后空间缩小到10M
#innodb_max_undo_log_size = 4G

#innodb_flush_sync = 0
#innodb_flush_neighbors = 0

#CPU多核处理能力设置
#innodb_write_io_threads = 8
#innodb_read_io_threads = 8
#innodb_purge_threads = 4
#innodb_page_cleaners = 4
#innodb_open_files = 65535
#innodb_max_dirty_pages_pct = 50

#innodb_flush_method = O_DIRECT
#innodb_lru_scan_depth = 4000
#innodb_checksum_algorithm = crc32

#在创建InnoDB索引时用于指定对数据排序的排序缓冲区的大小
#innodb_sort_buffer_size = 67108864
#表示每个表都有自已独立的表空间
#innodb_file_per_table = 1


#查询结果分配的内存
#query_cache_size = 256M
#缓存所有查询语音 除了 SELECT SQL_NO_CACHE
#query_cache_type = 1

#[mysqldump]
#quick

#sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION


这篇关于Mysql配置文件的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!