mysql需要调整的参数-白红宇

Mysql需要调整的参数

#以 mysql 5.6版本示例讲解

一、Innodb设置

innodb_buffer_pool_size：保存innodb数据和索引在该buffer中，

对专用mysql服务器，推荐50-80%物理内存大小，

如 5-6GB (8GB RAM)， 20-25GB (32GB RAM),

100-120GB(128GB RAM) 。

innodb_log_file_size：控制redo日志的大小，redo日志可以加速写，

持久性和crash恢复。通常使用两个文件，mysql 5.5总的redo日志限制为4GB，对写负载型的应用，应该增加该值，使后台检查点活动减少，提高性能，如4G左右，太大将使故障恢复需要更长的时间。

innodb_flush_method：控制数据和日志怎样刷新到磁盘，如果使用了RAID控制器，

应该使用O_DIRECT。

innodb_flush_neighbors：如果是SSD可以关闭，对连续IO没有任何性能改善。

innodb_io_capacity和innodb_io_capacity_max：设置每秒有多少后台线程工作，

根据硬件的能力进行设置。

innodb_lru_scan_depth：如果增加innodb_io_capacity，也要增加该值

innodb_file_per_table：设置为ON，每个表使用独立的.ibd文件存储数据和索引，允许当dropping、truncating或rebuilding表时回收再利用剩余空间，也能支持扩展的特性如压缩。

innodb_flush_log_at_trx_commit：默认为1，意味着Innodb是完全遵守ACID特性，刷新每个改变到redo日志；设置为2，每秒刷新一次；0更快，但是在crash时将丢失一些数据。

innodb_log_buffer_size：事务日志buffer，当有大的blob/text字段时，可以考虑提高该值，以免产生额外的I/O负载。通过观察Innodb_log_waits状态变量，如果非0，将增大该值。

二、复制

log-bin：启用二进制日志，默认不是crash安全的，根据业务情况进行设置，

如果需要持久性，需要开启sync_binlog=1，sync_relay_log=1，

relay-log-info-repository=TABLE和master-info-repository=TABLE

或sync_relay_log_info=1和sync_master_info=1。

expire-log-days：推荐1-10天

server-id：在主从复制环境，每个服务器需要有唯一的server-id

binlog_format=ROW：使用基于行的复制，提高性能，减少锁。同时需要设置

transaction-isolation=READ-COMMITIED，

innodb_autoinc_lock_mode=2

三、混合参数

timezone=GMT：适合全球化

character-set-server=utf8mb4

collation-server=utf8mb4_general_ci：utf8 #是更好字符集的格式，

也可以设置skip-character-set-client-handshake

忽略应用程序设置的字符集

sql-mode： #默认是宽容的，会静默的截断数据，应该更好的设置

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,

NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,

NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY

skip-name-resolve：对进来的连接禁用域名反向查找，禁止使用主机名进行授权，

必须使用IP地址进行授权

max_connect_errors：设置成100000，因使用防火墙阻止访问攻击

max-connections：建议300-500，推荐不要太高，建议使用应用连接池，或者mysql线程池

query_cache_size：关闭该选项，使用另外的方式，如好的索引、使用复制分担读负载，或使用外部cache机制(memcached、redis)提高性能。

本文转自 boy461205160 51CTO博客，原文链接:http://blog.51cto.com/461205160/1885588