欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

理论+实验——MySQL备份与恢复

程序员文章站 2022-06-22 17:15:59
文章目录一、数据库备份的分类1.1 备份的重要性1.2 数据库备份的分类1.3 常见的备份方法二、MySQL完全备份与恢复2.1 完全备份概述2.2 完全备份优缺点2.3 完全备份分类2.3.1 MySQL物理冷备份及恢复2.3.2 mysqldump 备份与恢复三、MySQL 增量备份与恢复3.1 MySQL 增量备份3.2 MySQL 数据库增量恢复四、MySQL 备份恢复案例4.1 一般恢复(丢什么数据,找什么数据)4.2 基于位置恢复4.3 基于时间恢复一、数据库备份的分类1.1 备份的重要性...

一、数据库备份的分类

1.1 备份的重要性

(1)在生产环境中,数据的安全性至关重要
(2)任何数据的丢失都可能产生严重的后果
(3)造成数据丢失的原因:

  • 程序错误
  • 人为操作错误
  • 运算错误
  • 磁盘故障
    灾难(如火灾、地震)和盗窃

1.2 数据库备份的分类

从物理与逻辑的角度,备份可分为:

  • 物理备份:对数据库操作系统的物理文件(如数据文件、日志文件等)的备份
    ◆ 物理备份方法:
    冷备份(脱机备份):是在关闭数据库的时候进行的
    热备份(联机备份):数据库处于运行状态,依赖于数据库的日志文件
    温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作
  • 逻辑备份:对数据库逻辑组件(如:表等数据库对象)的备份

从数据库的备份策略角度,备份可分为:

  • 完全备份:每次对数据库进行完整的备份
  • 差异备份:备份自从上次完全备份之后被修改过的文件
  • 增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份

1.3 常见的备份方法

(1)物理冷备

  • 备份时数据库处于关闭状态,直接打包数据库文件
  • 备份速度快,恢复是也是最简单的

(2)专用备份工具 mydump 或 mysqlhotcpy

  • mysqldump 常用的逻辑备份工具
  • mysqlhotcopy 仅拥有备份 MylSAM 和 ARCHIVE 表

(3)启动二进制日志进行增量备份

  • 进行增量备份,需要刷新二进制日志

(4)第三方工具备份

  • 免费的MySQL热备份软件 Percona XtraBackup

二、MySQL完全备份与恢复

2.1 完全备份概述

是对整个数据库、数据库结构和文件结构的备份
保存的是备份完成时刻的数据库
是差异备份与增量备份的基础

2.2 完全备份优缺点

优点

  • 备份与恢复操作简单方便

缺点

  • 数据存在大量的重复
  • 占用大量的备份空间
  • 备份与恢复时间长

2.3 完全备份分类

物理冷备份与恢复

  • 关闭MySQL数据库
  • 使用tar命令直接打包数据库文件夹
  • 直接替换现有MySQL目录即可

mysqldump备份与恢复

  • MySQL自带的备份工具,可方便实现对MySQL的备份
  • 可以将指定的库、表导出为SQL脚本
  • 使用命令mysql导入备份的数据

2.3.1 MySQL物理冷备份及恢复

备份数据库:

