【数据库】 MySQL备份恢复

目录

MySQL日志管理

一， MySQL日志类型 

二，错误日志

三， 通用查询日志

四， 慢查询日志

五，二进制日志

1，开启日志

2，二进制日志的管理

3，日志查看

5，二进制日志还原数据

MySQL备份

一， 备份类型

逻辑备份优缺点

二， 备份内容

三， 备份工具

1，MySQL自带的备份工具

2，文件系统备份工具

3，其它工具

---

MySQL日志管理

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

数据库中数据丢失或被破坏可能原因：

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

 

一， MySQL日志类型

MySQL有几个不同的日志文件，可以帮助你找出mysqld内部发生的事情：

日志类型	记入文件中的信息类型
错误日志	记录启动、运行或停止时出现的问题
查询日志	记录建立的客户端连接和执行的语句
慢日志	记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询
二进制日志	记录所有更改数据的语句。主要用于复制和即时点恢复
事务日志	记录InnoDB等支持事务的存储引擎执行事务时产生的日志

 

默认情况下，所有日志创建于mysqld数据目录中。通过刷新日志，你可以强制 mysqld来关闭和重新打开日志文件（或者在某些情况下切换到一个新的日志）。当你执行一个FLUSH LOGS语句或执行mysqladmin flush-logs或mysqladmin refresh时，出现日志刷新。如果你正使用MySQL复制功能，在复制服务器将维护更多日志文件，被称为接替日志。

 

在mysql中，执行SQL语句：

flush logs

在shell中，通过mysqladmin命令执行日志刷新：

mysql admin flush-logs

mysql admin refresh

 

 

 

二，错误日志

错误日志主要记录如下几种日志：

 

错误日志定义：

可以用--log-error[=file_name]选项指定或者配置文件指定log-error来指定mysqld保存错误日志文件的位置。如果没有给定file_name的值，mysqld使用错误日志名host_name.err并在数据目录中写入日志文件。如果日志文件不存在，mysql启动或者执行flush logs时会创建新的日志文件。

 

查看当前错误日志配置:

mysql> SHOW GLOBAL VARIABLES LIKE '%general_log%';

show variables like 'log_error';

当前错误日志的路径在   /var/log/mysqld.log下（默认开启）

配置文件在 /etc/my.cnf下面，如果此文件没有，那就在/etc/my.cnf.d/mysql-server.cnf中

log-error=/var/log/mysqld.log  指定了错误日志的配置存储路径

查看是否记录警告日志：

mysql> SHOW   GLOBAL   VARIABLES   LIKE  '%log_warnings%';

三， 通用查询日志

mysql服务器默认情况下并没有开启通用查询日志。如果需要通用查询日志，可通过修改配置文件来开启：

 

启动开关：general_log={ON|OFF} 

日志文件变量：general_log_file[=/PATH/TO/file]

全局日志开关：log={ON|OFF} 该开关打开后，所有日志都会被启用 

记录类型：log_output={TABLE|FILE|NONE}，FILE表示将日志存入文件，默认值是FILE；TABLE表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。mysql数据库支持同时两种日志存储方式，配置的时候以逗号隔开。

因此，要启用通用查询日志，需要至少配置general_log=ON，log_output={TABLE|FILE}。而general_log_file如果没有指定，默认名是host_name.log。

 

看看上述几个值的默认配置：

查看当前通用日志：

show variables like '%general_log%';

临时开启通用日志

Set ​ variable general_log=on

查看当前默认记录日志的方式

show variables like 'log_output';

​

四， 慢查询日志

MySQL如果启用了 slow_query_log=ON 选项，就会记录执行时间超过long_query_time的查询（初使表锁定的时间不算作执行时间）。日志记录文件为slow_query_log_file[=file_name]，如果没有给出file_name值， 默认为主机名，后缀为-slow.log。如果给出了文件名，但不是绝对路径名，文件则写入数据目录。

默认与慢查询相关变量：

1，默认没有启用慢查询，为了服务器调优，建议开启

mysql> SHOW  GLOBAL  VARIABLES  LIKE  '%slow_query_log%';

2， 开启方法,当前生效，永久有效在配置文件中设置

SET  GLOBAL  slow_query_log=ON;  

​3，使用 mysqldumpslow 命令获得日志中显示的查询摘要来处理慢查询日志

