备份与恢复

Posted on by zhezimi

备份数据库非常重要，这样您就可以恢复数据，并在发生问题时再次启动并运行，例如系统崩溃，硬件故障或用户错误地删除数据。在升级MySQL安装之前，备份也是必不可少的保护措施，它们可用于将MySQL安装转移到另一个系统或设置复制slave服务器。

为什么需要备份

  1. 灾难恢复：灾难恢复是下列场景下需要做的事情：硬件故障，一个不经意的BUG导致数据损坏，或者服务器由于某些原因不可获取或无法使用等。你需要准备好应付很多问题：某人偶然连错服务器执行了一个ALTER TABLE的操作，机房大楼被烧毁，恶意的黑客攻击或MySQL的Bug等。尽管遭受任何一个特殊的灾难的几率都非常低，但所有的风险叠加在一起就很有可能碰到
  2. 人们改变想法：不必惊讶，很多人经常在删除某些数据后又想恢复这些数据
  3. 审计：有时候需要数据或Schema在过去的某个时间点是什么样子的。
  4. 测试：一个最简单的基于实际数据来测试的方法是：定期用最新的生产环境数据更新测试服务器。如果使用备份的方案就非常简单：只需要把备份文件还原到测试服务器上即可。

备份策略列表：

  1. 物理与逻辑备份
  2. 在线与离线备份
  3. 快照备份
  4. 全量，增量，差异备份

物理备份

优点：

  1. 基于文件的物理备份，只需要将需要的文件复制到其他地方即可完成备份，不需要其他额外的工作来生成原始文件
  2. 物理备份的恢复可能就更简单了，这取决于存储引擎，对于MyISAM，只需要简单地复制文件到目的地即可。对于InnoDB则需要停止数据库服务，可能还要采取其他一些步骤
  3. InnoDB和MyISAM的备份非常容易跨平台，操作系统和MySQL版本（逻辑导出也是这样）
  4. 从物理备份中恢复会更快，因为MySQL服务器不需要执行任何SQL或构建索引。如果有很大的InnoDB表，无法完全缓存到内存中，则物理备份的恢复要快很多，至少要快一个数量级，事实上，逻辑备份最可怕的地方就是不确定的还原时间。

缺点：

  1. InnoDB的原始文件通常比相应的逻辑备份要大得多，InnoDB的表空间往往包含很多未使用的空间，还有很多空间被用来存储数据以外的用途（插入缓冲，回滚段等）
  2. 物理备份不总是可以跨平台，操作系统及MySQL版本。文件名大小敏感和浮点格式可能会遇到麻烦

物理备份通常更加简单高效，尽管如此，对于需要长期保留的备份，或者是满足法律合规要求的备份，尽量不要完全依赖物理备份，至少每隔一段时间还是需要做一次逻辑备份。

除非经过测试，不要假定备份（特别是物理备份）是正常的，对于InnoDB来说，这意味着需要启动一个MySQL实例，执行InnoDB恢复操作，然后运行CHECK TABLES。也可以跳过这一操作，仅对文件执行innochecksum，但不建议这样做。对于MyISAM，可以运行CHECK TABLES，或者使用mysqlcheck。使用mysqlcheck可以对所有的表执行CHECK TABLE操作。

建议混合使用物理和逻辑两种方式来做备份：先使用物理备份，以此数据启动MySQL服务器实例并运行mysqlcheck，然后，周期性地使用mysqldump进行逻辑备份。这样做可以获得两种方法的优点，不会使生产服务器在导出时有过度负担，如果能够方便地利用文件系统的快照，也可以生成一个快照，将该快照复制到另外一个服务器上并释放，然后测试原始文件，再执行逻辑备份。

逻辑备份

优点：

  • 逻辑备份是可以用编辑器或grep，sed这类的命令查看和操作的普通文件，当需要恢复数据或只想查看数据但不恢复时，这都非常有帮助。
  • 恢复非常简单，可以通过管道吧它们输入到mysql或者使用mysqlImport
  • 可以通过网络来备份和恢复—–也就是说，可以在于MySQL主机不同的另外一台机器上操作
  • 非常灵活，mysqldump支持很多选项，例如可以使用WHERE字句来限制需要备份哪些行。
  • 与存储引擎无关。因为是从MySQL服务器中提取数据生成，所以消除了底层存储的不同

缺点：

  • 必须由数据库服务器完成生成逻辑备份的工作，因此要使用更多的CPU周期
  • 逻辑备份在某些场景下比数据库文件本身要大。
  • 无法保证导出后还原出来的一定是同样的数据。浮点表示的问题，软件BUG等都会导致问题，尽管非常少见
  • 从逻辑备份中还原需要MySQL加载和解释语句，转化为存储格式，并重建索引，所以这一切会很慢

