MySQL的备份与恢复

jopen 10年前

数据库名称为datacrawl，数据库存储引擎为MyISAM。数据库运行在本地上，备份数据存储在主机名称为hadoop00的服务器上。

备份方案:周一到周日每天凌晨1:30实现一次物理备份，周日凌晨2:30实现一次逻辑备份。所有的备份都通过设置crontab自动执行。

以下是物理备份的脚本。使用shell脚本，mysqlhotcopy进行数据的备份，crontab将脚本设置为自动执行。

#!/bin/bash  #基本的参数配置  #备份文件存放路径  backup_target_dir=/backup/db  backup_compression_dir=/backup/compressiondb  db=datacrawldb      #复制数据库文件  mysqlhotcopy -u hive -p 123456 $db $backup_target_dir    #将文件在本地进行压缩  DATE=$(date +%Y%m%d)  cd $backup_compression_dir  tar -zcvf $backup_compression_dir/$db$DATE.tar.gz $backup_target_dir/$db    #删除本地的源文件  rm -rf $backup_target_dir/$db/    #将数据备份至hadoop00的机器上  scp -r $backup_compression_dir/$db$DATE.tar.gz root@hadoop00:$backup_compression_dir    #删除本地的压缩文件  rm -rf $backup_compression_dir/$db$DATE.tar.gz    #删除10天前的压缩数据  ccDATE=$(date "-d 10 day ago" +%Y%m%d)  ssh hadoop00 rm -rf $backup_compression_dir/$db$ccDATE.tar.gz

修改脚本的执行权限

chmod +x physicalbackup.sh

让脚本每天执行一次，每天备份一次数据库，在crontab 里面添加一行

30 1 * * * /liuzp/NSTC_DataCrawl/DataCrawl/djcode/website/mysqlbackup/physicalbackup.sh

还原数据库的时候只要把备份出的文件拷贝到数据库放置数据的目录下，修改权限。重启下数据库就完成了恢复了。

以下为逻辑备份的脚本。使用mysqldump进行备份。

#!/bin/bash  backup_compression_dir=/backup/logicalbackup  db=datacrawldb    #复制数据库文件  mysqldump -uhive -p123456 $db > $db.sql    #将文件压缩  DATE=$(date +%Y%m%d)  tar -zcvf $db$DATE.tar.gz $db.sql    #将文件备份至hadoop00服务器上  scp $db$DATE.tar.gz root@hadoop00:$backup_compression_dir    #删除源文件  rm -rf $db.sql  rm -rf $db$DATE.tar.gz    #删除上次的压缩数据  ccDATE=$(date "-d 7 day ago" +%Y%m%d)  ssh hadoop00 rm -rf $backup_compression_dir/$db$ccDATE.tar.gz

修改脚本的执行权限

chmod +x logicalbackup.sh

crontab用来自动执行备份的脚本文件

30 2 * * 0 /liuzp/NSTC_DataCrawl/DataCrawl/djcode/website/mysqlbackup/logicalbackup.sh

备份的作用

备份主要的作用：

（1）灾难恢复：硬件故障、一个不经意的bug导致数据损坏或者服务器机器数据由于某些原因不可获取或者无法使用

（2）人们改变想法：例如删除数据后又想恢复这些数据

（3）测试：基于实际数据来测试的方法是，定期用最新的生产环境数据更新测试服务器。

备份策略

（1）首先要确定是事务性还是非事务性，两种不同的存储引擎备份方式在处理数据一致性方面是不一样的；

（2）确定使用全备份还是增量备份；

（3）可以采用复制的方法做异地备份，但是，复制不能代替备份，它对数据库的误操作也无能为力；

（4）要定期的做备份，备份的周期要充分考虑系统可以承受的恢复时间，备份要在系统负载较小的时候进行；

（5）确保MySQL打开log-bin选项，有了BINLOG，MySQL才可以在必要的时候做完整恢复。

备份种类

离线备份

离线备份，是关闭MySQL做备份，是一种简单而且安全的备份方法。它不用关注InnoDB缓冲池中的脏页或者其他缓存。也不需要担心数据在尝试备份的过程中被修改，并且因为服务器不对应用提供访问，所以可以很快的完成备份，但是在高负载和高并发下对MySQL进行关闭和重启的时间还是无法容忍的，这会导致昂贵的代价。

在线备份

在线备份是不需要生产服务器停机的备份。但是由于一致性的要求，对服务器进行在线备份的时候仍然会有明显的服务中断。MyISAM引擎的数据库，一般系统会使用FLUSH TABLES WITH READ LOCK操作，这会导致MySQL关闭并锁住所有的表，将MyISAM的数据文件刷新到磁盘上，并且刷新查询缓存。

逻辑备份

逻辑备份是将数据导出到相应的文件中，以一种MySQL能够解析的形式进行保存。

逻辑备份的优点：

1. 可以使用编辑器像grep和sed之类的命令查看和操作的普通文件；

