mysql 配置参数详解

skip-external-locking （外部锁定）

外部锁定是使用文件系统锁定来管理MYISAM多个进程对数据库表的争用，在不能将单个进程（例如Mysql服务器）假定为唯一需要访问表的进程的情况下，可以使用外部锁定。简单说来：我们配置了一个目录为数据存储目录，但是不一定只有mysql服务器访问我们的数据存储目录，此时需要使用外部锁定
举例如下：

1. 如果运行多个使用相同数据库目录的服务器（不建议），则每个服务器必须启用外部锁定。
2. 如果使用MyIsamchk对表执行维护操作，则必须确保服务器未运行，或者服务器已启用外部锁定，以便根据需要锁定表文件以及与myisamchk协作以访问表。使用MYISAM打包MyIsam表也是如此。
3. 启用外部锁定后，需要访问表的每个进程都会在访问表之前获取表文件的文件系统锁，如果无法获取所有必须的锁，则会阻止该进程访问表。因此外部锁定会影响服务器性能，可使用skip-external-locking 来控制服务器不启用外部锁。

key_buffer_size

这个参数用来设置整个MYISAM的常规key cache 大小，一般来说，如果运行在32位平台，建议不超过2G，如果运行在64位平台上，最好不超过4G。

MyIsam的索引方式是非聚集索引，主索引和其他索引没有本质区别，在data域都是存储了具体记录行的地址，

key_buffer_size规定了系统将多少内存用作MYISAM的索引缓存，如果内存足够大，又不想去计算，一个简单的计算办法就是将所有的索引文件加起来作为key_buffer_size的大小，为了最小化磁盘I/O，MyIsam将最频繁访问的索引快都放在内存中，这样的内存缓冲区我们称之为key cache，他的大小可以通过参数key_buffer_size来控制。

max_allowed_packet

一个传输的包 a communication package 代表着：

1. 客户端发送到mysql服务端的单个sql statement
2. 服务端发送到客户端的单行数据
3. master发往slave的一个binary log event。

Mysql 单个package可以允许最大值是1G。当传输的package大于max_allowed_packet时，触发错误EN_NET_PACKET_TOO_LARGE并且关闭 connection。

客户端和服务端都有自己的max_allowed_packet变量，所以要调节此参数时。必须同时增加server端和client端的配置变量。
mysql client默认是16MB。
服务端默认64MB

table_open_cache

mysql是多线程的，可能在同一时刻有很多客户端访问某张特定的表，为了能最小化多个客户端在相同表上的不同状态问题，并发会话中访问的每张表都会单独打开，虽然这可能消耗过多的内存，但是通常会提高系统性能。

table_open_cache系统参数是和max_connection相关的，例如200个并发运行的连接，需要指定表缓存数量为200*N 其中N代表执行查询中每个链接涉及到的表最大数量（通常为表的数量），如果table_open_cache太高，mysql可能会消耗完文件描述符。出现拒绝链接或者查询失败,当设置的太低就会出现频繁的换进换出现象影响查询性能。

MYSQL会在如下场景关闭一个未使用的表，并将他从表缓存中移除。

1. 缓存满了，并且一个县城正在尝试打开一个缓存中的表。
2. 缓存中包含了超过table_open_cache定义的数量，并且缓存中的表 不在被任何线程使用。
3. 当我们执行flush tables，或者执行mysqladmin flush-tables以及mysqladmin refresh指令的时候。

sort_buffer_size

在表进行order by和group by 排序操作时，由于排序的字段没有索引，会出现Using filesort，为了提高性能，可用此参数增加每个线程分配的缓存区大小，默认时256k，这个参数不要设置过大，一般128~256k，另外一般出现using filesort的时候，要通过增加索引来解决

read_buffer_size

