admin 管理员组文章数量: 887021
2023年12月25日发(作者：数据服务工程师)
MySQL的备份与恢复
一、 MySQL的备份：
MySQL通过在全量备份基础上保证完整的二进制日志文件来达到增量备份的目的。因此MySQL实现增量备份主要有以下几个步骤：
1. 开启二进制日志（bin-log）：
mysql默认没有启二进制日志。首先我们需要在中修改参数：
在[mysqld]下添加：
log-bin=log-bin
这是设置开启二进制日志的参数，日志名为和log-bin.000001等。默认路径为：MySQL安装目录data。
expire_logs_day=7
这是设置二进制日志过期时间的参数。目前二进制日志在flush log时自动删除7天之前的日志。
然后重启MySQL服务。可以在E:MySQL Server5.5data目录下看到“log-bin.数字编号”的文件，如log-bin.000001。以后每次flush log时都会从当前时间开始生成新的log-bin，文件名中的数字编号依次递增。
2. MySQL的自动全量备份：
这里使用mysqldump备份数据库。mysqldump是MySQL用于转存储数据库的实用程序。它主要产生一个SQL脚本，其中包含从头重新创建数据库所必需的命令，在不同的 MySQL版本之间升级时相对比较合适，这也是最常用的备份方法。
这里编写了一个批处理程序，通过任务计划定时运行来完成自动备份功能。代码如下：
：
@echo off
title "数据库备份程序"
set MYSQLPATH=E:"MySQL Server5.5"
set DT=%date:~0,4%-%date:~5,2%-%date:~8,2% //获得当前日期
if %time:~0,2% leq 9 (set hour=0%time:~1,1%) else (set hour=%time:~0,2%)
//小时数前自动添0
set TM=%hour%-%time:~3,2%-%time:~6,2% //获得当前时间
set USERNAME=root
set PASSWORD=google //数据库用户名和密码
@echo on
mkdir E:iqe_data_backup //数据库备份的保存目录
%MYSQLPATH%binmysqladmin -u%USERNAME% -p%PASSWORD% flush-logs
//重写日志（新建日志文件储存当前时间之后的日志，其文件名的编号自动增加）
//同时删除7天前的日志
%MYSQLPATH%binmysqldump -u%USERNAME% iqe -p%PASSWORD% >
E:iqe_data_backup"%DT%_%TM%_iqe".sql
//备份数据库到目标路径，其文件名形式为“日期_时间_”
目前数据库比较小，大小157M，备份时间6s。设置为每天23:05分备份一次。
二、 MySQL的恢复：
二进制日志记录着mysql所有事件的操作，当mysql发生灾难性错误时，可以通过它做指定恢复时间的恢复和指定恢复时间的恢复。（以下命令均经过测试）
1. 指定恢复时间：
可以在mysqlbinlog语句中通过--start-date和--stop-date选项指定DATETIME格式的起止时间。例如，假设在2012-06-11上午10:00时，执行SQL语句删除了一个表。要想恢复表和数据，可以先恢复前一晚上的备份，再导入二进制日志的操作，假设二进制文件为log-bin.000022，则输入：
mysqlbinlog --stop-date="2012-06-11 9:59:59" “E:MySQL
Server5.5datalog-bin.000022” mysql -u root -pgoogle
该命令将恢复截止到2012-06-11 9:59:59的所有数据。
再次运行mysqlbinlog：
mysqlbinlog --start-date="2012-06-11 10:00:01" “E:MySQL
Server5.5datalog-bin.000022” mysql -u root -pgoogle
恢复2012-06-11 10:00:01之后的所有数据。这样就可以跳过删除命令恢复数据。
2. 指定恢复位置：
也可以不指定日期和时间，而使用mysqlbinlog的选项--start-position和--stop-position来指定日志位置。它们的作用与起止日选项相同，不同的是给出了从日志起的位置号。使用日志位置是更准确的恢复方法，特别是当由于破坏性SQL语句同时发生许多事务的时候。要想确定位置号，可以运行mysqlbinlog寻找执行了不期望的事务的时间范围，但应将结果重新指向文本文件以便进行检查。比如还是上个例子，操作方法为：
mysqlbinlog --start-date="2012-06-11 9:59:00" --stop-date="2012-06-11 10:01:00"
“E:MySQL Server5.5datalog-bin.000022” > E:logbin_
该命令将创建一个文本文件，以显示时间段内的SQL语句。用文本编辑器打开该文件，寻找不想重复的语句。比如，
# at 161349802
#120611 16:00:10 server id 1 end_log_pos 161349913 Query thread_id=10 exec_time=0 error_code=0
…
DROP TABLE `iqe_ftpkpi` /* generated by server */
则DROP TABLE `iqe_ftpkpi`这个命令的end_log_pos为161349913。于是恢复全量备份后运行以下命令：
mysqlbinlog --stop-position="161349912" “E:MySQL Server5.5datalog-bin.000022”
mysql -u root -pgoogle
mysqlbinlog --start-position="161349914" “E:MySQL Server5.5datalog-bin.000022”
mysql -u root -pgoogle
第一个命令将恢复到位置161349912为止的所有事务，第二个命令将恢复从位置161349914直到二进制日志结束的所有事务，由此可以跳过删除表操作恢复到当前时间的数据库。
版权声明：本文标题：MySQL备份与恢复 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.freenas.com.cn/free/1703449505h451912.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论