【MySQL】4.MySQL日志管理与数据库的备份和恢复

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

日志在备份、恢复中起着重要作用

一、数据库备份的重要性

在生产环境中，数据的安全性至关重要
任何数据丢失都可能产生严重的后果

造成数据丢失的原因有：
程序错误
人为操作错误
运算错误
磁盘错误
灾难（如火灾、地震）和盗窃

容灾

二、数据库备份的分类

数据库备份可以分为物理备份和逻辑备份

1.物理备份：

对数据库操作系统的物理文件（如：数据文件、日志文件等）进行备份；适用于在出现问题的时候需要快速恢复的大型重要数据库

物理备份又分为：

冷备份（脱机备份）：需要关闭数据库才能进行tar打包备份

热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件（mysqlhotcopy mysqlbackup）

温备份：数据库锁定表结构（不可写入但可读）的状态下进行备份操作（mysqldump）

2.逻辑备份：

逻辑备份是对数据库逻辑组件的备份，表示为逻辑数据库结构；这种类型的备份适用于可以编辑数据值或表结构

从数据库的备份策略来看，逻辑备份又分为完全备份、差异备份和增量备份

①完全备份

每次都对整个数据库进行完整的备份（包括数据库结构和文件结构）；
缺点：有大量重复数据，占用磁盘空间最大；
优点：结构完整，恢复时速度快，直接把文件导入即可；

②差异备份

备份自从上次完全备份之后修改过的文件；后续的差异备份都会备份前面差异备份的内容，导致出现重复的数据，占用额外的磁盘空间；恢复时会先恢复完全备份，再导入最后一次差异备份的数据即可；

③增量备份

只有在上次完全备份或者增量备份后 被修改的数据(或文件)才会被备份；不会出现重复数据(导致额外占用磁盘空间)；恢复时要先恢复完全备份，再按次序恢复增量备份

备份方式的比较

备份方式          完全备份            差异备份      增量备份
完全备份时的状态  表1、表2             表1、表2      表1、表2
第1次添加内容     创建表3              创建表3       创建表3
备份内容          表1、表2、表3        表3           表3
第2次添加内容     创建表4              创建表4       创建表4
备份内容          表1、表2、表3、表4   表3、表4      表4

逻辑备份的策略

逻辑备份策略的选择（频率）

一周一次进行完全备份，全备的时间需要再不提供业务或访问量少的时间区间进行，例：PM 10:00 到AM 5:00（看你公司情况）
增量备份：3天/2天/1天 一次增量备份
差异备份：选择特定的场景进行备份（比较少用到）

通过配置NFS可以提供额外的空间给mysql服务器使用；NFS严格上说不算是共享存储，知识共享了文件；

三、常见的备份方法

1.物理冷备

备份时，数据库要处于关闭状态，直接打包数据库文件；备份速度快，恢复也简单

2.专用备份工具mysqldump和mysqlhotcopy

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

3.启用二进制日志进行增量备份

进行增量备份时，需要刷新二进制文件

4.第三方工具备份

Navicat、免费的MySQL热备份软件Percona XtraBackup

四、数据库完全备份分类

是对整个数据库、数据库结构和文件结构的备份；保存的是备份完成时刻的数据库；是差异备份和增量备份的基础
优点：备份和恢复操作简单方便
缺点：数据大量重复，占用额外的磁盘空间，备份与恢复时等待时间长

物理冷备与恢复

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

mysqldump备份与回复

MySQL自带的备份工具，可以方便实现对MySQL的备份
可以见指定的库、表导出为SQL脚本文件
恢复：使用命令导入备份的数据

实验

环境准备

建库、建表，插入数据；

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

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
| test1          |
+----------------+
2 rows in set (0.00 sec)

mysql> select * from test;
+------+----------+-------+--------------+
| id   | name     | score | address      |
+------+----------+-------+--------------+
| 0001 | zhangsan |   100 | 地址不详     |
| 0002 | lisi     |    85 | bijing       |
| 0003 | wangwu   |    91 | 地球村       |
| 0004 | laoliu   |    75 | nanjing      |
+------+----------+-------+--------------+
4 rows in set (0.00 sec)

mysql> select * from test1;
+------+----------+-------+-------+
| id   | name     | score | phone |
+------+----------+-------+-------+
| 0001 | zhangsan | 82.50 |   111 |
| 0002 | lisi     | 85.00 |   222 |
| 0003 | wangwu   | 91.00 |   333 |
+------+----------+-------+-------+
3 rows in set (0.00 sec)

mysql> 

mysql完全备份与恢复