[root@localhost ~]# systemctl stop mysqld                                ###关闭数据库####
[root@localhost ~]# mkdir /backup                                        ###在根下新建个backup目录
[root@localhost ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/           #####/usr/local/mysql/data备份压缩
tar: Removing leading `/' from member names
/usr/local/mysql/data/
/usr/local/mysql/data/ibdata1
/usr/local/mysql/data/ib_logfile1
.
.
.
/usr/local/mysql/data/ib_buffer_pool
[root@localhost ~]# cd /backup/                                               ###进入/backup/         
[root@localhost backup]# ll                                                        ##查看备份
total 1384
-rw-r--r-- 1 root root 1413924 Sep 13 22:24 mysql_all-2020-09-13.tar.gz

模拟故障:

[root@localhost backup]# mkdir /bak                                            ####在根下新建bak目录
[root@localhost backup]# mv /usr/local/mysql/data/ /bak/          ####将目录data数据移动到bak 里面去
[root@localhost backup]# cd /usr/local/mysql/                             #####仅mysql目录查看data
[root@localhost mysql]# ll                                                              ####查看后data目录被移走 ,模拟故障成功
total 64
drwxr-xr-x  2 mysql mysql  4096 Sep 13 18:42 bin
-rw-r--r--  1 mysql mysql 17987 Sep 13  2017 COPYING
-rw-r--r--  1 mysql mysql 17987 Sep 13  2017 COPYING-test
drwxr-xr-x  2 mysql mysql    55 Sep 13 18:41 docs
drwxr-xr-x  3 mysql mysql  4096 Sep 13 18:41 include
drwxr-xr-x  4 mysql mysql   191 Sep 13 18:42 lib
drwxr-xr-x  4 mysql mysql    30 Sep 13 18:42 man
drwxr-xr-x 10 mysql mysql  4096 Sep 13 18:42 mysql-test
-rw-r--r--  1 mysql mysql  2478 Sep 13  2017 README
-rw-r--r--  1 mysql mysql  2478 Sep 13  2017 README-test
drwxr-xr-x 28 mysql mysql  4096 Sep 13 18:42 share
drwxr-xr-x  2 mysql mysql    90 Sep 13 18:42 support-files
drwxr-xr-x  3 mysql mysql    17 Sep 13 18:42 usr

数据库恢复:

[root@localhost mysql]# mkdir /restore                                                                                        ####根目录新建restore 目录
[root@localhost mysql]# tar zxvf /backup/mysql_all-2020-09-13.tar.gz -C /restore/              ####将备份数据库解压到 /restore目录下  不要无脑刷,时间节点不一样
usr/local/mysql/data/
usr/local/mysql/data/ibdata1
usr/local/mysql/data/ib_logfile1
.
.
.
usr/local/mysql/data/ib_buffer_pool
[root@localhost mysql]# mv /restore/usr/local/mysql/data/ /usr/local/mysql/          ####将备份数据移动到到/usr/local/mysql中
[root@localhost mysql]# systemctl start mysqld                                                          ####启动mysql数据库
[root@localhost mysql]# systemctl status mysqld                                                        ####查看mysql数据库启动状态,日志文件显示正常。
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Sun 2020-09-13 22:45:27 CST; 7s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 28858 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 28839 ExecStartPre=/usr/local/mysql/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 28860 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─28860 /usr/local/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/mysqld.p...

Sep 13 22:45:27 localhost.localdomain mysqld[28858]: 2020-09-13T14:45:27.023023Z 0 [Note] Ser...06
Sep 13 22:45:27 localhost.localdomain mysqld[28858]: 2020-09-13T14:45:27.023058Z 0 [Note] IPv...e.
Sep 13 22:45:27 localhost.localdomain mysqld[28858]: 2020-09-13T14:45:27.023066Z 0 [Note]   -...';
Sep 13 22:45:27 localhost.localdomain mysqld[28858]: 2020-09-13T14:45:27.023076Z 0 [Note] Ser...'.
Sep 13 22:45:27 localhost.localdomain mysqld[28858]: 2020-09-13T14:45:27.030563Z 0 [Note] Eve...ts
Sep 13 22:45:27 localhost.localdomain mysqld[28858]: 2020-09-13T14:45:27.030797Z 0 [Note] /us...s.
Sep 13 22:45:27 localhost.localdomain mysqld[28858]: Version: '5.7.20'  socket: '/usr/local/m...on
Sep 13 22:45:27 localhost.localdomain mysqld[28858]: 2020-09-13T14:45:27.030815Z 0 [Note] Exe...k.
Sep 13 22:45:27 localhost.localdomain mysqld[28858]: 2020-09-13T14:45:27.030819Z 0 [Note] Beg...es
Sep 13 22:45:27 localhost.localdomain systemd[1]: Started MySQL Server.
Hint: Some lines were ellipsized, use -l to show in full.

2.3.2 mysqldump 备份与恢复

  • 将指定的库、表或全部的库导出为SQL脚本
  • mysqldump 备份需要和mysql进行数据交互,如果关闭mysql 则无法备份和恢复

(1)mysqldump 命令库内特定的表进行完全备份

mysqldump [选项] 库名 [表名1] [表名2]> /备份路径/备份文件名

例:

mysqldump -u root -p mysql user > /backup/mysql-user.sql

(2)mysqldump 命令对单个或多个库进行完全备份

mysqldump [选项] --databases 库名1 [库名2]> /备份路径/备份文件名

例:

mysqldump -u root -p auth > /backup/auth.sql
mysqldump -u root -p mysql > /backup/mysql.sql
mysqldump -u root -p  --databases auth mysql > /backup/databases-auth-mysql.sql

(3)mysqldump 命令对所有库进行完全备份

mysqldump [选项] --all-databases  > /备份路径/备份文件名

例:

mysqldump -u root -p --opt --all-databases > /backup/all-data.sql

创建环境:

mysql> create database auth;                              	####创建auth 这个数据库
mysql> use auth;                              		####进入auth 这个数据库
Database changed
mysql> create table users (user_name CHAR(16) NOT NULL, user_passwd CHAR(48) DEFAULT '', PRIMARY KEY (user_name));
mysql> insert into users(user_name,user_passwd) values('zhangsan', password('123456'));
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> insert into users values('lisi', password('123456'));
Query OK, 1 row affected, 1 warning (0.01 sec)

例:

[root@localhost /]# mysqldump -u root -p mysql user >mysql-user.sql     ###备份数据路mysql中的user表  ,文件保存在当前操作目录下面
Enter password:                                                                                                  ###输入密码abc123 

例:

[root@localhost /]# mysqldump -u root -p --databases auth >auth.sql      ####备份auth数据库     文件保存在当前操作目录下面
Enter password:                                                                                             ####输入密码abc123 

查看备份文件

[root@localhost mysql]# grep -v "^--" auth.sql | grep -v "^/" | grep -v "^$"
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `auth` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `auth`;
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `user_name` char(16) NOT NULL,
  `user_passwd` char(48) DEFAULT '',
  PRIMARY KEY (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `users` WRITE;
INSERT INTO `users` VALUES ('lisi','*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'),('zhangsan','*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9');
UNLOCK TABLES;

恢复操作

mysql [选项] [库名] [表名] < /备份路径/备份文件名
[root@localhost /]# mysql -u root -p                  ###进入数据库###
Enter password:                                                    ###输入密码####
mysql> show databases;                                      ###查看数据库####
+--------------------+
| Database           |
+--------------------+
| information_schema |
| auth               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> create database text ;                           ####新建数据库text ,等下导表用###
Query OK, 1 row affected (0.00 sec)
mysql> exit                                                      ####退出#####
[root@localhost opt]# mysql -u root -p text < mysql-user.sql 
Enter password: 
[root@localhost /]# mysql -u root -p -e 'show tables from text'    #######验证输出结果####
Enter password:                                                                               ######输入密码
+----------------+
| Tables_in_text |
+----------------+
| user           |
+----------------+

数据库恢复

[root@localhost /]# mysql -u root -p -e 'drop database auth'            ######模拟故障删除auth数据库
Enter password: 
[root@localhost /]# mysql -u root -p -e 'show databases'                  ######查看databases所有数据库有没有auth,发现没有了
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| text               |
+--------------------+
[root@localhost /]# mysql -u root -p < ./auth.sql                             ####导入数据库auth
Enter password:                                                                                  ####输入密码
[root@localhost /]# mysql -u root -p -e 'show databases'                ######查看databases所有数据库有没有auth,发现有了
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| auth               |        ####导进去的auth数据库
| mysql              |
| performance_schema |
| sys                |
| text               |
+--------------------+

三、MySQL 增量备份与恢复

3.1 MySQL 增量备份

使用 mysqldump 进行完全备份存在的问题

  • 备份数据中有重复数据
  • 备份时间与恢复时间过长

是自上一次备份后增加/变化的文件或者内容
特点

  • 没有重复数据,备份量不大,时间短
  • 恢复需要上次完成完全备份及备份之后的所有增量备份才能恢复,而且要对所有增量备份进行逐个反推恢复
[root@localhost mysql]# vim /etc/my.cnf                                               #####编辑my.cnf配置文件
[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysqld]                                                                                                       #####找到这个模块
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1
log-bin=/usr/local/mysql/data/mysql-bin                            ####这个地方开启二进制日志功能

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES
                                      
[root@localhost mysql]# systemctl restart mysqld                                           ####重启数据库
[root@localhost ~]# ll /usr/local/mysql/data/
total 122924
drwxr-x--- 2 mysql mysql       54 Sep 13 23:27 auth
-rw-r----- 1 mysql mysql       56 Sep 13 19:01 auto.cnf
-rw-r----- 1 mysql mysql      342 Sep 14 00:07 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Sep 14 00:07 ibdata1
-rw-r----- 1 mysql mysql 50331648 Sep 14 00:07 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Sep 13 19:01 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Sep 14 00:07 ibtmp1
drwxr-x--- 2 mysql mysql     4096 Sep 13 19:01 mysql
-rw-r----- 1 mysql mysql      154 Sep 14 00:07 mysql-bin.000001                   ####二进制日志文件
-rw-r----- 1 mysql mysql       39 Sep 14 00:07 mysql-bin.index
drwxr-x--- 2 mysql mysql     8192 Sep 13 19:01 performance_schema
drwxr-x--- 2 mysql mysql     8192 Sep 13 19:01 sys
drwxr-x--- 2 mysql mysql       68 Sep 13 23:17 text

3.2 MySQL 数据库增量恢复

(1)一般恢复

  • 将所有备份的二进制日志内容全部恢复

(2)基于位置恢复

  • 数据库在某一时间点可能既有错误的操作也有正确的操作
  • 可以基于精准的位置跳过错误的操作

(3)基于时间点恢复

  • 跳过某个发生错误的时间点实现数据恢复

增量恢复的方法
- 一般恢复

mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -p

- 基于位置恢复
恢复数据到指定位置

mysqlbinlog --stop-position=‘操作 id’ 二进制日志 | mysql -u 用户名 -p 密码

从指定位置开始恢复数据

mysqlbinlog --start-position=‘操作 id’ 二进制日志 | mysql -u 用户名 -p 密码

- 基于时间点的恢复
从日志开头截止到某个时间点的恢复

mysqlbinlog [--no-defaults] --stop-datetime=‘年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码

从某个时间点到日志结尾的恢复

mysqlbinlog [--no-defaults] --start-datetime=‘年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码

从某个时间点到某个时间点的恢复

mysqlbinlog [--no-defaults] --start-datetime=‘年-月-日 小时:分钟:秒’ --stop-datetime=‘年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码

四、MySQL 备份恢复案例

4.1 一般恢复(丢什么数据,找什么数据)

[root@localhost opt]# mysql -u root -p
Enter password: 
mysql> create database client;Query OK, 1 row affected (0.01 sec)
mysql> use client;
mysql> create table user_info(身份证 char(20) not null,姓名 char(20) not null,姓别 char(4),用户ID号 char(10) not null,资费 int(10));
mysql> insert into user_info values('000006','张三','男','016','10');
mysql> insert into user_info values('000007','李四','女','017','91');
mysql> insert into user_info values('000008','王五','女','018','23');
mysql> select * from user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 姓别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   || 016         |     10 |
| 000007    | 李四   || 017         |     91 |
| 000008    | 王五   || 018         |     23 |
+-----------+--------+--------+-------------+--------+
3 rows in set (0.00 sec)
mysql> show master logs;                                    ####查看当前数据库binlog文件
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1492 |
+------------------+-----------+
1 row in set (0.00 sec)

4.1.1 进行一次完全备份

mysql> lock tables user_info read;                        #####备份前需要将数据库加读锁,防止数据在备份时写入。
Query OK, 0 rows affected (0.01 sec)
mysql> exit
By
[root@localhost ~]# mkdir /mysql_bak                       ###创建/mysql_bak 目录
[root@localhost opt]# mysqldump -u root -p client user_info >/mysql_bak/client_userinfo-$(date +%F).sql      ####这边括号中%F 的F必须是大写的
Enter password: 
[root@localhost ~]# ll /mysql_bak/
总用量 4
-rw-r--r-- 1 root root 2055 Sep 14 00:21 client_userinfo-2020-09-14.sql
[root@localhost mysql_bak]# mysqladmin -u root -p flush-logs
[root@localhost ~]#  ll /usr/local/mysql/data/
total 122928
drwxr-x--- 2 mysql mysql       54 Sep 13 23:27 auth
-rw-r----- 1 mysql mysql       56 Sep 13 19:01 auto.cnf
drwxr-x--- 2 mysql mysql       62 Sep 14 00:13 client
-rw-r----- 1 mysql mysql      342 Sep 14 00:07 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Sep 14 00:14 ibdata1
-rw-r----- 1 mysql mysql 50331648 Sep 14 00:14 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Sep 13 19:01 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Sep 14 00:21 ibtmp1
drwxr-x--- 2 mysql mysql     4096 Sep 13 19:01 mysql
-rw-r----- 1 mysql mysql     1539 Sep 14 00:24 mysql-bin.000001
-rw-r----- 1 mysql mysql      154 Sep 14 00:24 mysql-bin.000002
-rw-r----- 1 mysql mysql       78 Sep 14 00:24 mysql-bin.index
drwxr-x--- 2 mysql mysql     8192 Sep 13 19:01 performance_schema
drwxr-x--- 2 mysql mysql     8192 Sep 13 19:01 sys
drwxr-x--- 2 mysql mysql       68 Sep 13 23:17 text

4.1.2 继续录入新的内容并进行增量备份

[root@localhost mysql_bak]# mysql -u root -p
Enter password: 
mysql> unlock tables;             #######解除表锁。
Query OK, 0 rows affected (0.00 sec)
mysql> use client;
mysql> insert into user_info values('000009','赵六','男','019','37');
mysql> insert into user_info values('000010','孙七','男','020','36');
mysql> select * from user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 姓别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   || 016         |     10 |
| 000007    | 李四   || 017         |     91 |
| 000008    | 王五   || 018         |     23 |
| 000009    | 赵六   || 019         |     37 |
| 000010    | 孙七   || 020         |     36 |
+-----------+--------+--------+-------------+--------+
5 rows in set (0.00 sec)
mysql> exit
Bye
[root@localhost mysql_bak]# mysqladmin -u root -p flush-logs
Enter password: 
[root@localhost ~]# ll /usr/local/mysql/data
total 122932
drwxr-x--- 2 mysql mysql       54 Sep 13 23:27 auth
-rw-r----- 1 mysql mysql       56 Sep 13 19:01 auto.cnf
drwxr-x--- 2 mysql mysql       62 Sep 14 00:13 client
-rw-r----- 1 mysql mysql      342 Sep 14 00:07 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Sep 14 00:26 ibdata1
-rw-r----- 1 mysql mysql 50331648 Sep 14 00:26 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Sep 13 19:01 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Sep 14 00:21 ibtmp1
drwxr-x--- 2 mysql mysql     4096 Sep 13 19:01 mysql
-rw-r----- 1 mysql mysql     1539 Sep 14 00:24 mysql-bin.000001
-rw-r----- 1 mysql mysql      797 Sep 14 00:27 mysql-bin.000002
-rw-r----- 1 mysql mysql      154 Sep 14 00:27 mysql-bin.000003
-rw-r----- 1 mysql mysql      117 Sep 14 00:27 mysql-bin.index
drwxr-x--- 2 mysql mysql     8192 Sep 13 19:01 performance_schema
drwxr-x--- 2 mysql mysql     8192 Sep 13 19:01 sys
drwxr-x--- 2 mysql mysql       68 Sep 13 23:17 text
[root@localhost mysql]# cp /usr/local/mysql/data/mysql-bin.000002 /mysql_bak/

4.1.3 模拟误操作删除 user_info表

[root@localhost mysql]# mysql -u root -p -e 'drop table client.user_info'
Enter password: 
[root@localhost mysql]# mysql -u root -p -e 'select * from client.user_info'
Enter password: 
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist

4.1.4 恢复操作

[root@localhost mysql_bak]# mysql -uroot -p client </mysql_bak/client_userinfo-2020-09-14.sql 
Enter password: 
[root@localhost mysql_bak]# mysql -u root -p -e 'select * from client.user_info'
Enter password:
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 姓别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   || 016         |     10 |
| 000007    | 李四   || 017         |     91 |
| 000008    | 王五   || 018         |     23 |
+-----------+--------+--------+-------------+--------+[root@localhost ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 |mysql -u root -p
Enter password: 
[root@localhost ~]#  mysql -u root -p -e'select * from client.user_info'
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 姓别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   || 016         |     10 |
| 000007    | 李四   || 017         |     91 |
| 000008    | 王五   || 018         |     23 |
| 000009    | 赵六   || 019         |     37 |
| 000010    | 孙七   || 020         |     36 |
+-----------+--------+--------+-------------+--------+

4.2 基于位置恢复

[root@localhost mysql_bak]# mysql -uroot -p -e 'drop table client.user_info'    ### 删除表user_info
Enter password: 
[root@localhost mysql_bak]# mysql -uroot -p -e 'select * from client.user_info'
Enter password: 
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist
[root@localhost mysql]# mysql -u root -p client < /mysql_bak/client_userinfo-2020-09-14.sql 
Enter password: 
[root@localhost mysql]# mysql -u root -p -e'select * from client.user_info'
Enter password:+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 姓别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
+-----------+--------+--------+-------------+--------+
[root@localhost mysql]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /mysql_bak/mysql-bin.000002 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200914  0:24:13 server id 1  end_log_pos 123 CRC32 0x240a4a7c 	Start: binlog v 4, server v 5.7.20-log created 200914  0:24:13
# at 123
#200914  0:24:13 server id 1  end_log_pos 154 CRC32 0x9dbc8a3f 	Previous-GTIDs
# [empty]
# at 154
#200914  0:26:38 server id 1  end_log_pos 219 CRC32 0xb6fac0e2 	Anonymous_GTID	last_committed=0 sequence_number=1	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#200914  0:26:38 server id 1  end_log_pos 293 CRC32 0x5ac100f7 	Query	thread_id=7	exec_time=0	error_code=0
SET TIMESTAMP=1600014398/*!*/;
SET @@session.pseudo_thread_id=7/*!*/;
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 293
#200914  0:26:38 server id 1  end_log_pos 359 CRC32 0x08288216 	Table_map: `client`.`user_info` mapped to number 221
# at 359
#200914  0:26:38 server id 1  end_log_pos 421 CRC32 0xbba7b874 	Write_rows: table id 221 flags: STMT_END_F
### INSERT INTO `client`.`user_info`
### SET
###   @1='000009'
###   @2='赵六'
###   @3=''
###   @4='019'
###   @5=37
# at 421
#200914  0:26:38 server id 1  end_log_pos 452 CRC32 0xc076aaaf 	Xid = 52
COMMIT/*!*/;
# at 452
#200914  0:26:46 server id 1  end_log_pos 517 CRC32 0xded7939e 	Anonymous_GTID	last_committed=1 sequence_number=2	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 517
#200914  0:26:46 server id 1  end_log_pos 591 CRC32 0x450bea7d 	Query	thread_id=7	exec_time=0	error_code=0
SET TIMESTAMP=1600014406/*!*/;
BEGIN
/*!*/;
# at 591
#200914  0:26:46 server id 1  end_log_pos 657 CRC32 0x99805aad 	Table_map: `client`.`user_info` mapped to number 221
# at 657
#200914  0:26:46 server id 1  end_log_pos 719 CRC32 0x7f53cb3f 	Write_rows: table id 221 flags: STMT_END_F
### INSERT INTO `client`.`user_info`
### SET
###   @1='000010'
###   @2='孙七'
###   @3=''
###   @4='020'
###   @5=36
# at 719
#200914  0:26:46 server id 1  end_log_pos 750 CRC32 0x2cf9e4dc 	Xid = 53
COMMIT/*!*/;
# at 750
#200914  0:27:36 server id 1  end_log_pos 797 CRC32 0x12ede6cd 	Rotate to mysql-bin.000003  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*/;
[root@localhost ~]# mysqlbinlog --no-defaults --stop-position='517' /mysql_bak/mysql-bin.000002 |mysql -u root -p      ####操作的节点附近即可
[root@localhost ~]# mysql -u root -p -e'select * from client.user_info'
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 姓别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   | 男     | 016         |     10 |
| 000007    | 李四   | 女     | 017         |     91 |
| 000008    | 王五   | 女     | 018         |     23 |
| 000009    | 赵六   | 男     | 019         |     37 |
+-----------+--------+--------+-------------+--------+
[root@localhost ~]# mysql -u root -p -e 'drop table client.user_info'
Enter password: 
[root@localhost mysql]# mysql -u root -p client < /mysql_bak/client_userinfo-2020-09-14.sql 
Enter password: 
[root@localhost ~]# mysqlbinlog --no-defaults --start-position='591' /mysql_bak/mysql-bin.000002 |mysql -u root -p        ####操作的节点附近即可
Enter password: 
[root@localhost ~]# mysql -u root -p -e'select * from client.user_info'
Enter password:
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 姓别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000006    | 张三   || 016         |     10 |
| 000007    | 李四   || 017         |     91 |
| 000008    | 王五   || 018         |     23 |
| 000010    | 孙七   || 020         |     36 |
+-----------+--------+--------+-------------+--------+

总结: 基于位置恢复的操作,节点不要选择太靠近操作的节点,否则容易恢复失败

4.3 基于时间恢复

mysqlbinlog --no-defaults --stop-datetime='2020-9-14 0:26:46' /mysql_bak/mysql-bin.000002 |mysql -u root -p    ##恢复赵六
mysqlbinlog --no-defaults --start-datetime='2020-9-14 0:26:46' /mysql_bak/mysql-bin.000002 |mysql -u root -p    ##恢复孙七

可以参照基于位置恢复

本文地址:https://blog.csdn.net/ZG_66/article/details/108561833