MySQL数据备份与恢复

Alone-林

发布于 2023-03-17 10:54:59

3.1K0

发布于 2023-03-17 10:54:59

文章被收录于专栏：Linux云运维Linux云运维

MySQL数据备份与恢复

1、数据备份概述

备份是数据安全的最后一道防线，对于任何数据丢失的场景，备份虽然不一定能恢复百分之百的数据(取决于备份周期)，但至少能将损失降到最低。

数据丢失的场景举例：

人为操作失误造成某些数据被误操作
软件 BUG 造成部分数据或全部数据丢失
硬件故障造成数据库部分数据或全部数据丢失
安全漏洞被入侵数据恶意破坏

1.1 衡量备份恢复重要指标

衡量备份恢复有两个重要的指标：

恢复点目标(RPO)
- 恢复点目标是指数据能恢复到什么程度
恢复时间目标(RTO)
- 恢复时间目标是指数据恢复需要多长时间

1.2 数据库备份分类

数据库备份方式分很多种，从物理与逻辑的角度来看，备份可分为：

物理备份：指对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。物理备份又可以分为脱机备份(冷备份)和联机备份(热备份)。
- 冷备份:在关闭数据库时进行的备份操作，能够较好地保证数据库的完整性。
- 热备份:在数据库运行状态中进行操作，这种备份方法依赖于数据库的日志文件。
逻辑备份：指对数据库逻辑组件(如"表"等数据库对象)的备份。

数据库的备份从策略角度来看，备份可分为以下几类：

完全备份：每次对数据进行完整的备份。可以备份整个数据库，包含用户表、系统表、索引、视图和存储过程等所有数据库对象。但它需要花费更多的时间和空间，所以，做一次完全备份的周期要长些。
差异备份：备份那些自从上次完全备份之后被修改过的文件，只备份数据库部分的内容。它比最初的完全备份小，因为只包含自上次完全备份以来所改变的数据库。它的优点是存储和恢复速度快。
增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。

2、MySQL备份工具

2.1 mysqldump备份工具

MySQL官方提供了Mysqldump逻辑备份工具，它的备份原理是通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert语句，当我们需要还原这些数据时，只要执行这些insert语句，即可将对应的数据还原。

//mysqldump命令语法：
mysqldump [选项] 数据库名 [表名] > 备份文件名

选项列表：

选项名	含义
–host	服务器IP地址
–port	服务器端口号
–user	MySQL 用户名
–password	MySQL 密码
–databases	指定要备份的数据库
–all-databases	备份mysql服务器上的所有数据库
–compact	压缩模式，产生更少的输出
–comments	添加注释信息
–complete-insert	输出完成的插入语句
–lock-tables	备份前，锁定所有数据库表
–no-create-db / --no-create-info	禁止生成创建数据库语句
–force	当出现错误时仍然继续备份操作
–default-character-set	指定默认字符集
–add-locks	备份数据库表时锁定数据库表

2.2 mysql数据恢复

系统行命令

mysql [选项] 数据库名 [表名] > 备份文件名

source 方法

source 备份文件名

3、mysql全量备份与恢复实例演示

3.1 mysql全量备份

#备份整个数据库(全备)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zsl                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use zsl;
Database changed
mysql> show tables;
+---------------+
| Tables_in_zsl |
+---------------+
| course        |
| students      |
| teacher       |
+---------------+
3 rows in set (0.00 sec)

[root@localhost ~]# mysqldump -uroot -pPasswd123! --all-databases > all-database-$(date '+%F-%H-%M-%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-database-2022-07-29-10-45-04.sql  anaconda-ks.cfg


#备份zsl库的course表、students表和teacher表
[root@localhost ~]# mysqldump -uroot -pPasswd123! zsl course students teacher > table-$(date '+%F-%H-%M-%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-database-2022-07-29-10-45-04.sql  anaconda-ks.cfg  table-2022-07-29-10-45-27.sql