InnoDB 存储引擎的数据库在磁盘上存储成三个文件:

db.opt(表属性文件)
表名.frm(表结构文件)
表名.ibd(表数据文件)

1.物理冷备与恢复

第一步关闭mysqld

tar zxvf 

直接备份指定路径的文件，不包括路径

tar jxvf
按路径备份，会将整个路劲备份下来，但不会备份路径中其他的数据

第一种 zxvf

冷备

#关闭mysqld
systemctl stop mysqld
#
[root@test2 mysql]# cd /usr/local/mysql/
[root@test2 mysql]# tar zcvf data.tar.gz data/ -C /opt/mysql.bak/

恢复

第二种 jxvf

关闭mysql，安装 xz 压缩

systemctl stop mysqld
yum -y install xz

压缩备份

tar jcvf /opt/mysql_data_$(date +%F).tar.xz /usr/local/mysql/data/

#j    表示按路径备份(打包)数据

解压恢复

[root@test2 ~]# tar jxvf /opt/mysql_data_.tar.xz -C /usr/local/mysql/

[root@test2 ~]# cd /usr/local/mysql/

[root@test2 mysql]# mv usr/local/mysql/data /usr/local/mysql/

2.mysqldump备份与恢复

完全备份

建库、建表，插入数据

create database school;
use school;
create table class (id int(4) zerofill not null,name char(10),score decimal(5,2),address varchar(80));
insert into class values (1,'yiyi',78,'shanghai');
insert into class values (2,'lizi',92,'henan');
insert into class values (3,'monor',88,'nanjing');
select * from class;

完成后，quit退出MySQL

1）完全备份一个或多个完整的库（包括其中所有的表）

备份多个表
mysqldump -u用户名 -p密码 --databases 库1 库2 > 备份路径/备份文件名.sql

例：

[root@test2 ~]# mysqldump -uroot -p123456 --databases school test > /opt/mysql.bak/school.test_$(date +%F).sql

备份成功后可以通过 vim 来查看备份表的内容
[root@test2 ~]# vim /opt/mysql.bak/school.test_2024-03-25.sql

删除数据库

备份恢复

[root@test2 ~]# mysql -uroot -p123456 < /opt/mysql.bak/school.test_2024-03-25.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.

查看数据库和表

使用 -e 可以在bash环境中使用sql语句；

ps：报错

[root@test2 data]# mysql -uroot -p123456 < /opt/mysql.bak/school.test_2024-03-25.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1030 (HY000) at line 66: Got error 168 from storage engine


1.检查mysql用户是否有数据目录的读写权限
2.或导入的数据库引擎不匹配（把报错行下的引擎改为InnoDB或MyISAM试试）

另一种情况
df -h看一下磁盘空间是否有余，
若是不足的话        show variables like '%log';    查看general_log状态
关闭日志记录模式    set global general_log = OFF
清理日志            echo "" > mysql.log
再开启日志          set global general_log = OFF

2）完全备份MySQL服务器中所有库

mysqldump -u用户名 -p密码 --all-databases > /备份路径/备份文件名.sql

例

mysqldump -uroot -p123456 --all-databases > /opt/mysql.bak/all.$(date +%F).sql

 

3）完全备份指定库中的部份表

mysqldump -u用户名 -p密码 库名 表名1 表名2 > 备份路径/备份文件名.sql

例：

mysqldump -uroot -p123456 test test class > /opt/mysql.bak/table.test.class.$(date +%F).sql

保存数据库的表结构

#使用"-d"或"-t"选项，表示只保存数据库的表结构
#不使用"-d"或"-t"选项，说明表数据也要进行备份
#可以用来当作一个表结构模板

4）查看备份文件

grep -v "^--" 备份文件名| grep -v "^/" | grep -v "^$"

#-v "^$"    表示非空行

例：

grep -v "^--" /opt/mysql.bak/school.test_2024-03-25.sql| grep -v "^/" | grep -v "^$"

mysql完全恢复（有问题，搞不懂，重做）

恢复数据库

1.使用mysqldump导出的文件，可导入的方法有：

source命令
mysql命令

2.使用source 恢复数据库的步骤

登录mysql数据库

执行source备份sql脚本的路径

3.source 恢复的示例

#模拟故障（删除数据库）
[root@test2 mysql.bak]# mysql -uroot -pabc123456 登录数据库
mysql> show databases;  查看数据库信息
mysql> drop database school;  删除数据库school
mysql> show databases;

使用 source 命令恢复数据 

#进入mysql后使用
mysql> source /all-data.sql

