霜天部落 | 关注LAMP高性能、高并发架构的设计与研究

浅析MySql二进制日志的应用

mysql有4种不同的日志,分别是二进制日志,查询日志,慢查询日志和错误日志,这些日记记录着数据库工作的方方面面,可以帮助我们了解数据库的 不同方面的踪迹,下面先介绍二进制日志的作用和使用方法,并利用二进制日志对数据库进行各种维护和优化,其他日志也会在后面陆续会做详细的介绍。

二进制日志(bin-log日志)
bin-log日志记录了所有的DDL和DML的语句,但不包括查询的语句,语句以事件的方式保存,描述了数据的更改过程,此日志对发生灾难时数据恢复起到了极为重要的作用。

开启
mysql默认是没有开发bin-log日志,首先我们需要开启bin-log日志,在my.cnf中修改

指定了bin-log日志的路径,开启日志后需要myssqladmin flush log才生效,重启后我们发现在刚才设定的路径新增了log文件,这就是我们需要的二进制日志

由于日志是以二进制方式存储的,不能直接读取,需要使用mysql自带的mysqlbinlog工具来进行查看
语法如下:

#mysqlbinlog mysql-bin.000002
现在我们尝试向test1表插入数据

然后使用mysqlbinlog工具进行日志查看
#mysqlbinlog mysql-bin.000002 -d test

清理

如果每天都会生成大量的二进制日志,这些日志长时间不清理的话,将会对磁盘空间带来很大的浪费,所以定期清理日志是DBA维护mysql的一个重要工作

1)RESET MASTER

在上面查看日志存放的文件夹中,二进制日志命名的格式是以mysql-bin.*,*代表日志的序号,序号是递增的,其中还有mysql-bin.index是日志的索引文件,记录了日志的最大序号
我们执行RESET MASTER命名删除全部日志

查看删除后的日志

可以看到,以前的日志全部被清空,新的日志从00001开始

2)PURGE MASTER LOGS TOPURGE 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将数据恢复到故障前

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查看二进制,可看到

其中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

第一行是恢复到停止位置位置的所以事务,第二性是恢复从给定的起始位置知道二进制日志结束所有事物。

主从复制

mysql的复制是指将主数据库的DDL和DML操作通过二进制日志传到从服务器上,然后在从服务器上对这些日志做重新执行的操作,从而使得从服务器和主服务器保持数据的同步。

本文章转自:http://www.uini.net/2010/09/mysql-application-of-the-binary-log.html