系统学习Linux-MySQL数据库备份（四）

一、概述

数据库备份是指将数据库中的数据、表格、视图、存储过程、触发器等信息备份到另一个地方，一遍在数据库丢失或损坏时进行恢复，数据库备份是数据库管理中必不可少的一项工作，通过备份可以保护数据库中的数据和业务。

二、数据备份的重要性

备份的主要目的是灾难恢复，备份还可以测试应用，回滚数据修改，查询历史数据，审计等。

三、造成数据丢失的原因

程序错误

人为错误

运算失败

磁盘故障

灾难（如火灾、地震）和盗窃等等

四、备份类型

物理与逻辑角度	物理备份	对数据库操作系统的物理文件（如数据文件、日志文件等）的备份
		冷备份	是在关闭数据库的时候进行的
		热备份	数据库处于运行状态，这种备份方法依赖于数据库的日志文件
	逻辑备份	对数据库逻辑组件（如表等数据库对象）的备份，表示为逻辑数据库结构（create database、create table等语句）和内容（insert语句或分割文本文件）的信息

数据库备份策略角度	完整备份	完整备份指的是备份整个数据库的所有信息，包括数据库文件中所有的数据、表、视图、存储过程、触发器等。一般来说，完整备份是备份最全面、可靠的方式，但备份文件体积较大，备份时间也相对较长
	增量备份	增量备份指的是在完整备份的基础上，每隔一定时间备份新增的数据或修改的数据。这中备份方式可以节省备份时间和备份文件的空间，但需要配合特定软件或命令进行

五、常见的备份方法

物理备份	物理冷备份时需要在数据库处于关闭状态下，能够较好的保证数据库的完整性。物理冷备份以用于非核心业务，这类业务都允许中断，物理冷备份的特点就是速度快，恢复时也是最为简单的，通过直接打包数据库文件夹（/usr/local/mysql/data）来实现备份。

使用专用备份工具	mysqldump
	mysqlhotcopy

通过启用二进制日志增量备份

MySQL支持增量备份，进行增量备份时必须启用二进制日志。二进制日志文件为用户提供复制。对执行备份点后进行的数据库更改所需的信息进行备份。如果进行增量备份（包含上次完全备份或增量备份以来发生的数据修改），需要刷新二进制日志。

第三方工具备份

Percona XtraBackup是一个免费的MySQL热备份软件，支持在线备份innodb和XtraDB，也可以支持MySQL表备份，

注意事项	定期备份，指定备份计划，严格遵守
	除了完全备份之外，开启binlog日志功能很重要
	使用统一的、容易理解的备份名称，推荐使用库名或者表名加上时间的命名规则

六、完整备份