方式一：使用source命令恢复

创建备份

#备份class表
[root@test2 ~]# mysqldump -uroot -p123456 school class > /opt/class.sql

模拟故障(删除)

#登录数据库查看
[root@test2 ~]# mysql -uroot -p123456
#删除数据库的表
[root@test2 ~]# mysql -uroot -p123456 -e 'drop table school.class;

恢复数据表

#恢复表
mysql> select * from info;      查询所有字段
mysql> show tables;     查看表信息
mysql> source /opt/class.sql     恢复表

# -e 在数据库外面调用mysql命令
mysql -uroot -p123456 -e 'show tables from school;

方式二：使用MySQL命令恢复

#恢复class表
mysql -uroot -p123456 school < /opt/class.sql
#查看class表
mysql -uroot -p123456 school -e 'show tables from school;'

mysql命令恢复介绍

PS:mysqldump 严格来说属于温备份，会需要对表进行写入的锁定
在全量备份与恢复实验中，假设现有school库，school库中有一个class表，需要注意的一点为：

① 当备份时加 --databases ,表示针对于school库

#备份命令

mysqldump -uroot -p123456 --databases school > /opt/school.sql

#恢复命令过程为：

mysql -uroot -p123456
mysql> drop database school;
mysql> exit
mysql -uroot -p12356 < /opt/school.sql

② 当备份时不加 --databases，表示针对test库下的所有表

#备份命令

#只会复制库下的所有表，不会复制库，所以恢复时要确保库存在（没有就新建）
mysqldump -uroot -p123456 test > /opt/test.sql

#恢复过程：

mysql -uroot -p123456
mysql> drop database test;

#恢复前需要创建库
mysql> create database test;
mysql> exit
#恢复时需要指定库名
mysql -uroot -p123456 test < /opt/test.sql 

#查看school.sql 和test.sql 
主要原因在于两种方式的备份（前者会从"create databases"开始,而后者则全是针对表格进行操作） 

自动化备份

0 1 * * 6 /usr/local/mysql/data/ mysqldump -uroot -p123456 库1 库2 > /opt/备份名$(date +%Y%m%d).sql; /usr/local/mysql/bin/mysqladmin -uroot -p123456 flush-logs

MySQL 增量备份与恢复

MySQL数据库增量恢复

1.一般恢复

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

2.基于位置恢复

数据库在某一时间点可能既有错误的操作也有正确的操作
可以基于精准的位置跳过错误的操作
发生错误节点之前的一个节点，上一次正确操作的位置点停止

3.基于时间点恢复

跳过某个发生错误的时间点实现数据恢复
在错误时间点停止，在下一个正确时间点开始

MySQL增量备份

一、增备实验

1.开启二进制日志功能

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
#可选，指定二进制日志(binlog)的记录格式为MIXED（混合输入）
binlog_format = MIXED
#可加可不加该命令
server-id = 1

二进制日志(binlog)有3种不同的记录格式:

statement (基于SQL语句)、row(基于行)、mixed(混合模式)，默认格式是statement

[root@test2 ~]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
#可选，指定二进制日志(binlog)的记录格式为MIXED（混合输入）
binlog_format = MIXED
#可加可不加该命令
server-id = 1

①statement（基于sql语句）：

每一条涉及到被修改的sql都会记录在binlog中；
缺点：日志量大；如：sleep()函数，主从复制等架构记录日志时会出现问题
总结：增删改查通过sql语句来实现记录，高并发时会出错(有时间差异和延迟)，记录的数据不一定会按顺序记录；准确率低

②row（基于行）

只记录变动的记录，不记录sql的上下文环境
缺点：如遇到update、set、wehere true那么binlog的数据量会越来越大
总结：update、delete等以多行数据起作用，被记录下来时；只会记录变动的记录，不记录sql的上下文环境；比如sql语句记录一行，但是row就很可能记录10行，准确性高；但高并发时由于操作量大，所有记录都被记录下来，性能会变低

③mixde 混合；推荐使用

一般的语句使用statement ，函数使用row方式存储

systemctl restart mysqld

2.查看二进制文件

cp /usr/local/mysql/data/mysql-bin.000002 /opt/

① mysqlbinlog --no-defaults  /opt/mysql-bin.000002

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002

# --base64-output=decode-rows：使用64位编码机制去解码（decode）并按行读取（rows）
# -v: 显示详细内容
# --no-defaults : 默认字符集（不加会报UTF-8的错误）
PS: 可以将解码后的文件导出为txt格式，方便查阅
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002 > /opt/mysql-bin.000002