[root@mysql8-0-30mysql]# mysqldumpslow mysql8-0-30-slow.log

4，如果查询时长超过long_query_time的定义值（默认10秒），即为慢查询：

查看查询时长

mysql> SHOW  GLOBAL  VARIABLES  LIKE'long_query_time';

五，二进制日志

1，开启日志

二进制日志启动开关：log-bin [= file_name]。

注意：5.6以下版本默认file_name为$datadir/mysqld-binlog。查看二进制日志的工具为：mysqlbinlog。

 

二进制日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。

 

语句以“事件”的形式保存，它描述数据更改。二进制日志还包含关于每个更新数据库的语句的执行时间信息。 它不包含不会修改任何数据的语句，例如select，show等。

 

二进制日志的主要目的是在数据库存在故障时，恢复时能够最大可能地更新数据库（即时点恢复），因为二进制日志包含备份后进行的所有更新。二进制日志还用于在主复制服务器上记录所有将发送给从服务器的语句。

 

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

1，假如一个表有10万行数据，而现在要执行一个如下语句将amount字段的值全部在原来

      的基础上增加1000：

     UPDATE   sales.january   SET   amount=amount+1000;

     此时如果要记录执行后的结果数据的话，日志会非常大。因此在这种情况下应记录执行

     语句。这种方式就是基于语句的二进制日志。 

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

INSERT   INTO   tb   SET  Birthdate=CURRENT_TIME();

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

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

 

 

2，二进制日志的管理

日志滚动：例如在my.cnf中设定max_binlog_size = 200M，表示限制二进制日志最大尺寸为200M，超过200M后进行滚动。MySQL的滚动方式与其他日志不太一样，滚动时会创建一个新的编号大1的日志用于记录最新的日志，而原日志名字不会被改变。每次重启MySQL服务，日志都会自动滚动一次。

 

另外如果需要手动滚动，则使用命令 mysql> FLUSH LOGS ;

 

3，日志查看

1，查看二进制日志相关设置

show variables like '%log_bin%';

log_bin   二进制日志是否开启

log_bin_basename    二进制日志存放的地址

2，查看每个binlog文件的最大大小，默认是1G

show variables like 'max_binlog_size';

 

3， 查看有哪些二进制日志文件

show binary logs;

 Log_name  日志名

File_size   文件大小

Encrypted

 

 

4，查看当前正在使用的是哪一个二进制日志文件：

show master status;

5，查看二进制日志内容

show binlog events in 'binlog.000002';

 

 

说明：

log_name：表示当前事件所在的binlog文件名称

pos：当前事件的开始位置，每个事件都占用固定的字节大小，结束位置(End_log_position

            )减去Pos，就是这个事件占用的字节数。细心的读者可以看到了，第一个事件位置

          并不是从0开始，而是从4。Mysql通过文件中的前4个字节，来判断这是不是一个

          binlog文件。这种方式很常见，很多格式的文件，如pdf、doc、jpg等，都会通

          常前几个特定字符判断是否是合法文件。

Event_type：事件类型在event_type列中，在当前binlog v4版本中，每个binlog文件总是以

                         Format Description Event作为开始，以Rotate Event结束作为结束。

（1）Format_desc：也就是我们所说的Format Description Event，是binlog文件的第一个事件。在Info列，我们可以看到，其标明了Mysql Server的版本是8.0.30，Binlog版本是4。

​

（2）Previous_gtids：该事件完整名称为，PREVIOUS_GTIDS_LOG_EVENT。熟悉Mysql 基于GTID复制的同学应该知道，这是表示之前的binlog文件中，已经执行过的GTID。需要我们开启GTID选项，这个事件才会有值。

​

（3）Rotate：Rotate Event是每个binlog文件的结束事件。在Info列中，我们看到了其指定了下一个binlog文件的名称是binlog.000030。

​

Server_id：表示产生这个事件的mysql server_id，通过设置my.cnf中的server-id选项进行配置。

End_log_position：下一个事件的开始位置。

Info：当前事件的描述信息。

​

​

 

 

6，使用如下方式办法读取binlog日志

show  b inlog   events   IN  ['log_name']    [FROMpos]    [LIMIT[offset,] row_count];

log_name：指定要查询的binlog文件名(不指定就是第一个binlog文件)；

