Mysql 日志管理(学习笔记九)

mysql日志——记录着mysql数据库运行期间的操作和信息

日志作用

(1)当数据库遭到意外损害，可以通过日志文件来查询出错原因

(2)利用日志文件进行数据恢复

日志分类

(1)二进制日志:以二进制文件的形式记录了数据库中的操作，但是不记录查询语句

(2)错误日志:记录Mysql服务器的启动、关闭和运行错误等信息

(3)慢查询日志:记录执行时间超过指定时间的操作

(4)通用查询日志:记录用户的所有操作，包括启动和关闭MySQL服务、更新语句、查询语句等

日志设置

(1)除了二进制日志，其他都是文本文件

(2)日志文件通常存储在Mysql数据目录下

(3)默认只启动了错误日志功能，其他需要手工启动

(4)但启动日志功能会降低Mysql的执行速度，因为一条操作写进日志中是要花时间的

二进制日志

(1) 以二进制文件的形式记录了数据库中的操作，但是不记录查询语句

(2) 也叫变更日志(update log)，主要用于记录数据库的变化

(3) 启动与设置二进制日志:在Mysql的配置文件中,log-bin=DIR/filename

DIR是存放二进制日志的目录；

每启动一次Mysql，该目录下就会生成一个filename.00000x的文件；

目录下还有一个filename.index的文件，用于存储所有二进制文件清单；

如果我们没有设置DIR和filename，则默认在数据目录下以hostname-bin.00000x命名

# cat /etc/my.cnf|egrep "log-bin"

log-bin = /data/mysqldb/binlog/mysql-bin

# ls /data/mysqldb/binlog/

mysql-bin.000001 mysql-bin.000003 mysql-bin.000005 mysql-bin.000007 mysql-bin.index

mysql-bin.000002 mysql-bin.000004 mysql-bin.000006 mysql-bin.000008

(4)临时停止与启动二进制日志

-> set sql_log_bin=0; 停止-> set sql_log_bin=1; 启动

(5)注意：二进制日志文件与数据库数据文件最好不要放在同一块硬盘上，如果存放数据文件的硬盘坏了，可以用另一块硬盘的二进制日志来恢复数据

(6)查看二进制日志:

# mysqlbinlog mysql-bin.000001

(7)删除二进制日志：

//删除所有->reset master;//删除00004之前的所有->purge master logs to'mysql-bin.000004';//删除指定日期时间之前的所有->purge master logs to'2016-08-10 15:00:00';

(8)使用二进制日志还原数据库

# mysqlbinlog mysql-bin.000001 | mysql -u root -p

# mysqlbinlog mysql-bin.000002 |mysql -u root -p

这条命令可以理解为:使用mysqlbinlog读取二进制日志文件然后使用mysql命令还原到数据库中

注意还原时必须是编号小的先还原

(9)使用二进制日志文件的pos号|时间点恢复

//先执行3条操作-> create database 2test-> create database alvin-> drop database 2test//查看二进制日志，观察每一个命令的起始/结束pos号，和起始/结束时间# mysqlbinlog mysql-bin.000001# at 120#160813 15:13:53serverid 706end_log_pos 217CRC32 0x781f816eQuery    thread_id=21exec_time=0 error_code=0SET TIMESTAMP=1471072433/*!*/;

SET @@session.pseudo_thread_id=21/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=1075838976/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;create database 2test/*!*/;# at 217#160813 15:14:11serverid 706end_log_pos 314 CRC32 0x0fde9820Query    thread_id=21exec_time=0error_code=0SET TIMESTAMP=1471072451/*!*/;create database alvin/*!*/;//想回到什么哪条命令时的状态，可以指定起始/结束pos号恢复# mysqlbinlog --start-position="120" --stop-position="217" mysql-bin.000001 |mysql -u root -p

//日志也会记录下这条恢复语句，我们可以看到这条命令的时间和原来那条一模一样

# mysqlbinlog mysql-bin.000001# at 401#160813 15:13:53server id 706 end_log_pos 498 CRC32 0x3a899ca5 Query    thread_id=21    exec_time=619    error_code=0SET TIMESTAMP=1471072433/*!*/;create database 2test/*!*/;

//同理也可以用时间点来恢复

# mysqlbinlog –start-datetime=”2016-08-13 15:13:53” –stop-datetime=”2016-08-13 15:14:11” mysql-bin.000001 | mysql –uroot –p

