mysqlbinlog 恢复 忽略报错

【常用形式】

【1】在test库redo某个binlog中的文件
mysqlbinlog binlog.000011 | mysql -uroot -p123456 test

【2】时间点恢复
mysqlbinlog --stop-datetime='2019-04-24 11:12:00' binlog.000011 | mysql -uroot -p123456 test

【3】时间点重做
mysqlbinlog --start-datetime='2019-04-24 11:30:00' --stop-datetime='2019-04-24 18:10' binlog.000011 | mysql -uroot -p123456 test

【4】读取多个binlog
mysqlbinlog --start-datetime='2016-02-25 00:00:00' --stop-datetime='2016-03-15 17:00:00' mysql-bin.000023 mysql-bin.000024 >a.

 

【1】全库+binlog增量 

# 依据全备文件，定位起始binlog日志文件和pos
cd /tmp/
head -30 Full_Backup.sql|grep CHANGE
# -- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=268403;

# 抽取全备点之后的binlog日志
mysqlbinlog binlog.000002 --start-position=268403 >Full_binlog.sql
# 如果有其他binlog日志，则追加即可：
# mysqlbinlog binlog.000003 >>Full_binlog.sql

# 全库恢复
mysql -uroot -pvincent < Full_Backup.sql

# 追加binlog，增量恢复
mysql -uroot -pvincent < Full_binlog.sql

　　# 也可以如下，这样一个一个应用日志
   # mysqlbinlog --stop-position=567 binlog.000002 |mysql -uroot -pvincent

# 测试：
mysql -uroot -pvincent
select max(id) from vincent1.test1;
select max(id) from vincent2.test1;
select User,Host from mysql.user where user!='root';
exit
# 3867
# 3757
# vincent1
# vincent2
# 因为是全库恢复，因此创建的账号和俩业务库全部恢复
# 业务库中的表数据和源端保持一致，增量恢复成功

【2】单库恢复

# 依据单备文件，定位起始binlog日志文件和pos
cd /tmp/
head -30 Vincent1_Backup.sql|grep CHANGE
# -- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=362938;

# 抽取全备点之后的binlog日志，使用参数-d只抽取vincent1库相关操作
mysqlbinlog binlog.000002 -d vincent1 --start-position=362938 >Vincent1_binlog.sql

# 如果有其他binlog日志，则追加即可：
# mysqlbinlog -d vincent1 binlog.000003 >>Vincent1_binlog.sql

# 删除已经恢复的数据，做单库恢复
mysql -uroot -pvincent -e "drop database vincent1;"
mysql -uroot -pvincent -e "drop database vincent2;"
mysql -uroot -pvincent -e "drop user vincent1;"
mysql -uroot -pvincent -e "drop user vincent2;"
mysql -uroot -pvincent -e "flush privileges;"
# 因为单库备份脚本没有使用-B参数，无建库语句，因此要手动建库，库名必须和故障时一样啊，不然binlog中的日志无法对应找到库名
mysql -uroot -pvincent -e "create database vincent1;"
mysql -uroot -pvincent -o vincent1 < Vincent1_Backup.sql

# 追加binlog，增量恢复
mysql -uroot -pvincent < Vincent1_binlog.sql

# 测试：
mysql -uroot -pvincent
select max(id) from vincent1.test1;
select max(id) from vincent2.test1;
select User,Host from mysql.user where user!='root';
exit
# 3867
# 表不存在
# 无root账号之外的账号
# 因为是单库恢复，因此创建的业务账号和第二个业务库全部没有恢复
# 业务库1中的表数据和源端保持一致，增量恢复成功

【3】案例

场景：不小心误删除某张表

解决方法：在另外一台机器，恢复全库+日志，然后导出删除的表，再插入会生产库。

案例演示：

案例描述：test1数据库，test1表
　　1点全备，3点更新，4点删表。如何恢复？

模拟案例步骤如下：

（1）查看全备时的数据

　　 

 

（2）1点：进行全备

#切换到linux环境（mysqldump参考）

#备份所有数据库：mysqldump -F -R -uroot -p -A >/tmp/all_db.bak     # -F:刷新binlog日志，-R：存储过程及触发器。 -A:所有数据库

mysqldump -F -R -uroot -p  test1 >/tmp/test1_db.bak

（3）3点：更新表

update test1 set name = '赵更新' where id in (1,2);　

commit;

（4）4点：删除表

drop table test1;

（5）查看恢复时间点/恢复位置

a)查看当前binlog日志

show master status;

　　　　

b)查阅binlog日志  #切换到linux环境下

　　找到删除之前的点，就是567（时间点的话就是：2019-03-12 00:00:28）　　

　　　　

（6）在新机器上进行还原

【一个错误的假设】还原了test2，基于test1的全备，等下就用这个测试吧

#想利用全备恢复到本实例一个新的数据库名称，发现不行，binlog重做的时候，使用的还是原本的数据库名称。
#如果是上面的全库备份，可以使用-o来指定所需要恢复数据库  mysql -uroot -p -o test2 </tmp/test1_db.bak
 mysql -uroot -p test2 </tmp/test1_db.bak
#如果是使用的全备可以用--database=test1，来指定只重做test1库的日志 mysqlbinlog --stop-position=567 --database=test1 binlog.000002 |mysql -uroot -p test2

mysqlbinlog --stop-position=567 binlog.000002 |mysql -uroot -p test2

　　

【实际可行策略】 

我这里就没有其他机器，我删掉库重建演示吧，如果有其他实例或者机器，那就不用删除了。

6.1)删除重建

   drop database test1;

   create database test1 default charset utf8; #一定要和原数据库一样的字符编码

6.2)还原数据

   mysql -uroot -p test1 </tmp/test1_db.bak

6.3)重做binlog日志

   mysqlbinlog --stop-position=567 binlog.000002 |mysql -uroot -p test1

6.4)核验--成功

　　

 

6.5)备份表

　　mysqldump -uroot -p test1 test1 >/tmp/test1_table.bak

6.6)还原表到test2库看看

　　mysql -uroot -p test2 </tmp/test1_table.bak