读书笔记：MySQL备份与恢复

1.备份的分类

     ① 根据不同的备份方法可分为：

• 热备（Hot Backup）
• 冷备（Cold Backup）
• 温备（Warm Backup）
  

     热备指在数据库运行中直接备份，对正在运行的数据库操作没有任何影响。MySQL官方手册称之为在线备份（Online Backup）。

     冷备是在数据库停止运行的情况下备份，一般只需要复制相关的数据库物理文件，是最简单的备份方式。MySQL官方手册称之为离线备份（Offline Backup）。

     温备也是在数据库运行的时候进行，但会对当前的数据库有影响，如加一个全局读锁以保证备份数据的一致性。

     ②根据不同的备份文件内容可分为：

• 逻辑备份
• 裸文件备份
  

     逻辑备份备份出的文件内容是可读的。一般是文本文件，文件内容一般是一条条的SQL语句或者表中的实际数据，可以通过mysqldump命令和SELECT * INTO OUTFILE方法。逻辑备份的好处是可以查看修改备份文件的内容，适用于数据库升级和迁移等场景，缺点是备份恢复所需要的时间往往比较长。

     裸文件备份是指复制数据库的物理文件，既可以在数据库运行的时候复制（ibbackup、XtraBackup），也可以在数据库停止运行的时候直接复制数据文件。裸文件备份的恢复时间往往比逻辑备份短很多。

     ③根据不同的备份数据库内容可分为：

• 完全备份
• 增量备份
• 日志备份
  

     完全备份是对数据库进行一个完整的备份。

     增量备份是在上次完全备份的基础上，对更改的数据进行备份。

     日志备份指对MySQL二进制日志的备份，然后进行重做（replay）来完成数据库point-in-time的恢复，用于MySQL复制（replication）场景。

     ④根据不同的备份地点可分为：

• 本地备份
• 异地备份
  

     狭义的本地即MySQL运行的服务器，广义的本地指的是足够近的一个地理范围，异地即指本地以外了。本地备份是常备策略，而异地备份是灾备目的。本地库出现了不可抗损坏，还有异地备份。地球毁灭了，就上诺亚方舟。

2.逻辑备份

备份

     ①mysqldump

mysqldump -uroot -p [option] > mysqldump.sql

     mysqldump通常用来完成转存（dump）数据库的备份和不同数据库之间的移植。mysqldump是命令行工具，以下是一些常用的选项。

• --single-transaction。备份开始前先执行START TRANSACTION命令，以此获取一份一致性的备份。该参数只对InnoDB存储引擎有效。使用该参数进行备份时，要确保没有其他的DDL语句执行，一致性读并不能隔离DDL操作。
• --lock-tables（-l）。备份过程中依次锁住每个要备份的库下的所有表，一般用于MyISAM存储引擎，备份过程中只能对库进行读操作，但可以获取一份一致性备份。--lock-tables和--single-transaction是互斥（exclusive）的，因此对于仅MyISAM库表的备份使用--lock-tables；对于仅InnoDB库表的备份使用--single-transaction；包含两种存储引擎库表的备份就只能选择使用--lock-tables了。不过，因为--lock-tables选项是依次对每个要备份的库下的所有表加锁，因此只能保证每个库下表的一致性，而不能保证所有备份库表的一致性。
• --lock-all-tables（-x）。备份过程中一次性地对所有库表加锁，以此避免--lock-tables选项不能同时锁住所有库表的问题。
• --add-drop-database。dump文件中在CREATE DATABASE前加上DROP DATABASE语句。默认情况下，dump文件不会包含CREATE DATABASE，除非指定了该选项。该选项需要和--databases或者--all-databases一起使用。表级别的选项为--add-drop-table。
• --tab=path（-T path）。mysqldump对于每张表创建一个包含CREATE TABLE语句的table_name.sql文件和以TAB分割的数据文件table_name.txt文件。
• --master-data=value。以该选项创建的备份主要用来建立一个replication。value值为1时，转存文件中会写入CHANGE MASTER语句；value值为2时，CHANGE MASTER语句以SQL注释的方式写出；默认情况下，value值为空。
  

     ②SELECT ... INTO OUTFILE

