一、 mysql日志类型
MYSQL有不同类型的日志文件(各自存储了不同类型的日志),从它们当中可以查询到MYSQL里都做了些什么,对于MYSQL的管理工作,这些日志文件是不可缺少的。
1. 错误日志(The error log):记录了数据库启动、运行以及停止过程中错误信息;
2. ISAM操作日志(The isam log):记录了所有对ISAM表的修改,该日志仅仅用于调试ISAM模式;
3. SQL执行日志(The query log):记录了客户端的连接以及所执行的SQL语句;
4. 更新日志(The update log):记录了改变数据的语句,已经不建议使用,由二进制日志替代;
5. 二进制日志(The binary log):记录了所有对数据库数据的修改语句;
6. 超时日志(The slow log):记录所有执行时间超过最大SQL执行时间(long_query_time)或未使用索引的语句;
7. relay log:如果你是在用mysql的复制、备份功能,那么从服务器还提供了一种叫做relay log的日志文件。
默认情况下所有日志文件会记录在MYSQL的数据目录下,你可以通过强制mysql去关闭并重新打开一个文件进行日志记录,当然系统会自动加后缀 (如.00001, .00002),方式有在mysql环境下执行语句 mysql>flush logs; 或者通过mysqladmin管理程序执行 #mysqladmin flush-logs 或 #mysqladmin refresh
二、 mysql日志的配置
这些日志的启动方式可以在mysqld_safe方式启动数据库的时候,后面跟选项参数,也可以在配置文件里配置,推荐采用第二种方式,配置方法很简单,我只配置了三种日志:
[mysqld]
log=/var/log/mysqld_common.log
log-error=/var/log/mysqld_err.log
log-bin=/var/log/mysqld_bin.bin
三、 mysql日志的查看
日志的查看很简单,大部分都是文本,直接用vim、less、more之类的工具看就可以了,值得说明的是二进制文件的查看:
1. 首先确定是否开启了二进制文件记录功能
mysql>show variables like 'log_bin';
2. 如果你想知道现在记录二进制数据的文件具体信息,你可以通过下列语句看到现在正在记录哪个文件,以及记录的当前位置:
mysql>show master status;
3. 查看二进制数据需要借助程序mysqlbinlog,看看它支持哪些选项,根据自己需要来使用。
mysql>mysqlbinlog /var/log/mysql/mysql-bin.000040;
4. 将二进制日志文件导出txt文本文件查看
mysql>mysqlbinlog /var/log/mysql/mysql-bin.000040 >/var/log/mysql/000040.txt;
5. 查询某个时间范围的可以执行下列语句,如果记录很多可以将结果定向到一个文件里自己慢慢看:-) :
mysql>mysqlbinlog --start-datetime='2008-01-01 00:00:00' --stop-datetime='2008-08-08 00:00:00' /var/log/mysql/mysql-bin.000040 > ./tmp.log
四、 使用二进制日志恢复数据
mysqlbinlog工具的使用,大家可以看MySQL的帮助手册,里面有详细的用。在这个例子中,重点是--start-position参数和--stop-position参数的使用。
--start-position=N :从二进制日志中位置等于N参量时的事件开始读。
--stop-position=N:从二进制日志中位置等于和大于N参量时的事件起停止读。
1. 创建实验环境
在一测试数据库里,创建一个表,并添加记录,然后产生日志文件。
mysql> create table test(id int auto_increment not null primary key,val int,data varchar(20)); mysql> insert into test(val,data) values(10,'liang'); Query OK, 1 row affected (0.03 sec) mysql> insert into test(val,data) values(20,'jia'); Query OK, 1 row affected (0.08 sec) mysql> insert into test(val,data) values(30,'hui'); Query OK, 1 row affected (0.03 sec) mysql> flush logs; --产生第二个日志文件 Query OK, 0 rows affected (0.09 sec) mysql> insert into test(val,data) values(40,'aaa'); Query OK, 1 row affected (0.05 sec) mysql> insert into test(val,data) values(50,'bbb'); Query OK, 1 row affected (0.03 sec) mysql> insert into test(val,data) values(60,'ccc'); Query OK, 1 row affected (0.03 sec) mysql> delete from test where id between 4 and 5; --删除记录 Query OK, 2 rows affected (0.05 sec) mysql> insert into test(val,data) values(70,'ddd'); Query OK, 1 row affected (0.03 sec) mysql> flush logs; --产生第三个文件文件 Query OK, 0 rows affected (0.11 sec) mysql> insert into test(val,data) values(80,'dddd'); Query OK, 1 row affected (0.05 sec) mysql> insert into test(val,data) values(90,'eeee'); Query OK, 1 row affected (0.03 sec) mysql> drop table test; --删除表 Query OK, 0 row affected (0.05 sec)
2. 恢复数据
先用mysqlbinlog工具将日志文件生成txt文件出来分析。
mysql>mysqlbinlog /var/log/mysql/mysql-bin.000001 > /var/log/mysql/000001.txt; mysql>mysqlbinlog /var/log/mysql/mysql-bin.000002 > /var/log/mysql/000002.txt; mysql>mysqlbinlog /var/log/mysql/mysql-bin.000003 > /var/log/mysql/000003.txt;
通过这三个命令,可以生成分别记录了日志文件的内容,也就是用户操作的步骤。
因为我们需要重做第一个日志文件的所有操作,所以这里只需要将第一个日志文件全恢复就行了。
mysql>mysqlbinlog /var/log/mysql/mysql-bin.000001 | mysql -uroot –p
Ok,接着,我们需要分析的是第二个日志文件。为什么要分析它呢,因为它中途执行了一个操作是DELETE,因为我们要做的是恢复全部数据,也就是我们不希望去重做这个语句。所以在这里我们要想办法去绕开它。
我们先打开.txt文件来分析一下。
/* /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #090427 15:27:56 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.32-community-log created 090427 15:27:56 BINLOG ' fF71SQ8BAAAAZgAAAGoAAAAAAAQANS4xLjMyLWNvbW11bml0eS1sb2cAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC '/*!*/; # at 106 #090427 15:28:37 server id 1 end_log_pos 176 Query thread_id=1 exec_time=0 error_code=0 use mytest/*!*/; SET TIMESTAMP=1240817317/*!*/; SET @@session.pseudo_thread_id=1/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1344274432/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!/C gbk *//*!*/; SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=28/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 176 #090427 15:28:37 server id 1 end_log_pos 204 Intvar SET INSERT_ID=4/*!*/; # at 204 #090427 15:28:37 server id 1 end_log_pos 312 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1240817317/*!*/; insert into test(val,data) values(40,'aaa') /*!*/; # at 312 #090427 15:28:37 server id 1 end_log_pos 339 Xid = 12 COMMIT/*!*/; # at 339 #090427 15:28:46 server id 1 end_log_pos 409 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1240817326/*!*/; BEGIN /*!*/; # at 409 #090427 15:28:46 server id 1 end_log_pos 437 Intvar SET INSERT_ID=5/*!*/; # at 437 #090427 15:28:46 server id 1 end_log_pos 545 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1240817326/*!*/; insert into test(val,data) values(50,'bbb') /*!*/; # at 545 #090427 15:28:46 server id 1 end_log_pos 572 Xid = 13 COMMIT/*!*/; # at 572 #090427 15:29:35 server id 1 end_log_pos 642 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1240817375/*!*/; BEGIN /*!*/; # at 642 #090427 15:29:35 server id 1 end_log_pos 670 Intvar SET INSERT_ID=6/*!*/; # at 670 #090427 15:29:35 server id 1 end_log_pos 778 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1240817375/*!*/; insert into test(val,data) values(60,'ccc') /*!*/; # at 778 #090427 15:29:35 server id 1 end_log_pos 805 Xid = 14 COMMIT/*!*/; # at 805 #090427 15:30:21 server id 1 end_log_pos 875 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1240817421/*!*/; BEGIN /*!*/; # at 875 #090427 15:30:21 server id 1 end_log_pos 981 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1240817421/*!*/; delete from test where id between 4 and 5 /*!*/; # at 981 #090427 15:30:21 server id 1 end_log_pos 1008 Xid = 15 COMMIT/*!*/; # at 1008 #090427 15:30:34 server id 1 end_log_pos 1078 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1240817434/*!*/; BEGIN /*!*/; # at 1078 #090427 15:30:34 server id 1 end_log_pos 1106 Intvar SET INSERT_ID=7/*!*/; # at 1106 #090427 15:30:34 server id 1 end_log_pos 1214 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1240817434/*!*/; insert into test(val,data) values(70,'ddd') /*!*/; # at 1214 #090427 15:30:34 server id 1 end_log_pos 1241 Xid = 16 COMMIT/*!*/; # at 1241 #090427 15:30:41 server id 1 end_log_pos 1282 Rotate to mysql-bin.000003 pos: 4 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; ――――――――――――――――――――――――――――――――――――― */
在这个文件中,我们可以看到DELETE的操作的起始位置是875,终止位置是1008,那么我们只要重做第二个日志文件的开头到的操作,然后再从到末尾的操作,我们就可以把数据给恢复回来,而不会DELETE数据。所以执行两个命令:
mysql>mysqlbinlog /var/log/mysql/mysql-bin.000002 --stop-pos=875 | mysql -uroot –p mysql>mysqlbinlog /var/log/mysql/mysql-bin.000002 --start-pos=1008 | mysql -uroot -p
OK,现在第二个日志文件的数据了。
第三个日志文件也是同理,只要找到DROP TABLE的位置,就可以了。
mysql>mysqlbinlog /var/log/mysql/mysql-bin.000003 --stop-pos=574 | mysql -uroot –p
现在我们再查一下数据看看:
mysql> select * from test;
+----+------+-------+
| id | val | data |
+----+------+-------+
| 1 | 10 | liang |
| 2 | 20 | jia |
| 3 | 30 | hui |
| 4 | 40 | aaa |
| 5 | 50 | bbb |
| 6 | 60 | ccc |
| 7 | 70 | ddd |
| 8 | 80 | dddd |
| 9 | 90 | eeee |
+----+------+-------+
9 rows in set (0.00 sec)
可以看到,全部数据都回来了。
相关推荐
数据恢复工具:有一些第三方工具可以帮助你进行误删数据的恢复,比如使用类似于 "mysqlbinlog"、"mysqlflashback" 或其他 MySQL 数据恢复工具。 寻求专业帮助:如果以上方法无法解决问题,可以寻求专业的数据库恢复...
二进制日志的文件的作用 mysql二进制日志文件用来记录所有...使用二进制日志文件恢复数据库 开启日志文件 mysql默认是不开启日志文件的功能的,需要我们手动开启。操作如下: 1、 打开mysql安装目录\my.ini(windo
MySQL日志文件分类 1.错误日志(Error Log) 2.二进制日志(Binary Log & Binary Log Index) 3.通用查询日志(query log) 4.慢查询日志(slow query log) 5.Innodb的在线 redo 日志(innodb redo log) 6.更新日
如果MySQL服务器启用了二进制日志,你可以使用mysqlbinlog工具来恢复从指定的时间点开始 (例如,从你最后一次备份)直到现在或另一个指定的时间点的数据。“mysqlbinlog:用于处理二进制日志文件的实用工具”。 要...
一旦数据库中的数据出现了错误,就需要使用备份好的数据进行还原恢复。从而将损失降到最低。下面我们来了解一下MySQL常见的有三种备份恢复方式: 1、利用Mysqldump+二进制日志实现备份 2、利用LVM快照+二进制日志...
包含下列主题:mysql 日志文件,使用mysqld 加相应选项来启用某种日志。Mysql完全备份及恢复:mysqldump 对MyISAM 或InnoDB 完全备份,mysqlhotcopy 对MyISAM 完全备份。增量备份:使用二进制日志增量备份,使用...
由于mysqlbinlog命令下无法筛选出某一个表的数据所以只能把日志文件全部导出
目前MySQL 支持的免费备份工具有:mysqldump、mysqlhotcopy,还可以用 SQL 语法进行备份:BACKUP TABLE 或者 SELECT INTO OUTFILE,又或者备份二进制日志,还可以是直接拷贝数据文件和相关的配置文件。MyISAM 表是...
这些数据恢复方案在之前备份内容介绍时,此处总结一下恢复方案,并结合数据库的二进制日志做下数据恢复的示范! 一、恢复方案 1、数据量不是特别大,可以将mysqldump命令备份的数据使用mysql客户端命令或者source...
MySQL Binary Log也就是常说的bin-log, ,是mysql执行改动产生的二进制日志文件,其主要作用有两个: * 数据回复 * 主从数据库。用于slave端执行增删改,保持与master同步。 binlog基本配置和格式 binlog基本配置 ...
# 此参数确定数据日志文件的大小,更大的设置可以提高性能,但也会增加恢复故障数据库所需的时间 innodb_log_files_in_group = 3 # 为提高性能,MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3 innodb_max...
如果MySQL服务器启用了二进制日志,你可以使用mysqlbinlog工具来恢复从指定的时间点开始 (例如,从你最后一次备份)直到现在或另一个指定的时间点的数据。“mysqlbinlog:用于处理二进制日志文件的实用工具”。 要想...
在数据丢失的紧急情况下,我们往往会想到用binlog日志功能进行数据恢复(定时全备份+binlog日志恢复增量数据部分),化险为夷! 废话不多说,下面是梳理的binlog日志操作解说: 一、初步了解binlog MySQL的二进制...
从物理意义上来讲,InnoDB表由共享表空间文件(ibdata1)、独占表空间文件(ibd)、表结构文件(.frm)、以及日志文件(redo文件等)组成。 1、表结构文件 在MYSQL中建立任何一张数据表,在其数据目录对应的数据库...
传送门开启Binlog日志使用Binlog日志恢复数据启动Binlog利用binlog日志重做数据库操作通过binlog日志恢复表记录 开启Binlog日志 修改配置文件,并重启服务 vim /etc/my.cnf [mysqld] server_id=1 #指定server_id ...
本文为大家分享了MYSQL的日志与备份...从它的定义可以看出,这个日志记录了所有会改变表结构和表数据的事件,所以一旦数据被误删除或者因为其他原因导致数据丢失,我们可以通过这个日志将数据恢复。是不是觉得很炫酷。
5.11. MySQL日志文件 5.11.1. 错误日志 5.11.2. 通用查询日志 5.11.3. 二进制日志 5.11.4. 慢速查询日志 5.11.5. 日志文件维护 5.12. 在同一台机器上运行多个MySQL服务器 5.12.1. 在Windows下运行多个服务器 5.12.2....
目前 MySQL 支持的免费备份工具有:mysqldump、mysqlhotcopy,还可以用 SQL 语法进行备份:BACKUP TABLE 或者 SELECT INTO OUTFILE,又或者备份二进制日志(binlog),还可以是直接拷贝数据文件和相关的配置文件。...
MySQL有几个不同的日志文件,可以帮助你找出mysqld内部发生的事情: 日志文件:记入文件中的信息类型 错误日志:记录启动、运行或停止时出现的问题 查询日志:记录建立的客户端连接和执行的语句 二进制日志:...