mysql根据binlog恢复数据

avatar 2020年6月11日18:04:06 评论 789 次浏览

binlog日志的用处不仅仅是收集mysql的操作，在日志恢复的时候也能起到重要的作用，不管事主从复制，主主集群都是使用binlog的纪录在另一台服务器上学习并在本机执行。但是这里说的是如何跟进binlog日志恢复数据，前面章节已经说过mysql的日志开启参考：https://www.wulaoer.org/?p=1267 还有binlog日志的查看参考：https://www.wulaoer.org/?p=1278 我们能够开启日志，并且看到日志中mysql的纪录，但是这些纪录如何使用？不急请往下看。

我们看mysql的binlog日志，每操作的纪录都在binlog中，我这里就创建一个table，然后刷新一下binlog，看看。

[root@www.wulaoer.org ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database wulaoer;      #创建了一个库
Query OK, 1 row affected (0.02 sec)

mysql> use wulaoer
Database changed
mysql> CREATE TABLE wolf
    ->  (
    ->  id INT(11),
    ->  name VARCHAR(25),
    ->  deptId INT(11),
    ->  salary FLOAT
    ->  );
Query OK, 0 rows affected (0.03 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wulaoer            |
+--------------------+
5 rows in set (0.00 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000013 |       177 |
| mysql-bin.000014 |       177 |
| mysql-bin.000015 |       177 |
| mysql-bin.000016 |       177 |
| mysql-bin.000017 |       201 |
| mysql-bin.000018 |       177 |
| mysql-bin.000019 |       177 |
| mysql-bin.000020 |       201 |
| mysql-bin.000021 |       201 |
| mysql-bin.000022 |       369 |
| mysql-bin.000023 |       333 |
| mysql-bin.000024 |       177 |
+------------------+-----------+
10 rows in set (0.00 sec)

mysql> flush logs;                            ＃刷新一下binlog
Query OK, 0 rows affected (0.03 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000013 |       177 |
| mysql-bin.000014 |       177 |
| mysql-bin.000015 |       177 |
| mysql-bin.000016 |       177 |
| mysql-bin.000017 |       201 |
| mysql-bin.000018 |       177 |
| mysql-bin.000019 |       177 |
| mysql-bin.000020 |       201 |
| mysql-bin.000021 |       201 |
| mysql-bin.000022 |       369 |
| mysql-bin.000023 |       333 |
| mysql-bin.000024 |       177 |
| mysql-bin.000025 |       553 |
+------------------+-----------+
13 rows in set (0.00 sec)

mysql> drop database wulaoer;
Query OK, 0 rows affected (0.02 sec)

刷新一下binlog，会把创建库的语句纪录到mysql-bin.000025文件里mysql-bin.000025文件里创建了一个名称为wulaoer的库，并在库中创建了一个表叫wolf,下面我们看一下mysql-bin.000025文件中是否有创建的语句，并删掉创建的表。

mysql> show binlog events in 'mysql-bin.000025';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                                  |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------+
| mysql-bin.000025 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.26-log, Binlog ver: 4                                                                 |
| mysql-bin.000025 | 123 | Previous_gtids |         1 |         154 |                                                                                                       |
| mysql-bin.000025 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                  |
| mysql-bin.000025 | 219 | Query          |         1 |         322 | create database wulaoer                                                                               |
| mysql-bin.000025 | 322 | Anonymous_Gtid |         1 |         387 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                  |
| mysql-bin.000025 | 387 | Query          |         1 |         553 | use `wulaoer`; CREATE TABLE wolf
 (
 id INT(11),
 name VARCHAR(25),
 deptId INT(11),
 salary FLOAT
 ) |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------+
6 rows in set (0.01 sec)

mysql> drop table wolf;
Query OK, 0 rows affected (0.02 sec)

根据pod恢复

已经删除wulaoer库中的表，下面进行恢复，根据上日志的内容，我们可以看到切换到库并创建表的语句是在最后一行，pos为387开始，End_log_pos为553结束，知道开始和结束，下面进行恢复。

[root@www.wulaoer.org ~]# /usr/local/mysql/bin/mysqlbinlog --start-position=387 --stop-position=553 --database=wulaoer /usr/local/mysql/var/mysql-bin.000025 | /usr/local/mysql/bin/mysql -uroot -proot -v wulaoer
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/
--------------

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

--------------
ROLLBACK
--------------

--------------
BINLOG '
pXLYXg8BAAAAdwAAAHsAAAABAAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAClctheEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AcnXy3c=
'
--------------

--------------
SET TIMESTAMP=1591243564
--------------

--------------
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
--------------

--------------
/*!*/
--------------

--------------
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45
--------------

--------------
SET @@session.lc_time_names=0
--------------

--------------
SET @@session.collation_database=DEFAULT
--------------

--------------
CREATE TABLE wolf
 (
 id INT(11),
 name VARCHAR(25),
 deptId INT(11),
 salary FLOAT
 )
--------------

--------------
SET @@SESSION.GTID_NEXT= 'AUTOMATIC'
--------------

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

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

恢复完成，并没有报错，下面我们进行验证一下，是否在wulaoer库下有一个wolf表存在。

[root@www.wulaoer.org ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use wulaoer
Database changed
mysql> show tables;
+-------------------+
| Tables_in_wulaoer |
+-------------------+
| wolf              |
+-------------------+
1 row in set (0.00 sec)

验证通过，说明我们的操作是正确的，还有一种是根据日志中纪录的时间来恢复，下面我删除了wolf表，这里就不写过程了，我们看一下表内容。

[root@www.wulaoer.org ~]# /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/var/mysql-bin.000025
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200604 12:03:49 server id 1  end_log_pos 123 CRC32 0x77cbd7c9  Start: binlog v 4, server v 5.7.26-log created 200604 12:03:49 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
pXLYXg8BAAAAdwAAAHsAAAABAAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAClctheEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AcnXy3c=
'/*!*/;
.............................................
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database wulaoer
/*!*/;
# at 322
#200604 12:06:04 server id 1  end_log_pos 387 CRC32 0x44dab486  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 387
#200604 12:06:04 server id 1  end_log_pos 553 CRC32 0x73b77b8f  Query   thread_id=3     exec_time=0     error_code=0
use `wulaoer`/*!*/;
SET TIMESTAMP=1591243564/*!*/;
CREATE TABLE wolf
 (
 id INT(11),
 name VARCHAR(25),
 deptId INT(11),
 salary FLOAT
 )
/*!*/;
# at 553
#200604 12:07:41 server id 1  end_log_pos 618 CRC32 0x334f8c96  Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 618
#200604 12:07:41 server id 1  end_log_pos 719 CRC32 0x79de55ed  Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1591243661/*!*/;
.....................................................
[root@www.wulaoer.org ~]#

根据时间恢复

创建库后的时间是200604 12:06:04，切换后，创建表的结束时间200604 12:07:41，所以我们使用时间恢复就需要用到--start-datetime和--stop-datetime参数。

[root@www.wulaoer.org ~]# /usr/local/mysql/bin/mysqlbinlog --start-datetime="2020-06-04 12:06:04" --stop-datetime="2020-06-04 12:07:41" --database=wulaoer /usr/local/mysql/var/mysql-bin.000025 | /usr/local/mysql/bin/mysql -uroot -proot -v 
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/
--------------

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

--------------
ROLLBACK
--------------

--------------
BINLOG '
pXLYXg8BAAAAdwAAAHsAAAABAAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAClctheEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AcnXy3c=
'
--------------

--------------
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
--------------

--------------
SET TIMESTAMP=1591243564
--------------

--------------
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
--------------

--------------
/*!*/
--------------

--------------
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45
--------------

--------------
SET @@session.lc_time_names=0
--------------

--------------
SET @@session.collation_database=DEFAULT
--------------

--------------
CREATE TABLE wolf
 (
 id INT(11),
 name VARCHAR(25),
 deptId INT(11),
 salary FLOAT
 )
--------------

--------------
SET @@SESSION.GTID_NEXT= 'AUTOMATIC'
--------------

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

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

[root@www.wulaoer.org ~]#

这里就不验证了，表是存在的。如果遇到数据库根据日志恢复都可以根据以上两种方法进行恢复。

avatar

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: