mysql配置文件详细

[mysqld]

########basic settings########L6T7524S6BN053459

server-id = 11

#表示是本机的序号为11,一般来讲就是master的意思

port = 3306

#MySQL服务端口

#重要

bind_address = 10.166.224.32

#绑定IP,增加远程访问IP地址或者禁掉可以让远程机登陆访问了

autocommit = 0

#是否开启自动提交

#重要

character_set_server=utf8mb4

#数据库默认的字符集

#旧的写法为，从5.1之后，不建议使用这个配置

#default-character-set=UTF8

#重要

max_connections = 800

# MySQL的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，

当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，

就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。

可以过'conn%'通配符查看当前状态的连接数量，以定夺该值的大小。

max_connect_errors = 1000

# 对于同一主机，如果有超出该参数值个数的中断错误连接，则该主机将被禁止连接。如需对该主机进行解禁，执行：FLUSH HOST。

datadir = /data/mysql_data

#数据库文件存储目录

transaction_isolation = READ-COMMITTED

# MySQL支持4种事务隔离级别，他们分别是：

# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.

# 如没有指定，MySQL默认采用的是REPEATABLE-READ，ORACLE默认的是READ-COMMITTED

explicit_defaults_for_timestamp = 1

#重要

join_buffer_size = 12M

# 联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存

也是每连接独享

tmp_table_size = 64M

#临时HEAP数据表的最大长度

tmpdir = /tmp

#重要

max_allowed_packet = 16M

# 接受的数据包大小；增加该变量的值十分安全，这是因为仅当需要时才会分配额外内存。# 例如，仅当你发出长查询或MySQLd必须返回大的结果行时MySQLd才会分配更多内存。# 该变量之所以取较小默认值是一种预防措施，以捕获客户端和服务器之间的错误信息包，并确保不会因偶然使用大的信息包而导致内存溢出。

sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR _FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"

# SQL模式

wait_timeout = 1800

# 服务器关闭非交互连接之前等待活动的秒数。参数默认值：28800秒（8小时）

#重要

read_buffer_size = 16M

# MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。

# 如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能

read_rnd_buffer_size = 32M

# MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，

# MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。

但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大

#重要

sort_buffer_size = 33554432

# MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。

# 如果不能，可以尝试增加sort_buffer_size变量的大小

#重要

lower_case_table_names = 1

#是否区分大小写

########log settings########

#重要

log-bin = bin.log

#把对数据进行修改的所有SQL命令(也就是INSERT、UPDATE和DELETE命令)以二进制格式

记入日志(二进制变更日志，binary update log)。

这种日志的文件名是filename.n或默认的hostname.n，其中n是一个6位数字的整数(日志文件按顺序编号)。

binlog_format = mixed

expire_logs_days = 90

#bin log 自动删除/过期的天数。默认值为0,表示“没有自动删除”

log_error = error.log

#错误日志路径

#重要

slow_query_log = 1

#开启慢查日志

slow_query_log_file = slow.log

#慢查日志记录文件

long_query_time = 2

#慢查询时间超过2秒则为慢查询

log_queries_not_using_indexes = 1

#开启未使用索引查询

log_slow_admin_statements = 1

log_slow_slave_statements = 1

log_throttle_queries_not_using_indexes = 10

#参数用来做日志记录的流量控制，一分钟可以记录多少条，默认0是表示不限制。

min_examined_row_limit = 100

#类似于SELECT ... FROM TBL LIMIT N这样的全表扫描的查询因为用不到索引将要报告为慢查询，如果--log-queries-not-using-indexes被开启的话；

#可以在配置文件中使用min-examined-row-limit=Num of Rows来设置，如果要检查的行数大于等于这个量的查询，才会被报告为慢查询。

########innodb settings########

innodb_page_size = 8192

#重要

#每个数据页大小

innodb_buffer_pool_size = 6G

# InnoDB使用一个缓冲池来保存索引和原始数据, 不像MyISAM.

# 这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少.

# 在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的80% # 不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸.

# 注意在32位系统上你每个进程可能被限制在2-3.5G 用户层面内存限制,

# 所以不要设置的太高.

#重要

innodb_buffer_pool_instances = 8

#innodb_buffer_pool_instances可以开启多个内存缓冲池，上面配置分成8个区域，

