二十、备份

物理备份：备份数据文件，转储数据库物理文件到某一目录。物理备份恢复速度比较快，但占用空间比 较大，MySQL中可以用 xtrabackup 工具来进行物理备份。

逻辑备份：对数据库对象利用工具进行导出工作，汇总入备份文件内。逻辑备份恢复速度慢，但占用空 间小，更灵活。MySQL 中常用的逻辑备份工具为 mysqldump 。逻辑备份就是 时候执行备份的sql语句实现数据库数据的重现。

20.1 逻辑备份

备份一个数据库

mysqldump  –u 用户名称 –h 主机名称 –p密码 待备份的数据库名称[tbname, [tbname...]]> 备份文件名称.sql

说明： 备份的文件并非一定要求后缀名为.sql，例如后缀名为.txt的文件也是可以的。

备份所有数据库

若想用mysqldump备份整个实例，可以使用 --all-databases 或 -A参数

mysqldump -uroot -pxxxxxx --all-databases > all_database.sql
 mysqldump -uroot -pxxxxxx -A > all_database.sql

备份部分数据库

使用 --databases 或 -B 参数了，该参数后面跟数据库名称，多个数据库间用空格隔开。如果指定 databases参数，备份文件中会存在创建数据库的语句，如果不指定参数，则不存在。语法如下：

mysqldump  –u user –h host –p --databases  [数据库的名称1 [数据库的名称2...]] > 备份文件名称.sql

备份部份表

mysqldump  –u user –h host –p 数据库的名称  [表名1 [表名2...]] > 备份文件名称.sql

备份单表的部分数据、

有些时候一张表的数据量很大，我们只需要部分数据。这时就可以使用 --where 选项了。where后面附 带需要满足的条件。

mysqldump -uroot -p atguigu student --where="id < 10 " > student_part_id10_low_bak.sql

排除某些表的备份

如果我们想备份某个库，但是某些表数据量很大或者与业务关联不大，这个时候可以考虑排除掉这些 表，同样的，选项 --ignore-table 可以完成这个功能。

mysqldump -uroot -p atguigu --ignore-table=atguigu.student > no_stu_bak.sql

通过如下指定判定文件中没有student表结构：

grep "student"  no_stu_bak.sql

只备份结构或只备份数据

只备份结构的话可以使用 --no-data 简写为-d选项；只备份数据可以使用 --no-create-info 简写为 -t选项。

• 只备份数据

mysqldump -uroot -p atguigu --no-data > atguigu_no_data_bak.sql
#使用grep命令，没有找到insert相关语句，表示没有数据备份。
[root@node1 ~]# grep "INSERT" atguigu_no_data_bak.sql
[root@node1 ~]#

• 只备份结构

mysqldump -uroot -p atguigu --no-create-info > atguigu_no_create_info_bak.sql
#使用grep命令，没有找到create相关语句，表示没有数据结构。
[root@node1 ~]# grep "CREATE" atguigu_no_create_info_bak.sql
[root@node1 ~]#

备份中包含存储过程、函数、事件

mysqldump备份默认是不包含存储过程，自定义函数及事件的。可以使用 --routines 或-R选项来备份存储过程及函数，使用 --events 或-E参数来备份事件。

mysqldump -uroot -p -R -E --databases atguigu > fun_atguigu_bak.sql

20.2 逻辑恢复

基本语法

mysql –u root –p [dbname] < backup.sql

单库备份中恢复单库

如果备份文件中包含了创建数据库的语句，则恢复的时候不需要指定数据库名称，如下所示

mysql -uroot -p < atguigu.sql

否则就需要指定数据库：

mysql -uroot -p atguigu4< atguigu.sql

全量备份恢复

如果我们现在有昨天的全量备份，现在想整个恢复，则可以这样操作

mysql -uroot -p < all.sql

从全量备份中恢复单库

可能有这样的需求，比如说我们只想恢复某一个库，但是我们有的是整个实例的备份，这个时候我们可以从全量备份中分离出单个库的备份。

sed -n '/^-- Current Database: `atguigu`/,/^-- Current Database: `/p' all_database.sql > atguigu.sql

#分离完成后我们再导入atguigu.sql即可恢复单个库

从单库备份中恢复单表

cat atguigu.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `class`/!d;q' >

class_structure.sql
cat atguigu.sql | grep --ignore-case 'insert into `class`' > class_data.sql
#用shell语法分离出创建表的语句及插入数据的语句后 再依次导出即可完成恢复
use atguigu;

mysql> source class_structure.sql;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> source class_data.sql;
Query OK, 1 row affected (0.01 sec)