最大的缺点是从MySQL中导出数据和通过SQL语句将其加载回去的开销，如果使用逻辑备份，测试恢复需要的时间将非常重要。

在线与离线备份

在MySQL服务器运行时进行在线备份，以便从服务器获取数据库信息。在服务器停止时进行离线备份。这种区别也可以描述为“热”与“冷”备份。

“热”备份是服务器保持运行但在外部访问数据库文件时锁定以防止修改数据的备份。一般是使用FLUSH TABLES WITH READ LOCK操作，这会导致MySQL锁住所有的表。该操作需要很长的时间来完成，具体多长时间是不可预估的。解锁方法使用UNLOCK TABLES。

避免使用FLUSH TABLES WITH READ LOCK的最好的方法是只用innoDB表，在权限和其他系统信息表中使用MyISAM表是不可避免的，但是如果数据量该变量很少，你可以只刷新和锁住这些表，这不会有什么问题。

如果可能，关闭MySQL做备份是最简单最安全的，也是所有恢复一致性副本的方法中最好的，而且损坏或不一致的风险最小。如果关闭了MySQL，就根本不用关心InnoDB缓冲池中的脏页或其他缓存，也不需要担心数据在尝试备份的过程被修改，并且因为服务器不对应用提供访问，所以可以更快的完成备份。

快照备份

一些文件系统实现支持“快照”。它们在给定的时间点提供文件系统的逻辑副本，而不需要整个文件系统的物理副本。 （例如，实现可以使用写时复制技术，以便只需要复制快照时间之后修改的文件系统的一部分。）MySQL本身不提供获取文件系统快照的功能。 它可通过Veritas，LVM或ZFS等第三方解决方案获得。

全量，增量，差异备份

全量恢复可从全量备份恢复所有数据。这会将服务器实例还原到备份时的状态。如果该状态不是足够的当前状态，则可以在完全恢复之后恢复自完全备份以来所做的增量备份，从而使服务器处于更最新的状态。

增量恢复是恢复在给定时间跨度内所做的更改。这也称为时间点恢复，因为它使服务器的状态达到给定时间。

时间点恢复基于二进制日志，通常在备份文件完全恢复之后，将备份文件还原到备份时的状态。然后，在二进制日志文件中写入的数据更改将作为增量恢复应用于重做数据修改，并使服务器达到所需的时间点。

差异备份和增量备份的区别：

差异备份是对自上次全量备份后所有改动的部分而做的备份，而增量备份则是自从任意类型的上次备份后所有修改做的备份。

例如，假如在每周日做一个全量备份，在周一，对自周日以来所有的改变做一个差异备份。在周二，就有两个选择：备份自周日以来所有的改变（差异），或只备份自从周一备份后所有的改变（增量）。

增量，差异备份的缺点包括恢复复杂性，额外的风险，以及更长的恢复时间，如果可以做全量备份，那么尽量做全量备份。

恢复物理备份

恢复物理备份往往非常直接，直接把备份文件放到对应的数据库目录，然后开始启动MySQL。

在启动正在恢复的MySQL服务器之前，还有些步骤要做。

首先，在启动MySQL服务器之前检查服务器的配置，确保恢复的文件有正确的归属和权限，这些权限必须完全正确，否则MySQL可能无法启动，这些属性因系统的不同而不同，因此要仔细检查是否和之前做的记录吻合。一般都需要mysql用户和组拥有这些文件和目录，并且只有这个用户和组拥有可读/写权限。

建议观察MySQL启动时的错误日志，在UNIX类系统中，可以观察如下文件

tail -f /var/log/mysql/mysq.err

注意错误日志的准确位置会有所不同，一旦开始监测文件，就可以启动MySQL服务器并监测错误。如果一切进展顺利，MySQL启动后就有一个恢复好的数据库服务器了。

一个好的值得倡导的原则：为了防止一些无法意料的情况或者某些无法使用物理备份的场景，准备好逻辑备份总是值得推荐的。

恢复逻辑备份

一说到恢复逻辑备份，那么我们先要知道如何进行逻辑备份。像物理备份的话，我们只要把数据库文件夹直接复制到备份目录就可以了。逻辑备份的话需要用到对应的备份工具。

逻辑备份工具

