MySQL数据库-备份恢复

一、MySQL日志管理

1.为什么需要日志

• 用于排错
• 用来做数据分析
• 了解程序的运行情况，了解MySQL的性能

2.日志作用

在数据库保存数据时，有时候不可避免会出现数据丢失或者被破坏，这样情况下，就必须保证数据的安全性和完整性，则需要使用日志来查看或者恢复数据。

3.数据丢失或被破坏的原因

• 误删数据库
• 数据库工作时，意外断电或程序意外终止
• 由于病毒造成的数据库损坏或丢失
• 文件系统损坏后，系统进行自检操作
• 升级数据库时，命令语句不严格
• 设备故障等等

4.日志文件查看方法

由于多种安装mysql的方法可能导致文件存储位置和名称不同，需要先通过mysql配置文件确定错误日志文件位置及名称

通过mysql配置文件my.ini中查看：C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

datadir=C:/ProgramData/MySQL/MySQL Server 8.0\Data    


log-error="LEGION.err"

位置：C:\ProgramData\MySQL\MySQL Server 8.0\Data\LEGION.err

5.常见日志类型之错误日志

错误日志记录以下信息：

• 服务器启动和关闭过程中的信息
• 服务器运行过程中的错误信息
• 事物调度器运行一个时间时产生的信息
• 在服务器上启动从服务器进程时产生的信息

查看本机错误日志（查看错误日志位置及文件名，一般以主机名.err方式命名）：

mysql> show variables like 'log_error';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| log_error     | .\DESKTOP-2V2B7B2.err |
+---------------+-----------------------+
1 row in set, 1 warning (0.02 sec)

错误日志信息需要注意三类：[System]、[Warning]、[Error]

注意：my.ini中可以用log-error=file_name 选项来指定mysqld保存错误日志文件的位置。

6.常见日志类型之通用查询日志

由于数据库一般有多条连接，不一定是本地连接，所以需要记录每个连接客户端的所有操作，包括启动和关闭 MySQL 服务、更新语句和查询语句等，从而产生查询日志。

查看设置状态：

mysql> show global variables like '%general_log%';
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| general_log      | OFF                 |    # 默认关闭
| general_log_file | DESKTOP-2V2B7B2.log |    # 存储位置
+------------------+---------------------+
2 rows in set, 1 warning (0.01 sec)

启动记录查询日志：

mysql> set global general_log=on;
Query OK, 0 rows affected (0.02 sec)

mysql> show global variables like '%general_log%';
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| general_log      | ON                  |
| general_log_file | DESKTOP-2V2B7B2.log |
+------------------+---------------------+
2 rows in set, 1 warning (0.00 sec)

# 注意这是临时更改，若永久更改则在my.ini中配置general-log=1，并重启服务
# 注意可以在my.ini中设置general_log_file=路径/文件名 的形式设置永久更改存储位置

设置日志记录类型（表/文本文件/空）：

mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

# 注意可以在my.ini中设置log-output={TABLE|FILE|NONE}类型

关闭查询日志：

mysql> set global general_log=0;
Query OK, 0 rows affected (0.01 sec)

mysql> show global variables like "%general_log%";
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| general_log      | OFF                 |
| general_log_file | DESKTOP-2V2B7B2.log |
+------------------+---------------------+
2 rows in set, 1 warning (0.00 sec)

注意：

• 要启用通用查询日志，需要至少配置general-log=1，log-output={TABLE|FILE}
• general_log_file如果没有指定，默认名是：主机名.log
• 默认通用查询日志是不开启的，因为会消耗大量的磁盘空间、CPU以及内存，所以当需要通过查询日 志还原操作场景准确定位问题时可以短时间开启

7.常见日志类型之慢查询日志

慢日志记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询

解释：若某条查询语句的执行时间过长，超过了设定的阈值则就会记录到慢日志中，可以对其进行后期 select 语句的优化

查看慢查询日志状态：

mysql> show global variables like "%slow_query_log%";
+---------------------+--------------------------+
| Variable_name       | Value                    |
+---------------------+--------------------------+
| slow_query_log      | ON                       |
| slow_query_log_file | DESKTOP-2V2B7B2-slow.log |
+---------------------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

开启慢查询日志：

mysql> set global slow_query_log=1; # 0为关闭

C:\ProgramData\MySQL\MySQL Server 8.0\Data\LEGION-slow.log

