MySQL日志管理、备份与恢复
目录
MySQL日志管理、备份与恢复
一、MySQL日志管理
1. MySQL日志路径
2. 设置、修改日志路径
3. 查询日志功能是否开启
二、MySQL备份与恢复
1. 数据备份的重要性
2. 数据库备份的分类
（1）备份对象角度
①物理备份
②逻辑备份
（2）备份策略角度
①完全备份
②差异备份
③增量备份
3. 常见的备份方法
（1）物理备份
（2）专用备份工具mysqldump或mysqlhotcopy
（3）启用二进制日志进行增量备份
（4）第三方工具备份
4. MySQL完全备份
（1）完全备份的特点
（2）完全备份的优缺点
（3）完全备份的分类
①物理冷备份与恢复
②mysqldump备份与恢复
（4）物理冷备份与恢复
①建表
②物理冷备份
③物理冷恢复
（5）mysqldump备份与恢复
①完全备份一个或多个完整的库（包括其中所有的表）
②完全备份MySQL服务器中所有的库
③完全备份指定库中的部分表
④查看备份文件
⑤恢复数据库
⑥恢复数据表
5. MySQL增量备份与恢复
（1）Mysql增量备份
（2）增加备份的特点
（3）MySQL二进制日志对备份的意义
（4）增量备份
①开启二进制日志功能
②可每周对数据库或表进行完全备份
③可每天进行增量备份操作，生成新的二进制日志文件（例如mysql-bin.000003）
④插入新数据，以模拟数据的增加或变更
⑤再次生成新的二进制日志文件
⑥查看二进制日志文件的内容
（5）增量恢复--一般恢复
①模拟丢失更改的数据的恢复步骤
②模拟丢失所有数据的恢复步骤
（6）增量恢复--断点备份
①查看二进制文件，确定指令编号、时间
②基于位置恢复
③基于时间点恢复
④总结
一、MySQL日志管理
1. MySQL日志路径
MySQL的日志默认保存位置为/usr/local/mysql/data
2. 设置、修改日志路径
MySQL日志路径可在MySQL配置文件（/etc/my.cnf）中进行设置、修改。
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
##错误日志，用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启
log-error=/usr/local/mysql/data/mysql_error.log #指定日志的保存位置和文件名
##通用查询日志，用来记录MySQL的所有连接和语句，默认是关闭的
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
##二进制日志（binlog），用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启
log-bin=mysql-bin #也可以log_bin=mysql-bin
##慢查询日志，用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，默认是关闭的
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5 #设置超过5秒执行的语句被记录，缺省时为10秒
[root@localhost ~]# systemctl restart mysqld
3. 查询日志功能是否开启
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-log Source distribution
Copyright (c) 2000, 2017, 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 variables like 'general%';
##查看通用查询日志是否开启
+------------------+-----------------------------------------+
| Variable_name | Value |
+------------------+-----------------------------------------+
| general_log | ON |
| general_log_file | /usr/local/mysql/data/mysql_general.log |
+------------------+-----------------------------------------+
2 rows in set (0.00 sec)
mysql> show variables like 'log_bin%';
##查看二进制日志是否开启
+---------------------------------+---------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------+
| log_bin | ON |
| log_bin_basename | /usr/local/mysql/data/mysql-bin |
| log_bin_index | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+---------------------------------------+
5 rows in set (0.00 sec)
mysql> show variables like '%slow%';
##查看慢查询日志功能是否开启
+---------------------------+--------------------------------------------+
| Variable_name | Value |
+---------------------------+--------------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /usr/local/mysql/data/mysql_slow_query.log |
+---------------------------+--------------------------------------------+
5 rows in set (0.00 sec)
mysql> show variables like 'long_query_time';
##查看慢查询时间设置
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
mysql> set global slow_query_log=ON;
##在数据库中设置开启慢查询的方法
Query OK, 0 rows affected (0.00 sec)
二、MySQL备份与恢复
1. 数据备份的重要性
- 备份的主要目的是灾难恢复
在生产环境中，数据的安全性至关重要
任何数据的丢失都可能产生严重的后果
造成数据丢失的原因：
● 程序错误
● 人为操作错误
● 运算错误
● 磁盘故障
● 灾难（如火灾、地震）和盗窃
2. 数据库备份的分类
（1）备份对象角度
从物理与逻辑的角度，备份可分为物理备份和逻辑备份
①物理备份
对数据库操作系统的物理文件（如数据文件、日志文件等）的备份
物理备份的方法：
● 冷备份（脱机备份）：是在关闭数据库的时候进行的
● 热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件
● 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作
②逻辑备份
对数据库逻辑组件（如表等数据库对象）的备份
（2）备份策略角度
从数据库的备份策略角度，备份可分为完全备份、差异备份以及增量备份
①完全备份
每次对数据库进行完整的备份。
会导致备份文件占用空间巨大，并且有大量的重复数据。
恢复时，直接使用完全备份的文件即可。
②差异备份
备份自从上次完全备份之后被修改过的文件。
每次差异备份，都会备份上一次完全备份之后的数据，可能会出现备份重复数据，导致占用额外的磁盘空间。
恢复时，先恢复完全备份的数据，再恢复差异备份的数据。
③增量备份
只有在上次完全备份或者增量备份后被修改的文件才会被备份。
每次增量备份都是备份在上一次完全备份或者增量备份之后的数据，不会出现备份重复数据的情况，也不会占用额外的磁盘空间。
恢复数据，需要按照次序恢复完全备份和增量备份的数据。
3. 常见的备份方法
（1）物理备份
备份时数据库处于关闭状态，直接打包数据库文件。
备份速度快，恢复时也是最简单的。
（2）专用备份工具mysqldump或mysqlhotcopy
mysqldump常用的逻辑备份工具
mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
（3）启用二进制日志进行增量备份
进行增量备份，需要刷新二进制文件
（4）第三方工具备份
免费的MySQL热备份软件Percona XtraBackup
4. MySQL完全备份
（1）完全备份的特点
是对整个数据库、数据库结构和文件结构的备份
保存的是备份完成时刻的数据库
是差异备份与增量备份的基础
InnnoDB存储引擎在磁盘上存储成三个文件：db.opt(表属性文件)、表名.frm（表结构文件）、表名.ibd（表数据文件）
（2）完全备份的优缺点
优点：
备份与恢复操作简单方便
缺点：
数据存在大量的重复
占用大量的备份空间
备份与恢复时间长
（3）完全备份的分类
①物理冷备份与恢复
关闭MySQL数据库
使用tar命令直接打包数据库文件夹
直接替换现有MySQL目录即可
②mysqldump备份与恢复
MySQL自带的备份工具，可方便实现对MySQL的备份
可以将指定的库、表导出为SQL脚本
使用命令mysql导入备份的数据
（4）物理冷备份与恢复
①建表
mysql> use test;
Database changed
mysql> create table if not exists test (
-> id int(4) not null auto_increment,
-> name varchar(10) not null,
-> sex char(10) not null,
-> hobby varchar(50),
-> primary key (id));
Query OK, 0 rows affected (0.01 sec)
mysql> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| sex | char(10) | NO | | NULL | |
| hobby | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> insert into test values(1,'zhangsan','male','running');
Query OK, 1 row affected (0.01 sec)
mysql> insert into test values(2,'lisi','female','singing');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+----+----------+--------+---------+
| id | name | sex | hobby |
+----+----------+--------+---------+
| 1 | zhangsan | male | running |
| 2 | lisi | female | singing |
+----+----------+--------+---------+
2 rows in set (0.00 sec)
②物理冷备份
[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# yum install -y xz
[root@localhost ~]# tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
#压缩备份
③物理冷恢复
删除数据表
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-log Source distribution
Copyright (c) 2000, 2017, 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 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 |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)
mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
Empty set (0.00 sec)
mysql> quit
Bye
[root@localhost ~]# systemctl stop mysqld
恢复数据库文件
[root@localhost ~]# tar Jxvf /opt/mysql_all_2021-08-31.tar.xz -C /
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-log Source distribution
Copyright (c) 2000, 2017, 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 |
| test |
| usr |
+--------------------+
6 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 |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)
mysql> select * from test;
+----+----------+--------+---------+
| id | name | sex | hobby |
+----+----------+--------+---------+
| 1 | zhangsan | male | running |
| 2 | lisi | female | singing |
+----+----------+--------+---------+
2 rows in set (0.00 sec)
（5）mysqldump备份与恢复
①完全备份一个或多个完整的库（包括其中所有的表）
mysqldump -u 用户名 -p[密码] --databases 库名1 [库名2]... > /备份路径/备份文件名.sql
导出的就是数据库脚本文件
例如：
mysqldump -u root -p --databases test > /opt/test.sql
备份test库
[root@localhost ~]# mysqldump -u root -p --databases test > /opt/test.sql
Enter password:
[root@localhost ~]# cd /opt
[root@localhost opt]# ls
mysql-5.7.20 mysql-boost-5.7.20.tar.gz rh
mysql_all_2021-08-31.tar.xz test.sql
mysqldump -u root -p --databases mysql test > /opt/mysql_test.sql
备份mysql库和test库
[root@localhost opt]# mysqldump -u root -p --databases mysql test > /opt/mysql_test.sql
Enter password:
[root@localhost opt]# ls
mysql-5.7.20 mysql-boost-5.7.20.tar.gz rh
mysql_all_2021-08-31.tar.xz mysql_test.sql test.sql
②完全备份MySQL服务器中所有的库
mysqldump -u 用户名 -p[密码] --all-databases > /备份路径/备份文件名.sql
例如：
mysqldump -u root -p --all-databases > /opt/all.sql
备份全部库
[root@localhost opt]# mysqldump -u root -p --all-databases > /opt/all.sql
Enter password:
[root@localhost opt]# ls
all.sql mysql_all_2021-08-31.tar.xz mysql_test.sql test.sql
mysql-5.7.20 mysql-boost-5.7.20.tar.gz rh
③完全备份指定库中的部分表
mysqldump -u 用户名 -p[密码] [-d] 库名 [表名1] [表名2] ... > /备份路径/备份文件名.sql
使用“-d”选项，说明只保存数据库的表结构
不使用“-d”选项，说明表数据也进行备份
例如：
mysqldump -u root -p test test > /opt/test_test.sql
备份test库中的test表
[root@localhost opt]# mysqldump -u root -p test test > /opt/test_test.sql
Enter password:
[root@localhost opt]# ls
all.sql mysql_all_2021-08-31.tar.xz mysql_test.sql test.sql
mysql-5.7.20 mysql-boost-5.7.20.tar.gz rh test_test.sql
④查看备份文件
grep -v "^--" /opt/test_test.sql | grep -v "^/" | grep -v "^$"
[root@localhost opt]# grep -v "^--" /opt/test_test.sql | grep -v "^/" | grep -v "^$"
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`sex` char(10) NOT NULL,
`hobby` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,'zhangsan','male','running'),(2,'lisi','female','singing');
UNLOCK TABLES;
⑤恢复数据库
删除数据库
[root@localhost opt]# mysql -u root -p -e 'drop database test;'
#"-e"选项，用于指定连接MySQL后执行的命令，命令执行完后自动退出
Enter password:
[root@localhost opt]# mysql -u -root -p -e 'show databases;'
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
恢复数据库
[root@localhost opt]# mysql -u root -p < /opt/test.sql
Enter password:
[root@localhost opt]# mysql -u -root -p -e 'show databases;'
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
⑥恢复数据表
删除数据表
[root@localhost opt]# mysql -u root -p -e 'drop table test.test;'
Enter password:
[root@localhost opt]# mysql -u root -p -e 'show tables from test;'
Enter password:
恢复数据表
当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在
[root@localhost opt]# mysql -u root -p test < /opt/test_test.sql
Enter password:
[root@localhost opt]# mysql -u root -p -e 'show tables from test;'
Enter password:
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
5. MySQL增量备份与恢复
（1）Mysql增量备份
使用mysqldump进行完全备份存在的问题
● 备份数据中有重复数据
● 备份时间与恢复时间过长
是自上一次备份后增加/变化的文件或者内容
MySQL没有提供直接的增量备份方法
可通过MySQL提供的二进制日志间接实现增量备份
（2）增加备份的特点
没有重复数据，备份量不大，时间段
恢复需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复
（3）MySQL二进制日志对备份的意义
● 二进制日志保存了所有更新或者可能更新数据库的操作
● 二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件
● 只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份
（4）增量备份
①开启二进制日志功能
[root@localhost opt]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED
##可选，指定二进制日志（binlog）的记录格式为MIXED
##二进制日志（binlog）有3种不同的记录格式：STATEMENT(基于SQL语句)、ROW（基于行）、MIXED（混合模式），默认格式是STATEMENT
server-id = 1
[root@localhost opt]# systemctl restart mysqld
[root@localhost opt]# ls -l /usr/local/mysql/data/mysql-bin.*
-rw-r----- 1 mysql mysql 154 8月 31 21:17 /usr/local/mysql/data/mysql-bin.000001
-rw-r----- 1 mysql mysql 19 8月 31 21:17 /usr/local/mysql/data/mysql-bin.index
②可每周对数据库或表进行完全备份
[root@localhost opt]# mysqldump -u root -p test test > /opt/test_test_$(date +%F).sql
#备份test库test表
Enter password:
[root@localhost opt]# mysqldump -u root -p --all-databases test > /opt/test_$(date +%F).sql
#备份test库
[root@localhost opt]# ls
all.sql mysql_test.sql test_test_2021-08-31.sql
mysql-5.7.20 rh test_test.sql
mysql_all_2021-08-31.tar.xz test_2021-08-31.sql
mysql-boost-5.7.20.tar.gz test.sql
③可每天进行增量备份操作，生成新的二进制日志文件（例如mysql-bin.000003）
[root@localhost opt]# mysqladmin -u root -p flush-logs
Enter password:
[root@localhost opt]# ls -l /usr/local/mysql/data/mysql-bin.*
-rw-r----- 1 mysql mysql 201 8月 31 21:19 /usr/local/mysql/data/mysql-bin.000001
-rw-r----- 1 mysql mysql 154 8月 31 21:19 /usr/local/mysql/data/mysql-bin.000002
-rw-r----- 1 mysql mysql 38 8月 31 21:19 /usr/local/mysql/data/mysql-bin.index
④插入新数据，以模拟数据的增加或变更
[root@localhost opt]# mysql -u root -p -e "insert into test.test values(3,'wangwu','male','game');"
Enter password:
[root@localhost opt]# mysql -u root -p -e "insert into test.test values(4,'zhaoliu','female','reading');"
Enter password:
[root@localhost opt]# mysql -u root -p -e "select * from test.test;"
Enter password:
+----+----------+--------+---------+
| id | name | sex | hobby |
+----+----------+--------+---------+
| 1 | zhangsan | male | running |
| 2 | lisi | female | singing |
| 3 | wangwu | male | game |
| 4 | zhaoliu | female | reading |
+----+----------+--------+---------+
⑤再次生成新的二进制日志文件
[root@localhost opt]# mysqladmin -u root -p flush-logs
#之前的步骤4的数据库操作会保存到mysql-bin.000002文件中，之后数据库数据再发生变化则保存在mysql-bin.000003文件中
Enter password:
[root@localhost opt]# ls -l /usr/local/mysql/data/mysql-bin.*
-rw-r----- 1 mysql mysql 201 8月 31 21:19 /usr/local/mysql/data/mysql-bin.000001
-rw-r----- 1 mysql mysql 750 8月 31 21:22 /usr/local/mysql/data/mysql-bin.000002
-rw-r----- 1 mysql mysql 38 8月 31 21:19 /usr/local/mysql/data/mysql-bin.index
⑥查看二进制日志文件的内容
[root@localhost opt]# cp /usr/local/mysql/data/mysql-bin.000002 /opt/
[root@localhost opt]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
--base64-output=decode-rows：使用64位编码机制去解码并按行读取
-v：显示详细内容
（5）增量恢复--一般恢复
①模拟丢失更改的数据的恢复步骤
[root@localhost opt]# mysql -u root -p -e 'delete from test.test where id =3;'
Enter password:
[root@localhost opt]# mysql -u root -p -e 'delete from test.test where id =4;'
Enter password:
[root@localhost opt]# mysql -u root -p -e 'select * from test.test;'
Enter password:
+----+----------+--------+---------+
| id | name | sex | hobby |
+----+----------+--------+---------+
| 1 | zhangsan | male | running |
| 2 | lisi | female | singing |
+----+----------+--------+---------+
[root@localhost opt]# mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p
Enter password:
[root@localhost opt]# mysql -u root -p -e 'select * from test.test;'
Enter password:
+----+----------+--------+---------+
| id | name | sex | hobby |
+----+----------+--------+---------+
| 1 | zhangsan | male | running |
| 2 | lisi | female | singing |
| 3 | wangwu | male | game |
| 4 | zhaoliu | female | reading |
+----+----------+--------+---------+
②模拟丢失所有数据的恢复步骤
[root@localhost opt]# mysql -u root -p -e 'drop table test.test;'
Enter password:
[root@localhost opt]# mysql -u root -p test < /opt/test_test_2021-08-31.sql
Enter password:
[root@localhost opt]# mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p
Enter password:
[root@localhost opt]# mysql -u root -p -e 'select * from test.test;'
Enter password:
+----+----------+--------+---------+
| id | name | sex | hobby |
+----+----------+--------+---------+
| 1 | zhangsan | male | running |
| 2 | lisi | female | singing |
| 3 | wangwu | male | game |
| 4 | zhaoliu | female | reading |
+----+----------+--------+---------+
（6）增量恢复--断点备份
①查看二进制文件，确定指令编号、时间
[root@localhost opt]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210831 21:19:41 server id 1 end_log_pos 123 CRC32 0x04f0b9c1 Start: binlog v 4, server v 5.7.20-log created 210831 21:19:41
# Warning: this binlog is either in use or was not closed properly.
# at 123
#210831 21:19:41 server id 1 end_log_pos 154 CRC32 0xfdc17b75 Previous-GTIDs
# [empty]
# at 154
#210831 21:22:32 server id 1 end_log_pos 219 CRC32 0x5c0382cc Anonymous_GTIlast_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#210831 21:22:32 server id 1 end_log_pos 294 CRC32 0x0e5399b9 Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1630416152/*!*/;
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=1437073414/*!*/;
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=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 294
#210831 21:22:32 server id 1 end_log_pos 418 CRC32 0x81d36f40 Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1630416152/*!*/;
insert into test.test values(3,'wangwu','male','game')
/*!*/;
# at 418
#210831 21:22:32 server id 1 end_log_pos 449 CRC32 0x275cc650 Xid = 100
COMMIT/*!*/;
# at 449
#210831 21:22:57 server id 1 end_log_pos 514 CRC32 0xe136f17c Anonymous_GTIlast_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 514
#210831 21:22:57 server id 1 end_log_pos 589 CRC32 0xb9d988bb Query thread_id=11 exec_time=0 error_code=0
SET TIMESTAMP=1630416177/*!*/;
BEGIN
/*!*/;
# at 589
#210831 21:22:57 server id 1 end_log_pos 719 CRC32 0x86622d35 Query thread_id=11 exec_time=0 error_code=0
SET TIMESTAMP=1630416177/*!*/;
insert into test.test values(4,'zhaoliu','female','reading')
/*!*/;
# at 719
#210831 21:22:57 server id 1 end_log_pos 750 CRC32 0x9f0efa32 Xid = 103
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*/;
# at 294
#210831 21:22:32
##插入了“wangwu”的用户数据
# at 589
#210831 21:22:57
##插入了“zhaoliu”的用户数据
②基于位置恢复
- 仅恢复到操作ID为“589”之前的数据，即不恢复“zhaoliu”的数据
删除恢复
[root@localhost opt]# mysql -u root -p -e 'delete from test.test where id =3;'
Enter password:
[root@localhost opt]# mysql -u root -p -e 'delete from test.test where id =4;'
Enter password:
[root@localhost opt]# mysql -u root -p -e 'select * from test.test;'
Enter password:
+----+----------+--------+---------+
| id | name | sex | hobby |
+----+----------+--------+---------+
| 1 | zhangsan | male | running |
| 2 | lisi | female | singing |
+----+----------+--------+---------+
重新恢复
[root@localhost opt]# mysqlbinlog --no-defaults --stop-position='589' /opt/mysql-bin.000002 | mysql -u root-p
[root@localhost opt]# mysql -u root -p -e 'select * from test.test;'
Enter password:
+----+----------+--------+---------+
| id | name | sex | hobby |
+----+----------+--------+---------+
| 1 | zhangsan | male | running |
| 2 | lisi | female | singing |
| 3 | wangwu | male | game |
+----+----------+--------+---------+
- 仅恢复“zhaoliu”的数据，跳过“wangwu”的数据恢复
删除恢复
[root@localhost opt]# mysql -u root -p -e 'delete from test.test where id =3;'
Enter password:
[root@localhost opt]# mysql -u root -p -e 'select * from test.test;'
Enter password:
+----+----------+--------+---------+
| id | name | sex | hobby |
+----+----------+--------+---------+
| 1 | zhangsan | male | running |
| 2 | lisi | female | singing |
+----+----------+--------+---------+
重新恢复
[root@localhost opt]# mysqlbinlog --no-defaults --start-position='589' /opt/mysql-bin.000002 | mysql -u root-p
[root@localhost opt]# mysql -u root -p -e 'select * from test.test;'
Enter password:
+----+----------+--------+---------+
| id | name | sex | hobby |
+----+----------+--------+---------+
| 1 | zhangsan | male | running |
| 2 | lisi | female | singing |
| 4 | zhaoliu | female | reading |
+----+----------+--------+---------+
③基于时间点恢复
- 仅恢复到21:22:57之前的数据，即不恢复“wangwu”的数据
删除恢复
[root@localhost opt]# mysql -u root -p -e 'delete from test.test where id =4;'
Enter password:
[root@localhost opt]# mysql -u root -p -e 'select * from test.test;'
Enter password:
+----+----------+--------+---------+
| id | name | sex | hobby |
+----+----------+--------+---------+
| 1 | zhangsan | male | running |
| 2 | lisi | female | singing |
+----+----------+--------+---------+
重新恢复
[root@localhost opt]# mysqlbinlog --no-defaults --stop-datetime='2021-08-31 21:22:57' /opt/mysql-bin.000002 | mysql -u root-p
[root@localhost opt]# mysql -u root -p -e 'select * from test.test;'
Enter password:
+----+----------+--------+---------+
| id | name | sex | hobby |
+----+----------+--------+---------+
| 1 | zhangsan | male | running |
| 2 | lisi | female | singing |
| 3 | wangwu | male | game |
+----+----------+--------+---------+
- 仅恢复“zhaoliu”的数据，跳过“wangwu”的数据恢复
删除恢复
[root@localhost opt]# mysql -u root -p -e 'delete from test.test where id =3;'
Enter password:
[root@localhost opt]# mysql -u root -p -e 'select * from test.test;'
Enter password:
+----+----------+--------+---------+
| id | name | sex | hobby |
+----+----------+--------+---------+
| 1 | zhangsan | male | running |
| 2 | lisi | female | singing |
+----+----------+--------+---------+
重新恢复
[root@localhost opt]# mysqlbinlog --no-defaults --start-datetime='2021-08-31 21:22:57' /opt/mysql-bin.000002 | mysql -u root-p
[root@localhost opt]# mysql -u root -p -e 'select * from test.test;'
Enter password:
+----+----------+--------+---------+
| id | name | sex | hobby |
+----+----------+--------+---------+
| 1 | zhangsan | male | running |
| 2 | lisi | female | singing |
| 4 | zhaoliu | female | reading |
+----+----------+--------+---------+
④总结
如果恢复某条SQL语句之前的所有数据，就stop在这个语句的位置节点或者时间点
如果恢复某条SQL语句以及之后的所有数据，就从这个语句的位置节点或者时间点start