把需要缓冲的数据hash到不同的缓冲池中，这样可以并行的内存读写。

#可以提升InnoDB的并发性能。如果InnoDB缓存池被划分成多个区域，建议每个区域不小于1GB的空间。

innodb_buffer_pool_load_at_startup = 1

#解释：在启动时把热数据加载到内存。

innodb_buffer_pool_dump_at_shutdown = 1

#解释：在关闭时把热数据dump到本地磁盘。

#以上两个参数配合使用，MySQL5.6之后提供

#在之前的版本里，如果一台高负荷的机器重启后，内存中大量的热数据被清空，此时就会重新从磁盘加载到Buffer_Pool缓冲池里，

#这样当高峰期间，性能就会变得很差，连接数就会很高。

innodb_lock_wait_timeout = 5

#MySQL可以自动地监测行锁导致的死锁并进行相应的处理，但是对于表锁导致的死锁不能自动的监测，

#所以该参数主要被用于在出现类似情况的时候等待指定的时间后回滚。系统默认值是50秒

innodb_undo_directory = /undolog/

innodb_undo_logs = 128

innodb_undo_tablespaces = 3

#在MySQL5.6中开始支持把undo log分离到独立的表空间，并放到单独的文件目录下Undo log是InnoDB MVCC事务特性的重要组成部分。当我们对记录做了变更操作时就会产生undo记录，Undo记录默认被记录到系统表空间(ibdata)中，但从5.6开始，也可以使用独立的Undo 表空间。

Undo记录中存储的是老版本数据，当一个旧的事务需要读取数据时，为了能读取到老版本的数据，需要顺着undo链找到满足其可见性的记录。当版本链很长时，通常可以认为这是个比较耗时的操作

innodb_log_file_size = 4G

有很高写入吞吐量的系统需要增加该值以允许后台检查点活动在更长的时间周期内平滑写

入,得以改进性能. 将此值设置为4G以下是很安全的. 过去的实践表明,日志文件太大的缺点是增加了崩溃时所需的修复时间,但这在5.5和5.6中已得到重大改进.

innodb_large_prefix = 1

innodb_thread_concurrency = 64

innodb_print_all_deadlocks = 1

innodb_strict_mode = 1

innodb_sort_buffer_size = 67108864

#重要

innodb_file_per_table = 1

# InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间

# 独立表空间优点：

# 1．每个表都有自已独立的表空间。

# 2．每个表的数据和索引都会存在自已的表空间中。

# 3．可以实现单表在不同的数据库中移动。

# 4．空间可以回收（除drop table操作处，表空间不能自已回收）

# 缺点：

# 单表增加过大，如超过100G

# 结论：

# 共享表空间在Insert操作上少有优势。其它都没独立表空间表现好。当启用独立表空间时，请合理调整：innodb_open_files

[mysqld-5.7]

innodb_buffer_pool_dump_pct = 40

InnoDB_Buffer_Pool缓冲区有两个区域，一个是sublist of new blocks区域（经常被访问的数据——热数据），一个是sublist of old blocks区域（不经常访问的数据）。当用户访问数据时，如果缓冲区里有相应的数据则直接返回，否则会从磁盘读入缓冲区的sublist of old blocks区域，然后再移动到sublist of new blocks区域，并通过LRU最近最少使用算法来踢出旧数据页。但是这其中也许会存在一个问题，假如有些sql语句做统计用全表扫描，例如select * from t1，或者做一次MySQLdump，这时就会进入sublist of new blocks区域，把一些真正的热数据“踢走”，这样就会造成缓冲区的数据进进出出，导致磁盘I/O频繁。

所以从MySQL5.5.X版本开始，innodb_old_blocks_pct参数可以控制进入缓冲区sublist of old blocks区域的数量，默认是37，占整个缓冲池的比例为3/8。当全表扫描一个大表，或者做MySQLdump时，就可以将innodb_old_blocks_pct设置得小些，例如，设置innodb_old_blocks_pct=5，使数据块进入少量sublist of old blocks区域，并移动到sublist of new blocks区域，从而让更多的热数据不被踢出。当你访问一个小表，或者select查询结果很少时，则可以保持默认的innodb_old_blocks_pct=37，或者设置得更大，比如innodb_old_blocks_pct=50。