Mysql 备份与恢复

jinying8869

Mysql 备份与恢复的重要性就不多说了~~

备份方式：逻辑备份、物理备份（ 每种方式又可以分为热备跟冷备 ）

热备份：技术含量高，不影响业务，不停止数据库。

冷备份：停止数据库，拷贝所有数据文件，So Easy !

一、逻辑备份

逻辑备份就是备份软件以数据库逻辑结构对象为单位，将数据库中的数据按照预定义的逻辑关联格式一条一条生成相关纯文本的 SQL 语句。
逻辑备份一般用于数据迁移或者数据量小的时候，也是目前中小型系统最常使用的备份方式，逻辑备份采用的是数据导出的备份方式。

逻辑备份能做什么：

> 通过逻辑备份，我们可以通过执行相关的 SQL 或命令将数据库中的相关数据完全恢复到备份时候所处的状态，而不影响不相关的数据。
> 通过全库的逻辑备份，我们可以在新的 Mysql 环境下完全重建一个与备份时候完全一样的数据库，并且不受 Mysql 所处平台的类型限制。
> 通过特定条件的逻辑备份，我们可以将某些特定数据轻松迁移（同步）到其他的 Mysql 或者另外的数据库环境。
> 通过逻辑备份，我们可以仅仅恢复备份集中的部分数据而不需要全部恢复。

常用的逻辑备份实现方式：> 将数据库生成可以完全重现当前数据库中数据的 Insert 语句。

1、生成 Insert 语句的备份（ 使用其自带的 Mysqldump 逻辑备份工具 ）

[iyunv@Mysql backup]# mysqldump -uroot -p123456 --flush-logs --default-character-set=utf8 --single-transaction --master-data=2 foodb | gzip > foodb.sql.gz

## --flush-logs , -F       备份前刷新 binlog 日志
## --default-character-set 指定字符集为 utf8
## --single-transaction    只针对 Innodb 表有效，备份 MyISAM 表时会有 --lock-tables 选项开启，当所备份的表在多个数据库时要用 --lock-all-tables（ 针对 MyISAM ）
## --master-data=1,2       将当前数据库使用的 binlog 日志及 POS 值记录在备份文件中（ 2 为注释的方式 ）

## --all-databases , -A    备份全部数据库，这种备份恢复时，可以不创建数据库（ 可以在一个全新的 Mysql 环境下，生成跟原库一模一样的数据库 ）

## 思考：--single-transaction 跟 --lock-tables 是互斥的，也是就不可以一起使用。如果一个库中既有 Innodb ，又有 MyISAM 时，如何选用参数 ？

2、备份恢复

[iyunv@Mysql backup]# gunzip < foodb.sql.gz | mysql -uroot -p123456 foodb

## 如果备份的时候采用了压缩的格式，恢复时也要先解压才可以恢复（ 可以使用上面这条指令格式，也可以先解压、再恢复 ）

[iyunv@Mysql backup]# gunzip foodb.sql.gz
[iyunv@Mysql backup]# mysql -uroot -p123456 foodb < foodb.sql

## 先解压，再恢复采用这种方式

mysql> use foodb;
mysql> source /data/backup/foodb.sql

## 进入数据库的恢复方式

3、逻辑备份恢复测试

数据库备份最重要的、最关键的一个用途就是，当数据库出现某些异常状况，需要对数据进行恢复时使用的。
如果数据库出现问题，恢复时发现之前做的数据备份不可用或不能达到做备份时所预期的效果，那就很郁闷了~~
所以周期性的模拟恢复测试，是很有必要的。

怎么做恢复测试：线上环境肯定是不能用来做恢复测试的，不过我们可以做一套模拟环境来恢复数据，然后去检查备份数据时所预期的效果，或抽样恢复测试等等！

二、物理备份

物理备份就是对数据库的物理对象进行备份，就跟逻辑备份是根据数据库逻辑对象进行备份一样。
数据库物理对象主要由数据库的物理数据文件、日志文件以及配置文件等组成。Mysql 中，除 Mysql 系统共有的一些日志文件和系统表数据文件外，每种存储引擎还有不一样的物理对象。

