经验分享：MySQL日志维护策略总结

发布时间：2015-04-29 00:00
来源：未知 我要投稿

这几天要折腾mysql服务器，所以在网上搜罗了一些维护策略，然后自己总结实验，下面是我的总结经验和别人的一些建议。

日志类型：

MySQL有几个不同的日志文件，可以帮助你找出mysqld内部发生的事情：

日志文件	记入文件中的信息类型
错误日志	记录启动、运行或停止时出现的问题
查询日志	记录建立的客户端连接和执行的语句
二进制日志	记录所有更改数据的语句。主要用于复制和即时点恢复
慢日志	记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询
事务日志	记录InnoDB等支持事务的存储引擎执行事务时产生的日志

1.启动慢查询日志：

MySQL 如果启用了slow_query_log=ON选项，就会记录执行时间超过long_query_time(默认10s)的查询(初使表锁定的时间不算作 执行 时间)。日志记录文件为slow_query_log_file[=file_name]，如果没有给出file_name值， 默认为主机名，后缀为-slow.log。如果给出了文件名，但不是绝对路径名，文件则写入数据目录。

2.生产环境中关闭通用查询日志：

由 于打开通用查询日志是记录用户的所有操作，在生产环境中这个日志的量是非常大的，所以一般情况下都是不打开的，myslq默认的该日志功能也是关闭的，在 特殊情况下才进行打开

mysql> set global general_log = 1; #1：启动通用查询日志，0：关闭通用查询日志

mysql> show global variables like '%general_log%';

+------------------+----------------------------+  | Variable_name | Value |  +------------------+----------------------------+  | general_log | ON | #是否启用了通用查询日志  | general_log_file | /var/run/mysqld/mysqld.log | #日志路径  +------------------+----------------------------+ 

2 rows in set (0.00 sec)

3.定期备份二进制日志和sql数据：

在 my.cnf中log-bin = [filename]是启用二进制日志，默认以[filename].000001往上记录的，从启用log-bin之后，mysql就会开始记录每一个 sql语句，一旦mysql因各种原因需要重启，则会产生新的二进制日志，000001的后缀名会不断往上自加。若是在mysql当机期间mysql的数 据遭到了破坏(如磁盘损坏)，之前的数据全部都被破坏了，这时候这个备份策略就可以帮你挽回损失。你可以从二进制日志中恢复从开始到日前一次mysql重 启这段时间的数据。

4.sync_binlog全局变量的取值一定要合适：

默 认情况下，并不是每次写入时都将二进制日志与硬盘同步。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃，有可能二进制日志中最后的语句丢失了。 要想防止这种情况，你可以使用sync_binlog全局变量(1是最安全的值，但也是最慢的)，使二进制日志在每N次二进制日志写入后与硬盘同步。对非 事务表的更新执行完毕后立即保存到二进制日志中。

下面解释下sync_binlog:

“sync_binlog”：这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对于“sync_binlog”参数的各种设置的说明如下：

sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。

sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

在 MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系 统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为 1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。从以往经验和相关测试来看,对于 高并发事务的系统来说,“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

5.如果数据库有很多的事务型操作，则建议把二进制日志的回滚上限设置大一些：

对于事务表，例如BDB或InnoDB表，所有更改表的更新(UPDATE、DELETE或INSERT)被缓存起来，直到服务器接收到 COMMIT语句。在该点，执行完COMMIT之前，mysqld将整个事务写入二进制日志。当处理事务的线程启动时，它为 缓冲查询分配binlog_cache_size大小的内存。如果语句大于该值，线程则打开临时文件来保存事务。线程结束后临时文件被删除。

6.尽量把max_binlog_size设置大些

Binlog日志最大值，一般来说设置为512M或者1G，但不能超过1G。该大小并不能非常严格控制Binlog大小，尤其是当到达Binlog 比较靠近尾部而又遇到一个较大事务的时候，系统为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进入当前日志，直到该事务 结束。

7.下面是mysql环境的情况：

 mysql> show variables like '%binlog%';

博文出处：my.oschina.net/u/1759688/blog/384530

本文由 华域联盟 原创撰写：华域联盟 » 经验分享：MySQL日志维护策略总结

转载请保留出处和原文链接：https://www.cnhackhy.com/3038.htm