mysql 的备份和恢复

一、备份的分类

1.从备份的内容来分：

备份包括逻辑备份和物理备份

逻辑备份：备份的是数据过程中的逻辑结构，主要备份数据库对象。备份出来的主要是sql语句。

物理备份（裸备份）：其实就是复制数据目录中的文件。

逻辑备份保存的结果都是ASCII码，可以通过文本处理工具直接处理的。具有很好的兼容性。不过逻辑备份可能会丢失浮点数的精度信息。

物理备份还原速度快。

2.从备份数据集角度分：

完全备份：备份整个数据库中的所有数据。

增量备份：备份从上一次完全备份或增量备份以来的改变的数据。

差异备份：备份从上一次完全备份以来的改变的数据。

我们一般备份使用的是完全备份+增量备份或者完全备份+差异备份。

我们来思考一下上述两种结合更适合哪种场景中？

    增量备份可以节省空间，只备份上一次修改的数据。而差异备份则是从上一次完全备份之后的所有修改的数据进行备份，很显然需要占用大量的空间。

    你再来分析一下下面的场景；

例如：你的公司每天对数据进行一次备份，如果在第29天数据坏掉了。你该怎么办？

假如你使用的是增量备份，那你就得需要把前28天的备份都进行恢复。
而差异备份，则只需要恢复你第28天的备份就可以了。
所以我们要根据不同的场景，选择不同的备份机制。

3.从备份时服务器是否需要停机的角度分：

冷备份：需要把服务干净的关闭。而后直接复制数据进行备份

问备份：服务不关闭，但只允许读操作。

热备份；服务不关闭，且不会影响读写操作。

二、常用的备份工具的使用

1.mysqldump 提供逻辑备份，对INNODB引擎，可以支持到热备份，对其他引擎只支持到温备。备份速度慢，还原速度非常慢。

2.2.SELECT INTO OUTFILE 对所有引擎来说，逻辑备份工具，只支持支持到温备。备份慢，还原慢。

3.mk-parallel-backup （perl脚本）提供在多cpu主机上实现并行备份的能力。提供逻辑备份。备份中等，恢复中等。

4.ibbackup 物理备份，对InnoDB支持到热备，对其他引擎支持到温备。备份快，还原快。需要付费。

5.Filesystem(copy files) 物理备份，只能实现冷备。备份最快，还原也最快。

6.snapshot（基于快照） 物理备份，几乎是热备，备份时需要对表加上读锁。快照完之后，释放锁。无法对多个逻辑卷同时作同一时间点的快照，也就是说要备份的内容必须在同一逻辑卷上。备份比较快，还原比较快。

7.mysqlhotcopy 物理备份，几乎是冷备，只能应用于myISAM。备份速度快，还原速度快。

好，下面我就来介绍几个比较常用的工具。

说明：我的mysql数据库采用二进制编译方式安装。创建了一个逻辑卷/dev/myvg/mydata。数据目录为/mydata/data。将逻辑卷挂载在了/mydata目录上。

1.mysqldump

格式：

备份：mysqldump [options] db_name [tbl1_name] > /root/tb1.sql

还原：mysql < /root/tb1.sql

选项：--databases db1 db2，一次备份多个数据库

      --all-databases ，备份所有数据库

      --events ，创建事件指令

      --routings ，备份存储过程和存储函数

      --triggers ，备份触发器

      --extended-insert 扩展的insert语句。--skip-extended-insert 关闭其功能

由于mysqldump对其他引擎支持到温备，因此在备份时需要加读锁。

      --lock-all-tables 对所有表进行加锁

      --flush-logs 刷新日志到磁盘中的数据文件

      --no-data  只备份数据库中表的定义，不备份数据库中的数据

      --where   只备份某个表的某些行的数据

演示：

进入到mysql中，查看一下数据库。

在其中我创建了一个hanlenry和phpbb数据库。phpbb数据库是之前安装phpbb论坛时创建的。