# 注意：为了服务器调优，建议开启

慢日志的时间阈值：

mysql> show global variables like "long_query_time";
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |    # 默认10秒最小为0,精度可以到微秒
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)

8.*常见日志类型之二进制日志

（1）二进制日志作用：

• 记录所有更改数据的语句（insert、update、delete等），不记录查询语句
• 用于主从复制，因为从服务器需要到主服务器里拷贝二进制日志，然后根据二进制日志的内容去执行 SQL语句，从而达到主从服务器里的数据一模一样
• 用于恢复数据
• 日志审计的场景：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击 （mysql注入攻击：黑客可以提交一段数据库查询代码，根据程序返回的结果，获得某些想要得到的数据）

（2）查看二进制日志状态

mysql> show variables like "%log_bin%";

（3）日志查看：

# 查看有哪些二进制日志文件：
mysql> show binary logs;     # 显示名称、容量单位字节、加密与否

# 查看当前正在使用的是哪一个二进制日志文件
mysql> show master status;

# 查看二进制日志内容：
mysql> show binlog events in "DESKTOP-2V2B7B2-bin.000001";


# 使用命令mysqlbinlog查看二进制日志内容
# 打开命令提示符
C:\Users\Administrator>cd C:\ProgramData\MySQL\MySQL Server 8.0\Data

C:\ProgramData\MySQL\MySQL Server 8.0\Data>mysqlbinlog DESKTOP-2V2B7B2-bin.000001 >
test.sql

（4）日志刷新：

作用：通过刷新日志进行更新日志，对缓存数据进行磁盘I/O，并强制 mysqld来关闭和重新打开日志文件（或者在某些情况下切换到一个新的日志）

由于日志的记录不是直接写入日志文件中而是使用日志缓存的方式，当频繁执行多条修改的sql语句时，为了避免对磁盘频繁I/O，会将日志记录写入到内存的特殊空间即日志缓存中，之后每隔一个固定时间间隔将缓存的日志写入到磁盘文件中

刷新日志命令格式：

mysql> flush logs # 会产生新日志文件
mysql> show master status;

# 在shell中，通过mysqladmin命令执行日志刷新：
mysqladmin flush-logs -u 账户名 -p
mysqladmin refresh -u 账户名 -p

（5）二进制日志是记录执行的语句还是执行后的结果数据呢？分为三种情况：

• 假如一个表有10万行数据，而现在要执行一个如下语句将amount字段的值全部在原来的基础上增加 1000：

 UPDATE sales.january SET amount=amount+1000;

此时如果要记录执行后的结果数据的话，日志会非常大。因此在这种情况下应记录执行语句。这种方式就是基于语句的二进制日志。

• 如果向某个字段插入的是当前的时间呢？如下：

INSERT INTO tb SET Birthdate=CURRENT_TIME();

此时就不能记录语句了，因为不同时间执行的结果是不一样的。此时应该记录这一行的值，这种就是基于行（row）的二进制日志。

• 在有些情况，可能会结合两种方式来记录，这种叫做混合方式的二进制日志。

（6）删除二进制日志文件：

二进制日志文件不能直接删除的，如果使用 rm 等命令直接删除日志文件，可能导致 数据库的崩溃。必须使用命令 PURGE 删除日志，语法如下：

PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }

9.常见日志类型之事物日志

作用：记录InnoDB等支持事务的存储引擎执行事务时产生的日志。

事务：是一种机制，一个操作序列，它包含了一组数据库操作命令，并且把所有的命令作为一个整体一起 向系统提交或撤销操作请求。要么都执行，要么都不执行。

解释：比如在区块链技术中，支出时本地账本会记录同时同步全网，收入时也会记录并同步，若执行支出 时系统崩溃则会出现问题，采用事务日志记录见执行的事务以便与数据回滚等操作.

二、MySQL备份

1.备份类型

根据服务器状态，可以分为热备份、温备份、冷备份：

• 热备份：读、写不受影响
• 温备份：仅可以执行读操作
• 冷备份：离线备份；读、写操作均中止

从对象来分，可以分为物理备份与逻辑备份：

• 物理备份：复制数据文件
• 逻辑备份：将数据导出至文本文件中

从数据收集来分，可以完全备份、增量备份、差异备份：

• 完全备份：备份全部数据
• 增量备份：仅备份上次完全备份或增量备份以后变化的数据
• 差异备份：仅备份上次完全备份以来变化的数据