#备份zsl库
[root@localhost ~]# mysqldump -uroot -pPasswd123! --databases zsl  > zsl-database-$(date '+%F-%H-%M-%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-database-2022-07-29-10-45-04.sql  table-2022-07-29-10-45-27.sql
anaconda-ks.cfg                       zsl-database-2022-07-29-10-47-47.sql

3.2 mysql数据恢复

#模拟误删zsl数据库
mysql> drop database zsl;
Query OK, 3 rows affected (0.00 sec)

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


#恢复zsl数据库
##方法一：系统行命令数据恢复
[root@localhost ~]# mysql -uroot -pPasswd123! < zsl-database-2022-07-29-10-47-47.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zsl                |
+--------------------+
5 rows in set (0.00 sec)

方法二：source数据恢复
mysql> source zsl-database-2022-07-29-10-47-47.sql;
......
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
......
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zsl                |
+--------------------+
5 rows in set (0.00 sec)


#模拟删除zsl库的course表、students表
mysql> drop table course;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table students;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+---------------+
| Tables_in_zsl |
+---------------+
| teacher       |
+---------------+
1 row in set (0.00 sec)


#恢复zsl库的course表、students表
mysql> source table-2022-07-29-10-45-27.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
......
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_zsl |
+---------------+
| course        |
| students      |
| teacher       |
+---------------+
3 rows in set (0.00 sec)


#模拟删除整个数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zsl                |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database zsl;
Query OK, 3 rows affected (0.00 sec)

mysql> drop database sys;
Query OK, 101 rows affected (0.02 sec)

mysql> drop database mysql;
Query OK, 31 rows affected, 2 warnings (0.03 sec)

mysql> drop database performance_schema;
Query OK, 87 rows affected, 2 warnings (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)

#恢复整个数据库
[root@localhost ~]# mysql -uroot -pPasswd123! < all-database-2022-07-29-10-45-04.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| zsl                |
+--------------------+
3 rows in set (0.00 sec)

4、mysql差异备份与恢复实例演示

4.1 mysql差异备份

开启MySQL服务器的二进制日志功能