最常用的备份工具就是mysqldump。mysqldump的参数选项很多，可以使用mysqldump –help来查看所有的参数，我们列举一些比较重要的参数。

  • –single-transaction：在备份开始前，先执行START TRANSACTION 命令，以此来获得备份的一致性，当前该参数只对Innodb存储引擎有效。当启动该参数并进行备份时，确保没有其他任何的DDL语句执行，因为一致性读并不能隔离DDL操作
  • –lock-tables (-l)：在备份中，依次锁住每个架构下的所有表，一般用于MyISAM存储引擎，当备份时只能对数据库进行读取操作，不过备份依然可以保证一致性，对于innodb存储引擎，则不需要使用该参数，用–single-transaction即可。并且–single-transaction与–lock-tables是互斥的，不能同时使用。如果用户的MySQL数据库中，两种表都有，那么只能用–lock-tables了。
  • –lock-all-tables：在备份过程中，对所有架构中的所有表上锁，这个可以避免之前说的–lock-tables参数不能同时锁住所有表的问题。
  • –add-drop-database：在CREATE DATABASE前先运行DROP DATABASE。这个参数需要和–all-databases或者–databases选项一起使用，在默认情况下，导出的文本文件并不会有CREATE DATABASE，除非制定了这个参数。

常见备份方案：

1：备份所有数据库

shell> mysqldump –all-databases > dump.sql

2：备份指定数据库，在单数据库的情况下可以省略 –databases选项

shell> mysqldump –databases db1 db2 db3 > dump.sql

3：备份指定数据库的指定表

shell> mysqldump db1 table_1 table_3 table_3 > dump.sql

4：备份指定数据库，但需要排除某一个表。例如导出blog库，但blog.user表不需要导出

shell> mysqldump blog –ignore-table=blog.user > dump.sql

5：根据WHERE条件备份对应表的部分数据，例如导出blog库goods表中id>10的数据

shell> mysqldump blog goods –where=”id>10″ > dump.sql

还有更多用法，大家可以参考官方文档，我这里就不一一介绍了。

备份恢复

mysqldump的恢复比较简单，因为备份的文件就是导出的SQL语句，一般只要执行这个文件就可以了，可以通过以下的方法：

shell> mysql < dump.sql

或者在mysql中

mysql> source dump.sql

如果文件是不包含CREATE DATABASE和USE语句的单数据库转储，请首先创建数据库。

mysql> CREATE DATABASE IF NOT EXISTS db1;

mysql> USE db1;

mysql> source dump.sql

上面的逻辑备份是全量备份，全量备份的话不可能备份频率很高的，一般一天备份一次。假如我们都是凌晨3点来进行逻辑备份，上午10点的时候误删除了数据，如果我们使用3点的逻辑备份数据来进行恢复，那么就相当于3点到10点这个时间段的数据改动我们这边就没办法找回了，那么有没有办法解决这个问题呢？有的，那就是下面开始说的增量恢复了。

二进制日志进行时间点（增量）恢复

对MySQL做基于时间点的恢复常见的方法是还原最近一次全备份，然后从那个时间点开始重放二进制日志（有时候也叫前滚恢复）。只要有二进制日志，就可以恢复到任何希望的时间点。

主要的缺点是二进制日志重放可能会是一个很慢的过程，它大体上等同于复制。

时间点恢复的信息源是由完全备份操作之后生成的二进制日志文件表示的增量备份集。因此，必须使用–log-bin选项启动服务器以启用二进制日志记录。

要从二进制日志还原数据，您必须知道当前二进制日志文件的名称和位置

要查看所有二进制日志文件的列表，请使用以下语句：

mysql> SHOW BINARY LOGS;

要确定当前二进制日志文件的名称，请发出以下语句：

mysql> SHOW MASTER STATUS;

方案1：基于事件时间点的恢复

要指示恢复的开始和结束时间，请以DATETIME格式指定mysqlbinlog的–start-datetime和–stop-datetime选项。

例如，假设正好在2005年4月20日上午10点执行了删除大表的SQL语句。要恢复表和数据，可以恢复前一天晚上的备份，然后执行以下命令:

shell> mysqlbinlog –stop-datetime=”2005-04-20 9:59:59″  /var/log/mysql/bin.123456 | mysql -u root -p

此命令恢复到–stop-datetime选项给出的日期和时间之前的所有数据。

如果您没有检测到几小时后输入的错误SQL语句，您可能还希望恢复之后发生的活动。

基于此，您可以使用开始日期和时间再次运行mysqlbinlog，如下所示：

shell> mysqlbinlog –start-datetime=”2005-04-20 10:01:00″      /var/log/mysql/bin.123456 | mysql -u root -p

在此命令中，将重新执行从上午10:01开始记录的SQL语句。恢复前一天晚上的转储文件和两个mysqlbinlog命令的组合将恢复上午10:00前一秒之前的所有内容和从上午10:01开始的所有内容。

要使用此时间点恢复方法，您应检查日志以确保为命令指定的确切时间。 要显示日志文件内容而不执行它们，请使用以下命令：

shell> mysqlbinlog /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

然后使用文本编辑器打开/tmp/mysql_restore.sql文件以检查它。

方案2：使用事件位置的时间点恢复

mysqlbinlog的–start-position和–stop-position选项可用于指定日志位置，而不是指定日期和时间。

它们的工作方式与start和stop日期选项相同，只是您指定的是日志位置号而不是日期。

使用位置可以使您更准确地了解要恢复的日志部分，尤其是在许多事务与破坏性SQL语句同时发生的情况下。

要确定位置编号，请在执行不需要的事务的时间附近运行mysqlbinlog一段时间，但将结果重定向到文本文件以供检查。 这可以这样做：

shell> mysqlbinlog –start-datetime=”2005-04-20 9:55:00″     –stop-datetime=”2005-04-20 10:05:00″        /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

此命令在/ tmp目录中创建一个小文本文件，该文件包含执行有害SQL语句时的SQL语句。使用文本编辑器打开此文件，并查找不希望重复的语句。确定二进制日志中的位置以停止和继续恢复并记下它们。位置被标记为log_pos，后面跟着一个数字。恢复前一个备份文件后，使用位置号来处理二进制日志文件。例如，您可以使用以下命令:

shell> mysqlbinlog –stop-position=368312 /var/log/mysql/bin.123456          | mysql -u root -p

shell> mysqlbinlog –start-position=368315 /var/log/mysql/bin.123456          | mysql -u root -p

第一个命令恢复直到给定停止位置的所有事务。第二个命令从给定的起始位置恢复所有事务，直到二进制日志结束。因为mysqlbinlog的输出在记录每个SQL语句之前包含SET TIMESTAMP语句，所以恢复的数据和相关的MySQL日志将反映执行事务的原始时间。

下面我们来一个时间点恢复案例

我们登录到一个空的数据库

mysql> use study;
Database changed

mysql> show tables;
Empty set (0.00 sec)

我们创建一个test_student表

mysql> create table test_student(
-> id not null auto_increment primary key,
-> user_name varcha(11) not null default ” comment ‘姓名’
-> )engine=innodb;

我们接着插入几条数据。

mysql> insert into test_student (id, user_name) values (null, ‘张三’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_student (id, user_name) values (null, ‘lisi’);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_student (id, user_name) values (null, ‘wangwu’);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test_student (id, user_name) values (null, ‘zhaoliu’);
Query OK, 1 row affected (0.01 sec)

假如这个时候我们不小心把表给删除了

mysql> drop table test_student;
Query OK, 0 rows affected (0.01 sec)

我们发现数据误删除了，那么这个时候怎么操作呢。

首先我们要查看当前的时间

mysql> select now();
+———————+
| now() |
+———————+
| 2022-06-27 14:30:19 |
+———————+

接着查出当前的binlog

mysql> SHOW BINARY LOGS;
+——————+————+
| Log_name | File_size |
+——————+————+
| mysql-bin.000004 | 1073859592 |
| mysql-bin.000005 | 1073851895 |
| mysql-bin.000006 | 1074350106 |
| mysql-bin.000007 | 974031762 |
| mysql-bin.000008 | 2025384 |
| mysql-bin.000009 | 5465 |
| mysql-bin.000010 | 953363 |
| mysql-bin.000011 | 118205 |
+——————+————+

mysql> SHOW MASTER STATUS;
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000011 | 118205 | | | |
+——————+———-+————–+——————+——————-+
1 row in set (0.00 sec)

刷新日志，生成新的binlog文件

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW MASTER STATUS;
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000012 | 154 | | | |
+——————+———-+————–+——————+——————-+

binlog文件有时候会很大，我们可以用–start-datetime和–end-datetime来查出对应的位置。注意，此时的binlog文件是mysql-bin.000011 ，而不是mysql-bin.000012。

查询binlog的存放目录

mysql> show variables like ‘%datadir%’;
+—————+——————-+
| Variable_name | Value |
+—————+——————-+
| datadir | /www/server/data/ |
+—————+——————-+

[root@VM-12-4-centos ~]# mysqlbinlog -f –start-datetime ‘2022-06-27 14:00’ /www/server/data/mysql-bin.000011

我们根据前面查出来的时间和binglog文件来查到创建表的位置和删除表的位置

开始位置和结束位置都查出来了， 那么下面我们就可以导出对应的sql了。

[root@VM-12-4-centos ~]# mysqlbinlog -f –start-position=116398 –stop-position=118078 –database study /www/server/data/mysql-bin.000011 >/tmp/binlog.sql

接着我们再导入sql

mysql -u root -p –database=study < /tmp/study.binlog.sql

导入成功后，我们就可以看到原来的数据了。

参考资料：

https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

https://dev.mysql.com/doc/refman/5.7/en/backup-types.html

https://dev.mysql.com/doc/refman/5.7/en/flush.html#flush-logs

《高性能MySQL》

《MySQL技术内幕：InnoDB存储引擎 第2版》

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注