mysql-bin.index 恢复全部_通过binlog日志恢复表记录

使用binlog日志

1 使用binlog日志

1.1 问题

利用binlog恢复库表,要求如下:

启用binlog日志

创建db1库tb1表,插入3条记录

删除tb1表中刚插入的3条记录

使用mysqlbinlog恢复删除的3条记录

1.2 步骤

实现此案例需要按照如下步骤进行。

步骤一:启用binlog日志

1)调整/etc/my.cnf配置,并重启服务

[root@dbsvr1 ~]# vim /etc/my.cnf

[mysqld]

.. ..

log-bin-index=mysql-bin //启用二进制日志,并指定前缀

server_id=1

binlog_format=STATEMENT //在Mysql5.7中,binlog日志格式默认为ROW,但它不记录sql语句上下文相关信息。需要将binlog日志格式修改为STATEMENT

.. ..

[root@dbsvr1 ~]# systemctl restart mysqld.service

2)确认binlog日志文件

新启用binlog后,每次启动MySQl服务都会新生成一份日志文件:

[root@dbsvr1 ~]# ls /var/lib/mysql/mysql-bin.*

/var/lib/mysql/mysql-bin.000001 /var/lib/mysql/mysql-bin.index

其中mysql-bin.index文件记录了当前保持的二进制文件列表:

[root@dbsvr1 ~]# cat /var/lib/mysql/mysql-bin.index

./mysql-bin.000001

重启MySQL服务程序,或者执行SQL操作“FLUSH LOGS;”,会生成一份新的日志:

[root@dbsvr1 ~]# ls /var/lib/mysql/mysql-bin.*

/var/lib/mysql/mysql-bin.000001 /var/lib/mysql/mysql-bin.index

/var/lib/mysql/mysql-bin.000002

[root@dbsvr1 ~]# cat /var/lib/mysql/mysql-bin.index

./mysql-bin.000001

./mysql-bin.000002

步骤二:利用binlog日志重做数据库操作

1)执行数据库表添加操作

创建db1·库tb1表,表结构自定义:

mysql> CREATE DATABASE db1;

Query OK, 1 row affected (0.05 sec)

mysql> USE db1;

Database changed

mysql> CREATE TABLE tb1(

-> id int(4) NOT NULL,name varchar(24)

-> );

Query OK, 0 rows affected (0.28 sec)

插入3条表记录:

mysql> INSERT INTO tb1 VALUES

-> (1,'Jack'),

-> (2,'Kenthy'),

-> (3,'Bob');

Query OK, 3 rows affected (0.12 sec)

Records: 3 Duplicates: 0 Warnings: 0

确认插入的表记录数据:

mysql> SELECT * FROM tb1;

+----+--------+

| id | name |

+----+--------+

| 1 | Jack |

| 2 | Kenthy |

| 3 | Bob |

+----+--------+

3 rows in set (0.00 sec)

2)删除前一步添加的3条表记录

执行删除所有表记录操作:

mysql> DELETE FROM tb1;

Query OK, 3 rows affected (0.09 sec)

确认删除结果:

mysql> SELECT * FROM tb1;

Empty set (0.00 sec)

步骤三:通过binlog日志恢复表记录

binlog会记录所有的数据库、表更改操作,所以可在必要的时候重新执行以前做过的一部分数据操作,但对于启用binlog之前已经存在的库、表数据将不适用。

根据上述“恢复被删除的3条表记录”的需求,应通过mysqlbinlog工具查看相关日志文件,找到删除这些表记录的时间点,只要恢复此前的SQL操作(主要是插入那3条记录的操作)即可。

1)查看mysql-bin.000002日志内容

