日志操作是数据库维护中最重要的手段之一,日志文件会记录MySQL服务器的各种信息,所以当MySQL服务器遭到意外损坏时,不仅可以通过日志文件来查看出错的原因,而且还可以通过日志文件进行数据恢复。
MYSQL的日志文件分为二进制日志,错误日志,通用查询日志,慢查询日志。除了二进制文件外,其他日志文件都是文本文件。默认情况下,MySQL只会启动错误日志文件,而其他日志文件则需要手动启动才可以被启动。
二进制日志:该日志文件会以二进制形式记录数据库的各种操作,但是不记录查询语句。
错误日志:该日志文件会记录MySQL服务器启动,关闭和运行时出错等信息。
通用查询日志:该日志记录MySQL服务器的启动和关闭信息,客户端的连接信息,更新数据记录SQL语句和查询数据记录SQL语句。
慢查询日志:记录执行时间超过指定时间的各种操作,通过工具分析慢日志可以定位MySQL服务器性能瓶颈所在。
使用日志有优点也有缺点。启动日志后,虽然可以实现对MySQL服务器进行维护,但是会降低MySQL软件的执行速度。
1. 操作二进制日志
二进制日志(BinLog)详细记录了数据库额变化情况,即SQL语句中的DDL和DML语句,但是不包括数据记录查询操作。通过二进制日志文件,可以详细了解MySQL数据库中进行了哪些操作。
操作二进制文件包括启动二进制日志,查看二进制日志,停止二进制日志和删除二进制日志。
1.1 启动二进制日志
默认情况下,二进制日志是关闭的,如果想启动二进制日志,可以通过设置MySQL服务器的配置文件my.ini来实现,具体内容如下:
[mysqld]log-bin[=dir\[filename]]
参数dir用来指定二进制文件的存储路径,参数filename用来指定二进制文件的文件名,具体格式为filename.number，其中number的格式为000001,000002,000003等。
在具体启动二进制日志时,如果没有设置参数dir和filename,二进制日志文件将使用默认名字主机名-bin.number,保存到默认目录——数据库数据文件里。
默认配置:
[mysqld]log-bin
配置存放路径:
[mysqld]log-bin=d:\mysqllog\binlog
1.2 查看二进制日志
语法如下：
mysqlbinlog filename.number
1.3 停止二进制文件
可以直接到my.ini里面删除[mysqld]组里的log-bin内容即可。也可以通过命令SET实现暂停二进制日志,具体命令内容如下:
SET SQL_LOG_BIN=0SET SQL_LOG_BIN=1
SQL_LOG_BIN的值为0时表示暂停二进制日志功能,SQL_LOG_BIN的值为1时表示重新开启二进制日志功能。需要注意的是只有拥有SUPER权限的用户,才可以执行SET语句。
1.4 删除二进制文件
如果要删除二进制文件，需要通过执行命令RESET MASTER,PURGE MASTERLOGS TO和PURGE MASTERLOGS BEFORE来实现。
语法如下:
RESET MASTER;
执行上述命令可以删除所有二进制文件。
PURGE MASTER LOGS TO filename.number
执行上述命令，可以删除编号小于number的所有二进制日志文件。
PURGE MASTER LOGS BEFORE ‘yyyy-mm-dd hh:MM:ss’
执行上述命令，可以删除指定时间（yyyy-mm-dd hh:MM:ss）之前所创建的所有二进行日志文件。
2. 操作错误日志
2.1 启动错误日志
语法如下:
[mysqld]log-error[=dir\[filename]]
2.2 查看错误日志
文本文件，可直接用文本工具查看
2.3 删除错误日志
mysqladmin –u root –p flush-logs
3. 操作通用查询日志
通用查询日志主要用来记录用户关于MySQL服务器的所有操作,包含MySQL服务器的启动和关闭信息,客户端的连接信息,更新数据记录SQL语句和查询数据记录SQL语句。
3.1 启动通用查询日志
[mysqld]log [=dir\[filename]]
以上方式需要重启MySQL服务器才可以设置生效,还有一种方式通过设置MySQL的环境变量进行动态的控制通用查询日志的开启与关闭。通过设置环境变量general_log进行通用查询日志的动态控制,而不需要重启MySQL服务器,操作如下:
set global general_log=on;show variables like '%general_log%' \G;
3.2 查看通用查询日志
文本文件，可直接用文本工具查看
3.3 停止通用查询日志
[mysqld]#log [=dir\[filename]]
上述需要重启MySQL服务器，动态设置停止通用查询日志的另外一种方式：
set global general_log=off;
3.4 删除通用查询日志
mysqladmin –u root –p flush-logs
4. 操作慢查询日志
慢查询日志记录执行时间超过指定时间的各种操作,通过工具分析慢日志可以定位MySQL服务器性能瓶颈所在。
4.1 开启慢查询日志
[mysqld]log-slow-queries[=dir\[filename]]long_query_time=n
上述需要重启MySQL服务器，另外一种方式动态设置如下:
#通过设置环境变量slow_query_log进行慢查询日志的动态控制，on表示开启set global slow_query_log=on;#设置慢查询日志最大允许的时间，单位为秒set global long_query_time=3;
4.2 查看慢查询日志
文本文件，可直接用文本工具查看
4.3 分析慢查询日志
对应的工具为mysqldumpslow.pl，常用参数如下：
“-s”为分析慢查询日志时指定排序参数，可选的有：“al”表示平均锁定时间，
“ar”表示平均返回记录数，“at“表示平均查询时间。“-t“参数表示只显示指定的行数。
#分析慢查询日志
C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqldumpslow.pl -s at -t 1 "C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data\MHX0JCJYUC7PDAA-slow.log”
“-s at”表示将分析的结果按平均查询时间排序，“-t 1”表示只显示符合条件的第1条。
注意：在分析慢查询日志时，mysqldumpslow.pl为一perl语言编写的脚本，执行该脚本需要对应的perl语言环境，perl环境的安装包可以在http://www.perl.org/下载。
4.4 停止慢查询日志
[mysqld]#log-slow-queries[=dir\[filename]]#long_query_time=n
注释掉即可停止，但上述需要重启MySQL服务器，另外一种方式动态设置如下:
#通过设置环境变量slow_query_log进行慢查询日志的动态控制，off表示关闭set global slow_query_log=off;
4.5 删除慢查询日志
通过Windows的删除命令直接将满查询日志删除,然后使用以下命令重新创建对应文件,语法如下:
删除慢查询日志文件:
C:\Program Files\MySQL\MySQL Server 5.5\bin>del C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.5\data\MHX0JCJYUC7PDAA-slow.log
重新刷新慢查询日志:
mysqladmin –u root –p flush-logs
执行上述命令,MySQL服务器会创建一个新的慢查询日志,此时慢查询日志为空。