FROM pos 指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)；

LIMIT [offset,] 偏移量(不指定就是0)；

row_count 查询总条数(不指定就是所有行)

（1）方法一：

show binlog events in 'binlog.000005';

（2）方法二：\G方便观看

show binlog events in 'binlog.000005'\G;

 

（3）方法三：pos

日志信息为：

从指定pos位置开始，显示后面所有

show binlog events in 'binlog.000005' from 11255\G     

 

（4）方法四：pos,limit(0,行数）

显示从pos等于11129位置开始的两行

show binlog events in 'binlog.000005' from 11129 limit 2\G

 

（5）方法五：pos,limit(偏移量，行数）

显示从指定的pos等于10774的位置向下偏移1行开始的两行

show binlog events in 'binlog.000005' from 10974 limit 1,2\G

 

 

 

7，使用命令mysqlbinlog查看二进制日志内容：mysqlbinlog [options] log-files 

mysqlbinlog binlog.000005

​

 

8，重置(清空)所有binlog日志

mysql> reset master;

查看有哪些二进制日志

show binary logs;

 清空所有的二进制日志

reset master;

 在查看发现只剩下一个

show binary logs;

 

 

 

9，删除某个日志文件前的全部日志文件：

PURGE {MASTER | BINARY} LOGS TO 'log_name'；

现在有七个二进制日志文件

show binary logs;

删除4之前的所有二进制日志

purge master logs to 'binlog.000004';

查看删除4之前的所有二进制日志后的结果

show binary logs;

 

 10，删除指定日期前的全部日志文件：PURGE {MASTER | BINARY} LOGS BEFORE 'date'

 查看binlog.000004日志的内容，看到一条日期为230213

[root@localhost mysql]# mysqlbinlog binlog.000004

 

 

 

5，二进制日志还原数据

假设mysqlserver启用了二进制日志，在数据库出现意外丢失数据时。能够使用mysqlbinlog工具从指定的时间点开始（比如，最后一次备份）直到如今或者是到另外一个指定的时间点的日志中恢复数据。

 

1，mysqlbinlog恢复数据的语法如下：

 数据库chap05被删除了，

 获取日志信息方法一：

 获取日志信息方法二：

mysqlbinlog  [option]  filename  | mysql  -uuser  -ppassword

选项如下：

--start-datetime、--stop-datetime能够指定恢复数据库的起始时间点和结束时间点

--start-position、--stop--position能够指定恢复数据的开始位置和结束位置

恢复日志方法一：按照位置

[root@localhost mysql]# mysqlbinlog --stop-position=829 binlog.000007 | mysql -uroot -pAdmin123!

恢复日志方法一：按照时间

[root@localhost mysql]# mysqlbinlog --stop-datetime='23-02-13 21:16:48' binlog.000007 | mysql -uroot -pAdmin123!

此时查看所有数据库，chap05已经恢复

mysql8.0.30 [(none)]>show databases;

恢复日志方法三：按照整个文件恢复

现有下面三个日志文件

mysql8.0.30 [(none)]>show binary logs;

在3这个文件日志里面创建表t2的信息如下

刷新日志文件

mysql8.0.30 [chap05]>flush logs;

查看二进制日志文件，多了一个4

mysql8.0.30 [chap05]>show binary logs;

在4日志文件里面删除表t2

mysql8.0.30 [chap05]>drop table t2;

恢复日志文件3

[root@localhost mysql]# mysqlbinlog binlog.000003 | mysql -uroot -pAdmin123!

查询表t2，日志恢复成功

mysql8.0.30 [chap05]>select * from t2;

 

 

MySQL备份

一， 备份类型

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

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

   注：为保证所备份数据的完整性，在停止mysql数据库之前，需要先执行flush tables

          语句将所有数据写入到数据文件的文本文件里。

 

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

• 物理备份：复制数据文件；
• 逻辑备份：将数据导出至文本文件中，内容是一条条sql语句，或者是表内实际数据组成；

 

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

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

 

逻辑备份优缺点

 

逻辑备份的优点：

• 在备份速度上两种备份要取决于不同的存储引擎。
• 物理备份的还原速度非常快。但是物理备份的最小粒度只能做到表。
• 逻辑备份保存的结构通常都是纯ASCII的，所以我们可以使用文本处理工具来处理。
• 逻辑备份有非常强的兼容性，而物理备份则对版本要求非常高。
• 逻辑备份也对保持数据的安全性有保证。

逻辑备份的缺点：

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

 

 

二， 备份内容

数据文件

日志文件（比如事务日志，二进制日志）

存储过程，存储函数，触发器

配置文件（十分重要，各个配置文件都要备份）

用于实现数据库备份的脚本，数据库自身清理的Crontab等……

三， 备份工具

1，MySQL自带的备份工具

mysqldump，是mysql数据库管理系统自带的逻辑备份工具，支持所有引擎，MyISAM引擎是温备，InnoDB引擎是热备，备份速度中速，还原速度非常非常慢。但是在实现还原的时候，具有很大的操作余地，具有很好的弹性。

 

（1）备份某个数据库下的表

数据库chap05中的t1表有以下数据：

创建一个目录用来存放备份文件

备份chap05库的t1表，如果有多张表，用空格隔开

mysqldump -uroot -pAdmin123! chap05 t1 > /mysqlbackup/chap05-t1-`date +%F`.back.sql

删除数据库chap05里的表t1

drop table t1;

通过备份文件还原数据

方法一：

mysql -uroot -pAdmin123! chap05 < /mysqlbackup/chap05-t1-2023-02-14.back.sql

方法二：

source /mysqlbackup/chap05-t1-2023-02-14.back.sql

方法三：

cat /mysqlbackup/chap05-t1-2023-02-14.back.sql | mysql -uroot -pAdmin123! chap05

还原成功，查看数据库chap05中的表t1的所有记录

select * from t1;

（2）​备份某个数据库下的所有表

 只写库名不写表名表示：备份chap05里面的所有表

mysqldump -uroot -pAdmin123! chap05 > /mysqlbackup/chap05-`date +%F`.bak.sql

删除数据库chap05里面所有的表

还原数据库chap05下的所有表

mysql -uroot -pAdmin123! chap05 < /mysqlbackup/chap05-2023-02-14.bak.sql

查看还原结果

show tables;

（3）备份数据库和数据库下所有的表

备份数据库和该库里面所有的表，指定多个库时用空格隔开

mysqldump -uroot -pAdmin123! --databases chap05 > ./chap05-`date +%F`.bak.sql

删除数据库chap05

drop database chap05;

还原数据库chap05下的所有表

source /mysqlbackup/chap05-2023-02-14.bak.sql

还原结果：

show tables;

mysqlhotcopy 物理备份工具，但只支持MyISAM引擎。基本上属于冷备的范畴，物理备份，速度比较快。多用于mysql5.5之前。

mysqlhotcopy使用lock tables、flush tables和cp或scp来快速备份数据库或单个表，属于裸文件备份（物理备份），本质是使用锁表语句，然后cp或scp。

2，文件系统备份工具

cp命令， 冷备份，支持所有引擎，复制命令，只能实现冷备，物理备份。使用归档工具，cp命令对其进行备份的备份速度快，还原速度几乎最快，但是灵活度很低，可以跨系统，但是跨平台能力很差。

lvm 几乎是热备份，支持所有引擎，基于快照(LVM，ZFS)的物理备份，速度非常快，恢复速度也比较快，几乎是热备。只影响数据几秒钟而已。但是创建快照的过程本身就影响到了数据库在线的使用。而且LVM的限制：不能对多个逻辑卷同一时间进行备份，所以数据文件和事务日志等各种文件必须放在同一个LVM上。而ZFS则非常好的可以在多逻辑卷之间备份。

 

3，其它工具

ibbackup：商业工具 MyISAM是温备份，InnoDB是热备份 ，备份和还原速度都很快，这个软件它的每服务器授权版本是5000美元。

 xtrabackup：开源工具 MyISAM是温备份，InnoDB是热备份 ，是ibbackup商业工具的替代工

mysqlbackup：ORACLE公司也提供了针对企业的备份软件MySQL Enterprise Backup简称：mysqlbackup。MySQL企业备份工具执行在线“热备“，无阻塞的MySQL数据库备份。全备份可以在所有InnoDB数据库上执行，而无需中断MySQL查询或更新。此外，支持增量备份，只备份自上次备份后有变化的数据。另外部分备份，支持特定的表或表空间按需要进行备份。