MySQL 备份&恢复

1. 备份&恢复策略

2. 逻辑备份&恢复

3. 物理备份&恢复

 

1. 备份&恢复策略

定制合理的备份策略无疑是很重要的，以下是我们在进行备份或恢复操作时需要考虑的一些因素。

• 确定要备份的表的存储引擎是事务型还是非事务性，两种不同的存储引擎备份方式在处理数据一致性方面是不太一样的。
• 确定使用全备份还是增量备份：
  • 全备份的优点是备份保持最新备份，恢复的时候可以花费更少的时间；缺点是如果数据量大，将会花费很多的时间，并对系统造成较长时间的压力。
  • 增量备份则恰恰相反，只需要备份每天的增量日志，备份时间少，对负载压力也小；缺点就是恢复的时候需要全备份加上次备份到故障前的所有日志，恢复时间会长些。
• 可以考虑采取复制的方法来做异地备份，但是记住，复制不能代替备份，它对数据库的误操作也无能为力。
• 要定期做备份，备份的周期要充分考虑系统可以承受的恢复时间。备份要在系统负载较小的时候讲行
• 确保 MySQL 打开了 log-bin 选项，有了 BINLOG，MySQL 才可以在必要的时候做完整恢复，或基于时间点的恢复，或基于位置的恢复。
• 要经常做备份恢复测试，确保备份是有效的，并日是可以恢复的。

  

2. 逻辑备份&恢复

在 MySQL 中，逻辑备份的最大优点是对于各种存储引擎，都可以用同样的方法来备份；而物理备份则不同，不同的存储引擎有着不同的备份方法。

因此，对于不同存储引擎混合的数据库，用逻辑备份会更简单一些。

MySQL 中的逻辑备份是指将数据库中的数据备份为一个文本文件，备份的文件可以被查看和编辑。在 MySQL 中，使用 mysqldump 工具来完成逻辑备份。

备份

有以下 3 种方法来调用 mysqldump。

• 备份指定的数据库（表结构及数据），或者此数据库中某些表：

shell> mysqldump [options] db_name [tables]

• 备份指定的一个或多个数据库：

shell> mysqldump [options] --database DB1 [DB2 DB3...]

• 备份所有数据库：

shell> mysqldump [options] --all--database

如果没有指定数据库中的任何表，默认导出所有数据库中所有表。

以下给出一些使用 mysqldump 工具进行备份的例子：

• 备份所有数据库到当前目录下：

[zzx@localhost ~]$ mysqldump -uroot -p --all-database > all.sql
Enter password:

• 备份数据库 test：

[zzx@localhost ~]$ mysqldump -uroot -p test > d:/test.sql
Enter password:

• 备份数据库 test 下的表 emp：

[zzx@localhost ~]$ mysqldump -uroot -p test emp > emp.sql

• 其中 mysqldump 的选项很多，具体可以使用“--help”参数查看帮助：

mysqldump --help

需要强调的是，为了保证数据备份的一致性，MyISAM 存储引擎在备份的时候需要加上 -l 参数，表示将所有表加上读锁，在备份期间，所有表将只能读而不能进行数据更新。但是对于事务存储引擎（InnoDB 和 BDB）来说，可以采用更好的选项 --single-transaction，此选项将使得 InnoDB 存储引擎得到一个快照（Snapshot），使得备份的数据能够保证一致性。

恢复

恢复操作默认会覆盖重名的库表数据，所以恢复时要谨慎使用。

恢复的两种命令

mysqldump

mysqldump 的恢复也很简单，将备份作为输入执行即可，具体语法如下：

mysql -uroot -p dbname < bakfile

注意，将备份恢复后的数据并不是实时最新的，因此若要完全恢复，还需要将备份操作后的执行日志进行重做，语法如下：

mysqlbinlog binlog_file | mysql -u root -p***

source

还有一种恢复方式是在 MySQL 命令行下用 source 命令执行 SQL 文件，如：

mysql> use dbname;
Database changed
mysql> source d:/bakfile;

以上两种恢复方式的区别：

• mysqldump：仅恢复数据库中目前存在的库表数据。例如备份文件中的某表在数据库中已被删除，则不会恢复该表及数据。
• source：会恢复备份文件中的所有数据。 

基于时间点的恢复

由于误操作，比如误删除了一张表，这时使用完全恢复是没有用的，因为日志里面还存在误操作的语句，我们需要的是恢复到误操作之前的状态，然后跳过误操作语句，再恢复后面的执行语句，完成我们的恢复。这种恢复叫不完全恢复，在 MySQL 中，不完全恢复分为基于时间点的恢复和基于位置的恢复。

以下是基于时间点恢复的操作步骤：

• 1）如果上午 10 点发生了误操作，可以用以下语句用备份和 BINLOG 将数据恢复到故障前：

shel1>mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/bin.123456| mysql -uroot -pmypwd

• 2）跳过故障时的时间点，继续执行后面的 BINLOG，完成恢复。