Mysql 物理备份所需文件：( 无论什么存储引擎，每个数据库都会在 Datadir 目录下创建一个文件夹，包括系统信息数据库 )

1、MyISAM 存储引擎（ 非事物存储引擎 ）：所有数据都存放在 Mysql 配置中所设定的 Datadir 目录下。

MyISAM 存储引擎除了备份 Mysql 系统共有的物理文件外，有三个文件需要备份，记录表结构元数据的 .frm 文件、存储表数据的 .MYD 文件、存储索引数据的 .MYI 文件。

2、Innodb 存储引擎（ 事务性存储引擎 ）：存放数据的位置取决于对 Innodb 的相关配置。

决定 Innodb 存放数据位置的参数：Innodb_data_home_dir 、Innodb_data_file_path 、Innodb_log_group_home_dir（ 指定了数据和日志文件存放的位置 ）
决定 Innodb 表空间存储方式参数：Innodb_file_per_table（ 决定 Innodb 是以共享表空间还是独享表空间存储数据 ）

如果使用共享表空间存储方式，Innodb 需要备份 Innodb_data_home_dir 和 Innodb_data_file_path 所设定的数据文件及 Datadir 中相应的数据目录下的所有 Innodb 存储引擎表的 .frm 文件。
如果使用独享表空间存储方式，除了备份上面所有文件外，还要备份 Datadir 中相应数据目录下的所有 .idb 文件（ 该文件中存放的才是独享表空间方式下 Innodb 存储引擎表的数据 ）。

## 使用独享表空间时，数据并不是全部放在 Datadir 下数据库目录下的 .ibd 文件中，这里存放的只是表数据而以。
## Innodb 是事务存储引擎，它的 undo 和 redo 信息，不管是使用独享还是共享表空间存储数据，与事务相关的 undo 信息以及其它一些元数据信息，
## 都是存放在 Innodb_data_home_dir 和 Innodb_data_file_path 这两个参数所设定的数据文件中的。所以这两个目录下的数据文件不管什么情况（ 物理备份 ）都必须备份。

Innodb 还有自己存放 redo 信息和相关事务信息的日志文件在 Innodb_log_group_home_dir 参数所设定的位置，这里的所有日志文件也必须备份。

3、NDB Cluster（ 存储引擎 ）：Mysql Cluster

物理备份时需要备份的文件：

> 元数据（ Metadata ），包含所有的数据库以及表的定义信息
> 表数据（ Tables Records ），保存实际数据的文件
> 事务日志数据（ Transaction Log ），维持事务一致性和完整性，以及恢复过程中所需要的事务信息

MyISAM 存储引擎物理热备方法：

Mysqlhotcopy 是 Mysql 提供的一个使用 perl 编写的，专门用来备份 MyISAM 存储引擎的工具。

实现原理：先通过 Lock tables ，然后执行 Flush tables 动作，

• [root@Mysql ~]# mysqlhotcopy --help
  
  
• 
  
• Can't locate DBI.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .)
  
• at /usr/local/mysql/bin/mysqlhotcopy line 25.
  
• BEGIN failed--compilation aborted at /usr/local/mysql/bin/mysqlhotcopy line 25.
  

## 有一条报错信息，需要执行 yum -y install perl-DBD-MySQL ( 注意大小写 )

