[mysqlbinlog]记生产中利用binlog数据恢复一例

情况描述：

时间2013-11-11 18:21:04分; dba在数据维护的时候，误删除了某张表。删除之后马上又创建了同样的表，这样后续的数据写入就不用恢复了

处理步骤：

找到了2013-11-11 02:00:00分的mysqldump数据完整的备份(pay.full.sql) + 02:00:00时备份数据库时flush-logs的binlog(mysql-bin.000085:记录着2点之后新增的数据)：增量备份就是基于binlog做的

1. 刷新下binlog

mysqladmin -uroot -pyangcan flush-logs

2. 导出binlog在误删之前的数据,注意时间可以通过先查看在确定

mysqlbinlog -d pay --stop-datetime='2013-11-11 18:21:04' mysql-bin.000085 > ok1.sql

3. 因为特殊情况是不能直接在生产中直接恢复数据(因为binlog里面只能精确到某个库，而不能精确到某个表，如果全部导入，必然是不能这样操作的); 我的处理方法是把数据文件+binlog下载到本地去恢复，完成之后在把本地的数据导出来，在导回到生产中;

在本地操作：如果导入数据中出错，请使用-f参数忽略错误，强制导入

mysql -uroot -pyangcan pay < pay.full.sql
mysql -uroot -pyangcan -f pay < ok1.sql

4.  这样就得到了pay误删之前的数据了。下面我们只需要恢复误删的paylog表即可,

注意：采用的是表结构和数据分开的方式，因为现在生产中的数据是一件在写入数据的。主键自增,不可能按insert的方式插入数据了

mkdir /tmp/paylog
chown mysql:mysql /tmp/paylog
mysqldump -uroot -pyangcan --tab=/tmp/paylog/ pay paylog

5. 把得到的paylog.txt 上传到服务器;在服务器导入数据，这里跳过了id自增的列

load data infile '/tmp/ok/paylog.txt' into table pay.paylog(@id,orderid,money,gold,orderdate,spid,server,user,char,flags);

 

6. 其他

mysqlbinlog常用参数：
--start-datetime
--stop-datetime
--start-position
--stop-position
-d --database=name ,指定只显示指定数据库的binlog信息;但仅限于读本地的binlog
--force : 当master正在写二进制日志,所以需要加--force

# 远程读取binlog
[root@localhost ~]# mysqlbinlog --force-read --read-from-remote-server -uptcheck -p -h192.168.100.33 --start-datetime='2013-08-21 16:00:00' --stop-datetime='2013-08-21 16:10:00' slave-bin.000001

 

mysqldump常用参数：

# database 导出
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]

--skip-add-drop-table
--skip-add-drop-database
--add-drop-table　　　　　　 (默认)
--add-drop-database　　　　　(默认)　　　

# 不增加create database
-n, --no-create-db # 在指定了--databases 或 --all-databases 时可用, 可以指定不创建数据库语句
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `repl_test` /*!40100 DEFAULT CHARACTER SET utf8 */;

# 不复制数据
-d, --no-data No row information.

# 不创建表
-t, --no-create-info
# 导出存储过程
-R, --routines Dump stored routines (functions and procedures).
-E, --events
--triggers
--default-character-set=name 字符集设置

# 关于分隔符号
--fields-terminated-by=name
--fields-enclosed-by=name
--fields-escaped-by=name
--fields-optionally-enclosed-by=name
--lines-terminated-by=name

-F, --flush-logs && use --lock-all-tables or --master-data with --flush-logs.
--ignore-table=repl_test.checksums 
-l, --lock-tables Lock all tables for read. # 锁表,默认是on

--opt Same as --add-drop-table, --add-locks, --create-options,--quick, --extended-insert, --lock-tables, --set-charset,and --disable-keys. Enabled by default, disable with
--skip-opt.

--delete-master-logs 　　　　 : 执行备份后删除二进制日志
--ignore-table=db.tb1 　　　　: 跳过某些表,需要是db.tbname
--lock-all-tables 　　　　　　 : 在dump中锁定所有数据库中的所有表
--lock-tables 　　　　　　　　 : 在包含表前锁定所有表
--single-transaction 　　　　　  : 在数据slave中导出之前执行begine sql语句,这允许innodb表的一致性快照
--master-date={0|1|2}

# 只复制表结构
mysqldump -uroot -p -d -R -E --triggers --databases test > test.schema.sql
# 复制表的数据和表的结构分开
[root@localhost ~]# mysqldump -R -E --triggers --tab=/tmp/data/ repl_test
[root@localhost data]# mysqldump --fields-terminated-by=@ --fields-enclosed-by=" --tab=/tmp/data/ repl_test

mysql> load data infile '/tmp/data/school.txt' into table t1 FIELDS TERMINATED BY ',' (@dropid,name);
enclosed by '"';