前言

在信息技术与数据管理领域，备份指将文件系统或数据库系统中的数据加以复制；一旦发生灾难或错误操作时，得以方便而及时地恢复系统的有效数据和正常运作。最好将重要数据制作三个，或三个以上的备份，并且放置在不同的场所异地备援，以利日后回存之用。

1. 备份

1.1. 备份的方式

• 全量备份（full backup)：把硬盘或数据库内的所有文件、文件夹或数据作一次性的复制。
• 增量备份：针对上次全量或增量备份后，有更新的数据进行备份
• 差异备份：针对已有的完整备份，对比数据差异部分，将有差异的内容进行备份
• 选择性备份：对系统部分数据进行备份
• 冷备：系统停机或维护状态下备份
• 热备：系统运行情况下的备份

1.2. 备份涉及的相关技术

• 数据压缩：降低备份数据的大小
• 数据重复删除：系统的多个相似数据的备份，可以选择性的保留指定个数，降低数据备份的冗余
• 数据复制技术：备份过程中，将数据同步到第二组设备上
• 数据加密技术
• 数据缓存技术

2. 数据库备份内容点

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

• 备份和恢复类型（热备、冷备、增量备份）
• 数据库备份方法（磁盘冗余、mysqldump、binlog）
• 数据库备份和恢复的策略（定期全量，定期增量，定期删除多余备份）
• 使用mysqldump进行备份
• 使用binlog进行备份
• MyISAM表维护和崩溃恢复

2.1. Mysql备份和恢复主题

• 备份类型：逻辑vs物理，完整vs增量
• 创建备份的方法
• 恢复备份的方法
• 备份策略，压缩以及加密
• mysqldump使用
• InnoDB相关备份成分
• 复制可以在多个服务器维护相同的数据（可以做到负载均衡和高可用）
• MySQL InnoDB集群

2.2. 备份和恢复类型

2.2.1. 物理 vs 逻辑备份

• 物理备份：连通存储数据库的内容文件一起备份，针对大型重要数据
  • 包含数据库目录和文件
  • 物理备份方法比逻辑更快，因为它们只涉及文件复制而不进行转换
  • 输出比逻辑备份更紧凑
  • 备份和还原粒度范围从整个数据目录的级别到单个文件的级别（InnoDB表可以分别位于单独的文件中，也可以与其他InnoDB表共享文件存储; 每个 MyISAM表唯一地对应于一组文件。）
  • 备份仅可移植到具有相同或类似硬件特征的其他计算机
  • MEMORY表的数据很难以这种方式备份
  • 可以在MySQL服务器未运行时执行备份。如果服务器正在运行，则必须执行适当的锁定，以便服务器在备份期间不会更改数据库内容。
  • MyISAM表的系统级命令（如cp，scp，tar，rsync），InnoDB的MySQL企业备份工具：mysqlbackup
• 逻辑备份：表示为逻辑数据库结构（CREATE DATABASE， CREATE TABLE语句）和内容（INSERT语句或分隔文本文件）的信息。此类备份适用于较少量的数据，您可以在其中编辑数据值或表结构，或在不同的计算机体系结构上重新创建数据。
  • 查询MySQL服务器以获取数据库结构和内容信息来完成备份
  • 备份比物理方法慢，因为服务器必须访问数据库信息并将其转换为逻辑格式。如果输出写在客户端，则服务器还必须将其发送到备份程序。
  • 输出大于物理备份，特别是以文本格式保存时。
  • 备份和还原粒度可在服务器级别（所有数据库），数据库级别（特定数据库中的所有表）或表级别中使用。无论存储引擎如何，都是如此。
  • 备份不包括日志或配置文件
  • 以逻辑格式存储的备份与机器无关且具有高度可移植性。
  • 需要在运行MySQL服务器的情况下执行逻辑备份。
  • 逻辑备份工具包括mysqldump 程序和SELECT ... INTO OUTFILE语句。这些适用于任何存储引擎，甚至MEMORY。
  • 使用LOAD DATA语句或mysqlimport客户端恢复