[root@localhost ~]# vim /etc/my.cnf 
[root@localhost ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
server-id=1					      #设置服务器标识符
log-bin=mysql_bin 				#开启二进制日志功能

[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

对数据库进行完全备份

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

mysql> show tables from zsl;
+---------------+
| Tables_in_zsl |
+---------------+
| course        |
| students      |
| teacher       |
+---------------+
3 rows in set (0.00 sec)

mysql> select * from zsl.course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | HTML        |
|  2 | JAVA        |
|  3 | MySQL       |
|  4 | Python      |
|  5 | C++         |
+----+-------------+
5 rows in set (0.01 sec)

mysql> select * from zsl.students;
+----+-------+------+------+--------+-----------+
| id | name  | age  | sex  | height | course_id |
+----+-------+------+------+--------+-----------+
|  1 | meng  |   25 | 女   |    160 |         1 |
|  2 | ke    |   22 | 男   |    180 |         3 |
|  3 | yang  |   18 | 男   |    175 |         2 |
|  4 | ding  |   19 | 女   |    165 |         4 |
|  5 | zhong |   20 | 男   |    165 |         5 |
+----+-------+------+------+--------+-----------+
5 rows in set (0.00 sec)

mysql> select * from zsl.teacher;
+----+------+------+------+--------+-----------+
| id | name | age  | sex  | height | course_id |
+----+------+------+------+--------+-----------+
|  1 | aa   |   25 | 女   |    160 |         1 |
|  2 | bb   |   22 | 男   |    180 |         3 |
|  3 | cc   |   18 | 男   |    175 |         2 |
|  4 | dd   |   19 | 女   |    165 |         4 |
|  5 | ee   |   20 | 男   |    165 |         5 |
+----+------+------+------+--------+-----------+
5 rows in set (0.00 sec)


#开始完全备份
[root@localhost ~]# mysqldump -uroot -pPasswd123! --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-database-$(date '+%F-%H-%M-%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll
total 1732
-rw-r--r--. 1 root root 878539 Jul 29 10:45 all-database-2022-07-29-10-45-04.sql
-rw-r--r--. 1 root root 878710 Jul 29 11:21 all-database-2022-07-29-11-21-10.sql
-rw-------. 1 root root   1097 Jul 21 18:41 anaconda-ks.cfg
-rw-r--r--. 1 root root   3726 Jul 29 10:45 table-2022-07-29-10-45-27.sql
-rw-r--r--. 1 root root   3862 Jul 29 10:47 zsl-database-2022-07-29-10-47-47.sql


#添加或删减内容
mysql> select * from course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | HTML        |
|  2 | JAVA        |
|  3 | MySQL       |
|  5 | C++         |
|  6 | wuli        |
|  7 | huaxue      |
|  8 | shuxue      |
|  9 | yuwen       |
+----+-------------+
8 rows in set (0.00 sec)

mysql> select * from students;
+----+-------+------+------+--------+-----------+
| id | name  | age  | sex  | height | course_id |
+----+-------+------+------+--------+-----------+
|  1 | meng  |   25 | 女   |    160 |         1 |
|  2 | ke    |   22 | 男   |    180 |         3 |
|  3 | yang  |   18 | 男   |    175 |         2 |
|  4 | ding  |   19 | 女   |    165 |         4 |
|  5 | zhong |   20 | 男   |    165 |         5 |
+----+-------+------+------+--------+-----------+
5 rows in set (0.00 sec)

mysql> select * from teacher;
+----+------+------+------+--------+-----------+
| id | name | age  | sex  | height | course_id |
+----+------+------+------+--------+-----------+
|  1 | aa   |   25 | 女   |    160 |         1 |
|  2 | bb   |   22 | 男   |    180 |         3 |
|  3 | cc   |   18 | 男   |    175 |         2 |
|  4 | dd   |   19 | 女   |    165 |         4 |
|  5 | ee   |   20 | 男   |    165 |         5 |
|  6 | qq   |   20 | 男   |    156 |         2 |
|  7 | gg   |   60 | 男   |    186 |         3 |
|  8 | ff   |   20 | 女   |    185 |         2 |
|  9 | rr   |   15 | 男   |    189 |         9 |
+----+------+------+------+--------+-----------+
9 rows in set (0.00 sec)

4.2 mysql差异备份数据恢复

#模拟误删数据
[root@localhost ~]# mysql -uroot -pPasswd123! -e 'drop database zsl;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -pPasswd123! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
//由上可以看到zsl这个数据库已被删除


#刷新创建新的二进制日志
[root@localhost ~]# ll /opt/data/
total 122984
-rw-r-----. 1 mysql mysql       56 Jul 29 02:43 auto.cnf
-rw-------. 1 mysql mysql     1680 Jul 29 02:43 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 client-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 29 02:43 client-key.pem
-rw-r-----. 1 mysql mysql      669 Jul 29 11:12 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 29 11:39 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 29 11:39 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 29 02:43 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 29 11:21 ibtmp1
-rw-r-----. 1 mysql mysql    20346 Jul 29 11:12 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 Jul 29 11:10 mysql
-rw-r-----. 1 mysql mysql     3913 Jul 29 11:39 mysql_bin.000003
-rw-r-----. 1 mysql mysql       19 Jul 29 11:21 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 Jul 29 11:12 mysql.pid
-rw-r--r--. 1 root  root         6 Jul 29 11:10 mysql_upgrade_info
drwxr-x---. 2 mysql mysql     8192 Jul 29 11:10 performance_schema
-rw-------. 1 mysql mysql     1676 Jul 29 02:43 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 29 02:43 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 server-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 29 02:43 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 29 11:10 sys
[root@localhost ~]#  mysqladmin -uroot -pPasswd123! flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll /opt/data/
total 122988
-rw-r-----. 1 mysql mysql       56 Jul 29 02:43 auto.cnf
-rw-------. 1 mysql mysql     1680 Jul 29 02:43 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 client-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 29 02:43 client-key.pem
-rw-r-----. 1 mysql mysql      669 Jul 29 11:12 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 29 11:41 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 29 11:41 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 29 02:43 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 29 11:21 ibtmp1
-rw-r-----. 1 mysql mysql    20346 Jul 29 11:12 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 Jul 29 11:10 mysql
-rw-r-----. 1 mysql mysql     3960 Jul 29 11:41 mysql_bin.000003
-rw-r-----. 1 mysql mysql      154 Jul 29 11:41 mysql_bin.000004
-rw-r-----. 1 mysql mysql       38 Jul 29 11:41 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 Jul 29 11:12 mysql.pid
-rw-r--r--. 1 root  root         6 Jul 29 11:10 mysql_upgrade_info
drwxr-x---. 2 mysql mysql     8192 Jul 29 11:10 performance_schema
-rw-------. 1 mysql mysql     1676 Jul 29 02:43 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 29 02:43 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 server-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 29 02:43 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 29 11:10 sys


#恢复完全备份
[root@localhost ~]# mysql -uroot -pPasswd123! < all-database-2022-07-29-11-21-10.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -pPasswd123! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zsl                |
+--------------------+
[root@localhost ~]# mysql -uroot -pPasswd123! -e 'show tables from zsl;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+
| Tables_in_zsl |
+---------------+
| course        |
| students      |
| teacher       |
+---------------+
[root@localhost ~]# mysql -uroot -pPasswd123! -e 'select * from zsl.course;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | HTML        |
|  2 | JAVA        |
|  3 | MySQL       |
|  4 | Python      |
|  5 | C++         |
+----+-------------+
[root@localhost ~]# mysql -uroot -pPasswd123! -e 'select * from zsl.students;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+------+------+--------+-----------+
| id | name  | age  | sex  | height | course_id |
+----+-------+------+------+--------+-----------+
|  1 | meng  |   25 | 女   |    160 |         1 |
|  2 | ke    |   22 | 男   |    180 |         3 |
|  3 | yang  |   18 | 男   |    175 |         2 |
|  4 | ding  |   19 | 女   |    165 |         4 |
|  5 | zhong |   20 | 男   |    165 |         5 |
+----+-------+------+------+--------+-----------+
[root@localhost ~]# mysql -uroot -pPasswd123! -e 'select * from zsl.teacher;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+------+------+--------+-----------+
| id | name | age  | sex  | height | course_id |
+----+------+------+------+--------+-----------+
|  1 | aa   |   25 | 女   |    160 |         1 |
|  2 | bb   |   22 | 男   |    180 |         3 |
|  3 | cc   |   18 | 男   |    175 |         2 |
|  4 | dd   |   19 | 女   |    165 |         4 |
|  5 | ee   |   20 | 男   |    165 |         5 |
+----+------+------+------+--------+-----------+


#恢复差异备份
[root@localhost ~]# ll /opt/data/
total 189544
-rw-r-----. 1 mysql mysql       56 Jul 29 02:43 auto.cnf
-rw-------. 1 mysql mysql     1680 Jul 29 02:43 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 client-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 29 02:43 client-key.pem
-rw-r-----. 1 mysql mysql      669 Jul 29 11:12 ib_buffer_pool
-rw-r-----. 1 mysql mysql 79691776 Jul 29 11:47 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 29 11:47 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 29 02:43 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 29 11:21 ibtmp1
-rw-r-----. 1 mysql mysql    20346 Jul 29 11:12 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 Jul 29 11:45 mysql
-rw-r-----. 1 mysql mysql     3960 Jul 29 11:41 mysql_bin.000003
-rw-r-----. 1 mysql mysql   859354 Jul 29 11:45 mysql_bin.000004
-rw-r-----. 1 mysql mysql       38 Jul 29 11:41 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 Jul 29 11:12 mysql.pid
-rw-r--r--. 1 root  root         6 Jul 29 11:10 mysql_upgrade_info
drwxr-x---. 2 mysql mysql     8192 Jul 29 11:10 performance_schema
-rw-------. 1 mysql mysql     1676 Jul 29 02:43 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 29 02:43 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 server-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 29 02:43 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 29 11:10 sys
drwxr-x---. 2 mysql mysql      134 Jul 29 11:45 zsl

#检查误删数据库的位置在什么地方
mysql> show binlog events in 'mysql_bin.000003';
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000003 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.37-log, Binlog ver: 4 |
| mysql_bin.000003 |  123 | Previous_gtids |         1 |         154 |                                       |
| mysql_bin.000003 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 |  219 | Query          |         1 |         290 | BEGIN                                 |
| mysql_bin.000003 |  290 | Table_map      |         1 |         349 | table_id: 180 (zsl.teacher)           |
| mysql_bin.000003 |  349 | Write_rows     |         1 |         510 | table_id: 180 flags: STMT_END_F       |
| mysql_bin.000003 |  510 | Xid            |         1 |         541 | COMMIT /* xid=988 */                  |
| mysql_bin.000003 |  541 | Anonymous_Gtid |         1 |         606 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 |  606 | Query          |         1 |         677 | BEGIN                                 |
| mysql_bin.000003 |  677 | Table_map      |         1 |         728 | table_id: 178 (zsl.course)            |
| mysql_bin.000003 |  728 | Write_rows     |         1 |         808 | table_id: 178 flags: STMT_END_F       |
| mysql_bin.000003 |  808 | Xid            |         1 |         839 | COMMIT /* xid=989 */                  |
| mysql_bin.000003 |  839 | Anonymous_Gtid |         1 |         904 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 |  904 | Query          |         1 |         975 | BEGIN                                 |
| mysql_bin.000003 |  975 | Table_map      |         1 |        1034 | table_id: 180 (zsl.teacher)           |
| mysql_bin.000003 | 1034 | Write_rows     |         1 |        1201 | table_id: 180 flags: STMT_END_F       |
| mysql_bin.000003 | 1201 | Xid            |         1 |        1232 | COMMIT /* xid=990 */                  |
| mysql_bin.000003 | 1232 | Anonymous_Gtid |         1 |        1297 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 1297 | Query          |         1 |        1368 | BEGIN                                 |
| mysql_bin.000003 | 1368 | Table_map      |         1 |        1427 | table_id: 180 (zsl.teacher)           |
| mysql_bin.000003 | 1427 | Delete_rows    |         1 |        1483 | table_id: 180 flags: STMT_END_F       |
| mysql_bin.000003 | 1483 | Xid            |         1 |        1514 | COMMIT /* xid=994 */                  |
| mysql_bin.000003 | 1514 | Anonymous_Gtid |         1 |        1579 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 1579 | Query          |         1 |        1650 | BEGIN                                 |
| mysql_bin.000003 | 1650 | Table_map      |         1 |        1709 | table_id: 180 (zsl.teacher)           |
| mysql_bin.000003 | 1709 | Delete_rows    |         1 |        1765 | table_id: 180 flags: STMT_END_F       |
| mysql_bin.000003 | 1765 | Xid            |         1 |        1796 | COMMIT /* xid=995 */                  |
| mysql_bin.000003 | 1796 | Anonymous_Gtid |         1 |        1861 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 1861 | Query          |         1 |        1932 | BEGIN                                 |
| mysql_bin.000003 | 1932 | Table_map      |         1 |        1991 | table_id: 180 (zsl.teacher)           |
| mysql_bin.000003 | 1991 | Delete_rows    |         1 |        2048 | table_id: 180 flags: STMT_END_F       |
| mysql_bin.000003 | 2048 | Xid            |         1 |        2079 | COMMIT /* xid=996 */                  |
| mysql_bin.000003 | 2079 | Anonymous_Gtid |         1 |        2144 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 2144 | Query          |         1 |        2215 | BEGIN                                 |
| mysql_bin.000003 | 2215 | Table_map      |         1 |        2274 | table_id: 180 (zsl.teacher)           |
| mysql_bin.000003 | 2274 | Delete_rows    |         1 |        2331 | table_id: 180 flags: STMT_END_F       |
| mysql_bin.000003 | 2331 | Xid            |         1 |        2362 | COMMIT /* xid=997 */                  |
| mysql_bin.000003 | 2362 | Anonymous_Gtid |         1 |        2427 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 2427 | Query          |         1 |        2498 | BEGIN                                 |
| mysql_bin.000003 | 2498 | Table_map      |         1 |        2557 | table_id: 180 (zsl.teacher)           |
| mysql_bin.000003 | 2557 | Delete_rows    |         1 |        2614 | table_id: 180 flags: STMT_END_F       |
| mysql_bin.000003 | 2614 | Xid            |         1 |        2645 | COMMIT /* xid=998 */                  |
| mysql_bin.000003 | 2645 | Anonymous_Gtid |         1 |        2710 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 2710 | Query          |         1 |        2781 | BEGIN                                 |
| mysql_bin.000003 | 2781 | Table_map      |         1 |        2840 | table_id: 180 (zsl.teacher)           |
| mysql_bin.000003 | 2840 | Delete_rows    |         1 |        2897 | table_id: 180 flags: STMT_END_F       |
| mysql_bin.000003 | 2897 | Xid            |         1 |        2928 | COMMIT /* xid=999 */                  |
| mysql_bin.000003 | 2928 | Anonymous_Gtid |         1 |        2993 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 2993 | Query          |         1 |        3064 | BEGIN                                 |
| mysql_bin.000003 | 3064 | Table_map      |         1 |        3123 | table_id: 180 (zsl.teacher)           |
| mysql_bin.000003 | 3123 | Delete_rows    |         1 |        3180 | table_id: 180 flags: STMT_END_F       |
| mysql_bin.000003 | 3180 | Xid            |         1 |        3211 | COMMIT /* xid=1000 */                 |
| mysql_bin.000003 | 3211 | Anonymous_Gtid |         1 |        3276 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 3276 | Query          |         1 |        3347 | BEGIN                                 |
| mysql_bin.000003 | 3347 | Table_map      |         1 |        3406 | table_id: 180 (zsl.teacher)           |
| mysql_bin.000003 | 3406 | Delete_rows    |         1 |        3463 | table_id: 180 flags: STMT_END_F       |
| mysql_bin.000003 | 3463 | Xid            |         1 |        3494 | COMMIT /* xid=1001 */                 |
| mysql_bin.000003 | 3494 | Anonymous_Gtid |         1 |        3559 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 3559 | Query          |         1 |        3630 | BEGIN                                 |
| mysql_bin.000003 | 3630 | Table_map      |         1 |        3681 | table_id: 178 (zsl.course)            |
| mysql_bin.000003 | 3681 | Delete_rows    |         1 |        3728 | table_id: 178 flags: STMT_END_F       |
| mysql_bin.000003 | 3728 | Xid            |         1 |        3759 | COMMIT /* xid=1002 */                 |
| mysql_bin.000003 | 3759 | Anonymous_Gtid |         1 |        3824 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 3824 | Query          |         1 |        3913 | drop database zsl                     |
| mysql_bin.000003 | 3913 | Rotate         |         1 |        3960 | mysql_bin.000004;pos=4                |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
65 rows in set (0.00 sec)

#使用mysqlbinlog恢复差异备份
[root@localhost ~]# mysqlbinlog --stop-position=3824 /opt/data/mysql_bin.000003 |mysql -uroot -pPasswd123!
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -pPasswd123! -e 'select * from zsl.course;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | HTML        |
|  2 | JAVA        |
|  3 | MySQL       |
|  5 | C++         |
|  6 | wuli        |
|  7 | huaxue      |
|  8 | shuxue      |
|  9 | yuwen       |
+----+-------------+

扩展

二进制日志转换文本文件

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql_bin.000003 > /opt/mysql_bin003.txt

根据时间点恢复数据

mysqlbinlog --no-defaults --stop-datetime=‘2022-07-29 12:06:12’ /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p //基于时间恢复从开头到指定的时间停止之前的都会执行操作 mysqlbinlog --no-defaults --start-datetime=‘2022-07-29 12:06:16’ /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p //基于正确的时间恢复从指定的时间点到结尾都会执行操作

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022-07-29，如有侵权请联系 cloudcommunity@tencent.com 删除

数据备份