mysql数据库的备份与恢复

By | 2011/10/22

书到用时方恨少,前几天突然遇到了误删除mysql数据的情况,数据丢了上万条,抓虾了……忙里偷闲,赶紧小补一下mysql数据库备份及还原的知识,哪怕以后能简单恢复也可以。

其实mysql的备份和恢复原理很简单,一般数据库的备份有完全备份和增量备份,mysql的完全备份可以用mysqldump命令来实现,增量备份只需要在mysql的my.cnf(win下是my.ini)的配置文件中开启bin-log功能就可以了。bin-log生成的是二进制的数据库操作日志,日志内容只包含写操作,即insert、delete、create一类的,不包含select的查询语句。

mysqldump是把数据及表结构导出为mysql语句,而bin-log实际也是根据时间记录的sql的语句,本质上mysql数据库的还原就是根据备份和日志,把一段时间内的sql语句重新执行一遍,其重点在于确定恢复到的时间点。

重点在于语句的使用,mysqldump命令用来导出数据,mysqlbinlog命令用来根据日志还原数据库。两个命令都支持指定数据库操作,这样就可以不影响同一个mysql下的其他数据库。下面把数据备份及恢复的简要流程说下:

数据库备份

mysqldump –opt databasename -uroot -p –flush-logs > databasename_backup.sql

其中–opt配置项可省略,因为默认就是开启的。此选项会默认开启很多相关的设置,比如锁表、创建表的create信息等等。
虽然支持通过–all-databases来备份所有的数据库,但是为了便于还原,建议还是每个数据库做一个备份。
–flush-logs是在dump之前,截断数据库日志,这样在恢复的时候,就知道通过哪一个bin-log文件来进行恢复了。

数据库还原

先找到最近的dump备份,直接把数据导入到数据库中:

mysql -uroot -p databasename < databasename_backup.sql

此sql首先会清空数据库的表,然后创建新表。操作完成后,根据需要恢复的时间点通过mysqlbinlog命令进行恢复:

mysqlbinlog –database=databasename –start-datetime=’2011-10-10 23:22:22′ –stop-datetime=”2011-10-11 23:22:22″ hostname-bin.[0-9]* | mysql -uroot -p

第一个参数是指定要恢复的数据库;第二个和第三个分别是设置要回复的时间段,当然可以不写任何其中的一个,注意开始时间是包含”[“,结束时间是不包含的”)”;第四部分是指定恢复的日志文件,因为日志文件可能是多个,因此使用了通配符,如果确认只有一个日志文件直接输入文件名就行;第五部分就是导入到数据库中了。这样就完成了整个数据的恢复工作。

注意事项

从原理上看,整个操作并不复杂,不过也可能会遇到各种错误,比如5.0的前几个版本就存在导入数据报错的情况,导致数据无法导入。具体情况还得具体分析。

在导入数据前,最好把当时的数据库也copy一份,免得恢复不成功,把之前的数据也弄丢了。

对于根据日志文件导入数据,除了可以根据时间点外,还可以根据记录的事件id进行恢复(–start-position=N –stop-position=M),时间点和时间id可以通过mysqlbinlog –database=databasename hostname-bin.000001 >1.txt导入到文件中查看。

hostname-bin.index文件会存放二进制日志的索引信息,以确定那个日志需要恢复。

One thought on “mysql数据库的备份与恢复

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据