在MySQL的日常运维中，MySQL的配置文件My.cnf不容忽视。MySQL的默认参数不能满足我们日常在线业务的需求，因此优化参数也是一个不可或缺的环节。我不想在这里列出My.在官方文件中可以找到cnf配置中有多少项和每一项的含义。以下仅说明日常工作中应注意的一些参数。

以下是一些参数的描述。当然，还有其他设置可以工作，这取决于您的负载或硬件：在慢内存和快磁盘、高并发和密集负载下，您将需要特殊调整。然而，这里的目标是让你快速获得一个稳定的MySQL配置，而不需要花太多时间来调整一些无关紧要的MySQL设置或阅读文档，找出哪些设置对你很重要。

从MySQL 从5.5版本开始，InnoDB是默认的存储引擎，比其他任何存储引擎都使用得多。这就是为什么它需要仔细配置。

存储在共享表空间或单独表空间中的数据和索引。我们的工作场景安装默认设置了innodb_file_per_table = ON，这样也有助于在工作中进行单独表空间的迁移。MySQL 在5.6中，该属性的默认值为ON。

默认值为1，这意味着InnoDB完全支持ACID功能。当您的主要关注点是数据安全时，此值是最合适的，例如在主节点上。然而，对于磁盘（读写）速度较慢的系统，它将带来巨大的成本，因为每次将flush更改为redo日志时，都需要额外的fsyncs。

如果将其值设置为2，则不可靠（unreliable）。因为提交的事务每秒只能flush到redo日志一次，但在某些场景中是可以接受的。例如，主节点的备份节点值是可以接受的。若值为0，则速度较快，但在系统崩溃时可能会丢失一些数据：仅适用于备份节点。说到这个参数，你会想到另一个sync_binlog。

这种配置决定了将数据和日志写入硬盘的方式。默认使用O_有三种方式DIRECT?。O_DIRECT模式:数据文件的写入操作直接从mysql开始 innodb 当buffer到达磁盘时，不需要通过操作系统进行缓冲，真正完成的是flush，日志仍然需要通过OS缓冲。

这种配置决定了未执行的事务分配的缓存。一般来说，默认值(1MB)就足够了，但如果你的事务包含二进制大对象或大文本字段，缓存很快就会被填满并触发额外的I/O操作。看看Innodb_log_waits状态变量，如果不是0，添加innodb_log_buffer_size。

这个参数在操作和维护中应该注意。缓冲池是数据和索引缓存的地方，属于MySQL的核心参数，默认为128MB。正常情况下，该参数设置为物理内存的60%~70%。(但是我们的例子基本都是多例混合的，所以这个值要根据业务规模来分析。)

这是redo日志的大小。redo日志用于确保快速可靠的编写操作，并在崩溃时恢复。如果您知道您的应用程序需要经常编写数据，并且您使用MySQL 5.6，那么你可以从一开始就把它变成4G。(具体尺寸要根据自己的业务适当调整)

innodb_support_xa可以开关InnoDB的XA两段式事务提交。默认情况下，innodb_support_xa=true，支持提交XA两段式事务。由于XA两段式事务的提交导致多余的flush等操作，性能影响将达到10%。为了提高性能，一些DBA将设置innodb_support_xa=false。在这种情况下，redolog和binlog将无法同步，并且可能存在在主库中提交的事务，但binlog没有记录。这也可能导致事务数据丢失。

该参数用于存储数据字段信息和其他内部数据结构。表越多，需要在这里分配的内存就越多。如果InnoDB使用了池中所有的内存，InnoDB开始从操作系统中分配内存，并在MySQL错误日志中写入警告信息，默认为8MB。一般设置16MB。

默认情况下，MySQL服务器的连接数相对较小，一般只有100个，最好设置最大值。一般设置500~1000，每个链接都会占用一定的内存，所以这个参数越大越好。有些人遇到too many conections将增加参数的大小，但事实上，如果业务量或程序逻辑有问题或SQL写得不好，即使增加参数也无济于事。再次报错只是时间问题。在MySQL中使用连接池或进程池有助于解决这个问题。

• 内存分配在Seesion级别

max_threads(当前活动连接数)* (

read_buffer_size– 顺序读缓冲，提高顺序读效率

 read_rnd_buffer_size– 随机读取缓冲，提高随机读取效率

 sort_buffer_size– 排序缓冲，提高排序效率

 join_buffer_size– 表连接缓冲，提高表连接效率

 binlog_cache_size– 二进制日志缓冲，提高二进制日志写入效率?

 tmp_table_size– 临时内存表，提高临时表的存储效率

 thread_stack– 线程堆栈，临时存储SQL语句/存储过程

 thread_cache_size– 线程缓存减少了反复打开线程的费用

 net_buffer_length– 线程连接缓冲和读取结果缓冲

 bulk_insert_buffer_size– MyISAM表批量写入数据缓冲

)

