mysql备份binglog_mysql备份还原-基于binlog的增量备份还原

启用binlog

vi my.cnf

log-bin=/var/lib/mysql/mysql-bin.log，如果是这样的话log-bin=mysql-bin.log默认在datadir目录下面

[root@BlackGhost mysql]# ls |grep mysql-bin

mysql-bin.000001

mysql-bin.000002

mysql-bin.000003

mysql-bin.000004

mysql-bin.000005

mysql-bin.000006

mysql-bin.index

查看mysql-bin.000002这样的文件里面到底是什么东西

[root@BlackGhost mysql]# mysqlbinlog   /var/lib/mysql/mysql-bin.000002 > /tmp/add.sql

binlog增量备份和增量还原

1，增量备份

既然我们知道了，mysql里面新增加的数据在mysql-bin这样的文件里面，我们只要把mysql-bin这样的文件进行备份就可以了。

cp /var/lib/mysql/mysql-bin* /data/mysql_newbak/

或

备份命令

mysqlbinlog --read-from-remote-server --raw --host=192.168.244.145 --port=3306 --user=repl --password=repl --stop-never mysql-bin.000001

解释如下：

–read-from-remote-server：用于备份远程服务器的binlog。如果不指定该选项，则会查找本地的binlog。

–raw：binlog日志会以二进制格式存储在磁盘中，如果不指定该选项，则会以文本形式保存。

–user：复制的MySQL用户，只需要授予REPLICATION SLAVE权限。

–stop-never：mysqlbinlog可以只从远程服务器获取指定的几个binlog，也可将不断生成的binlog保存到本地。指定此选项，代表只要远程服务器不关闭或者连接未断开，mysqlbinlog就会不断的复制远程服务器上的binlog。

mysql-bin.000001：代表从哪个binlog开始复制。

除了以上选项外，还有以下几个选项需要注意：

–stop-never-slave-server-id：在备份远程服务器的binlog时，mysqlbinlog本质上就相当于一个从服务器，该选项就是用来指定从服务器的server-id的。默认为-1。

–to-last-log：代表mysqlbinlog不仅能够获取指定的binlog，还能获取其后生成的binlog，获取完了，才终止。如果指定了–stop-never选项则会隐式打开–to-last-log选项。

–result-file：用于设置远程服务器的binlog，保存到本地的前缀。譬如对于mysql-bin.000001，如果指定–result-file=/test/backup-，则保存到本地后的文件名为/test/backup-mysql-bin.000001。注意：如果将–result-file设置为目录，则一定要带上目录分隔符“/”。譬如–result-file=/test/，而不是–result-file=/test，不然保存到本地的文件名为/testmysql-bin.000001。

我们现在要结合Binlog来恢复，但前提要找出误操作前的pos点，

通过事件的位置来恢复(不完全恢复)

[root@localhost ~]# mysqlbinlog -v –base64-output=DECODE-ROWS localhost-bin.000002 |grep -C 10 -i "drop database"

### INSERT INTO `xuanzhi`.`tb1`

### SET

### @1=5

### @2=’ee’

# at 290

#170327 21:10:55 server id 1313306 end_log_pos 321 CRC32 0x825a2f99 Xid = 78

COMMIT/*!*/;

# at 321 

#170327 21:19:25 server id 1313306 end_log_pos 422  

SET TIMESTAMP=1490620765/*!*/;

drop database xuanzhi

上面的黄色加粗的就是，一个是start-position，一个是stop-position

从上面可以看到，误操作前的pos点是321，那我们现在通过binlog来进行数据恢复：

[root@localhost mysql-5.6]# mysqlbinlog –start-position=329 –stop-position=321 localhost-bin.000001 localhost-bin.000002 |mysql -uroot -p123456 xuanzhi

–start-position是备份后记录下的pos点，

–stop-position是误操前的pos点，

如果多个binlog文件，那么start-position是第一个binlog文件的pos点，stop-position是最后一个binlog的pos点

通过事件的时间来恢复(不完全恢复)

我们可以通过参数–start-datetime 和 –stop-datetime指定恢复binlog日志的起止时间点，时间使用DATETIME格式。

比如在时间点2005-04-20 10:00:00我们删除掉一个库，我们要恢复该时间点前的所有日志

[root@localhost /]# mysqlbinlog –stop-datetime="2005-04-20 9:59:59" /usr/local/mysql/data/binlog.123456 | mysql -u root

我们可能几个小时后才发现该错误，后面又有一系列的增删查改等操作，我们还需要恢复后续的binlog，我们可以指定起始时间

组合

和基于时间点恢复类是,但是更加精确.因为同一时间点可能有多条SQL语句执行;

例:

#mysqlbinlog –start-date="2010-10-31 9:55:00"  –stop-date="2010-10-31 10:05:00" /usr/local/mysql/var/mysql-bin.000013 > /tmp/mysql_restore.sql

该命令将在/tmp/目录下创建小的文件,编辑它找到错误语句前后的位置号,例如前后位置号分别是368312 和 368315

(2)恢复了以前的备份文件后,输入

#mysqlbinlog –stop-position="368312" /usr/local/mysql/var/mysql-bin.000013 |mysql -uroot -p

#mysqlbinlog –start-position="368315" /usr/local/mysql/var/mysql-bin.000013 |mysql -uroot –p

总结：

一、在恢复全备数据之前必须将该binlog文件移出，否则恢复过程中，会继续写入语句到binlog，最终导致增量恢复数据部分变得比较混乱

二、做好数据文件及binlog的备份至关重要，但不是备份完就算了，要定期进行数据恢复测试或演练

三、恢复时建议对外停止更新，即禁止更新数据库