shell>mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/bin.123456| mysql -u root -pmypwd

基于位置的恢复

和基于时间点的恢复类似，但是更精确，因为同一个时间点可能有很多条 SQL 语句同时执行。

恢复的操作步骤如下：
在 shell 下执行如下命令:

shell>mysqlbinlog --start-date="2005-04-209:55:00" --stop-date="2005-04-20 10:05:00" /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

该命令将在 /tmp 目录创建小的文本文件，编辑此文件，找到出错语句前后的位置号，例如前后位置号分别是 368312 和 368315。

恢复了以前的备份文件后，应从命令行输入下面内容：

shell>mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456| mysql -u root-pmypwd
shell>mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456| mysql -u root -pmypwd

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

 

3. 物理备份&恢复

物理备份又分为冷备份和热备份两种，和逻辑备份相比，它的最大优点是备份和恢复的速度更快，因为物理备份的原理都是基于文件的 cp。

冷备份

冷备份其实就是停掉数据库服务，cp 数据文件的方法。这种方法对 MyISAM 和 InnoDB 存储引擎都适合，但是一般很少使用，因为很多应用是不允许长时间停机的。

进行备份的操作如下：停掉 MySQL 服务，在操作系统级别备份 MySQL 的数据文件和日志文件到备份目录。

进行恢复的操作如下：首先停掉 MySQL 服务，在操作系统级别恢复 MySQL 的数据文件；然后重启 MySQL 服务，使用 mysqlbinlog 工具恢复自备份以来的所有 BINLOG。

热备份

InnoDB 存储引擎

ibbackup 是 Innobase 公司（www.innodb.com）的一个热备份工具，专门对 InnoDB 存储引擎进行物理热备份，此工具是收费的，但可以免费使用 1 个月。Innobase 公司已经于 2005 年被 Oracle 公司所收购。
下面简单介绍一下使用 ibbackup 工具的备份步骤。

1）编辑用于启动的配置文件 my.cnf 和用于备份的配置文件 backup-my.cnf。

my. cnf 的例子如下：

[mysqld]
datadir = /home/heikki/data
innodb_data_home_dir = /home/heikki/data
innodb_data_file_path = ibdatal:100M;ibdata2:200M;ibdata3:500M:autoextend
innodb_log_group_home_dir = /home/heikki/data
set-variable = innodb_log_files_in_group-2
set-variable = innodb_log_file_size=20M

2）如果想备份到 /home/heikki/backup，则 backup-my.cnf 的例子如下：

[mysqld]
datadir = /home/heikki/backup
innodb_data_home_dir = /home/heikki/backup
innodb_data_file_path = ibdatal:100M;ibdata2:200M; ibdata3:500M:autoextend
innodb_log_group_home_dir =/home/heikki/backup
set-variable = innodb_log_files_in_group=2
set-variable = innodb_log_file_size=20M

3）开始备份，具体如下：

$ibbackup /home/pekka/my.cnf /home/pekka/backup-my.cnf

注意：ibbackup 工具不会覆盖任何重名的文件，因此在新的备份开始之前，需要确保备份目录中没有重名文件，否则备份很可能会失败。

因为在 cp 数据文件时，文件内容在不断地变化，因此在不同的时间点 cp 的数据块中的数据很可能是不一致的。因此，ibbackup 在备份期间用一个日志文件 ibbackup_logfile 来记录备份期间数据的变化，在恢复的时候就可以用此日志文件对备份的数据文件进行日志重做，使得备份的数据能够保持完整性和一致性。

当主数据库出现故障时，我们需要用备份进行恢复，恢复的步骤如下：

• 1）进行日志重做。如前面所述，利用下面的命令对备份数据进行日志重做：

shell>ibbackup --apply-log /home/pekka/backup-my.cnf

• 2）恢复后重启数据库服务：

shell>./bin/mysqld_saft --defaults-file-/home/pekka/backup-my.cnf &

• 3）服务重启后，利用 BINLOG 日志将备份点与故障点之间的剩余数据进行恢复：

mysqlbinlog binlog_file | mysql -u root -p***

ibbackup 还有一些其他的功能，比如压缩备份、不完全恢复等。

MyISAM 存储引擎

MyISAM 存储引警的热备份有很多方法，本质其实就是将要备份的表加读锁，然后再 cp 数据文件到备份目录。常用的有以下两种方法。

方法 1：使用 mysqlhotcopy 工具

mysqlhotcopy 是 MysQL 自带的一个热备份工具，使用方法很简单：

shell> mysqlhotcopy db_name [/path/to/new_directory]

mysqlhotcopy 有很多选项，具体可以使用“--help”查看帮助:

mysqlhotcopy --help

方法2：手工锁表 copy

在 mysqlhotcopy 使用不正常的情况下，可以手工来做热备份，操作步骤如下：

首先数据库中所有表加读锁：

mysql>flush tables for read;

然后 cp 数据文件到备份目录即可。