• Global级内存分配

global buffer(全局内存分配总和) =

innodb_buffer_pool_size

— InnnoDB高速缓冲、行数据、索引缓冲、事务锁、自适应哈希等

  innodb_additional_mem_pool_size

— InnoDB数据字典的额外内存，缓存所有表数据字典

 innodb_log_buffer_size

— InnoDB REDO日志缓冲，提高REDO日志写入效率

 key_buffer_size

— 高速缓冲MyISAM表索引，提高MyISAM表索引读写效率

 query_cache_size

–查询高速缓存，缓存查询结果，提高反复查询返回效率 table_cahce — 表空间文件描述符缓存，提高数据表打开效率

 table_definition_cache

–表定义文件描述符缓存，提高数据表打开效率

优化参数的最终目的是让MySQL通过合理控制内存分配，更好地利用资源，合理使用CPU，建议减少Session的内存分配。

确保复制架构 server-id 一般来说，主ID小于从ID。

如果您希望数据库服务器作为主节点的备份节点，则需要打开二进制日志。如果你这样做了，别忘了把server_id作为唯一的值。即使只有一个服务器，如果你想根据时间点进行数据恢复，这（打开二进制日志）也非常有用：从您最近的备份中恢复（全备份），并应用于二进制日志中的修改（增量备份）。

二进制日志一旦创建，将永久保存。因此，如果你不想耗尽磁盘空间，你可以使用它 PURGE BINARY LOGS 清除旧文件，或设置expire_logs_days 指定将自动删除多少天的日志。记录二进制日志并非没有成本，因此如果您不需要在非主节点的复制节点上，建议关闭此选项。

当客户端连接到数据库服务器时，服务器将分析主机名称，当DNS非常慢时，建立连接将非常慢。因此，建议在启动服务器时关闭skip_name_resolve选项而不是DNS搜索。唯一的限制是IP地址只能在GRANT语句中使用，所以在添加到现有系统中时必须格外小心。

sync_binlog 默认值为0。就像操作系统刷其他文件的机制一样，MySQL不会同步到磁盘上，而是依靠操作系统刷新binary log。

sync__binlog =N (N>0) ，MySQL 每次写N次二进制日志binary 当log使用fdatasync()函数写二进制日志binary时 log同步到磁盘。innodb_flush_log_at_trx_commit和sync_binlog ?都为 1 在mysqld服务崩溃或服务器主机crash的情况下，是最安全的，binary log最多只能丢失一个句子或一个事务。但是鱼和熊掌不能兼得，双1会导致频繁的IO操作，所以这种模式也是最慢的方式。在业务压力允许的情况下，默认情况下，我们的业务是双1配置。

当业务需要使用级联架构时，log_slave_update = 必须打开此参数，否者第三级可能无法接收第一级生成的binlog，因此无法同步数据。

如果内存临时表超过限制，MySQL将自动将其转换为基于磁盘的MyISAM表，并存储在指定的tmpdir目录中。因此，tmpdir应尽可能配置在性能好、速度快的存储设备上。

slow_query_log = 1 ??#打开慢日志

slow_query_log_file = /mysql/log/mysql.slow

long_query_time = 0.5 ?#设置多少秒的查询将进入慢日志

随着科学技术的发展，越来越多的存储设备开始从传统的机械元件转向由电子元件组成的永久存储，价格越来越被企业所接受。存储组件速度提高后，使用传统机械组件的DB配置是浪费，因此需要调整不同存储技术的MySQL配置，如 innodb_io_capacity需要调大， 将日志文件和redo放入机械硬盘中， 将undo放在SSD中， atomic write不需要Doublete Write Buffer， InnoDB压缩， 单机多实例 cgroup等。分析 I/O 情况，动态调整 innodb_io_capacity 和 innodb_max_dirty_pages_pct；试图调整 innodb_adaptive_flushing，查看效果。

针对innodb_write_io_threads 和 innodb_read_io_threads 我们目前还没有做调整，但我相信调整为8或16，系统 I/O 性能会更好。此外，应注意以下几点：任何调整都应以数据支持和严格分析为基础，否则都是空谈；?这种调优很有意义，真的能带来价值，所以要多下功夫，尽可能明白为什么要这样调整。

• Innodb_thread_concurrency=0
• Innodb_sync_spin_loops=288
• table_definition_cache=2000

• Innodb_flush_method 建议用O_DIRECT
• Innodb_io_capacity 设置磁盘支持最大IOPSS
• Innodb_wirte_io_threads=8
• Innodb_read_io_threads=8
• Innodb_purge_threads=1
• 在Innnodb预读方面，建议禁止基于主建或唯一索引的系统预读
• Innodb_random_read_ahead = off

以上是MySQL优化参数参考！详情请关注其他相关文章！