2.逻辑备份优缺点

在备份速度上两种备份要取决于不同的存储引擎：

（1）物理备份的还原速度非常快。但是物理备份的最小粒度只能做到表。

（2）逻辑备份保存的结构通常都是纯ASCII的，所以我们可以使用文本处理工具来处理

（3）逻辑备份有非常强的兼容性，而物理备份则对版本要求非常高

（4）逻辑备份也对保持数据的安全性有保证

逻辑备份的缺点：

• 逻辑备份要对RDBMS产生额外的压力，而裸备份无压力
• 逻辑备份的结果可能要比源文件更大。所以很多人都对备份的内容进行压缩
• 逻辑备份可能会丢失浮点数的精度信息

3.备份内容

• 数据文件
• 日志文件（比如事务日志，二进制日志）
• 存储过程，存储函数，触发器
• 配置文件（十分重要，各个配置文件都要备份）
• 用于实现数据库备份的脚本，数据库自身清理的Crontab等……

4.备份工具

MySQL自带的备份工具--mysqldump，是mysql数据库管理系统，自带的逻辑备份工具，支持完全备份+增加备份，速度相对较慢，适合中小型数据库支持所有引擎，备份策略：第一次完全备份，每天一次增量备份，每周再做一次完全备份，如此一直重复

全备语法：

mysqldump -u用户名 --password=密码 -A > 绝对路径\备份文件.sql

备份库和表语法：

mysqldump -u用户名 --password=密码 数据库名 表1 表2 > 绝对路径\备份文件.sql

示例1：使用mysqldump工具实现全量备份

# 原理：必须先做全量备份，使得数据库有一个基准还原点，在做增量备份，还原差异数据

# 注意：在命令提示符中处理，不要在powershell中

# 切换到命令提示符终端，完成全备
C:\Users\Administrator> mysqldump -u root --password=123456 -A > C:\back1.sql
mysqldump: [Warning] Using a password on the command line interface can be
insecure. # 警告表示将密码显示在屏幕上不安全，备份的盘符一定要和MySQL安装目录在同一个盘符中，
要不然会提示：拒绝访问

# 切换终端到mysql
C:\Users\Administrator> mysql -uroot -p
Enter password: ******

mysql> drop database mydb1_test; # 删库跑路

mysql> drop database mydb2_stuinfo;
……

mysql> show databases; # 查看数据库

mysql> source C:\back1.sql # 全库恢复

示例2：局部备份恢复

# 切换到命令提示符终端，完成全备
C:\Users\Administrator> mysqldump -u root --password=123456 -B mydb1_test >
C:\back2.sql

# 切换终端到mysql
C:\Users\Administrator> mysql -uroot -p
Enter password: ******

mysql> drop database mydb1_test; # 删库跑路

mysql> drop database mydb1_test;

# 开始恢复
mysql> create database mydb1_test; # 需要先创建待恢复的数据库

mysql> use mydb1_test;

mysql> source C:\back2.sql

mysql> show tables; # 查看，已还原

mysql> select * from t1 ; # 查看表

文件系统备份工具：

• cp命令， 冷备份，支持所有引擎，复制命令，只能实现冷备，物理备份。使用归档工具，cp命令， 对其进行备份的，备份速度快，还原速度几乎最快，但是灵活度很低，可以跨系统，但是跨平台能力很差。
• lvm 几乎是热备份，支持所有引擎，基于快照(LVM，ZFS)的物理备份，速度非常快，几乎是热备。 只影响数据几秒钟而已。但是创建快照的过程本身就影响到了数据库在线的使用，所以备份速度比较 快，恢复速度比较快，没有什么弹性空间，而且LVM的限制：不能对多个逻辑卷同一时间进行备份， 所以数据文件和事务日志等各种文件必须放在同一个LVM上。而ZFS则非常好的可以在多逻辑卷之间备份。

其它工具：

• ibbackup 商业工具 MyISAM是温备份，InnoDB是热备份 ，备份和还原速度都很快，这个软件它的每服务器授权版本是5000美元
• xtrabackup 开源工具 MyISAM是温备份，InnoDB是热备份 ，是ibbackup商业工具的替代工具
• mysqlbackup ORACLE公司也提供了针对企业的备份软件MySQL Enterprise Backup简称： mysqlbackup