打包数据库文件备份	位置	源码包的位置/usr/local/mysql/data/
		rpm包的位置/var/lib/mysql
		在数据库中创建数据表，并写入数据
	备份	在数据库中创建数据表，并写入数据
		停止数据库服务
		创建备份目录
		按照特定格式对数据库目录进行备份 tar  czf  mysql_all-$(date  +%F).tar.gz  /var/lib/mysql/*
	还原	创建还原目录
		模拟元数据丢失
		将解压之后的数据还原值原目录
		登录查看

备份 工具 备份	备份	工具	mysqldump
		对单个库进行完全备份	mysqldump -u用户名 -p[密码] [选项] --databases [数据库名] > /备份路径/备份文件名
		对多个库进行完全备份	mysqldump -u用户名 -p[密码] [选项] --databases 库名1 [库名2]…… > /备份路径/备份文件名
		对所有库进行完全备份	mysqldump -u用户名 -p[密码] [选项] --opt --all-databases > /备份路径/备份文件名
		对表进行完全备份	mysqldump -u用户名 -p[密码] [选项] 数据库名 表名 > /备份路径/备份文件名
		对表的结构进行备份	mysqldump -u用户名 -p[密码] -d 数据库名 表名 > /备份路径/备份文件名
	还原	工具	source
			登录mysql 数据库执行source  备份sql脚本路径
			mysql
			mysql -u用户名 -p[密码] < 库备份脚本的路径
			mysql -u用户名 -p[密码] 库名 < 表备份脚本的路径

七、增量备份

使用完全备份时，备份与恢复的时间过长
增量备份就是备份自上一次备份之后增加的或改变的文件内容
特点	没有重复数据，备份量不大，时间短
	恢复麻烦，需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复
	MySQL没有提供直接的增量备份办法，可以通过MySQL提供的二进制日志（binary logs（binlog））间接实现增量备份
MySQL二进制日志对备份的意义	二进制日志保存了所有更新或者可能更新数据库的操作。
	二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件。
开启二进制日志备份功能	MySQL的配置文件的[mysqld]项中加入 log-bin=文件存放路径/文件前缀，如 log-bin=mysql-bin，然后重启mysqld服务。默认此配置存在。
	使用mysqld --log-bin=文件存放路径/文件前缀 重新启动mysqld服务每周选择服务器负载较轻的时间段，或者用户访问较少的时间段进行备份

增量恢复

应用场景	人为的SQL语句破坏了数据库
	在进行下一次全备之前发生系统故障导致数据库丢失
	在主从架构中，主库数据发生了故障，保证从库数据一致性
方法	一般恢复	备份的二进制日志内容全部恢复
		格式： mysqldbinlog [--no-defaults] 增量备份文件 | 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密码
	基于位置的恢复	可能在同一时间点既有错误的操作也有正确的操作，基于位置进行恢复更加准确
		mysqlbinlog --stop-position=‘操作 id‘ 二进制日志 | mysql -u用户名 -p密码
		mysqlbinlog --start-position=‘操作 id‘ 二进制日志 | mysql -u用户名 -p密码

---

MySQL完整/增量备份与恢复

MySQL安全备份与恢复

1）数据备份的重要性

备份的主要目的是灾难恢复，备份还可以测试应用，回滚数据修改，查询历史数据，审计等。

在企业中数据的价值至关重要，数据保障了企业业务的运行，因此数据的安全性及可靠性是运维的重中之重，任何数据的丢失都有可能会对企业产生严重的后果，造成数据丢失的原因如下：

●   程序错误

●   人为错误

●   运算失败

●   磁盘故障

●   灾难（如火灾、地震）和盗窃

---

2.1）数据库备份的类型

从物理与逻辑的角度

备份可以分为物理备份和逻辑备份

        物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份，物理备份又可分为脱机备份（冷备份）和练级备份（热备份），这种类型的备份使用于出现问题时需要快速恢复的大型重要数据库。

1.冷备份：是在关闭数据库的时候进行的

2.热备份：数据库处于运行状态，这种备份方法依赖于数据库的日志文件

3.温备份：数据库锁定表格（不可写入但可读）的状态下进行的

        逻辑备份：对数据库逻辑组件（如表等数据库对象）的备份，表时为逻辑数据库结构 （create database、 create table等语句）和内容（insert语句或分割文本文件）的信息。这种类型的备份使用于可以编辑数据值或表结构较小的数据量，或者在不同机器体系结构上中心创建数据。

2.2）从数据库的备份策略角度

备份可分为完全备份、差异备份和增量备份

        完全备份：每次对数据进行完整备份，即对整个数据库的备份、数据库结构和文件结构的备份，保证的是备份完成时刻的数据库状态，是差异备份与增量备份的基础。

优点：备份与恢复操作简单方便。

缺点：数据存在大量的重复；占用大量的空间；备份与恢复时间长。

        差异备份：备份那些自从上次完全备份之后修改过的所有文件，备份的时间起点是从上次完整备份起，备份数据量会越来越大。恢复数据时，只需恢复上次的完全备份与最近的一次差异备份。

        增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次的增量备份的时间为时间点，仅备份这之间的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份起到最后一次增量备份一次恢复，如中间某次的备份数据损坏，将导致数据的丢失。

2.3）常见的备份方法

        MySQL数据库的备份可以采用很多种方法，如直接打包数据库文件（物理冷备份），专用备份工具（mysqldump），二进制日志增量备份，第三方工具备份等

物理备份：

物理冷备份时需要在数据库处于关闭状态下，能够较好的保证数据库的完整性。物理冷备份以用于非核心业务，这类业务都允许中断，物理冷备份的特点就是速度快，恢复时也是最为简单的，通过直接打包数据库文件夹（/usr/local/mysql/data）来实现备份

专用备份工具 mysqldump或mysqlhotcopy：

Mysqldump和mysqlhotcopy都可以做备份。Mysqldump时客户端常用逻辑备份程序，能够产生一组被执行以再现原始数据库对象定义和表数据的SQL语句。它可以转储一个到多个MySQL数据库，其进行备份或传输到远程SQL服务器。MySQL更为通用，因为它可以备份各种表。Mysqlhotcopy仅适用于某些存储引擎

Mysqlhotcopy是由Tim Bunce最初编写和贡献的Perl脚。Mysqlhotcopy仅用于备份myISAM和ARCHIVE表。只能运行在Unix或Linux操作系统上

通过启用二进制（binary log， binlog）日志进行增量备份：

MySQL支持增量备份，进行增量备份时必须启用二进制日志。二进制日志文件为用户提供复制。对执行备份点后进行的数据库更改所需的信息进行备份。如果进行增量备份（包含上次完全备份或增量备份以来发生的数据修改），需要刷新二进制日志

通过第三方工具备份

Percona XtraBackup是一个免费的MySQL热备份软件，支持在线备份innodb和XtraDB，也可以支持MySQL表备份，不过MyISAM表的备份要在表锁的情况进行

Percona XtraBackup主要的工具：xtrabackup、innobackupex、xbstream

●  Xtrabackup：是一个编译了的二进制文件，只能备份innodb/Xtradb数据文件

●  Innobackupex：是一个封装了Xtrabackup的Perl脚本，除了可以备份innodb/xtradb之外，还可以备份MyISAM。

●  Xbstream：是一个新的组件，能够允许将文件格式转换成xbstream格式或从xbstream格式转到文件格式。

Xtrabackup工具可以单独使用，但推荐使用Innobackupex来进行备份，因为其本身已

经包含了Xtrabackup的所有功能

Xtrabackup是基于innodb的灾难恢复功能进行设计的，备份工具复制innodb的数据文件，但是由于不锁表，这样复制出来的数据将不一致，innodb维护了一个重要日志，包含innodb数据的所有改动情况。在xtrabackup备份innodb的数据同时，xtrabackup还有另外一个线程用来监控重做日志，一旦日志发生变化，就把发生变化的日志数据复制走。这样就可以利用重做日志做灾难恢复了。

以上是备份过程，如果我们需要恢复数据，则在准备阶段，Xtrabackup就需要使用之前复制的重做日志对备份出来的innodb数据文件进行灾难恢复，此阶段完成之后，数据库就可以进行重建还原了。

Percona Xtrabackup对MyISAM的复制是按顺序进行的，先锁定表，然后复制，再解锁表。

---

3）MySQL完全备份操作

        MySQL数据库的完全备份可以采用多种方式，物理冷备份一般用他人命令直接打包数据库文件（数据目录），而在备份前需要先停库

一、直接打包数据文件

进入mysql

创建实验数据库并指向

创建数据表

添加Rich数据和ID

查看user数据表

[root@localhost beifen]# mysql -uroot -p123456MariaDB [(none)]> create database experiment;
Query OK, 1 row affected (0.00 sec)MariaDB [(none)]> use experiment;
Database changedMariaDB [experiment]> create table user(-> name char(10)not null,-> ID int(48)-> );
Query OK, 0 rows affected (0.01 sec)MariaDB [experiment]> insert into user values('Rich','123');
Query OK, 1 row affected (0.01 sec)MariaDB [experiment]> select * from user;
+------+------+
| name | ID   |
+------+------+
| Rich |  123 |
+------+------+
1 row in set (0.02 sec)MariaDB [experiment]> exit
Bye
[root@localhost beifen]# 

2.模拟数据丢失

指定存储目录 格式bzip2归档

查看刚刚创建的根目录backup 压缩文件存在

创建存储数据文件夹，移动mysql原有所有数据

报错进入不了

[root@localhost /]# tar jcf backup/mysql_all-$(date +%F).tar.gz /var/lib/mysql/*
tar: 从成员名中删除开头的“/”[root@localhost /]# ls /backup
mysql_all-2023-07-28.tar.gz模拟数据丢失
[root@localhost /]# mkdir bak; mv /var/lib/mysql/* bak[root@localhost /]# mysql -uroot -p'123456'
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

3.数据恢复

创建 恢复文件夹

解压备份压缩包文件到restore

移动备份文件到  mysql原配置文件夹里

重启mysql服务

[root@localhost /]# mkdir restore[root@localhost /]# tar xf backup/mysql_all-2023-07-28.tar.gz -C restore/[root@localhost /]# ls restore/
var[root@localhost /]# mv restore/var/lib/mysql/* /var/lib/mysql/[root@localhost /]# systemctl restart mariadb[root@localhost /]# mysql -uroot -p'123456'
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.68-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> 

---

二、使用专用备份工具（mysqldump）

MySQL自带的备份工具mysqldump，可以很方便的对MySQL进行备份。通过该命令工具可以将数据库、数据表或全部的库导出为SQL脚本，便于该命令在不同版本的MySQL服务器上使用。例如，当需要升级MySQL服务器时，可以先使用mysqldump命令将原有库信息导出，然后直接在升级后的MySQL服务器中导入即可。

1.对experiment单个库进行完全备份

退出mysql

格式：mysqldump -u用户名 -p[密码] [选项] --databases [数据库名] > /备份路径/备份文件名

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| asd                |
| experiment         |
| mysql              |
| performance_schema |
| students           |
| sy                 |
| test               |
| userBase           |
+--------------------+
9 rows in set (0.01 sec)
MariaDB [experiment]> exit
Bye
[root@localhost /]# mysqldump -uroot -p'123456' --databases experiment > backup/experiment
[root@localhost /]# ls backup/
experiment  mysql_all-2023-07-28.tar.gz

2.对多个库进行完全备份

格式：mysqldump -u用户名 -p[密码] [选项] --databases 库名1 [库名2]…… > /备份路径/备份文件名

[root@localhost ~]# mysqldump -uroot -p'123456' --databases sy mysql > /backup/mysql_sy
[root@localhost ~]# ls /backup/
experiment    mysql_sy

3.对所有库进行完全备份

格式：mysqldump -u用户名 -p[密码] [选项] --opt --all-databases > /备份路径/备份文件名

[root@localhost ~]# mysqldump -uroot -p123456 --opt --all-databases > backup/mysql_all.$(date +%Y%m%d).sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.[root@localhost ~]# ls backup/
auth-20200730.sql  mysql_all.20200730.sql  mysql_all-2020-07-30.tar.gz  mysql+auth-20200730.sql

4.对表进行完全备份

格式：mysqldump -u用户名 -p[密码] [选项] 数据库名 表名 > /备份路径/备份文件名

[root@localhost ~]# mysqldump -uroot -p123456 --databases auth user > backup/auth_user-$(date +%Y%m%d).sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1049: Unknown database 'user' when selecting the database[root@localhost ~]# ls backup/
auth-20200730.sql       mysql_all.20200730.sql       mysql+auth-20200730.sql
auth_user-20200730.sql  mysql_all-2020-07-30.tar.gz

5.对表结构的备份

格式：mysqldump -u用户名 -p[密码] -d 数据库名 表名 > /备份路径/备份文件名

[root@localhost ~]# mysqldump -uroot -p123456 -d mysql user > backup/desc_mysql_user-$(date +%Y%m%d).sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls backup/
auth-20200730.sql       desc_mysql_user-20200730.sql  mysql_all-2020-07-30.tar.gz
auth_user-20200730.sql  mysql_all.20200730.sql        mysql+auth-20200730.sql

---

五、使用mysqldump备份后，恢复数据库

1.source命令

登录到MySQL数据库，执行source备份sql脚本路径

[root@localhost ~]# mysql -uroot -p123456
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Rich               |
| auth               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)mysql> drop database auth;
Query OK, 1 row affected (0.01 sec)mysql> source backup/auth-20200730.sqlmysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Rich               |
| auth               |
| mysql              |
| performance_schema |
| sys                |	
+--------------------+
6 rows in set (0.00 sec)

2.mysql命令

格式：mysql -u用户名 -p[密码] < 库备份脚本的路径

mysql -u用户名 -p[密码] 库名 < 表备份脚本的路径

[root@localhost ~]# mysql -uroot -p123456 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Rich               |
| auth               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@localhost ~]# mysql -uroot -p123456 -e 'drop database auth;'
[root@localhost ~]# mysql -uroot -p123456 < backup/auth-20200730.sql 
[root@localhost ~]# mysql -uroot -p123456 -e 'show databases;'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Rich               |
| auth               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

六、MySQL备份思路

1、定期实施备份，指定备份计划或策略，并严格遵守

2、除了进行完全备份，开启MySQL服务器的binlog日志功能是很重要的（完全备份加上日志，可以对MySQL进行最大化还原）

3、使用统一和易理解的备份名称，推荐使用库名或者表名加上时间的命名规则，如mysql_user-20200430.sql，不要使用backup1或者abc之类没有意义的名字。

---

MySQL增量备份与恢复

一、MySQL增量备份概念

使用mysqldump进行完全备份，备份的数据中有重复数据，备份时间与恢复时间过长。而增量备份就是备份自上一次备份之后增量或改变的文件或内容。

增量备份的特点：

没有重复数据，备份量不大，时间短

恢复麻烦：需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复。

MySQL没有提供直接的增量备份办法，可以通过MySQL提供的二进制日志（binary logs（binlog））间接实现增量备份。

MySQL二进制日志对备份的意义：

二进制日志保存了所有更新或者可能更新数据库的操作。

二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件。

[root@localhost ~]# vim /etc/my.cnf
max_binlog_size = 1024000		#二进制日志最大1M

只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份。

要进行MySQL的增量备份，首先要开启二进制日志功能，开启MySQL的二进制日志功能。

方法一：MySQL的配置文件的[mysqld]项中加入 log-bin=文件存放路径/文件前缀，如 log-bin=mysql-bin，然后重启mysqld服务。默认此配置存在。

[root@localhost ~]# awk /log-bin/'{print NR,$0}' /etc/my.cnf
[mysqld]
server-id=1
log-bin=mysql-bin

方法二：使用mysqld --log-bin=文件存放路径/文件前缀

重新启动mysqld服务每周选择服务器负载较轻的时间段，或者用户访问较少的时间段进行备份。

二、MySQL增量恢复

应用场景

1. 人为的SQL语句破坏了数据库

2. 在进行下一次全备之前发生系统故障导致数据库丢失

3. 在主从架构中，主库数据发生了故障，保证从库数据一致性

增量恢复的方法

1. 一般的恢复：备份的二进制日志内容全部恢复

格式： mysqldbinlog [--no-defaults] 增量备份文件 | mysql -u用户名 -p密码

2. 基于时间点的恢复：便于跳过某个发生错误的时间点实现数据恢复

格式：从日志开头截止到某个时间点的恢复：

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密码

3. 基于位置的恢复：

可能在同一时间点既有错误的操作也有正确的操作，基于位置进行恢复更加准确

格式：

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

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

三、制定企业备份策略的思路

1. 确定当前mysql是处于哪种表类型下工作的，它们支持事物处理还是非事务的，因为我们需要根据不同的特点来做一些设置。

2. 要选择备份的形式是完全备份还是增量备份，它们各有优缺点

3. 为了保证恢复的完整性，我们得开启binary log功能，同时binlog给恢复工作也带来了很大的灵活性，可以基于时间或是位置进行恢复。考虑到数据库性能，我们可以将binlog文件保存到其他安全的硬盘中。

4. 正如最初所提到的，备份操作和应用服务同时运行这样就十分消耗系统资源了，会导致数据库服务性能下降，这就要求我们选择一个合适的时间（比如在应用负担很小的时候）再来进行备份操作。

5. 不是备份完就完事大吉，我们还得确认备份是否可用，所以之后的恢复测试是完全有必要的。

1. 根据数据更新频繁，则应该较为频繁的备份
2. 数据重要，则在有适当更新时进行备份
3. 在数据库压力小的时刻进行备份，如一周一次完全备份，然后每天进行增量备份
4. 中小公司，全备一般可一天一次
5. 大公司可每周进行一次全备，每天进行一次增量备份
6. 尽量为企业实现主从复制架构

---

四、MySQL企业备份案例

需求描述：

北京移电通信公司的用户信息数据库为client，用户资费数据表为user_info

请为该公司每周进行完全备份

每天为该公司进行增量备份

新增加的用户信息如表所示

---

安装mysql（yum 方式）

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
character-set-server=utf8对于中文支持
MariaDB [(none)]> show variables like 'character_set_%';
[root@localhost ~]# /etc/init.d/mysqld start

添加数据库、表，录入数据

[root@localhost ~]# mysql -uroot -p123456
mysql> create database client;
Query OK, 1 row affected (0.00 sec)mysql> use client;
Database changed
mysql> create table user_info(身份证 char(20) not null,姓名 char(20) not null,性别 char(4),用户ID号 char(10) not null,资费 int(10)) DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)mysql> insert into user_info values('000000006','张三','男','016','10');
mysql> insert into user_info values('000000007','李四','女','017','91');
mysql> insert into user_info values('000000008','王五','女','018','23');mysql> select * from user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000000006 | 张三   | 男     | 016         |     10 |
| 000000007 | 李四   | 女     | 017         |     91 |
| 000000008 | 王五   | 女     | 018         |     23 |
+-----------+--------+--------+-------------+--------+
3 rows in set (0.00 sec)

先进行一次完全备份

[root@localhost ~]# mkdir /mysql_bak
[root@localhost ~]# mysqldump -uroot -p123456 client user_info > /mysql_bak/client_userinfo-$(date +%F).sql
[root@localhost ~]# mysqldump -uroot -p123456 --databases client > /mysql_bak/client-$(date +%F).sql
[root@localhost ~]# ls /mysql_bak/
client-2020-07-30.sql  client_userinfo-2020-07-30.sql[root@localhost ~]# vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=mysql-bin[root@localhost ~]# /etc/init.d/mysqld restart

进行一次日志回滚（生成新的二进制日志）

[root@localhost ~]# ls /usr/local/mysql/data/
auto.cnf        ibdata1      ibtmp1                     mysql-bin.000001  performance_schema
client          ib_logfile0  localhost.localdomain.pid  mysql-bin.index   sys
ib_buffer_pool  ib_logfile1  mysql                      mysql.log
[root@localhost ~]# mysqladmin -uroot -p123456 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls /usr/local/mysql/data/
auto.cnf        ibdata1      ibtmp1                     mysql-bin.000001  mysql.log
client          ib_logfile0  localhost.localdomain.pid  mysql-bin.000002  performance_schema
ib_buffer_pool  ib_logfile1  mysql                      mysql-bin.index   sys

继续录入新的数据

[root@localhost ~]# mysql -uroot -p123456
mysql> use client;
mysql> insert into user_info values('000000009','赵六','男','019','37');
mysql> insert into user_info values('000000010','孙七','男','020','36');
mysql> select * from user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000000006 | 张三   | 男     | 016         |     10 |
| 000000007 | 李四   | 女     | 017         |     91 |
| 000000008 | 王五   | 女     | 018         |     23 |
| 000000009 | 赵六   | 男     | 019         |     37 |
| 000000010 | 孙七   | 男     | 020         |     36 |
+-----------+--------+--------+-------------+--------+
5 rows in set (0.00 sec)

进行增量备份

[root@localhost ~]# mysqladmin -uroot -p123456 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls /usr/local/mysql/data/
auto.cnf        ibdata1      ibtmp1                     mysql-bin.000001  mysql-bin.index     sys
client          ib_logfile0  localhost.localdomain.pid  mysql-bin.000002  mysql.log
ib_buffer_pool  ib_logfile1  mysql                      mysql-bin.000003  performance_schema[root@localhost ~]# mv /usr/local/mysql/data/mysql-bin.000002 /mysql_bak/
[root@localhost ~]# mysqlbinlog -v /mysql_bak/mysql-bin.000002#查看新操作的日志记录

模拟误操作删除user_info表

mysql> drop table client.user_info;
Query OK, 0 rows affected (0.01 sec)mysql> select * from client.user_info;
ERROR 1146 (42S02): Table 'client.user_info' doesn't exist

恢复完全备份

[root@localhost ~]# mysql -uroot -p123456 client < /mysql_bak/client_userinfo-2020-07-30.sqlmysql> select * from client.user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000000006 | 张三   | 男     | 016         |     10 |
| 000000007 | 李四   | 女     | 017         |     91 |
| 000000008 | 王五   | 女     | 018         |     23 |
+-----------+--------+--------+-------------+--------+
3 rows in set (0.00 sec)

恢复增量备份

[root@localhost ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 | mysql -uroot -p123456mysql> select * from client.user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000000006 | 张三   | 男     | 016         |     10 |
| 000000007 | 李四   | 女     | 017         |     91 |
| 000000008 | 王五   | 女     | 018         |     23 |
| 000000009 | 赵六   | 男     | 019         |     37 |
| 000000010 | 孙七   | 男     | 020         |     36 |
+-----------+--------+--------+-------------+--------+
5 rows in set (0.00 sec)

基于时间点的增量备份恢复

mysql> drop table client.user_info;
mysql> select * from client.user_info;
ERROR 1146 (42S02): Table 'client.user_info' doesn't exist[root@localhost ~]# mysql -uroot -p123456 client < /mysql_bak/client_userinfo-2020-07-30.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.[root@localhost ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 -v
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200730 16:10:02 server id 1  end_log_pos 123 CRC32 0x7d9089a5 	Start: binlog v 4, server v 5.7.24-log created 200730 16:10:02
BINLOG '
WoAiXw8BAAAAdwAAAHsAAAAAAAQANS43LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AaWJkH0=
'/*!*/;
# at 123
#200730 16:10:02 server id 1  end_log_pos 154 CRC32 0x5b1f353d 	Previous-GTIDs
# [empty]
# at 154
#200730 16:14:26 server id 1  end_log_pos 219 CRC32 0x2814ab60 	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
#200730 16:14:26 server id 1  end_log_pos 293 CRC32 0x5db0af10 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1596096866/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
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=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 293
#200730 16:14:26 server id 1  end_log_pos 359 CRC32 0x39d532e6 	Table_map: `client`.`user_info` mapped to number 108
# at 359
#200730 16:14:26 server id 1  end_log_pos 424 CRC32 0xdb25adb2 	Write_rows: table id 108 flags: STMT_END_FBINLOG '
YoEiXxMBAAAAQgAAAGcBAAAAAGwAAAAAAAEABmNsaWVudAAJdXNlcl9pbmZvAAX+/v7+Awj+PP48
/gz+HhTmMtU5
YoEiXx4BAAAAQQAAAKgBAAAAAGwAAAAAAAEAAgAF/+AJMDAwMDAwMDA5Bui1teWFrQPnlLcDMDE5
JQAAALKtJds=
'/*!*/;
### INSERT INTO `client`.`user_info`
### SET
###   @1='000000009'
###   @2='赵六'
###   @3='男'
###   @4='019'
###   @5=37
# at 424
#200730 16:14:26 server id 1  end_log_pos 455 CRC32 0xd3cd7cbc 	Xid = 10
COMMIT/*!*/;
# at 455
#200730 16:14:52 server id 1  end_log_pos 520 CRC32 0x7aefdd68 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 520
#200730 16:14:52 server id 1  end_log_pos 594 CRC32 0x628d89c4 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1596096892/*!*/;
BEGIN
/*!*/;
# at 594
#200730 16:14:52 server id 1  end_log_pos 660 CRC32 0x5ec8e443 	Table_map: `client`.`user_info` mapped to number 108
# at 660
#200730 16:14:52 server id 1  end_log_pos 725 CRC32 0x7301c8fd 	Write_rows: table id 108 flags: STMT_END_FBINLOG '
fIEiXxMBAAAAQgAAAJQCAAAAAGwAAAAAAAEABmNsaWVudAAJdXNlcl9pbmZvAAX+/v7+Awj+PP48
/gz+HhRD5Mhe
fIEiXx4BAAAAQQAAANUCAAAAAGwAAAAAAAEAAgAF/+AJMDAwMDAwMDEwBuWtmeS4gwPnlLcDMDIw
JAAAAP3IAXM=
'/*!*/;
### INSERT INTO `client`.`user_info`
### SET
###   @1='000000010'
###   @2='孙七'
###   @3='男'
###   @4='020'
###   @5=36
# at 725
#200730 16:14:52 server id 1  end_log_pos 756 CRC32 0x55b5519b 	Xid = 11
COMMIT/*!*/;
# at 756
#200730 16:16:35 server id 1  end_log_pos 803 CRC32 0x4dfaca3f 	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-datetime="2020-07-30 16:14:52" /mysql_bak/mysql-bin.000002 | mysql -uroot -p123456mysql> select * from client.user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000000006 | 张三   | 男     | 016         |     10 |
| 000000007 | 李四   | 女     | 017         |     91 |
| 000000008 | 王五   | 女     | 018         |     23 |
| 000000009 | 赵六   | 男     | 019         |     37 |
+-----------+--------+--------+-------------+--------+
4 rows in set (0.00 sec)只恢复孙七，不恢复赵六：
mysql> drop table client.user_info;[root@localhost ~]# mysql -uroot -p123456 client < /mysql_bak/client_userinfo-2020-07-30.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysqlbinlog --no-defaults --start-datetime="2020-07-30 16:14:52" /mysql_bak/mysql-bin.000002 | mysql -uroot -p123456mysql> select * from client.user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000000006 | 张三   | 男     | 016         |     10 |
| 000000007 | 李四   | 女     | 017         |     91 |
| 000000008 | 王五   | 女     | 018         |     23 |
| 000000010 | 孙七   | 男     | 020         |     36 |
+-----------+--------+--------+-------------+--------+
4 rows in set (0.00 sec)