mysql> select * into outfile 'mysqltb.txt' from mysqltb;

     SELECT ... INTO OUTFILE是另一种逻辑备份的方法，主要是导出一张表中的数据。导出文件所在的路径的权限必须是MySQL所有（mysql:mysql），并且没有重复文件存在，否则会写出失败。mysqldump也可以导出一张表中的数据，而且可以一次完成多张表的导出，而且能够实现数据一致性，因此可以作为更佳选择。

恢复

     ①sql语句文件

• mysql命令行

mysql -uroot -p < mysqldump.sql

• SQL命令SOURCE
  

mysql> source mysqldump.sql;

     ②文本数据文件

• mysqlimport命令行
  

mysqlimport -uroot -p mysqldb mysqltb.txt

• SQL命令LOAD DATA INFILE
  

mysql> load data infile 'mysqltb.txt' into table mysqldb.mysqltb;

3.二进制日志备份与恢复

     二进制日志默认情况下并没有启用，要开启二进制日志记录，需要去MySQL配置文件进行设置。MySQL的point-in-time恢复工作和高可用架构replication都需要二进制日志。就InnoDB存储引擎而言，除了启用二进制日志以外，还需要开启一些其他的参数来保证最为安全和正确地记录二进制日志。以下是推荐的配置。默认情况下，InnoDB已经设置“ innodb_flush_log_at_trx_commit ”的值为“ 1 ”，即确保每个提交的事务写入重做日志文件。

[mysqld]# 开启二进制日志记录log-bin = mysql-bin# 设置二进制日志磁盘同步写sync_binlog = 1# 确保二进制日志与InnoDB数据文件一致innodb_support_xa = 1

备份

     备份二进制日志之前，可以通过FLUSH LOGS命令来生成一个新的二进制日志文件，然后备份之前的二进制日志。

恢复

mysqlbinlog [option] mysqlbinlog.*

     二进制日志的恢复需要使用mysqlbinlog命令行工具。

     ①简单恢复

mysqlbinlog binlog.0000001 | mysql -uroot -p

     ②多文件恢复

mysqlbinlog binlog.[0-10]* | mysql -uroot -p

     ③文件中转导入（导入前可以修改中转文件）

mysqlbinlog binlog.0000001 > binlog.sqlmysqlbinlog binlog.0000002 >> binlog.sqlmysql -uroot -p < binlog.sql(或：mysql -uroot -p -e "source binlog.sql")

     ④带条件选项恢复

     --start-position和--stop-position选项指定从二进制日志的某个偏移量进行恢复；--start-datetime和--stop-datetime选项指定从二进制日志的某个时间点进行恢复。

mysqlbinlog --start-position=1024 binlog.0000001 | mysql -uroot -p

4.热备

     MySQL热备主要是借助一些第三方工具，如ibbackup和XtraBackup。对于InnoDB存储引擎，热备的工作原理基本如下：

①记录备份开始时InnoDB存储引擎重做日志文件检查点的LSN。

②复制共享表空间文件和独立表空间文件。

③记录复制完表空间文件后InnoDB存储引擎重做日志文件检查点的LSN。

④复制在备份过程中产生的重做日志。

     热备过程中不会对数据库本身有任何影响，所做的操作只是复制数据库文件，因此任何数据库操作都是允许和不阻塞的。

     ibbackup是InnoDB存储引擎官方提供的热备工具，但属于商业软件；Xtrabackup是数据库服务商Percona公司开发的开源免费热备工具，实现了ibbackup的所有功能，并且扩展支持真正的增量备份功能，因此可以作为热备工具的极佳选择，所以另一篇独立的博文专门学习使用它，参考http://blog.csdn.net/sweeper_freedoman/article/details/73523693。

5.复制

     复制（replication）是MySQL数据库提供的高可用高性能解决方案，因其实用和操作性原因，所以另一篇独立的博文专门学习使用它，参考http://blog.csdn.net/sweeper_freedoman/article/details/53428483。

内容整理自《MySQL技术内幕 InnoDB存储引擎》（https://book.douban.com/subject/24708143/）。