全备+binlog方式将数据库恢复至指定位置

生产环境中会出现误删数据，使用增备又无法恢复到指定位置。可以通过全备+binlog server方式将数据库恢复至指定位置。

环境描述：

1.将3316实例全备，apply好的数据拷贝到数据库：

[root@bogon backup]# ll
总用量 2586672
-rw-r-----. 1 root root        387 7月  26 17:27 backup-my.cnf
drwx------. 2 root root       4096 7月  26 17:27 employees
-rw-r-----. 1 root root 1073741824 7月  26 17:30 ibdata1
-rw-r--r--. 1 root root  524288000 7月  26 17:30 ib_logfile0
-rw-r--r--. 1 root root  524288000 7月  26 17:29 ib_logfile1
-rw-r--r--. 1 root root  524288000 7月  26 17:30 ib_logfile2
drwx------. 2 root root       4096 7月  26 17:27 mysql
drwx------. 2 root root       4096 7月  26 17:27 performance_schema
-rw-r-----. 1 root root         21 7月  26 17:27 xtrabackup_binlog_info
-rw-r--r--. 1 root root         26 7月  26 17:29 xtrabackup_binlog_pos_innodb
-rw-r-----. 1 root root        117 7月  26 17:29 xtrabackup_checkpoints
-rw-r-----. 1 root root        502 7月  26 17:27 xtrabackup_info
-rw-r-----. 1 root root    2097152 7月  26 17:29 xtrabackup_logfile
drwx------. 2 root root       4096 7月  26 17:27 zhangshuo
[root@bogon backup]# mv * /data/mysql/mysql3318/data/

2.启动3318实例并查看zhangshuo库：

[root@bogon backup]# /usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/my3318.cnf &

root@localhost:mysql3318.sock  17:55:40 [(none)]>use zhangshuo;
Database changed
root@localhost:mysql3318.sock  17:55:44 [zhangshuo]>select * from zs;
Empty set (0.03 sec)

3.将3316实例binlog文件拷贝到新实例3318并查找到误删数据的起始位置：

[root@bogon backup]# cp -p /data/mysql/mysql3316/logs/mysql-bin.* /binlog/

[root@bogon data]# /usr/local/mysql/bin/mysqlbinlog -v --base64-output=decode-rows /binlog/mysql-bin.000014 

BEGIN
/*!*/;
# at 197
#160726 17:54:45 server id 1283316  end_log_pos 250 CRC32 0xc6b65dcb    Table_map: `zhangshuo`.`zs` mapped to number 70
# at 250
#160726 17:54:45 server id 1283316  end_log_pos 301 CRC32 0xf809a3da    Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `zhangshuo`.`zs`
### SET
###   @1=1
###   @2='zhangshuo'
# at 301
#160726 17:54:45 server id 1283316  end_log_pos 332 CRC32 0x7f925ebd    Xid = 54
COMMIT/*!*/;

可以看到误删数据begin位置197和commit位置301.

4.在新实例3318 zhangshuo库中将数据恢复：

[root@bogon data]# /usr/local/mysql/bin/mysqlbinlog --start-position=197 --stop-position=301 /binlog/mysql-bin.000014 |/usr/local/mysql/bin/mysql -S /tmp/mysql3318.sock zhangshuo

root@localhost:mysql3318.sock  18:12:39 [zhangshuo]>select * from zs;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | zhangshuo |
+------+-----------+
1 row in set (0.00 sec)

至此，数据恢复成功。

远程备份binlog文件：

/usr/local/mysql/bin/mysqlbinlog  --read-from-remote-server --host=192.168.1.113 --user=repl --password=repl --port=3316 --raw  --stop-never  mysql-bin.000001 --result-file=/binlog/master3316/

--read-from-remote-server #远程备份mysql binlog的核心选项

--raw #使用该选项，mysqlbinlog将获取的二进制日志原原本本的记录到磁盘，若不是用会生成变换为文本形式的二进制文件

--stop-never #只要远程服务器未关闭，或连接未断开，将会持续不断的复制远程服务器的二进制日志