基于位置的恢复

mysql> drop table client.user_info;[root@localhost ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 -v
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200730 16:10:02 server id 1  end_log_pos 123 CRC32 0x7d9089a5 	Start: binlog v 4, server v 5.7.24-log created 200730 16:10:02
BINLOG '
WoAiXw8BAAAAdwAAAHsAAAAAAAQANS43LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AaWJkH0=
'/*!*/;
# at 123
#200730 16:10:02 server id 1  end_log_pos 154 CRC32 0x5b1f353d 	Previous-GTIDs
# [empty]
# at 154
#200730 16:14:26 server id 1  end_log_pos 219 CRC32 0x2814ab60 	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
#200730 16:14:26 server id 1  end_log_pos 293 CRC32 0x5db0af10 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1596096866/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
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=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 293
#200730 16:14:26 server id 1  end_log_pos 359 CRC32 0x39d532e6 	Table_map: `client`.`user_info` mapped to number 108
# at 359
#200730 16:14:26 server id 1  end_log_pos 424 CRC32 0xdb25adb2 	Write_rows: table id 108 flags: STMT_END_FBINLOG '
YoEiXxMBAAAAQgAAAGcBAAAAAGwAAAAAAAEABmNsaWVudAAJdXNlcl9pbmZvAAX+/v7+Awj+PP48
/gz+HhTmMtU5
YoEiXx4BAAAAQQAAAKgBAAAAAGwAAAAAAAEAAgAF/+AJMDAwMDAwMDA5Bui1teWFrQPnlLcDMDE5
JQAAALKtJds=
'/*!*/;
### INSERT INTO `client`.`user_info`
### SET
###   @1='000000009'
###   @2='赵六'
###   @3='男'
###   @4='019'
###   @5=37
# at 424
#200730 16:14:26 server id 1  end_log_pos 455 CRC32 0xd3cd7cbc 	Xid = 10
COMMIT/*!*/;
# at 455
#200730 16:14:52 server id 1  end_log_pos 520 CRC32 0x7aefdd68 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 520
#200730 16:14:52 server id 1  end_log_pos 594 CRC32 0x628d89c4 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1596096892/*!*/;
BEGIN
/*!*/;
# at 594
#200730 16:14:52 server id 1  end_log_pos 660 CRC32 0x5ec8e443 	Table_map: `client`.`user_info` mapped to number 108
# at 660
#200730 16:14:52 server id 1  end_log_pos 725 CRC32 0x7301c8fd 	Write_rows: table id 108 flags: STMT_END_FBINLOG '
fIEiXxMBAAAAQgAAAJQCAAAAAGwAAAAAAAEABmNsaWVudAAJdXNlcl9pbmZvAAX+/v7+Awj+PP48
/gz+HhRD5Mhe
fIEiXx4BAAAAQQAAANUCAAAAAGwAAAAAAAEAAgAF/+AJMDAwMDAwMDEwBuWtmeS4gwPnlLcDMDIw
JAAAAP3IAXM=
'/*!*/;
### INSERT INTO `client`.`user_info`
### SET
###   @1='000000010'
###   @2='孙七'
###   @3='男'
###   @4='020'
###   @5=36
# at 725
#200730 16:14:52 server id 1  end_log_pos 756 CRC32 0x55b5519b 	Xid = 11
COMMIT/*!*/;
# at 756
#200730 16:16:35 server id 1  end_log_pos 803 CRC32 0x4dfaca3f 	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 ~]# mysql -uroot -p123456 client < /mysql_bak/client_userinfo-2020-07-30.sql
[root@localhost ~]# mysqlbinlog --no-defaults --stop-position='520' /mysql_bak/mysql-bin.000002 | mysql -uroot -p123456mysql> select * from client.user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000000006 | 张三   | 男     | 016         |     10 |
| 000000007 | 李四   | 女     | 017         |     91 |
| 000000008 | 王五   | 女     | 018         |     23 |
| 000000009 | 赵六   | 男     | 019         |     37 |
+-----------+--------+--------+-------------+--------+
4 rows in set (0.00 sec)只恢复孙七，不恢复赵六：
mysql> drop table client.user_info;[root@localhost ~]# mysql -uroot -p123456 client < /mysql_bak/client_userinfo-2020-07-30.sql
[root@localhost ~]# mysqlbinlog --no-defaults --start-position='520' /mysql_bak/mysql-bin.000002 | mysql -uroot -p123456mysql> select * from client.user_info;
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户ID号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000000006 | 张三   | 男     | 016         |     10 |
| 000000007 | 李四   | 女     | 017         |     91 |
| 000000008 | 王五   | 女     | 018         |     23 |
| 000000010 | 孙七   | 男     | 020         |     36 |
+-----------+--------+--------+-------------+--------+
4 rows in set (0.00 sec)