2. 可以通过网络来备份和恢复；

3. 与存储引擎无关；

4. 有助于避免数据损坏。

逻辑备份的缺点：

1. 必须由数据库服务器生成逻辑备份的工作，会消耗很多的CPU周期；

2.逻辑备份在某些场景下比数据库文件本身更大；

3.从逻辑备份中还原需要MySQL加载与解释语句，转化为存储格式，并重建索引，所有这一切会很慢。

物理备份

物理备份是直接复制原始文件的方式进行备份。原始文件是指存在于硬盘上的文件。

物理备份的优点：

1. 基于文件的物理备份，只需要将需要的文件复制到其他地方即可完成备份。不需要其他额外的工作来生成原始文件。

2. 从物理备份中恢复比较快速，因为MySQL服务器不需要执行任何SQL或构建索引。

物理备份的缺点：

1. InnoDB的原始文件通常比相应的逻辑备份要大很多；

2. 物理备份不总是可以跨平台、操作系统及MySQL版本。

混合备份

建议混合使用物理和逻辑两种方式来做备份：先使用物理复制，以此数据启动MySQL服务器实例并运行mysqlcheck。

增量备份

增量备份是自从任意类型的上次备份后所有修改做的备份。增量备份对减少服务器开销、备份时间及备份空间而言都很适合。

增量备份的缺点包括增加恢复复杂度，额外的风险，以及更长的恢复时间。如果可以做全备，考虑到简便性，做好使用全备份。

一致性

数据一致性

在线备份，需要所有的相关表的一致性备份。如果使用的不是事务型存储引擎，需要在备份时候使用LOCK TABLES来锁住所有要一起备份的表，备份完成后再释放锁。InnoDB需要在服务器上使用REPEATABLE READ事务隔离级别，并且没有任何DDL以及基于时间点的数据快照，切回备份过程中不会阻塞任何后续的工作。

复制

复制不是备份。备库对生成备份是一个干涉较少的源，但它不是备份本身。对于RAID卷，SAN和文件系统快照，也同样如此。从备库中备份最大的好处是可以不干扰主库，避免在主库上增加额外的负载。

备份内容

生成物理备份

冷备份

冷备份就是停掉数据库服务，cp数据文件的方法，这种方法对MyISAM和InnoDB存储引擎都适合。但是一般很少用，因为很多应用是不允许长时间停机的。进行备份的操作是：暂停MySQL服务，在操作系统级别备份MySQL的数据文件和日志文件到备份目录。

热备份

MyISAM存储引擎

MyISAM存储引擎的热备份是有很多方法，但是其实质就是将要备份的表加读锁，然后再cp数据文件到备份目录。

方法一：使用mysqlhotcopy工具

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

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

方法二：手工锁表copy

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

mysql>flush tables for read;

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

InnoDB存储引擎

Percona XtraBackup工具是InnoDB进行热备份的工具。它是开源并且免费的，除了核心备份工具外，还有一个用Perl写的封装脚本，可以提供更多高级功能，它支持类似流、增量、压缩和多线程备份操作。也有许多特别的功能，用以降低在高负载的系统上备份的影响。

生成逻辑备份

有两种类型的逻辑备份：SQL导出和符号分割文件。

SQL导出

我们一般使用mysqldump方式进行SQL导出的操作。如下图是使用默认参数的mysqldump得到的输出结果。导出数据包括表结构和数据，都以有效的SQL命令形式写出。

其中参数-l表示给所有表加一个读锁，-F表示生成一个新的日志文件

符号分割文件备份

使用SQL命令SELECT INTO OUTFILE以符号分隔文件格式创建数据的逻辑备份。符号分隔文件包含以ASCII展示的原始数据，没有SQL、注释和列名。

mysql->select * into outfile '/tmp/user.txt'       ->fields terminated by ',' optionally enclosed by '"'       ->lines terminated by '\n'       ->from datacrawldb.users_user

以下是user.txt文件的内容：

从备份中恢复

还原逻辑备份

加载SQL文件

如果有一个SQL导出文件，它将包含可执行的SQL。运行这个文件，就可以进行还原功能。

$mysql < sakila-backup.sql

将备份恢复后的数据并不完整，还需要将备份后执行的日志进行重做，语法如下：

$mysqlbinlog binlog-file | mysql -u root -p ***

加载符号分隔文件

如果是通过SELECT INTO OUTFILE导出的符号分隔文件，可以使用LOAD DATA INFILE通过相同的参数来加载，也可以使用mysqlimport，这是LOAD DATA FILE的一个包装，这种方式依赖命令约定决定从哪里加载一个文件的数据。

基于时间点的恢复

如果发生了误操作，这时使用完全恢复是没有用的，因为日志里面还存在误操作的语句。我们应该首先恢复到误操作之前的状态，然后跳过误操作语句，在恢复后面执行的语句，完成我们的恢复。这种恢复叫做不完全恢复。

还原物理备份

冷备份还原

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