2.2.2. 在线(热备) vs 离线备份(冷备)

• 热备：系统运行情况下的备份
  • 备份对其他客户端的干扰较小，其他客户端可以在备份期间连接到MySQL服务器，并且可以根据需要执行的操作来访问数据。
  • 必须小心施加适当的锁定，以便不会发生会损害备份完整性的数据修改。
• 冷备：系统停机或维护状态下备份
  • 客户端可能会受到不利影响，因为备份期间服务器不可用。因此，此类备份通常来自复制从属服务器，可以脱机而不会损害可用性。
  • 备份过程更简单，因为不会受到客户端活动的干扰。

恢复过程中，与热备相比，客户端更有可能受到在线恢复的影响，因为恢复需要更强的锁定

2.2.3. 本地 vs 远程备份

• mysqldump可以连接到本地或远程服务器。对于SQL输出（CREATE和 INSERT语句），可以完成本地或远程转储并在客户端上生成输出。对于分隔文本输出（使用该 –tab选项），将在服务器主机上创建数据文件。
• SELECT ... INTO OUTFILE可以从本地或远程客户端主机启动，但输出文件是在服务器主机上创建的。
• 物理备份方法通常在MySQL服务器主机上本地启动，以便服务器可以脱机，尽管复制文件的目标可能是远程的。

2.2.4. 系统快照

在给定时间点提供文件系统的逻辑副本，而不需要整个文件系统的物理副本。（可通过Veritas，LVM或ZFS等第三方解决方案获得）

2.2.5. 全量 vs 增量备份

• 备份
  • 全量备份：包括MySQL服务器在给定时间点管理的所有数据。
  • 增量备份：包括在给定时间跨度内（从一个时间点到另一个时间点）对数据所做的更改。（通过启用服务器的二进制日志（服务器用于记录数据更改），可以实现增量备份。）
• 恢复
  • 全量恢复：可从完整备份中恢复所有数据。这会将服务器实例还原到备份时的状态。如果该状态不够充分，则还可以在全量还原之后，附加恢复自全量备份以来所做的增量备份，以使服务器处于更新状态。
  • 增量恢复：是恢复在给定时间跨度内所做的更改。这也称为时间点恢复，因为它使服务器的状态达到给定时间。时间点恢复基于二进制日志，通常在备份文件完全恢复之后，将备份文件还原到备份时的状态。然后，在二进制日志文件中写入的数据更改将作为增量恢复应用于重做数据修改，并使服务器达到所需的时间点。

2.2.6. 备份策略

设定自动化备份计划，压缩备份输出可减少空间需求，输出加密可提供更好的安全性，防止未经授权访问备份数据。

3. 数据库备份方法

3.1. 使用mysqldump进行备份

3.2. 创建包含表数据的文本文件备份

3.2.1. MyISAM

1. 增加读锁，客户端可以在您复制数据库目录中的文件时继续查询表，同时也保证一致的备份： FLUSH TABLES tbl_list WITH READ LOCK;
2. 通过复制表文件（.MYD，.MYI文件和关联*.sdi文件）来备份MyISAM表

3.2.2. InnoDB

针对InnoDB引擎，表文件复制方法不起作用。

此外，即使服务器未主动更新数据，InnoDB 仍可能已将修改后的数据缓存在内存中而不会刷新到磁盘。

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

MySQL支持增量备份：必须使用–log-bin启用二进制日志记录的选项启动服务器;

二进制日志文件为您提供了在执行备份之后复制数据库所需的信息，若想要进行增量备份（包含自上次完全备份或增量备份以来发生的所有更改），需要使用旋转二进制日志FLUSH LOGS。完成此操作后，需要将所有增量备份二进制日志复制到备份位置，这些日志的范围从上次完全备份或增量备份到最后一个备份之一。下次执行完整备份时，还应使用FLUSH LOGS或mysqldump --flush-logs旋转二进制 日志。