• Warning: /usr/local/mysql/bin/mysqlhotcopy is deprecated and will be removed in a future version.
  
  
• DBI connect(';host=localhost;mysql_read_default_group=mysqlhotcopy','root',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock
  

## 在执行备份的时候，提示找不到 mysql.sock 文件，这个文件默认是放在 /tmp/mysql.sock 的，可以加 -S 指定位置，也可以 ln -s 一下，或直接修改其位置

• [root@Mysql ~]# mkdir /data/backup/hotcopy_foodb  ## 创建备份存放位置
  
• 
  
• [root@Mysql ~]# mysqlhotcopy -u root -p 123456 -S /tmp/mysql.sock foodb /data/backup/hotcopy_foodb/  ## 执行备份，注意：这工具指定用户跟密码时，参数跟值之间必须要加空格
  
• 
  
• ## 备份过程中屏幕会输出一些信息
  
• 
  
• [root@Mysql ~]# ls /data/backup/hotcopy_foodb/foodb/  ## 可以看到备份目录 foodb 库里面的内容
  
• activity.frm                        market_selling.MYI              system_config.MYI                temp_equip_melt.MYI              temp_old_player.MYI
  
• activity.MYD                        mount.frm                       t_act.frm                        temp_equipment.frm               temp_open_box.frm
  
• activity.MYI                        mount.MYD                       t_act.MYD                        temp_equipment_levelup.frm       temp_open_box.MYD
  
• activity_rotate.frm                 mount.MYI                       t_act.MYI                        temp_equipment_levelup.MYD       temp_open_box.MYI
  
• activity_rotate.MYD                 notice.frm                      task_daily.frm                   temp_equipment_levelup.MYI       temp_pet_aptitude.frm
  
• activity_rotate.MYI                 notice.MYD                      task_daily.MYD                   temp_equipment.MYD               temp_pet_aptitude.MYD
  
• arena_rank.frm                      notice.MYI                      task_daily.MYI                   temp_equipment.MYI               temp_pet_aptitude.MYI
  
• arena_rank.MYD                      opera.frm                       task_finish.frm                  temp_equip_polish_attr.frm       temp_pet_develop.frm
  
• arena_rank.MYI                      opera.MYD                       task_finish.MYD                  temp_equip_polish_attr.MYD       temp_pet_develop.MYD
  
• ban_account_list.frm                opera.MYI                       task_finish.MYI                  temp_equip_polish_attr.MYI       temp_pet_develop.MYI
  
• ban_account_list.MYD                pet.frm                         task_heaven.frm                  temp_equip_polish.frm            temp_pet.frm
  
• ban_account_list.MYI                pet.MYD                         task_heaven.MYD                  temp_equip_polish.MYD            temp_pet_growth.frm
  
• 
  
• ## 省略好多~~
  

## 此工具只用于非事务性存储引擎，默认只能用于 MyISAM 存储引擎，记得一定要做恢复测试，恢复时直接 CP 备份文件覆盖到原位置即可！

Innodb 存储引物理热备方法：

Innodb 存储引擎由于是事务性存储引擎，有 redo 日志和相关的 undo 信息，而且对数据的一致性和完整性的要求也比 MyISAM 要严格很多，所以备份起来也要复杂的多。

一般都是通过专门的 Innodb 在线物理备份软件来完成的，如 Innobase 公司开发的收费版本 Ibbackup ( 只限备份 Innodb 存储引擎 )。

再介绍一下备份工具，Xtrabackup（ 开源软件 ），之后单独研究。

## Innodb 存储引擎的恢复，如果是冷备，那么直接 CP 就可以，热备的话就需要用备份工具来恢复了。

NDB Cluster 存储引擎热备方法：

NDB Cluster 存储引擎也是一款事物性存储引擎，也有 redo 日志，不过 NDB Cluster 存储引擎自己提供了备份功能，可以通过相关的命令实现。

## 这个暂时还没用到，先不说了~~

三、备份策略

备份策略设计是否合理，关系到了所作备份是否能满足需求，所以这是很重要的。

1、对于较为核心的在线应用业务，必须要有备用的主机通过 Mysql 复制进行相应的备份，复制线程一直开启，恢复线程一天恢复一次，尽量让备份机数据延迟主机一段时间，一般为一天。
   这样当数据丢失时，就可以通过增量备份快速恢复数据，甚至可以直接开放备份机对外提供服务。

2、对于重要级别稍微低一些的应用，恢复时间要求不是太高的话，可以不使用备份机，而采用周期性的物理全备份加每小时或每合适时间的二进制日志备份。

3、对于更为级别低一些的应用，恢复时间没有太多要求，但不希望丢失太多数据，则可以采用定期逻辑全备份加二进制日志备份的方式。因为逻辑备份更为简单，可以在线备份，不影响业务系统。

4、如果是测试环境，那么只需做逻辑全备份即可。