二进制日志中需要关注的部分

开始的位置点    at
结束的位置点    end_log_pos
时间戳          210712 11:50:30
SQL语句

2.进行完全备份

增量备份是基于完全备份的，所以需要先完全备份一次数据库

mysqldump -uroot -p school class > /opt/school.class.$(date +%F).sql
mysqldump -uroot -p123456 school > /opt/school.all.$(date +%F).sql

3.可每天进行增量备份操作，生成新的二进制日志文件002

mysqladmin -uroot -p123456 flush-logs

4.插入新数据，模拟数据的增加或变更

ps：在第一次完全备份之后刷新二进制文件，在第二个二进制文件中记载着“增量备份的数据”

建库、建表、添加数据

mysql> create database school;
Query OK, 1 row affected (0.00 sec)

mysql> use school;
Database changed
mysql> create table test1 (id int(4),name varchar(4));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values(1,'one');
Query OK, 1 row affected (0.00 sec)



mysql> insert into test1 values(2,'two');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+------+------+
| id   | name |
+------+------+
|    1 | one  |
|    2 | two  |
+------+------+
2 rows in set (0.00 sec)

5.再次生成新的二进制日志文件003

mysqladmin -uroot -p123456 flush-logs

#之前的步骤4的数据库操作会保存到mysql-bin.000002文件中，之后我们测试删除school库的操作会保存在mysql-bin.000003文件中 （以免当我们基于mysql-bin.000002日志进行恢复时，依然会删除库）

MySQL增量恢复

一、一般恢复

1、模拟丢失更改的数据库的就恢复步骤（直接使用恢复即可）

① 备份school库中test1表
mysqldump -uroot -p123456 school test1 > /opt/school_test1.sql
② 删除school库中test1表
drop table school.test1;
③ 恢复test1表
mysql -uroot -p school < school_test1.sql

#查看日志文件

[root@test2 data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002

2、模拟丢失所有数据的恢复步骤

① 模拟丢失所有数据

[root@test2 data]# mysql -uroot -p123456
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
| test               |
+--------------------+
7 rows in set (0.00 sec)

mysql> drop database school;
Query OK, 1 row affected (0.00 sec)

mysql> exit

② 基于mysql-bin.000002恢复

mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p

二、断点恢复

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002

例:
# at 302
#201122 16:41:16
插入了"user3"的用户数据

# at 623
#201122 16:41:24 
插入了"user4"的用户数据

1、基于位置恢复

① 插入三条数据

mysql> use school;

mysql> select * from test1;
+------+------+
| id   | name |
+------+------+
|    1 | one  |
|    2 | two  |
+------+------+
2 rows in set (0.00 sec)

mysql> insert into test1 values(3,'true');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test1 values(4,'f');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test1 values(5,'t');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+------+------+
| id   | name |
+------+------+
|    1 | one  |
|    2 | two  |
|    3 | true |
|    4 | f    |
|    5 | t    |
+------+------+
5 rows in set (0.00 sec)

#需求：以上id =4的数据操作失误，需要跳过

② 确认位置点，刷新二进制日志并删除test1表

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000003

960 停止 
1066 开始

#刷新日志

mysqladmin -uroot -p123456 flush-logs

mysql> use school;
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| test1            |
+------------------+
1 row in set (0.00 sec)

mysql> drop table school.test1;
Query OK, 0 rows affected (0.00 sec)

③ 基于位置点恢复

#仅恢复到操作 ID 为“623"之前的数据，即不恢复"user4"的数据

mysqlbinlog --no-defaults --stop-position='623' /opt/mysql-bin.000002 | mysql -uroot -p123456

#仅恢复"user4"的数据，跳过"user3"的数据恢复

mysqlbinlog --no-defaults --start-position='623' /opt/mysql-bin.000002 | mysql -uroot -p123456

#恢复从位置为400开始到位置为623为止

mysqlbinlog --no-defaults --start-position='400' --stop-position='623' /opt/mysql-bin.000002 | mysql -uroot -p123456

2、基于时间点恢复

格式

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

#仅恢复到16:41:24 之前的数据，即不恢复"user4"的数据

mysqlbinlog --no-defaults --stop-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 | mysql -uroot -p123456

#仅恢复"user4"的数据，跳过"user3"的数据恢复

mysqlbinlog --no-defaults --start-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 | mysql -uroot -p123456

如果恢复某条SQL语之前的所有数据，就stop在这个语句的位置节点或者时间点
如果恢复某条SQL语句以及之后的所有数据，就从这个语句的位置节点或者时间点start