开启
mysql默认是没有开发bin-log日志,首先我们需要开启bin-log日志,在my.cnf中修改
指定了bin-log日志的路径,开启日志后需要myssqladmin flush log才生效,重启后我们发现在刚才设定的路径新增了log文件,这就是我们需要的二进制日志
由于日志是以二进制方式存储的,不能直接读取,需要使用mysql自带的mysqlbinlog工具来进行查看
语法如下:
现在我们尝试向test1表插入数据
然后使用mysqlbinlog工具进行日志查看
清理
如果每天都会生成大量的二进制日志,这些日志长时间不清理的话,将会对磁盘空间带来很大的浪费,所以定期清理日志是DBA维护mysql的一个重要工作
1) RESET MASTER
在上面查看日志存放的文件夹中,二进制日志命名的格式是以mysql-bin.*,*代表日志的序号,序号是递增的,其中还有mysql-bin.index是日志的索引文件,记录了日志的最大序号
我们执行RESET MASTER命名删除全部日志
查看删除后的日志
可以看到,以前的日志全部被清空,新的日志从00001开始
2) PURGE MASTER LOGS TO & PURGE MASTER LOGS BEFORE
执行PURGE MASTER LOGS TO 'mysql-bin.******'命令,是将'******'编号之前的所有日志进行删除
执行PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh:mm:ss'命令,是将在'yyyy-mm-dd hh:mm:ss'时间之前的所有日志进行删除
3)- EXPIRE_LOGS_DAYS
此参数是设置日志的过期天数,过期的日志将会被自动删除,这有利于减少我们管理日志的工作量,需要修改my.cnf
这里我们设定保存日志为3天,3天之后过期的日志将被自动删除
恢复
bin-log是记录着mysql所有事件的操作,当mysql发生灾难性错误时,可以通过bin-log做完整恢复,基于时间点的恢复,和基于位置的恢复
完整恢复,假定我们每天凌晨2点都会使用mysqldump备份数据库,但在第二天早上9点由于数据库出现了故障,数据无法访问,需要恢复数据,先使用昨天凌晨备份的文件进行恢复到凌晨2点的状态,在使用mysqlbinlog恢复自mysqldump备份以来的binlog
mysql localhost mysql-bin.000001 | mysql -uroot -p
这样数据库就可以完全的恢复到崩溃前的完全状态
基于时间点的恢复,由于误操作,比如说删除了一张表,这时使用上面讲的完全恢复是没有用的,因为日志里面还存在误操作的语句,,我们需要的是恢复到误操作前的状态,然后跳过误操作的语句,再恢复后面操作的语句,假定我们删除了一张表的误操作发生在10:00这个时间点,我们可以使用下面的语句用备份和binlog将数据恢复到故障前
然后跳过误操作的时间点,继续执行后面的binlog
其中--stop-date='2010-09-04 9:59:59' 和 --start-date='2010-09-04 10:01:00' 其中的时间是你误操作的时间点,当然了,这个时间点你需要你自己计算的,而且这个时间点还可以涉及到的不只是误操作,还可以有正确的操作也被跳过去了。
基于位置恢复,由于上面提到的,使用基于时间点的恢复可能出现,在一个时间点里面可能存在误操作和其他正确的操作,所以我们需要一种更为精确的恢复方式
使用mysqlbinlog查看二进制,可看到
其中drop tables test1这个误操作的end_log_pos为8879917,几下这个id,得出它前后操作的id分别为8879916,8879918
我们将进行位置恢复操作
mysql默认是没有开发bin-log日志,首先我们需要开启bin-log日志,在my.cnf中修改
![](http://www.blogjava.net/images/blogjava_net/dongbule/mysql%E4%BA%8C%E8%BF%9B%E5%88%B6%E6%97%A5%E5%BF%97/1.jpg)
指定了bin-log日志的路径,开启日志后需要myssqladmin flush log才生效,重启后我们发现在刚才设定的路径新增了log文件,这就是我们需要的二进制日志
![](http://www.blogjava.net/images/blogjava_net/dongbule/mysql%E4%BA%8C%E8%BF%9B%E5%88%B6%E6%97%A5%E5%BF%97/2.jpg)
由于日志是以二进制方式存储的,不能直接读取,需要使用mysql自带的mysqlbinlog工具来进行查看
语法如下:
#mysqlbinlog mysql
-
bin.
000002
现在我们尝试向test1表插入数据
![](http://www.blogjava.net/images/blogjava_net/dongbule/mysql%E4%BA%8C%E8%BF%9B%E5%88%B6%E6%97%A5%E5%BF%97/3.jpg)
然后使用mysqlbinlog工具进行日志查看
#mysqlbinlog mysql
-
bin.
000002
-
d test
![](http://www.blogjava.net/images/blogjava_net/dongbule/mysql%E4%BA%8C%E8%BF%9B%E5%88%B6%E6%97%A5%E5%BF%97/4.jpg)
清理
如果每天都会生成大量的二进制日志,这些日志长时间不清理的话,将会对磁盘空间带来很大的浪费,所以定期清理日志是DBA维护mysql的一个重要工作
1) RESET MASTER
在上面查看日志存放的文件夹中,二进制日志命名的格式是以mysql-bin.*,*代表日志的序号,序号是递增的,其中还有mysql-bin.index是日志的索引文件,记录了日志的最大序号
我们执行RESET MASTER命名删除全部日志
![](http://www.blogjava.net/images/blogjava_net/dongbule/mysql%E4%BA%8C%E8%BF%9B%E5%88%B6%E6%97%A5%E5%BF%97/5.jpg)
查看删除后的日志
![](http://www.blogjava.net/images/blogjava_net/dongbule/mysql%E4%BA%8C%E8%BF%9B%E5%88%B6%E6%97%A5%E5%BF%97/6.jpg)
可以看到,以前的日志全部被清空,新的日志从00001开始
2) PURGE MASTER LOGS TO & PURGE MASTER LOGS BEFORE
执行PURGE MASTER LOGS TO 'mysql-bin.******'命令,是将'******'编号之前的所有日志进行删除
执行PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh:mm:ss'命令,是将在'yyyy-mm-dd hh:mm:ss'时间之前的所有日志进行删除
3)- EXPIRE_LOGS_DAYS
此参数是设置日志的过期天数,过期的日志将会被自动删除,这有利于减少我们管理日志的工作量,需要修改my.cnf
![](http://www.blogjava.net/images/blogjava_net/dongbule/mysql%E4%BA%8C%E8%BF%9B%E5%88%B6%E6%97%A5%E5%BF%97/7.jpg)
这里我们设定保存日志为3天,3天之后过期的日志将被自动删除
恢复
bin-log是记录着mysql所有事件的操作,当mysql发生灾难性错误时,可以通过bin-log做完整恢复,基于时间点的恢复,和基于位置的恢复
完整恢复,假定我们每天凌晨2点都会使用mysqldump备份数据库,但在第二天早上9点由于数据库出现了故障,数据无法访问,需要恢复数据,先使用昨天凌晨备份的文件进行恢复到凌晨2点的状态,在使用mysqlbinlog恢复自mysqldump备份以来的binlog
mysql localhost mysql-bin.000001 | mysql -uroot -p
这样数据库就可以完全的恢复到崩溃前的完全状态
基于时间点的恢复,由于误操作,比如说删除了一张表,这时使用上面讲的完全恢复是没有用的,因为日志里面还存在误操作的语句,,我们需要的是恢复到误操作前的状态,然后跳过误操作的语句,再恢复后面操作的语句,假定我们删除了一张表的误操作发生在10:00这个时间点,我们可以使用下面的语句用备份和binlog将数据恢复到故障前
mysqlbinlog
--
stop-date='2010-09-04 9:59:59' /var/log/mysql-bin.000001 | mysql -uroot -p
然后跳过误操作的时间点,继续执行后面的binlog
mysqlbinlog
--
start-date='2010-09-04 10:01:00' /var/log/mysql-bin.000001 | mysql -uroot -p
其中--stop-date='2010-09-04 9:59:59' 和 --start-date='2010-09-04 10:01:00' 其中的时间是你误操作的时间点,当然了,这个时间点你需要你自己计算的,而且这个时间点还可以涉及到的不只是误操作,还可以有正确的操作也被跳过去了。
基于位置恢复,由于上面提到的,使用基于时间点的恢复可能出现,在一个时间点里面可能存在误操作和其他正确的操作,所以我们需要一种更为精确的恢复方式
使用mysqlbinlog查看二进制,可看到
![](http://www.blogjava.net/images/blogjava_net/dongbule/mysql%E4%BA%8C%E8%BF%9B%E5%88%B6%E6%97%A5%E5%BF%97/8.jpg)
其中drop tables test1这个误操作的end_log_pos为8879917,几下这个id,得出它前后操作的id分别为8879916,8879918
我们将进行位置恢复操作
mysqlbinlog
--
stop-position='8879916' /var/log/mysql-bin.000001 | mysql -uroot -p
mysqlbinlog -- start-position='8879918' /var/log/mysql-bin.000001 | mysql -uroot -p
mysqlbinlog -- start-position='8879918' /var/log/mysql-bin.000001 | mysql -uroot -p
第一行是恢复到停止位置位置的所以事务,第二性是恢复从给定的起始位置知道二进制日志结束所有事物。
转载自:http://www.blogjava.net/dongbule/archive/2010/09/04/331050.html