Loading

mysqlbinlog恢复bin-log数据

mysqlbinlog恢复bin-log数据

 

Binlog日志即binary log,是二进制日志文件,有两个作用,一个是增量备份,另一个是主从复制,即从节点同步主节点数据时获取的即是bin-log,也可以通过bin-log日志来进行本机数据恢复。

 

1、可以登录mysql客户端查看bin-log有没有开启:

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.02 sec)

log_bin为ON时,binlog即开启,为OFF,未开启。以上已开启

 

2、开启mysql binlog日志:

进入mysql主配置文件(vim /etc/my.cnf),在mysqld模块下增加以下参数

server-id = 1(单个节点id)
log-bin= /var/lib/mysql/mysql-bin(位置一般和mysql库文件所在位置一样)
expire_logs_days = 10(表示此日志保存时间为10天;在mysql 8的版本中已经不建议使用此参数)

 

3、binlog日志包括两类文件;第一个是二进制索引文件(后缀为.index),第二个为日志文件(后缀名称为.0000*),记录所有的DML(除SELECT)及DDL语句事件

[root@TestCentos7 ~]# ls /var/lib/mysql/
mysql-bin.000001 mysql-bin.000002 mysql-bin.index  mysql.sock

 

4、查看binlog日志列表:show master logs;

mysql> show master logs; 
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |      2412 | No        |
| mysql-bin.000002 |       445 | No        |
+------------------+-----------+-----------+
2 rows in set (0.00 sec)

 

5、查看最后一个binlog日志的编号名称及其最后一个操作事件pos结束点的值:show master status;

mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000002 |      445 |              |                  | c9fba9e2-db3b-11eb-81d4-000c298d8da1:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

注意:如果mysql没有开启GTID的话,不会有pos结束点的值,可以使用 show variables like '%gtid_mode%'; 检查是否开启。

 

6、flush logs 刷新日志,生成一个新编号的binlog文件:

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

mysql> show master logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |      2412 | No        |
| mysql-bin.000002 |       492 | No        |
| mysql-bin.000003 |       195 | No        |
+------------------+-----------+-----------+
3 rows in set (0.00 sec)

每当mysqld服务重启后,会自动刷新binlog文件,mysqldump数据时也可以加-F选项来刷新binlog日志文件

 

7、清空所有binlog日志文件用 reset master;

 

8、查看binlog文件内容,使用查看工具mysqlbinlog来查看(vi/vim/cat等都打不开)

# mysqlbinlog mysql-bin.000002 | more

BEGIN
/*!*/;
# at 309
#201123 14:16:09 server id 100  end_log_pos 369 CRC32 0x73d92352     Table_map: `vfan`.`student` mapped to number 86
# at 369
#201123 14:16:09 server id 100  end_log_pos 414 CRC32 0x0e056855     Write_rows: table id 86 flags: STMT_END_F

BINLOG '
qVO7XxNkAAAAPAAAAHEBAAAAAFYAAAAAAAEABHZmYW4AB3N0dWRlbnQAAwMPAQI8AAABAYACASFS
I9lz
qVO7Xx5kAAAALQAAAJ4BAAAAAFYAAAAAAAEAAgAD/wAFAAAAA0hlZxNVaAUO
'/*!*/;
# at 414
#201123 14:16:09 server id 100  end_log_pos 445 CRC32 0xfaf4f9c6     Xid = 10
COMMIT/*!*/;
# at 445
#201123 14:19:20 server id 100  end_log_pos 492 CRC32 0x95a77234     Rotate to mysql-bin.000003  pos: 4

14:16:09  —— 时间

server id 100 —— server id(my.cnf中设置的id)

end_log_pos 492  —— 结束的Pos点

 

9、上边的方法内容比较多,也不太容易观察,以下命令更方便查看:show binlog events in 'mysql-bin.000002';

mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000002 |   4 | Format_desc    |       100 |         124 | Server ver: 8.0.16, Binlog ver: 4                                 |
| mysql-bin.000002 | 124 | Previous_gtids |       100 |         155 |                                                                   |
| mysql-bin.000002 | 155 | Gtid           |       100 |         234 | SET @@SESSION.GTID_NEXT= 'c9fba9e2-db3b-11eb-81d4-000c298d8da1:1' |
| mysql-bin.000002 | 234 | Query          |       100 |         309 | BEGIN                                                             |
| mysql-bin.000002 | 309 | Table_map      |       100 |         369 | table_id: 86 (vfan.student)                                       |
| mysql-bin.000002 | 369 | Write_rows     |       100 |         414 | table_id: 86 flags: STMT_END_F                                    |
| mysql-bin.000002 | 414 | Xid            |       100 |         445 | COMMIT /* xid=10 */                                               |
| mysql-bin.000002 | 445 | Rotate         |       100 |         492 | mysql-bin.000003;pos=4                                            |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
8 rows in set (0.00 sec)

 

10、指定查询

1)从Pos点414开始查询,如下:

mysql> show binlog events in 'mysql-bin.000002' from 414;
+------------------+-----+------------+-----------+-------------+------------------------+
| Log_name         | Pos | Event_type | Server_id | End_log_pos | Info                   |
+------------------+-----+------------+-----------+-------------+------------------------+
| mysql-bin.000002 | 414 | Xid        |       100 |         445 | COMMIT /* xid=10 */    |
| mysql-bin.000002 | 445 | Rotate     |       100 |         492 | mysql-bin.000003;pos=4 |
+------------------+-----+------------+-----------+-------------+------------------------+
2 rows in set (0.00 sec)

 

2)从Pos点155开始查询,跳过中间两行,查询后4条数据

mysql> show binlog events in 'mysql-bin.000002' from 155 limit 2,4;
+------------------+-----+------------+-----------+-------------+--------------------------------+
| Log_name         | Pos | Event_type | Server_id | End_log_pos | Info                           |
+------------------+-----+------------+-----------+-------------+--------------------------------+
| mysql-bin.000002 | 309 | Table_map  |       100 |         369 | table_id: 86 (vfan.student)    |
| mysql-bin.000002 | 369 | Write_rows |       100 |         414 | table_id: 86 flags: STMT_END_F |
| mysql-bin.000002 | 414 | Xid        |       100 |         445 | COMMIT /* xid=10 */            |
| mysql-bin.000002 | 445 | Rotate     |       100 |         492 | mysql-bin.000003;pos=4         |
+------------------+-----+------------+-----------+-------------+--------------------------------+

 

3)从Pos点155开始查询后四行

mysql> show binlog events in 'mysql-bin.000002' from 155 limit 4;
+------------------+-----+------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos | Event_type | Server_id | End_log_pos | Info                                                              |
+------------------+-----+------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000002 | 155 | Gtid       |       100 |         234 | SET @@SESSION.GTID_NEXT= 'c9fba9e2-db3b-11eb-81d4-000c298d8da1:1' |
| mysql-bin.000002 | 234 | Query      |       100 |         309 | BEGIN                                                             |
| mysql-bin.000002 | 309 | Table_map  |       100 |         369 | table_id: 86 (vfan.student)                                       |
| mysql-bin.000002 | 369 | Write_rows |       100 |         414 | table_id: 86 flags: STMT_END_F                                    |
+------------------+-----+------------+-----------+-------------+-------------------------------------------------------------------+
4 rows in set (0.00 sec)

 

11、利用bin-log日志恢复mysql数据,现在有一张表student如下:

mysql> select * from student;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | Tony |  18 |
|  2 | Any  |  17 |
|  3 | Goy  |  20 |
|  4 | Baly |  18 |
|  5 | Heg  |  19 |
+----+------+-----+
5 rows in set (0.00 sec)

 

1)先模拟全量备份,进行mysqldump备份

[root@TestCentos7 data]# mysqldump -uroot -proot1 vfan > dump1.sql

 

2)由于业务需要,需要对此表进行插入(6 和 7)

mysql> select * from student;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | Tony |  18 |
|  2 | Any  |  17 |
|  3 | Goy  |  20 |
|  4 | Baly |  18 |
|  5 | Heg  |  19 |
|  6 | Mom  |  20 |
|  7 | Lei  |  21 |
+----+------+-----+
7 rows in set (0.00 sec)

 

3)由于业务需要,要将id=3的name更改为Giil

mysql> UPDATE student set name='Giil' where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | Tony |  18 |
|  2 | Any  |  17 |
|  3 | Giil |  20 |
|  4 | Baly |  18 |
|  5 | Heg  |  19 |
|  6 | Mom  |  20 |
|  7 | Lei  |  21 |
+----+------+-----+
7 rows in set (0.01 sec)

 