[root@dbsvr1 ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000002

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#170412 12:05:32 server id 1 end_log_pos 123 CRC32 0x6d8c069c Start: binlog v 4, server v 5.7.17-log created 170412 12:05:32 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

BINLOG '

jKftWA8BAAAAdwAAAHsAAAABAAQANS43LjE3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAACMp+1YEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

AZwGjG0=

'/*!*/;

# at 123

#170412 12:05:32 server id 1 end_log_pos 154 CRC32 0x17f50164 Previous-GTIDs

# [empty]

# at 154

#170412 12:05:59 server id 1 end_log_pos 219 CRC32 0x4ba5a976 Anonymous_GTID last_committed=0 sequence_number=1

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 219

#170412 12:05:59 server id 1 end_log_pos 310 CRC32 0x5b66ae13 Query thread_id=3 exec_time=0 error_code=0

SET TIMESTAMP=1491969959/*!*/;

SET @@session.pseudo_thread_id=3/*!*/;

SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

SET @@session.sql_mode=1436549152/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

CREATE DATABASE db1

/*!*/;

# at 310

#170412 12:06:23 server id 1 end_log_pos 375 CRC32 0x2967cc28 Anonymous_GTID last_committed=1 sequence_number=2

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 375

#170412 12:06:23 server id 1 end_log_pos 502 CRC32 0x5de09aae Query thread_id=3 exec_time=0 error_code=0

use `db1`/*!*/;

SET TIMESTAMP=1491969983/*!*/;

CREATE TABLE tb1(

id int(4) NOT NULL,name varchar(24)

)

/*!*/;

# at 502

#170412 12:06:55 server id 1 end_log_pos 567 CRC32 0x0b8cd418 Anonymous_GTID last_committed=2 sequence_number=3

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 567

#170412 12:06:55 server id 1 end_log_pos 644 CRC32 0x7e8f2fa0 Query thread_id=3 exec_time=0 error_code=0

SET TIMESTAMP=1491970015/*!*/;

BEGIN

/*!*/;

# at 644

#170412 12:06:55 server id 1 end_log_pos 772 CRC32 0x4e3f728e Query thread_id=3 exec_time=0 error_code=0 //插入表记录的起始时间点

SET TIMESTAMP=1491970015/*!*/;

INSERT INTO tb1 VALUES(1,'Jack'),(2,'Kenthy'), (3,'Bob')

/*!*/;

# at 772

#170412 12:06:55 server id 1 end_log_pos 803 CRC32 0x6138b21f Xid = 10

//确认事务的时间点

COMMIT/*!*/;

# at 803

#170412 12:07:24 server id 1 end_log_pos 868 CRC32 0xbef3f472 Anonymous_GTID last_committed=3 sequence_number=4

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 868

#170412 12:07:24 server id 1 end_log_pos 945 CRC32 0x5684e92c Query thread_id=3 exec_time=0 error_code=0

SET TIMESTAMP=1491970044/*!*/;

BEGIN

/*!*/;

# at 945

#170412 12:07:24 server id 1 end_log_pos 1032 CRC32 0x4c1c75fc Query thread_id=3 exec_time=0 error_code=0 //删除表记录的时间点

SET TIMESTAMP=1491970044/*!*/;

DELETE FROM tb1

/*!*/;

# at 1032

#170412 12:07:24 server id 1 end_log_pos 1063 CRC32 0xccf549b2 Xid = 12

COMMIT/*!*/;

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

2) 执行指定Pos节点范围内的sql命令恢复数据

根据上述日志分析,只要恢复从2014.01.12 20:12:14到2014.01.12 20:13:50之间的操作即可。可通过mysqlbinlog指定时间范围输出,结合管道交给msyql命令执行导入重做:

[root@dbsvr1 ~]# mysqlbinlog \

--start-datetime="2017-04-12 12:06:55" \

--stop-datetime="2017-04-12 12:07:23" \

/var/lib/mysql/mysql-bin.000002 | mysql -u root -p

Enter password: //验证口令

3)确认恢复结果

mysql> SELECT * FROM db1.tb1;

+----+--------+

| id | name |

+----+--------+

| 1 | Jack |

| 2 | Kenthy |

| 3 | Bob |

+----+--------+

3 rows in set (0.00 sec)

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值