mysql gtid

MySQL 通过 mysqlbinlog 恢复数据库

Posted by Sunday on 2019-03-12

通过mysqlbinlog 恢复误删或update错的表和库。

环境

Percona MySQL 5.7 GTID ROW

恢复前提

  1. MySQL 开启 binlog 二进日志
  2. 有最近（每晚或隔三或每周）全量备份数据库。

查看命令

1
2
3
mysqlbinlog -v --base64-output=decode-rows --skip-gtids=true \
--start-datetime='2019-03-12 3:00:00' --stop-datetime='2019-03-12 11:13:00' \
-d mall_amusic_shop mysql-bin.000125

常用命令参数

1
2
3
4
5
6
7
--start-datetime   #解析binlog开始时间
--stop-datetime    #解析binlog结束时间
--start-position   #解析pos开始位
--stop-position    #解析pos结束位
--base64-output=decode-rows  #解析row base-64,MySQL 5.1+,默认base64加密
-d          #指定数据库名
-r filename #指定输出文件或 > 管道输出

恢复步骤

1.导入昨晚3点全量备份

1
2
3
show master status;
create database mall_amusic;
mysql mall_amusic < /data/bak/mysql/mall_amusic_2018_03-12.sql

2.binlog 重放恢复指定时间段数据

1
2
3
mysqlbinlog --skip-gtids=true --start-datetime='2019-03-12 3:00:00' \
--stop-datetime='2019-03-12 11:13:00' -d mall_amusic mysql-bin.000125 > backup.sql
mysql < backup.sql

其他恢复命令

1
2
3
4
5
6
7
8
9
10
11
12
根据position从20-2000查找resource库相关记录，并输出到指定文件
mysqlbinlog --start-position="20" --stop-position="2000" --database=resource mysql-bin.000125 > result.sql 

根据position从20-2000查找resource库相关记录，并导入数据库
mysqlbinlog --start-position="20" --stop-position="2000" --database=resource mysql-bin.000125 | mysql -u root

根据时间为2018-03-20 零晨1点到11点从20-2000查找resource库相关记录，并导入数据库
mysqlbinlog --start-datetime="2018-03-20 1:00:00" --stop-datetime="2018-03-20 11:00:00" mysql-bin.000125 --result-file=result.sql

原中文编码为gb2312，转换utf8编码 
mysqlbinlog --set-charset=utf8 --database=resource mysql-bin.000125 --result-file=result_resource.sql 
mysqlbinlog --no-defaults mysql-bin.000014 mysql-bin.000015 --start-datetime='2018-03-20 1:00:00' > bin.sql

GTID 恢复不成功 解决

MySQL 5.7 使用了 GTID 以及 binlog 格式为 ROW，恢复不成功，也没有报错信息原因

1.不要使用 --base64-output=decode-rows 参数

1
mysqlbinlog -v --base64-output=decode-rows --start-position=XXX --stop-position=XXX mysql-bin.0000XX

这是解析 ROW 级别 binlog 日志时使用的命令，我们可以很直观的分析 binlog 日志。
但是我们想要恢复到数据库中的时候，则不能使用–base64-output=decode-rows 参数，否则是无法恢复到数据库的。

2.是否使用 --skip-gtids=true 参数

如果我们是要恢复数据到源数据库或者和源数据库有相同 GTID 信息的实例，那么就要使用该参数。如果不带该参数的话，是无法恢复成功的。因为包含的 GTID 已经在源数据库执行过了，根据 GTID 特性，一个 GTID 信息在一个数据库只能执行一次，所以不会恢复成功。

1
2
3
4
mysqlbinlog --skip-gtids=true  mysql-bin.000012 |mysql -uroot -p
或者
mysqlbinlog --skip-gtids=true  mysql-bin.000012 > backup.sql
mysql -uroot -p < backup.sql

如果是恢复到其他实例的数据库并且不包含源实例的 GTID 信息，那么可以不使用该参数，使用或者不使用都可以恢复成功

mysqlbinlog 恢复数据注意事项
mysqlbinglog 参数

CATALOG
    FEATURED TAGS
    mysql gtid
    FRIENDS