3.4. 基于从库进行备份

基于在从服务器上而不是在主服务器上设置复制和执行备份

• LOAD DATA
• 备份SQL_LOAD-* files
• 指定临时文件--slave-load-tmpdir

3.5. 使用文件系统快照进行备份

Veritas file system(LVM和ZFS都类似):

• From a client program, execute FLUSH TABLES WITH READ LOCK.
• From another shell, execute mount vxfs snapshot.
• From the first client, execute UNLOCK TABLES.
• Copy files from the snapshot.
• Unmount the snapshot.

4. 备份和恢复示例

4.1. 选择负载较低时，使用mysqldump进行备份

1

shell> mysqldump --all-databases --master-data --single-transaction > backup_sunday_1_PM.sql    

mysqldump相关说明：

1. mysqldump备份操作在转储开始时（使用FLUSH TABLES WITH READ LOCK）获取所有表的全局读锁定。
2. 获取此锁定后，将读取二进制日志坐标并释放锁定。(在FLUSH发出语句时，Mysql正在运行长更新语句则备份操作可能会停止，直到这些语句完成，然后转储释放锁。）
3. 假设要备份的表是InnoDB表，因此--single-transaction使用一致的读取并保证mysqldump看到的数据不会更改。（mysqldump进程InnoDB看不到其他客户端对表所做的更改。）

4.2. 全量备份和增量备份考虑

• 全量备份：完整备份是必要的，但创建花费大量时间生成，即使是自上次完全备份以来未发生更改的部分。
• 增量备份：增量备份更小，生成时间更短。
• 折中权衡：定期进行初始完整备份然后进行增量备份更有效；在恢复时，只能通过重新加载完整备份来恢复数据，然后处理增量备份以恢复增量更改。

要进行增量备份，我们需要保存增量更改：

1. 应始终使用--log-bin启用该日志的选项启动MySQL服务器；

2. 启用二进制日志记录后，服务会在更新数据时将每个数据更改写入文件。(SHOW BINARY LOGS;)

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

   $ cat /var/lib/mysql-data/binlog.index ./binlog.000001 ./binlog.000002 ./binlog.000003 ./binlog.000004 ./binlog.000005 ./binlog.000006 mysql> SHOW BINARY LOGS; +---------------+-----------+ | Log_name | File_size | +---------------+-----------+ | binlog.000001 | 3071654 | | binlog.000002 | 178 | | binlog.000003 | 3688 | +---------------+-----------+

3. 每次MySQL重新启动时，服务都会使用序列中的下一个数字创建一个新的二进制日志文件;

4. 服务运行时，通过发出FLUSH LOGSSQL语句或使用mysqladmin flush-logs命令，冲刷当前的二进制日志文件并手动开始新的日志文件，mysqldump也有一个刷新日志的选项--flush-logs；

5. 数据目录中的.index文件包含目录中所有MySQL二进制日志的列表；

6. --master-data选项导致mysqldump将二进制日志信息写入其输出：

   • 如果选项值为1，语句不会写为注释，并在重新加载转储文件时生效，默认值为1。
   • 如果选项值为2，语句将写为SQL注释，因此仅提供信息，重新加载转储文件时没有任何效果。

7. 确保在进行完全备份时FLUSH日志(--flush-logs)，则之后创建的二进制日志文件，将包含自备份以来所做的所有数据更改记录；

   1 2 3 4 5 6 7 8

   shell> mysqldump --single-transaction --flush-logs --master-data=2 \ --all-databases > backup_sunday_1_PM.sql // 查看sql文件 -- -- Position to start replication or point-in-time recovery from -- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000009', MASTER_LOG_POS=155;

8. 因为mysqldump做了全量备份，上述注释意味着：

   • 转储文件包含了在binlog.000009二进制日志文件之前所做的所有更改；
   • 在mysqldump备份后记录的所有数据更改，都不在转储sql文件中，但存放在binlog.000009的二进制日志文件或更高版本中；

9. MySQL二进制日志占用磁盘空间，为了腾出空间，不时清除它们，一种方法是删除不再需要的二进制日志--delete-master-logs，（但如果是主从结构，删除主的可能会到导致从尚未完全处理二进制文件而报错），例如当我们进行完整备份时：

   1 2	shell> mysqldump --single-transaction --flush-logs --master-data=2 \ --all-databases --delete-master-logs > backup_sunday_1_PM.sql

4.3. 备份恢复示例

1. 首先我们恢复最近的上一次完整备份;

   1	shell> mysql < backup_sunday_1_PM.sql

2. 基于全量备份的时间点位置，进行增量备份

   1	shell> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql

3. 上述已经将数据恢复到周二下午1点的状态，但仍然缺少从该日期到崩溃日期的更改。为了继续恢复，我们需要考虑让MySQL服务器将其MySQL二进制日志存储到与存储其数据文件的地方不同的安全位置（RAID磁盘，SAN，…），以便这些日志不会在被破坏的磁盘上。

4. 继续恢复gbichot2-bin.000009 手头的文件（以及任何后续文件）：

   1	shell> mysqlbinlog gbichot2-bin.000009 ... | mysql

4.4. 备份策略

确保您能够安然入睡，请遵守以下准则

1. 始终使用--log-bin选项运行MySQL服务；
2. 使用mysqldump命令进行定期完整备份，该命令可以进行在线非阻塞备份；
3. 通过使用FLUSH LOGS或mysqladmin flush-logs刷新日志来定期进行增量备份；

5. mysqldump使用

本节介绍如何使用mysqldump生成转储文件，以及如何重新加载转储文件。

转储文件可以通过多种方式使用:

• 作为备份，在数据丢失的情况下启用数据恢复；
• 作为配置复制从属的数据源；
• 作为实验数据的来源：
  • 制作可以在不更改原始数据的情况下使用的数据库副本。
  • 测试潜在的升级不兼容性。

mysqldump产生两种类型的输出，具体取决于是否给出了选项--tab：

Create tab-separated textfile for each table to given path. (Create .sql and .txt files.) NOTE: This only works if mysqldump is run on the same machine as the mysqld server.

• 没有--tab， mysqldump将SQL语句写入标准输出。
  • 此输出包含 CREATE用于创建转储对象（数据库，表，存储例程等）的INSERT语句，以及 用于将数据加载到表中的语句。输出可以保存在文件中，稍后使用mysql重新加载 以重新创建转储的对象。选项可用于修改SQL语句的格式，以及控制转储哪些对象。
• 使用--tab， mysqldump为每个转储表生成两个输出文件。
  • 服务器将一个文件写为制表符分隔的文本，每个表行一行。此文件tbl_name.txt在输出目录中命名。服务器还将CREATE TABLE表的语句发送 到mysqldump，mysqldump将其写为tbl_name.sql输出目录中指定的文件 。

5.1. 使用mysqldump以SQL格式转储数据

1. 默认情况下，mysqldump将信息作为SQL语句写入标准输出:

   1	shell> mysqldump [arguments] > file_name

2. 要转储所有数据库：

   1	shell> mysqldump --all-databases > dump.sql

3. 仅转储特定数据库:

   1	shell> mysqldump --databases db1 db2 db3 > dump.sql

4. 如果没有--databases选项，mysqldump会将第一个名称视为数据库名称，将后续名称视为表名称

使用--all-databases或者--databases， mysqldump 在每个数据库的转储输出之前写入CREATE DATABASE和USE语句。

这可确保在重新加载转储文件时，如果每个数据库不存在，它将创建每个数据库并使其成为默认数据库，以便将数据库内容加载到它们所来自的同一数据库中。 如果要在重新创建数据库之前使转储文件强制删除每个数据库，请同时使用该--add-drop-database选项。

5.2. 使用mysqldump转存储单库

1
2
3
4

shell> mysqldump --databases test > dump.sql

// 在单数据库的情况下，允许省略 --databases选项 （生成的sql不会含 CREATE DATABASE 部分）：
shell> mysqldump test > dump.sql

两个命令之间的区别在于，没有--databases，转储输出则不会包含CREATE DATABASE或USE语句，同时也需要注意：

1. 重新加载转储文件时，必须指定默认数据库名称，以便服务器知道要重新加载的数据库。
2. 可以指定与原始名称不同的数据库名称，方便将数据重新加载到其他数据库中。
3. 若重新加载的数据库不存在，则必须先创建它。
4. 由于输出不包含任何CREATE DATABASE语句，因此该--add-drop-database 选项无效，如果你使用它，也不会产生任何DROP DATABASE声明。

5.3. 使用mysqldump转存储单库中多表

1

shell> mysqldump test t1 t3 t7 > dump.sql

5.4. SQL格式备份恢复

要重新加载由mysqldump编写的 包含SQL语句的转储文件，请将其用作mysql客户端的输入。

1. 如果转储文件是由mysqldump使用 –all-databasesor –databases选项创建的 ，则它包含CREATE DATABASE和 USE语句，并且没有必要指定要加载数据的默认数据库：

   1	shell> mysql < dump.sql

2. 从mysql中，使用source命令：

   1	mysql> source dump.sql

3. 如果文件是不包含CREATE DATABASE和 USE语句的单数据库转储 ，请首先创建数据库（如有必要），然后在加载转储文件时指定数据库名称：

   1 2	shell> mysqladmin create db1 shell> mysql db1 < dump.sql

4. 也可以在mysql中创建数据库，将其选为默认数据库，然后加载转储文件：

   1 2 3

   mysql> CREATE DATABASE IF NOT EXISTS db1; mysql> USE db1; mysql> SOURCE dump.sql

5.5. 分隔文本格式备份

5.5.1. 备份

1
2
3

shell> mysqldump --tab=/tmp db1
shell> mysqldump --tab=/tmp --fields-terminated-by=,
         --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1

5.5.2. 恢复

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

// 基于命令行mysqlimport
shell> mysql db1 < t1.
shell> mysqlimport db1 t1.txt

// 基于mysql客户端中的LOAD DATA语句 
mysql> USE db1;
mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1;

// 若导出过程中指定了分隔符，则导入也必须指定
shell> mysqlimport --fields-terminated-by=,
         --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1 t1.txt

mysql> USE db1;
mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1
       FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY '"'
       LINES TERMINATED BY '\r\n';

5.6. mysqldump tips

5.6.1. 数据库的拷贝

1
2
3

shell> mysqldump db1 > dump.sql
shell> mysqladmin create db2
shell> mysql db2 < dump.sql

5.6.2. 数据库从一个服务器拷贝到另一个

1. server 1:

   1	shell> mysqldump --databases db1 > dump.sql

2. 拷贝dump.sql到server 2

3. server 2:

   1	shell> mysql < dump.sql

5.6.3. 数据库表定义和内容分开导出

1
2

shell> mysqldump --no-data test > dump-defs.sql
shell> mysqldump --no-create-info test > dump-data.sql

5.6.4. 几个选项控制mysqldump如何处理存储的程序（存储过程和函数，触发器和事件）

• –events: Dump Event Scheduler events
• –routines: Dump stored procedures and functions
• –triggers: Dump triggers for tables

1

shell> mysqldump --no-data --routines --events test > dump-defs.sql

6. 增量恢复：使用二进制日志进行时间点/位置恢复

通常，在还原完整备份之后执行此类型的恢复。时间点恢复是指恢复自给定时间点以来所做的数据更改，必须使用--log-bin启用二进制日志记录的选项启动服务器。

1. 查看所有二进制日志文件的列表：

   1	mysql> SHOW BINARY LOGS;

2. 确定当前二进制日志文件的名

   1	mysql> SHOW MASTER STATUS;

3. mysqlbinlog实用程序将二进制日志文件中的事件从二进制格式转换为文本，以便可以执行或查看它们：

   1	shell> mysqlbinlog binlog_files | mysql -u root -p

4. 如果二进制日志文件已加密，mysqlbinlog无法直接读取它们，可以使用–read-from-remote-server从服务器读取它们：

   1	shell> mysqlbinlog --read-from-remote-server --host=host_name --port=3306 --user=root --password --ssl-mode=required binlog_files | mysql -u root -p

5. 需要确定事件时间或位置以在执行事件之前选择部分日志内容时，查看日志内容非常有用：

   1 2 3 4 5 6

   shell> mysqlbinlog binlog_files | more // 导出到文件再看 shell> mysqlbinlog binlog_files > tmpfile shell> ... edit tmpfile ... // 可续编辑tmpfile，然后再导入 shell> mysql -u root -p < tmpfile

6. 多个二进制文件，安全方法是使用与服务器的单个连接来处理它们，为防止创建表和使用表在不同的binlog中，考虑多个binlog联合使用：

   1 2 3 4 5

   shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p // 另一种方法是将所有日志写入单个文件，然后处理该文件： shell> mysqlbinlog binlog.000001 > /tmp/statements.sql shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql shell> mysql -u root -p -e "source /tmp/statements.sql"

7. 从包含GTID的二进制日志中读取时写入转储文件

   1 2 3

   shell> mysqlbinlog --skip-gtids binlog.000001 > /tmp/dump.sql shell> mysqlbinlog --skip-gtids binlog.000002 >> /tmp/dump.sql shell> mysql -u root -p -e "source /tmp/dump.sql"

6.1. 基于Event Times恢复

1. 指示恢复的故障产生之前的数据

   1 2 3

   // 假设确切地在2005年4月20日上午10:00执行了删除大表的SQL语句。要还原表和数据，可以还原前一晚的备份，然后执行以下命令 shell> mysqlbinlog --stop-datetime="2005-04-20 9:59:59" \ /var/log/mysql/bin.123456 | mysql -u root -p

2. 希望恢复之后发生的活动

   1 2 3

   // 在此命令中，将重新执行从上午10:01开始记录的SQL语句。 shell> mysqlbinlog --start-datetime="2005-04-20 10:01:00" \ /var/log/mysql/bin.123456 | mysql -u root -p

3. 查看日志确保命令的确切执行时间：

   1 2	// 转binlog为text，然后使用文本编辑器打开文件进行检查 shell> mysqlbinlog /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

6.2. 基于Event Positions恢复

mysqlbinlog 的–start-position和 –stop-position选项 可用于指定日志位置，而不是指定日期和时间 。除了指定日志位置编号而不是日期之外，它们的工作方式与启动和停止日期选项相同。使用位置可以使您更准确地了解要恢复的日志部分，尤其是在许多事务与破坏性SQL语句同时发生的情况下。

1. 确定位置编号: 在不需要的事务的时间附近执行mysqlbinlog，将结果重定向到文本文件以供检查。

   1 2 3 4

   // 此命令在/tmp目录中创建一个小文本文件，该文件包含执行有害SQL语句时的SQL语句。 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

2. 使用文本编辑器打开/tmp/mysql_restore.sql，然后查找您不想重复的语句，确定二进制日志中的位置以停止和恢复恢复并记下它们。
3. 恢复上一个备份文件后，使用位置编号处理二进制日志文件：

   1 2 3 4 5 6 7

   // 恢复所有事务，直到给出停止位置。 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

7. 总结

文章内容通过信息技术与数据管理中的备份了解，逐步展开过渡到Mysql的各种备份类型，备份方式，以及备份策略的了解，同时对比了各自的特点以及优劣；

接着基于mysqldump工具的备份和恢复使用简单介绍，然后结合binlog，对故障产生后，进行数据库数据的恢复的的操作进行了简要描述。

希望可以对Mysql备份和恢复有了一个细致和全面的了解。