MySQL日志维护策略

日志类型&＃xff1a;

MySQL有几个不同的日志文件&＃xff0c;可以帮助你找出mysqld内部发生的事情&＃xff1a;

1.启动慢查询日志&＃xff1a;

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

这个可以在调试mysql性能的时候启用,可以找出是哪个sql指令最浪费时间。生产环境中建议关闭。

2.生产环境中关闭通用查询日志&＃xff1a;

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

一般只有在开发测试环境中&＃xff0c;为了定位某些功能具体使用了哪些SQL语句的时候&＃xff0c;才会在短时间段内打开该日志来做相应的分析。

mysql> set global general_log &＃61; 1; #1&＃xff1a;启动通用查询日志&＃xff0c;0&＃xff1a;关闭通用查询日志

mysql> show global variables like ‘%general_log%’;

&＃43;------------------&＃43;----------------------------&＃43;

| Variable_name | Value |

&＃43;------------------&＃43;----------------------------&＃43;

| general_log | ON | #是否启用了通用查询日志

| general_log_file | /var/run/mysqld/mysqld.log | #日志路径

&＃43;------------------&＃43;----------------------------&＃43;

2 rows in set (0.00 sec)

3.定期备份二进制日志和sql数据&＃xff1a;

【本地一份&＃xff0c;远程日志主机一份&＃xff0c;存储主机一份】

在 my.cnf中log-bin &＃61; [filename]是启用二进制日志&＃xff0c;默认以[filename].000001往上记录的&＃xff0c;从启用log-bin之后此时最好用mysqldump 保存当前的mysql某个库的数据&＃xff0c;因为二进制日志只是记录了从现在起到最近一次mysql当机重启中的所有sql语句&＃xff0c;mysql就会开始记录每一个 sql语句&＃xff0c;一旦mysql因各种原因需要重启&＃xff0c;则会产生新的二进制日志&＃xff0c;000001的后缀名会不断往上自加。
若是在mysql当机期间mysql的数 据遭到了破坏(如磁盘损坏)&＃xff0c;之前的数据全部都被破坏了&＃xff0c;这时候这个备份策略就可以帮你挽回损失。
你可以从二进制日志中恢复从开始到最近一次mysql重 启这段时间的数据。
【二进制日志中记录的是每一个sql语句&＃xff0c;可以用mysqlbinlog [filename]查看日志内容】

4.sync_binlog全局变量的取值一定要合适&＃xff1a;

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

sync_binlog&＃xff1a;这个参数是对于MySQL系统来说是至关重要的&＃xff0c;他不仅影响到Binlog对MySQL所带来的性能损耗&＃xff0c;而且还影响到MySQL中数据的完整性。

对于sync_binlog参数的各种设置的说明如下&＃xff1a; sync_binlog&＃61;0&＃xff0c;当事务提交之后&＃xff0c;MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘&＃xff0c;而让Filesystem自行决定什么时候来做同步&＃xff0c;或者cache满了之后才同步到磁盘。

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

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

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

对于事务表&＃xff0c;例如BDB或InnoDB表&＃xff0c;所有更改表的更新(UPDATE、DELETE或INSERT)被缓存起来&＃xff0c;直到服务器接收到 COMMIT语句。
在该点&＃xff0c;执行完COMMIT之前&＃xff0c;mysqld将整个事务写入二进制日志。
当处理事务的线程启动时&＃xff0c;它为 缓冲查询分配binlogcachesize大小的内存。如果语句大于该值&＃xff0c;线程则打开临时文件来保存事务所以如果 bunlogcachesize足够大&＃xff0c;就避免了过多的磁盘的I/O操作,可以把数据全部缓存在内存中。
线程结束后临时文件被删除。 max_binlog_cache_size和binlog_cache_size相对应&＃xff0c;但是所代表的是binlog能够使用的最大cache内存大小。
当我们执行多语句事务的时候&＃xff0c;max_binlog_cache_size如果不够大的话&＃xff0c;系统可能会报出 "Multi-statement transaction required more than max_binlog_cache_size bytesofstorage” 的错误。
所以最好也把maxbinlogcache_size也调大些(具体多大看你的服务器了)。

6.尽量把maxbinlogsize设置大些

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

7.下面是mysql环境的情况&＃xff1a;

mysql> show variables like ‘%binlog%’;

&＃43;--------------------------------&＃43;------------&＃43; | Variable_name | Value | &＃43;--------------------------------&＃43;------------&＃43;

| binlog_cache_size | 1048576 |

| innodb_locks_unsafe_for_binlog | OFF |

| max_binlog_cache_size| 4294967295 |

| max_binlog_size| 1073741824 |

| sync_binlog| 0|

&＃43;--------------------------------&＃43;------------&＃43;