目标
模拟MySQL数据被误删除数据，利用binlog日志文件回放回放数据。
步骤
1.准备数据
创建数据库
mysql> CREATE DATABASE TEST CHARACTER SET = utf8;
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| TEST |
| information_schema |
| mysql |
| performance_schema |
| shinuo |
| sys |
+--------------------+
6 rows in set (0.00 sec)
创建表
mysql> use TEST;
Database changed
mysql> CREATE TABLE t(id int, msg varchar(20));
Query OK, 0 rows affected (0.03 sec)
mysql> desc t;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| msg | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
insert种子数据
mysql> insert into t(id, msg) values(1, 'aaa');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t(id, msg) values(2, 'bbb');
Query OK, 1 row affected (0.01 sec)
mysql> select id, msg from t;
+------+------+
| id | msg |
+------+------+
| 1 | aaa |
| 2 | bbb |
+------+------+
2 rows in set (0.00 sec)
查看当前日志文件
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000007 | 156 | No |
| binlog.000008 | 1130 | No |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)
重置日志
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 156 | No |
+---------------+-----------+-----------+
1 row in set (0.00 sec)
2.备份数据
root@0a24a50d6986:/# mysqldump --skip-opt --quick --extended-insert=false --single-transaction --master-data=2 --databases TEST -u root -p > TEST.sql
Enter password:
root@0a24a50d6986:/# ls -lsh | grep .sql
4.0K -rw-r--r-- 1 root root 1.6K Dec 21 06:39 TEST.sql
mysqldump命令参数说明：
- –skip-opt：使用自定义配置；
- –quick：一次检索所有表；
- –extended-insert:false表示禁止使用一个insert语句插入多行的语法，否则反之；
- –single-transaction：在一个事务里面处理，对事务表使用这个配置；
- –master-data=2：CHANGE MASTER TO语句将写为SQL注释，默认是1；
- –databases：指定对库
- -u：用户
- -p：需要输入密码
TEST.sql
root@0a24a50d6986:/# cat TEST.sql
-- MySQL dump 10.13 Distrib 8.0.21, for Linux (x86_64)
--
-- Host: localhost Database: TEST
-- ------------------------------------------------------
-- Server version 8.0.21
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=156;
--
-- Current Database: `TEST`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `TEST` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `TEST`;
--
-- Table structure for table `t`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `t` (
`id` int DEFAULT NULL,
`msg` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t`
--
INSERT INTO `t` VALUES (1,'aaa');
INSERT INTO `t` VALUES (2,'bbb');
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2020-12-21 6:39:49
– CHANGE MASTER TO MASTER_LOG_FILE=‘binlog.000001’, MASTER_LOG_POS=156;
这里的156就是日志位置。
3.误删除模拟
mysql> insert into t values(3, 'ccc');
Query OK, 1 row affected (0.03 sec)
mysql> insert into t value(4, 'ddd');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t;
+------+------+
| id | msg |
+------+------+
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
| 4 | ddd |
+------+------+
4 rows in set (0.00 sec)
mysql> drop table t;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from t;
ERROR 1146 (42S02): Table 'TEST.t' doesn't exist
这里就是先插入数据，然后删除整个表，以模拟误删除操作。
4.查看删除日志位置
4.1切换日志
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 923 | No |
+---------------+-----------+-----------+
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 967 | No |
| binlog.000002 | 156 | No |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)
使用flush logs;来切换日志。
4.2查看误操作日志位置
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 967 | No |
| binlog.000002 | 156 | No |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)
mysql> show binlog events in 'binlog.000001';
+---------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| binlog.000001 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| binlog.000001 | 125 | Previous_gtids | 1 | 156 | |
| binlog.000001 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 235 | Query | 1 | 310 | BEGIN |
| binlog.000001 | 310 | Table_map | 1 | 363 | table_id: 165 (TEST.t) |
| binlog.000001 | 363 | Write_rows | 1 | 407 | table_id: 165 flags: STMT_END_F |
| binlog.000001 | 407 | Xid | 1 | 438 | COMMIT /* xid=155 */ |
| binlog.000001 | 438 | Anonymous_Gtid | 1 | 517 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 517 | Query | 1 | 592 | BEGIN |
| binlog.000001 | 592 | Table_map | 1 | 645 | table_id: 165 (TEST.t) |
| binlog.000001 | 645 | Write_rows | 1 | 689 | table_id: 165 flags: STMT_END_F |
| binlog.000001 | 689 | Xid | 1 | 720 | COMMIT /* xid=156 */ |
| binlog.000001 | 720 | Anonymous_Gtid | 1 | 797 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 797 | Query | 1 | 923 | use `TEST`; DROP TABLE `t` /* generated by server */ /* xid=158 */ |
| binlog.000001 | 923 | Rotate | 1 | 967 | binlog.000002;pos=4 |
+---------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
15 rows in set (0.00 sec)
797
这里的797就是误删除操作日志位置。
查询binlog日志位置：
root@0a24a50d6986:/# cat /etc/mysql/my.cnf
# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# The MySQL Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
# Custom config should go here
!includedir /etc/mysql/conf.d/
datadir = /var/lib/mysql
由这一行可知，数据文件位置在/var/lib/mysql目录。
查看binlog日志：
root@0a24a50d6986:/var/lib/mysql# cd /var/lib/mysql
root@0a24a50d6986:/var/lib/mysql# ls -lsh | grep binlog.000001
4.0K -rw-r----- 1 mysql mysql 967 Dec 21 06:58 binlog.000001
967
这里的文件大小就是binlog日志里面文件最后一条日志的结束位置。
5.查看备份数据日志位置
root@0a24a50d6986:/# grep "CHANGE MASTER" ./TEST.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=156;
156
这个就是备份数据日志位置。
6.新库还原备份数据
从这步开始一定要用新机器，新库，不可在生产环境。
从这步开始一定要用新机器，新库，不可在生产环境。
从这步开始一定要用新机器，新库，不可在生产环境。
6.1准备一个新的mysql机器
root@9211c2dcb8b1:/# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.21 MySQL Community Server - GPL
Copyright (c) 2000, 2020, 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，和需要恢复的生产mysql不是同一台机器
6.2在新mysql机器上面还原备份文件数据
将生产环境的备份sql文件，上传导测试环境的mysql中，进行还原。
root@9211c2dcb8b1:/# mysql -u root -p< TEST.sql
Enter password:
查看还原效果：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| TEST |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use TEST;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_TEST |
+----------------+
| t |
+----------------+
1 row in set (0.00 sec)
mysql> desc t
-> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| msg | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> select * from t;
+------+------+
| id | msg |
+------+------+
| 1 | aaa |
| 2 | bbb |
+------+------+
2 rows in set (0.00 sec)
查看还原的mysql的日志文件和日志：
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 3104223 | No |
| binlog.000002 | 1286 | No |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)
mysql> show binlog events in 'binlog.000002';
+---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------+
| binlog.000002 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| binlog.000002 | 125 | Previous_gtids | 1 | 156 | |
| binlog.000002 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 235 | Query | 1 | 442 | CREATE DATABASE /*!32312 IF NOT EXISTS*/ `TEST` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */ /* xid=11 */ |
| binlog.000002 | 442 | Anonymous_Gtid | 1 | 521 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 521 | Query | 1 | 722 | use `TEST`; CREATE TABLE `t` (
`id` int DEFAULT NULL,
`msg` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 /* xid=18 */ |
| binlog.000002 | 722 | Anonymous_Gtid | 1 | 801 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 801 | Query | 1 | 876 | BEGIN |
| binlog.000002 | 876 | Table_map | 1 | 929 | table_id: 85 (TEST.t) |
| binlog.000002 | 929 | Write_rows | 1 | 973 | table_id: 85 flags: STMT_END_F |
| binlog.000002 | 973 | Xid | 1 | 1004 | COMMIT /* xid=20 */ |
| binlog.000002 | 1004 | Anonymous_Gtid | 1 | 1083 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 1083 | Query | 1 | 1158 | BEGIN |
| binlog.000002 | 1158 | Table_map | 1 | 1211 | table_id: 85 (TEST.t) |
| binlog.000002 | 1211 | Write_rows | 1 | 1255 | table_id: 85 flags: STMT_END_F |
| binlog.000002 | 1255 | Xid | 1 | 1286 | COMMIT /* xid=21 */ |
+---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------+
16 rows in set (0.00 sec)
7.按binlog日志恢复数据
将生产环境mysql中的binlog日志文件：binlog.000001上传到新的测试环境mysql机器中。从4.2查看误操作日志位置中可只，需要恢复的日志位置是从4到797的这段时间的数据。
7.1上传binlog日志文件
root@9211c2dcb8b1:/# ls -ls | grep binlog.000001
4 -rw-r----- 1 501 dialout 967 Dec 21 06:58 binlog.000001
7.2查看待恢复待日志：
root@9211c2dcb8b1:/# mysqlbinlog --start-position=4 --stop-position=797 --base64-output=DECODE-ROWS -vv ./binlog.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#201221 1:53:54 server id 1 end_log_pos 125 CRC32 0xac4528b1 Start: binlog v 4, server v 8.0.21 created 201221 1:53:54 at startup
ROLLBACK/*!*/;
# at 125
#201221 1:53:54 server id 1 end_log_pos 156 CRC32 0xabb111ae Previous-GTIDs
# [empty]
# at 156
#201221 6:55:47 server id 1 end_log_pos 235 CRC32 0x62c88b8a Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes original_committed_timestamp=1608533747631946 immediate_commit_timestamp=1608533747631946 transaction_length=282
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1608533747631946 (2020-12-21 06:55:47.631946 UTC)
# immediate_commit_timestamp=1608533747631946 (2020-12-21 06:55:47.631946 UTC)
/*!80001 SET @@session.original_commit_timestamp=1608533747631946*//*!*/;
/*!80014 SET @@session.original_server_version=80021*//*!*/;
/*!80014 SET @@session.immediate_server_version=80021*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 235
#201221 6:55:47 server id 1 end_log_pos 310 CRC32 0x5de2e427 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1608533747/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 310
#201221 6:55:47 server id 1 end_log_pos 363 CRC32 0x55451c1e Table_map: `TEST`.`t` mapped to number 165
# at 363
#201221 6:55:47 server id 1 end_log_pos 407 CRC32 0x4abe71ef Write_rows: table id 165 flags: STMT_END_F
### INSERT INTO `TEST`.`t`
### SET
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
### @2='ccc' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 407
#201221 6:55:47 server id 1 end_log_pos 438 CRC32 0x21f4228a Xid = 155
COMMIT/*!*/;
# at 438
#201221 6:55:59 server id 1 end_log_pos 517 CRC32 0x7502c2af Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes original_committed_timestamp=1608533759854903 immediate_commit_timestamp=1608533759854903 transaction_length=282
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1608533759854903 (2020-12-21 06:55:59.854903 UTC)
# immediate_commit_timestamp=1608533759854903 (2020-12-21 06:55:59.854903 UTC)
/*!80001 SET @@session.original_commit_timestamp=1608533759854903*//*!*/;
/*!80014 SET @@session.original_server_version=80021*//*!*/;
/*!80014 SET @@session.immediate_server_version=80021*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 517
#201221 6:55:59 server id 1 end_log_pos 592 CRC32 0x40074772 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1608533759/*!*/;
BEGIN
/*!*/;
# at 592
#201221 6:55:59 server id 1 end_log_pos 645 CRC32 0xdb39f3ab Table_map: `TEST`.`t` mapped to number 165
# at 645
#201221 6:55:59 server id 1 end_log_pos 689 CRC32 0xcc0449a2 Write_rows: table id 165 flags: STMT_END_F
### INSERT INTO `TEST`.`t`
### SET
### @1=4 /* INT meta=0 nullable=1 is_null=0 */
### @2='ddd' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 689
#201221 6:55:59 server id 1 end_log_pos 720 CRC32 0x41273e0d Xid = 156
COMMIT/*!*/;
# at 720
#201221 6:56:13 server id 1 end_log_pos 797 CRC32 0x97330193 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no original_committed_timestamp=1608533773102193 immediate_commit_timestamp=1608533773102193 transaction_length=203
# original_commit_timestamp=1608533773102193 (2020-12-21 06:56:13.102193 UTC)
# immediate_commit_timestamp=1608533773102193 (2020-12-21 06:56:13.102193 UTC)
/*!80001 SET @@session.original_commit_timestamp=1608533773102193*//*!*/;
/*!80014 SET @@session.original_server_version=80021*//*!*/;
/*!80014 SET @@session.immediate_server_version=80021*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
BEGIN /*added by mysqlbinlog */ /*!*/;
ROLLBACK /* added by mysqlbinlog */ /*!*/;
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*/;
mysqlbinlog命令说明：
- –start-position=4:从第4位置开始
- –stop-position=797：到797位置结束
- –base64-output=DECODE-ROWS：使用base-64编码打印二进制日志条目，这个参数不是必要的；
- -vv：查询详情
7.3回放binlog日志文件
root@9211c2dcb8b1:/# mysqlbinlog --start-position=4 --stop-position=797 -vv ./binlog.000001 | mysql -u root -p
验证回放数据：
mysql> select * from t;
+------+------+
| id | msg |
+------+------+
| 1 | aaa |
| 2 | bbb |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from t;
+------+------+
| id | msg |
+------+------+
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
| 4 | ddd |
+------+------+
4 rows in set (0.00 sec)
查看一下回放日志：
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 3104223 | No |
| binlog.000002 | 1864 | No |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)
mysql> show binlog events in 'binlog.000002';
+---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------+
| binlog.000002 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| binlog.000002 | 125 | Previous_gtids | 1 | 156 | |
| binlog.000002 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 235 | Query | 1 | 442 | CREATE DATABASE /*!32312 IF NOT EXISTS*/ `TEST` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */ /* xid=11 */ |
| binlog.000002 | 442 | Anonymous_Gtid | 1 | 521 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 521 | Query | 1 | 722 | use `TEST`; CREATE TABLE `t` (
`id` int DEFAULT NULL,
`msg` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 /* xid=18 */ |
| binlog.000002 | 722 | Anonymous_Gtid | 1 | 801 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 801 | Query | 1 | 876 | BEGIN |
| binlog.000002 | 876 | Table_map | 1 | 929 | table_id: 85 (TEST.t) |
| binlog.000002 | 929 | Write_rows | 1 | 973 | table_id: 85 flags: STMT_END_F |
| binlog.000002 | 973 | Xid | 1 | 1004 | COMMIT /* xid=20 */ |
| binlog.000002 | 1004 | Anonymous_Gtid | 1 | 1083 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 1083 | Query | 1 | 1158 | BEGIN |
| binlog.000002 | 1158 | Table_map | 1 | 1211 | table_id: 85 (TEST.t) |
| binlog.000002 | 1211 | Write_rows | 1 | 1255 | table_id: 85 flags: STMT_END_F |
| binlog.000002 | 1255 | Xid | 1 | 1286 | COMMIT /* xid=21 */ |
| binlog.000002 | 1286 | Anonymous_Gtid | 1 | 1372 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 1372 | Query | 1 | 1447 | BEGIN |
| binlog.000002 | 1447 | Table_map | 1 | 1500 | table_id: 85 (TEST.t) |
| binlog.000002 | 1500 | Write_rows | 1 | 1544 | table_id: 85 flags: STMT_END_F |
| binlog.000002 | 1544 | Xid | 1 | 1575 | COMMIT /* xid=68 */ |
| binlog.000002 | 1575 | Anonymous_Gtid | 1 | 1661 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 1661 | Query | 1 | 1736 | BEGIN |
| binlog.000002 | 1736 | Table_map | 1 | 1789 | table_id: 85 (TEST.t) |
| binlog.000002 | 1789 | Write_rows | 1 | 1833 | table_id: 85 flags: STMT_END_F |
| binlog.000002 | 1833 | Xid | 1 | 1864 | COMMIT /* xid=78 */ |
+---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------+
26 rows in set (0.00 sec)
总结
主要就是mysqldump和mysqlbinlog的使用，到这里就完成来mysql的binlog日志回放恢复。
1276
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