4)操作失误,勿将 vfan 数据库删除

mysql> drop database vfan;
Query OK, 1 row affected (0.10 sec)

 

5)先进行全量恢复一下:

mysql> CREATE DATABASE vfan;
mysql> use vfan
Database changed
mysql> source /data/dump1.sql
mysql> select * from student;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | Tony |  18 |
|  2 | Any  |  17 |
|  3 | Goy  |  20 |
|  4 | Baly |  18 |
|  5 | Heg  |  19 |
+----+------+-----+
5 rows in set (0.00 sec)

 

然后通过binlog进行增量恢复,先flush logs;一下,防止再有操作写进此binlog日志中,会增加恢复难度:

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

mysql> show master logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |      2412 | No        |
| mysql-bin.000002 |       492 | No        |
| mysql-bin.000003 |      1219 | No        |
| mysql-bin.000004 |       195 | No        |
+------------------+-----------+-----------+
4 rows in set (0.00 sec)

 

查看mysql-bin.000003的具体信息,如下:


INSERT操作:事务的起始Pos点为274,事务提交结束点为495

UPDATE操作:事务的起始Pos点为574,事务提交结束点为886

 

6)开始恢复INSERT的数据,执行:

mysqlbinlog --start-position=274 --stop-position=495 --database=vfan /var/lib/mysql/mysql-bin.000003 | mysql -uroot -proot1 -v vfan

此命令意思为mysqlbinlog读取binlog日志内容并通过管道符传给mysql命令,-v表示执行此mysql命令

 

我执行后有一个报错,大概是GTID_MODE的参数错误:

# mysqlbinlog --start-position=274 --stop-position=495 --database=vfan /var/lib/mysql/mysql-bin.000003 | /mysql -uroot -proot1 -v vfan
ERROR 1782 (HY000) at line 23: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.

 

只需将gtid_mode设置为OFF_PERMISSIVE即可

mysql> set global gtid_mode='on_permissive';
Query OK, 0 rows affected (0.01 sec)

mysql> set global gtid_mode='off_permissive';
Query OK, 0 rows affected (0.12 sec)

mysql> show variables like '%gtid_mode%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| gtid_mode     | OFF_PERMISSIVE |
+---------------+----------------+
1 row in set (0.00 sec)

 

再重新执行以上mysqlbinlog命令,执行成功!

再次查看student表内数据:

mysql> select * from student ;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | Tony |  18 |
|  2 | Any  |  17 |
|  3 | Goy  |  20 |
|  4 | Baly |  18 |
|  5 | Heg  |  19 |
|  6 | Mom  |  20 |
|  7 | Lei  |  21 |
+----+------+-----+
7 rows in set (0.00 sec)

INSERT操作的数据已经恢复

 

7)开始恢复UPDATE的数据

/usr/local/mysql/bin/mysqlbinlog --start-position=574 --stop-position=886 --database=vfan /var/lib/mysql/mysql-bin.000003 | /usr/local/mysql/bin/mysql -uroot -proot1 -v vfan

 

再次查看,UPDATE的数据也已经恢复:

mysql> select * from student ;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | Tony |  18 |
|  2 | Any  |  17 |
|  3 | Giil |  20 |
|  4 | Baly |  18 |
|  5 | Heg  |  19 |
|  6 | Mom  |  20 |
|  7 | Lei  |  21 |
+----+------+-----+
7 rows in set (0.00 sec)

 

12、还有另一种可以通过时间来进行恢复:

还是将数据库误删,然后全量恢复完毕,准备增量恢复。

mysql> select * from student;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | Tony |  18 |
|  2 | Any  |  17 |
|  3 | Goy  |  20 |
|  4 | Baly |  18 |
|  5 | Heg  |  19 |
+----+------+-----+
5 rows in set (0.00 sec)

 

1)先使用mysqlbinlog查看操作时间

mysqlbinlog mysql-bin.000003 | more

 

2)开始恢复,直接将INSERT和UPDATE数据一并恢复

mysqlbinlog --start-datetime="2020-11-23 14:44:18" --stop-datetime="2020-11-23 14:46:28" --database=vfan /var/lib/mysql/mysql-bin.000003 | mysql -uroot -proot1 -v vfan
posted @ 2020-12-10 22:45  塔克拉玛攻城狮  阅读(486)  评论(0编辑  收藏  举报