Mysql参考手册地址:https://dev.mysql.com/doc/refman/8.0/en/backup-and-recovery.html
官网中的例子:如下↓↓↓↓↓↓↓↓↓↓
例如,假设在2020年3月11日20:06:00左右执行了一条删除表的SQL语句。您可以执行时间点恢复,以将服务器恢复到表删除之前的状态。这些是实现该目标的一些示例步骤:
-
恢复在感兴趣的时间点之前创建的最后一个完整备份(在我们的示例中,将其命名为 ,即2020年3月11日的20:06:00)。完成后,记下您已还原服务器以供以后使用的二进制日志位置,然后重新启动服务器。
笔记虽然还原和服务器重新启动后,InnoDB还会显示恢复的最后一个二进制日志位置,但这不是获取还原结束日志位置的可靠方法,因为可能发生了DDL事件和非InnoDB更改在显示位置反映的时间之后。您的备份和还原工具应为您提供恢复的最后一个二进制日志位置:例如,如果您使用的是mysqlbinlog 对于该任务,请检查二进制日志重播的停止位置;如果使用MySQL Enterprise Backup,则最后一个二进制日志位置已保存在备份中。请参阅 时间点恢复。
-
查找与要还原数据库的时间点相对应的精确二进制日志事件位置。在我们的示例中,假设我们知道删除表的大致时间(),我们可以通过使用mysqlbinlog实用工具检查该时间前后的日志内容来找到日志位置。使用和 选项在周围指定较短的时间 ,然后在输出中查找事件。例如:
--start-datetime
and--stop-datetime
shell> mysqlbinlog --start-datetime="2020-03-11 20:05:00" \ --stop-datetime="2020-03-11 20:08:00" --verbose \ /var/lib/mysql/bin.123456 | grep -C 15 "DROP TABLE" /*!80014 SET @@session.original_server_version=80019*//*!*/; /*!80014 SET @@session.immediate_server_version=80019*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 232 #200311 20:06:20 server id 1 end_log_pos 355 CRC32 0x2fc1e5ea Query thread_id=16 exec_time=0 error_code=0 SET TIMESTAMP=1583971580/*!*/; SET @@session.pseudo_thread_id=16/*!*/; 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 utf8mb4 *//*!*/; SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; /*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/; DROP TABLE `pets`.`cats` /* generated by server */ /*!*/; # at 355 #200311 20:07:48 server id 1 end_log_pos 434 CRC32 0x123d65df Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no original_committed_timestamp=1583971668462467 immediate_commit_timestamp=1583971668462467 transaction_length=473 # original_commit_timestamp=1583971668462467 (2020-03-11 20:07:48.462467 EDT) # immediate_commit_timestamp=1583971668462467 (2020-03-11 20:07:48.462467 EDT) /*!80001 SET @@session.original_commit_timestamp=1583971668462467*//*!*/; /*!80014 SET @@session.original_server_version=80019*//*!*/; /*!80014 SET @@session.immediate_server_version=80019*//*!*/; SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 434 #200311 20:07:48 server id 1 end_log_pos 828 CRC32 0x57fac9ac Query thread_id=16 exec_time=0 error_code=0 Xid = 217 use `pets`/*!*/; SET TIMESTAMP=1583971668/*!*/; /*!80013 SET @@session.sql_require_primary_key=0*//*!*/; CREATE TABLE dogs
从mysqlbinlog的输出中,
DROP TABLE `pets`.`cats`
可以在该行# at 232
和之间的二进制日志段中找到该 语句# at 355
,这意味着该语句发生 在日志位置232之后,并且日志位于该DROP TABLE
语句之后的位置355 。笔记仅使用
--start-datetime
和--stop-datetime
选项来帮助您找到感兴趣的实际事件位置。不建议使用两个选项来指定要应用的二进制日志段的范围:使用这些选项时,丢失二进制日志事件的风险更高。使用--start-position
和--stop-position
代替。 -
将事件以二进制日志文件的形式应用于服务器,从您在步骤1中找到的日志位置开始(假设为155),结束于您感兴趣的时间点之前在步骤2中找到的位置(这是232):
shell> mysqlbinlog --start-position=155 --stop-position=232 /var/lib/mysql/bin.123456 \ | mysql -u root -p
该命令从起始位置恢复到停止位置之前的所有事务。因为mysqlbinlog的输出
SET TIMESTAMP
在记录每个SQL语句之前都包含 语句,所以恢复的数据和相关的MySQL日志反映了执行事务的原始时间。现在,您的数据库已还原到感兴趣的时间点,即在删除表之前。
tp
pets.cats
-
除了已完成的时间点恢复之外,如果您还想在感兴趣的时间点之后重新执行所有语句 ,请再次使用mysqlbinlog将所有事件应用于服务器。我们在第2步中注意到,在要跳过的语句之后,日志位于位置355;否则,日志将移至位置355。我们可以将其用于该 选项,以便包括该头寸之后的所有语句:
tp
--start-position
shell> mysqlbinlog --start-position=355 /var/lib/mysql/bin.123456 \ | mysql -u root -p
您的数据库已还原到二进制日志文件中记录的最新语句,但是跳过了所选事件。
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
mysql8版本 ,已开启log_bin, 在D:\software\mysql\mysql-8.0.22-winx64\bin> 下操作
1. 进入mysql安装路径:D:\software\mysql\mysql-8.0.22-winx64\data 目录下,你会看到很多命名类似binlog.000001 的文件,其中记录着sql操作信息.
2.查看data:执行mysqladmin -u root -p flush-logs 刷新日志文件,此时为0037
3.接下来,我使用 mysqldump 做一次完整备份:备份backup_test数据库下table_1表
mysqldump -u root -p backup_test table_1> D:\software\mysql\backup\table_3.sql
备份会生成sql文件,如下:记录着mysql等相关信息...
-- MySQL dump 10.13 Distrib 8.0.22, for Win64 (x86_64)
--
-- Host: localhost Database: backup_test
-- ------------------------------------------------------
-- Server version 8.0.22
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!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 */;
--
-- Table structure for table `table_1`
--
DROP TABLE IF EXISTS `table_1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `table_1` (
`id` int NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `table_1`
--
LOCK TABLES `table_1` WRITE;
/*!40000 ALTER TABLE `table_1` DISABLE KEYS */;
INSERT INTO `table_1` VALUES (1,'22'),(4,'44'),(5,'55'),(6,'66'),(7,'77'),(8,'88'),(9,'99'),(10,'10'),(11,'11'),(12,'12');
/*!40000 ALTER TABLE `table_1` ENABLE KEYS */;
UNLOCK TABLES;
/*!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 */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2021-03-31 21:30:58
4.对table_3进行更新操作...
1.新增 13 13
2.删除 11 11
3.修改12 222
5. mysqladmin -u root -p flush-logs刷新日志,会出现0038 ,0037被保存
6.使用下面命令查看binlog 0037, --verbose参数可以查看到sql语句
mysqlbinlog D:\software\mysql\mysql-8.0.22-winx64\data\binlog.000037 --verbose
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210331 21:26:38 server id 1 end_log_pos 125 CRC32 0xdac5e173 Start: binlog v 4, server v 8.0.22 created 210331 21:26:38
BINLOG '
jnhkYA8BAAAAeQAAAH0AAAAAAAQAOC4wLjIyAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigBc+HF2g==
'/*!*/;
# at 125
#210331 21:26:38 server id 1 end_log_pos 156 CRC32 0xf4925165 Previous-GTIDs
# [empty]
# at 156
#210331 21:33:15 server id 1 end_log_pos 235 CRC32 0x4a4a685f Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes original_committed_timestamp=1617197595184185 immediate_commit_timestamp=1617197595184185
transaction_length=302
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1617197595184185 (2021-03-31 21:33:15.184185 中国标准时间)
# immediate_commit_timestamp=1617197595184185 (2021-03-31 21:33:15.184185 中国标准时间)
/*!80001 SET @@session.original_commit_timestamp=1617197595184185*//*!*/;
/*!80014 SET @@session.original_server_version=80022*//*!*/;
/*!80014 SET @@session.immediate_server_version=80022*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 235
#210331 21:33:15 server id 1 end_log_pos 317 CRC32 0x1e697cd7 Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1617197595/*!*/;
SET @@session.pseudo_thread_id=10/*!*/;
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 utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@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 317
#210331 21:33:15 server id 1 end_log_pos 383 CRC32 0x8b83eb60 Table_map: `backup_test`.`table_1` mapped to number 93
# at 383
#210331 21:33:15 server id 1 end_log_pos 427 CRC32 0xc396eed9 Write_rows: table id 93 flags: STMT_END_F
BINLOG '
G3pkYBMBAAAAQgAAAH8BAAAAAF0AAAAAAAEAC2JhY2t1cF90ZXN0AAd0YWJsZV8xAAIDDwL9AgIB
AQACASFg64OL
G3pkYB4BAAAALAAAAKsBAAAAAF0AAAAAAAEAAgAC/wANAAAAAgAxM9nulsM=
'/*!*/;
### INSERT INTO `backup_test`.`table_1`
### SET
### @1=13
### @2='13'
# at 427
#210331 21:33:15 server id 1 end_log_pos 458 CRC32 0xa2b98fd4 Xid = 85
COMMIT/*!*/;
# at 458
#210331 21:33:22 server id 1 end_log_pos 537 CRC32 0xcab0df75 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes original_committed_timestamp=1617197602429942 immediate_commit_timestamp=1617197602429942
transaction_length=302
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1617197602429942 (2021-03-31 21:33:22.429942 中国标准时间)
# immediate_commit_timestamp=1617197602429942 (2021-03-31 21:33:22.429942 中国标准时间)
/*!80001 SET @@session.original_commit_timestamp=1617197602429942*//*!*/;
/*!80014 SET @@session.original_server_version=80022*//*!*/;
/*!80014 SET @@session.immediate_server_version=80022*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 537
#210331 21:33:22 server id 1 end_log_pos 619 CRC32 0x1f07a544 Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1617197602/*!*/;
BEGIN
/*!*/;
# at 619
#210331 21:33:22 server id 1 end_log_pos 685 CRC32 0x92da4a60 Table_map: `backup_test`.`table_1` mapped to number 93
# at 685
#210331 21:33:22 server id 1 end_log_pos 729 CRC32 0xd63bfc6e Delete_rows: table id 93 flags: STMT_END_F
BINLOG '
InpkYBMBAAAAQgAAAK0CAAAAAF0AAAAAAAEAC2JhY2t1cF90ZXN0AAd0YWJsZV8xAAIDDwL9AgIB
AQACASFgStqS
InpkYCABAAAALAAAANkCAAAAAF0AAAAAAAEAAgAC/wALAAAAAgAxMW78O9Y=
'/*!*/;
### DELETE FROM `backup_test`.`table_1`
### WHERE
### @1=11
### @2='11'
# at 729
#210331 21:33:22 server id 1 end_log_pos 760 CRC32 0x805d46e6 Xid = 88
COMMIT/*!*/;
# at 760
#210331 21:33:28 server id 1 end_log_pos 839 CRC32 0x7556a37c Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes original_committed_timestamp=1617197608416725 immediate_commit_timestamp=1617197608416725
transaction_length=322
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1617197608416725 (2021-03-31 21:33:28.416725 中国标准时间)
# immediate_commit_timestamp=1617197608416725 (2021-03-31 21:33:28.416725 中国标准时间)
/*!80001 SET @@session.original_commit_timestamp=1617197608416725*//*!*/;
/*!80014 SET @@session.original_server_version=80022*//*!*/;
/*!80014 SET @@session.immediate_server_version=80022*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 839
#210331 21:33:28 server id 1 end_log_pos 930 CRC32 0xf36aee76 Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1617197608/*!*/;
BEGIN
/*!*/;
# at 930
#210331 21:33:28 server id 1 end_log_pos 996 CRC32 0x89764960 Table_map: `backup_test`.`table_1` mapped to number 93
# at 996
#210331 21:33:28 server id 1 end_log_pos 1051 CRC32 0x928424a0 Update_rows: table id 93 flags: STMT_END_F
BINLOG '
KHpkYBMBAAAAQgAAAOQDAAAAAF0AAAAAAAEAC2JhY2t1cF90ZXN0AAd0YWJsZV8xAAIDDwL9AgIB
AQACASFgSXaJ
KHpkYB8BAAAANwAAABsEAAAAAF0AAAAAAAEAAgAC//8ADAAAAAIAMTIADAAAAAMAMjIyoCSEkg==
'/*!*/;
### UPDATE `backup_test`.`table_1`
### WHERE
### @1=12
### @2='12'
### SET
### @1=12
### @2='222'
# at 1051
#210331 21:33:28 server id 1 end_log_pos 1082 CRC32 0x9209a26e Xid = 91
COMMIT/*!*/;
# at 1082
#210331 21:34:46 server id 1 end_log_pos 1126 CRC32 0x99ca794a Rotate to binlog.000038 pos: 4
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*/;
7.上述记录了我们在完整备份之后,执行的所有操作,现在我们恢复数据. mysqldump 备份会保存为sql文件
执行完整备份:table_3.sql:
执行: mysqldump -u root -p backup_test < D:\software\mysql\backup\table_3.sql PS:插如不进去,手动执行的
重复binlog中的操作:
mysqlbinlog D:\software\mysql\mysql-8.0.22-winx64\data\binlog.000037 | mysql -u root -p
就恢复了.
可以选择跳过错误操作,执行前和之后的sql操作,像官网上说的,每一步操作早binlog中都有行数的记录...