该参数用于表的顺序扫描，表示每个线程分配的缓冲区大小，比如在进行全表扫描时，mysql会按照数据的存储顺序一次读取数据块，每次读取的数据块首先会暂存在read_buffer_size中，当buffer空间被写满或者全部数据读取结束后，在将buffer中的数据返回给上层调用者，以提高效率默认128k，也不要设置过大

read_rnd_buffer_size

该参数用于表的随机读取，表示每个线程分配的缓冲区大小，比如，按照一个非索引字段做orderby排序操作时，就会利用这个缓冲区来暂存读取的数据，默认时256k，也不要设置过大

thread_cache_size

线程池，线程缓冲。用来缓冲空闲的线程，以至于不被销毁，如果线程缓冲在的空闲线程，需要重新建立新连接，则会优先调用线程池中的缓冲，很快就能相应连接请求，每建立一个连接，都需要一个线程与之匹配。

query_cache_size（mysql 查询缓存在8.0之后移除）

缓存select语句和结果集大小的参数。查询缓存会存储一个select查询的文本与被传送到客户端的相应结果。如果之后接收到一个相同的查询，服务器会从查询缓存中检索结果，而不是再次分析和执行这个同样的查询。如果你的环境中写操作很少，读操作频繁，那么打开query_cache_type=1，会对性能有明显提升，如果写操作频繁，则应该关闭它（query_cache_type=0）。

max_connections

该参数用来设置最大连接数，告诉你当前你的服务器允许多少并发连接。默认为100，一般设置为512-1000即可。请记住，太多的连接会导致内存的使用量过高并且会锁住你的 MySQL 服务器。一般小网站需要 100-200 的连接数，而较大可能需要 500-800 甚至更多。这里的值很大程度上取决于你 MySQL/MariaDB 的使用情况。

max_connect_errors

如果有时网络抽风，或者应用配置错误，或者其他原因导致客户端短时间内不断的尝试连接，客户端可能会被列入黑名单，然后将无法连接，直到再次刷新主机缓存。这个选项默认值太小了，可以考虑设的足够大（如果你的服务器配置够强大的话）。

open_files_limit

mysql打开最大文件数注意与前面提及的table_open_cache关系，table_open_cache设置超过open_files_limit，mysql可能会因为无法打开新的文件描述符出现连接异常。

log-bin

日志备份（主要用于主从数据同步）

binlog_format

mysql复制主要有以下三种方式：基于SQL语句的复制（SBR），基于行的复制(RBR)，混合复制模式(MBR)，对应的binlog的格式：STATEMENT，ROW，MIXED。

1. STATEMENT模式(SBR)
   每一条修改数据的sql语句会记录到binlog中，优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能，缺点是在某些情况下会导致master_slave中的数据不一致。

2. ROW模式（RBR）
   不记录每条sql语句的上下文信息，仅记录哪条数据被修改，修改成啥样，缺点是会产生大量日志。

3. MIXED 模式（MBR）
   以上两种模式混用，一般复制使用STATEMENT来保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式来保存binlog。

server-id

给服务器分配一个独一无二的ID编号; n的取值范围是1~2的32次方启用二进制日志功能。在复制数据同步的时候会用到

expire_logs_days

启用二进制日志后，保留日志的天数。服务器会自动清理指定天数前的日志文件，如果不设置则会导致服务器空间耗尽。一般设置为7～14天。

default_storage_engine

新数据表的默认存储引擎

innodb_file_per_table

提供了更灵活的方式，它把每个数据库的信息保存在一个 .ibd 数据文件中。每个 .idb 文件代表它自己的表空间。通过这样的方式可以更快地完成类似 “TRUNCATE” 的数据库操作，当删除或截断一个数据库表时，你也可以回收未使用的空间。这样配置的另一个好处是你可以将某些数据库表放在一个单独的存储设备。这可以大大提升你磁盘的 I/O 负载。

innodb_buffer_pool_size