好，我们现在就来备份这两个数据库。因为对其他存储引擎mysqldump只支持到温备，因此在执行备份时需要加上读锁。

备份：

#mysqldump --lock-all-tables --databases hanlenry phpbb > /root/mydata.sql

接下来，把hanlenry和phpbb两个数据库删除。

#mysql
mysql> drop database hanlenry;
mysql> drop database phpbb;

再查看一下数据库

mysql> show databases;

那我们把刚才删掉的数据库在恢复回来。

#mysql < /root/mydata.sql

再来查看一下数据库

mysql> show databases;

此时数据库中又出现了我们删除的两个数据库。

如果你没有使用参数--lock-all-tables的话，你需要手动对表进行加锁

#mysql
mysql> flush tables with read lock;

备份完成后，解除读锁。

mysql> unlock tables;

2.基于快照实现数据库的物理备份。

要想使用快照就得创建逻辑卷，并且你需要将你的数据放到逻辑卷上。这些我都已经准备好了。

具体的操作步骤：

（1）数据文件在逻辑卷上； 

（2）对数据库中的表施加读锁；

（3）对数据在的LV作快照； 

（4）释放数据库的读锁； 

（5）挂载快照卷，备份数据文件（如果是INnoDB的引擎，还得备份事务日志文件）； 

（6）删除快照卷

有几个非常重要的选项，首先你得查看一下。

SHOW VARIABLES LIKE 'log_bin';

log_bin 为ON

SHOW VARIABLES LIKE 'sync_binlog';

sync_binlog 为1

如果不为1，则修改：SET GLOBAL sync_binlog=1;

SHOW VARIABLES LIKE 'innodb_support_xa';

innodb_support_xa为ON。

基于快照的备份同样要对表进行加锁。

#mysql
mysql> flush tables with read lock;
mysql> quit;

创建快照

#lvcreate -L 50M -s -n mysnap -p r /dev/myvg/mydata

解锁

#mysql
mysql> unlock tables;
mysql> quit;

对快照卷挂载，并使用。

 #mount /dev/myvg/mysnap /mnt
 #cd /mnt
 #cd data
 #tar -jcf /root/mysqldata.tar.bz2 ./*
 #cd
 #umount /mnt
 #lvremove /dev/myvg/mysnap

我们来模拟数据目录损坏，我将数据目录下的所有文件都删除掉。

#rm -rf /mydata/data/*

那我们我恢复一下吧！首先将mysql服务关闭掉。

#service mysqld stop
如果停不掉则使用#killall mysqld,把mysql服务给停掉

恢复

#cd /mydata/data
#tar xf /root/mysqldata.tar.bz2 -C ./

启动mysql，查看数据库

#service mysqld start
#mysql
mysql> show databases;

3.当我们做了完全备份之后，对于新修改的数据，要改怎么备份呢？此时就要靠二进制日志了，通过二进制日志做时间点恢复。从上次备份之后，可以指定从一个点到另一个点的备份。

mysqlbinlog 

start-position  开始的位置

      stop-position   结束的位置

      start-datetime  开始的时间

      stop-datetime   结束的时间

我们通过使用show master status，来查看正在使用的二进制日志文件。以及当前所处的位置。

我现在对hanlenry数据库做修改，创建一个表han.

#mysql
mysql> use hanlenry;
mysql> create table han (
    -> id int(10) not null primary key auto_increment,
    -> name varchar(10) not null,
    -> age int(5) not null,
    -> *** enum ('F','M') default 'M');

插入一条数据

mysql> insert into han values (1,'bob',23,'M');

查看结果：

查看操作结束时的位置

备份刚才操作的部分

#cd /mydata/data
#mysqlbinlog --start-position 107 --stop-position 525 mysql-bin.000005 > /root/mysqlhandata.sql

如图：

测试：把刚才创建的表han删除掉

#mysql
mysql> use hanlenry;
mysql> show tables;
mysql> drop table han;
mysql> quit;

恢复

#mysql < mysqlhandata.sql

查看数据库；

ok,表又恢复过来了。