错误日志

(1)主要用于记录Mysql服务的开启、关闭和错误信息,若服务启动不成功第一件事应该就是去看这个错误日志

(2)错误日志是默认开启的，且错误日志无法被关闭

(3)设置错误日志：在配置文件中，log-error=DIR/filename

# cat /etc/my.cnf |egrep "log-error"

log-error = /data/mysqldb/log/mysql-error.log

# ls /data/mysqldb/log

mysql-error.log

(4)删除错误日志

mysqladmin -u root -p flush-logs

# ls

mysql-error.log mysql-slow_query.log

通用查询日志

(1)用来记录用户的所有操作:启动和关闭mysql、更新语句、查询语句

(2)启用和设置通用查询日志:配置文件中，log=DIR/filename

(3)删除通用查询日志:

# mysqladmin -u root -p flush-logs

或# echo >access.log

慢查询日志

(1)记录执行时间超过指定时间的执行语句

(2)启动和设置慢查询日志:在配置文件中

log-slow-queries=DIR/filename

long_query_time=n//设置时间为n秒,默认为10s

(3)删除慢查询日志:

# mysqladmin -u root -p flush-logs

MySQL日志类型

a 错误日志（Error Log）

日志文件名称：用--log-error[=file_name]选项来指定mysql保存错误日志文件的位置，如果没有指定file_name，mysql使用默认的错误日志名host_name.err；

日志文件路径：默认情况下，日志文件都保存在mysql的数据目录中。如果执行FLUSH LOGS，错误日志用-old重新命名后缀，并且mysql创建一个新的空日志文件。（如果未给出-log-error选项，则不会重新命名）。

日志其他说明：如果不指定--log-error，或者（Windows）使用--console选项，错误被写入标准错误输出stderr。通常标准输出为终端。

b 查询日志（Query Log）

日志文件名称：（老版本方法）用--log=[file_name]或-l[file_name]选项进行启用。如果未指定file_name，默认名称为host_name.log

在/etc/my.cnf中[mysqld]下面添加log=/var/lib/mysql/Dragonwake.log重启后

#servicemysql restart

查看错误日志：

正确的配置方法，在/etc/my.cnf中[mysqld]下面添加下如内容

# 查询日志

general_log=ON

general_log_file=/var/lib/mysql/Dragonwake.log

重启mysql后

生成的查询日志文件Dragonwake.log

查询日志内容：

#tail -f /var/lib/mysql/Dragonwake.log

c 慢查询日志（Slow Query Log）

日志配置：用--log-slow-queries[=file_name]选项启动时，mysql写一个包含了所有执行超过了long_query_time秒的SQL语句的日志。如果未指定file_name，默认为host_name-slow.err。如果给出了文件名，但不是绝对路径，文件则写入默认的数据目录。

在/etc/my.cnf的[mysqld]下面添加

# 执行超过1秒的sql会被log下来

long_query_time=1

# 将查询执行时间较慢的语句进行记录

log-slow-queries=/var/lib/mysql/Dragonwake-slow.log

重启mysql服务后，出现如下错误，

正确日志配置：mysql5.6版本以上，取消了参数log-slow-queries，更改为slow-query-log-file，同时必须设置slow_query_log=on

# 慢查询日志

# 执行超过1秒的sql会被log下来

long_query_time=1

# 开启慢查询

slow_query_log=on

# 慢查询日志路径

slow-query-log-file=/var/lib/mysql/Dragonwake-slow.log

重启MySQL后

输出慢查询日志Dragonwake-slow.log

慢查询日志内容

d 更新日志（Update Log）

日志配置：用--log-update[=file_name]选项（不建议使用）

在/etc/my.cnf中[mysqld]下面添加，

# 更新查询

log-update=/var/lib/mysql/Dragonwake-update.log

出现如下错误：

暂未找到解决办法

e 二进制日志（Binary Log & Binary Log Index）

日志设置：用--log-bin[=file_name]选项

在/etc/my.cnf中[mysqld]下面添加，

# 二进制查询

log-bin=/var/lib/mysql/Dragonwake-bin.log

重启MySQL后

输出二进制日志

查看二进制日志内容

#tail -f /var/lib/mysql/Dragonwake-bin.000001

f）mysql日志查询

mysql> show variables like'log_%';

h）mysql当前日志

mysql> show master status;

i）mysql二进制日志数目

mysql> show master logs;\