该参数定义了InnoDb存储引擎的表数据和索引文件的最大内存缓冲区大小，和MyIsam存储引擎不同，MyIsam的key_buffer_size只缓存索引建，而innodb_buffer_pool_size却同时为数据快和索引快做缓存，这个值设置的越高，访问表中数据需要的磁盘I/O就越少，通常可以设置为物理内存的60%-70%。

innodb_log_file_size

事务日志文件写操作缓存区的最大长度(默认设置是1MB)。

innodb_log_buffer_size

事务日志所使用的缓存区。InnoDB在写事务日志的时候，为了提高性能，先将信息写入Innodb Log Buffer中，当满足innodb_flush_log_trx_commit参数所设置的相应条件（或者日志缓冲区写满）时，再将日志写到文件（或者同步到磁盘）中。可以通过innodb_log_buffer_size参数设置其可以使用的最大内存空间。默认是8MB，一般为16～64MB即可。

innodb_flush_log_at_trx_commit

这个选项决定着什么时候把日志信息写入日志文件以及什么时候把这些文件物理地写(术语称为”同步”)到硬盘上。设置值0的意思是每隔一秒写一次日 志并进行 同步，这可以减少硬盘写操作次数，但可能造成数据丢失; 设置值1(设置设置)的意思是在每执行完一条COMMIT命令就写一次日志并进行同步，这可以防止数据丢失，但硬盘写操作可能会很频繁; 设置值2是一般折衷的办法，即每执行完一条COMMIT命令写一次日志，每隔一秒进行一次同步。

innodb_lock_wait_timeout

如果某个事务在等待n秒(s)后还没有获得所需要的资源，就使用ROLLBACK命令放弃这个事务。这项设置对于发现和处理未能被InnoDB数据表驱动 程序识别出来的死锁条件有着重要的意义。这个选项的默认设置是50s。

百万级别配置参考

[mysqld]
port = 3306
socket = /tmp/mysql.sock
user = mysql
server_id　　= 10
datadir　　= /data/mysql/
old_passwords　　= 1
lower_case_table_names　　= 1
character-set-server　　= utf8
default-storage-engine　　= INNODB
log-bin　　= bin.log
log-error　　= error.log
pid-file　　= mysql.pid
long_query_time　　= 2
slow_query_time　　= 2
slow_query_log　
slow_query_log_file　　= slow.log
binlog_cache_size　　= 4MB
binlog_format　　= mixed
max_binlog_cache_size　　= 16MB
max_binlog_size　　= 1GB
expire_logs_days　　= 30
ft_min_word_len　　= 4
back_log　　= 512
max_allowed_packet　　= 64MB
max_connections　　= 4096
max_connect_errors　　= 100
join_buffer_size　　= 2MB
read_buffer_size　　= 2MB
read_rnd_buffer_size　　= 2MB
sort_buffer_size　　　　= 2MB
query_cache_size　　= 2MB
table_open_cache　　= 10000
thread_cache_size　　= 256
max_heap_table_size　　= 64MB
tmp_table_size　　= 64MB
thread_stack　　= 192KB
thread_concurrency　　= 24
local-infile　　= 0
skip-show-database
skip-name-resolve
skip-external-locking
connect_timeout　　= 600
interactive_timeout　　= 600
wait_timeout　　= 600
innodb_buffer_pool_size　　= 64G
innodb_additional_mem_pool_size　　= 32MB
innodb_data_file_path　　=　ibdata1:1G;ibdata2:1G:autoextend
innodb_read_io_threads　　= 8
innodb_write_io_threads 　　= 8
innodb_file_per_table　　= 1
innodb_flush_log_at_thx_commit　　= 2
innodb_lock_wait_timeout　　= 120
innodb_log_buffer_size　　= 8MB
innodb_log_file_size　　= 256MB
innodb_log_files_in_group　　= 3
innodb_max_dirty_pages_pct　　= 90
innodb_thread_concurrency　　= 16
innodb_open_files　　= 10000
#innodb_force_recovery　　= 4

#replication slave
read-only
#skip-salve-start
relay-log　　= relay.log
log-slave-updates