数据库的备份和恢复
物理备份与逻辑备份
- 物理备份：备份数据文件，转储数据库物理文件到某一目录。物理备份恢复速度比较快，但占用空间比较大，MySQL中可以用 xtrabackup 工具来进行物理备份。
- 逻辑备份：对数据库对象利用工具进行导出工作，汇总入备份文件内。逻辑备份恢复速度慢，但占用空间小，更灵活。MySQL 中常用的逻辑备份工具为 mysqldump 。逻辑备份就是 备份sql语句 ，在恢复的时候执行备份的sql语句实现数据库数据的重现。
mysqldump实现逻辑备份
备份一个数据库
基本语法：
mysqldump –u 用户名称 –h 主机名称 –p密码 待备份的数据库名称[tbname, [tbname...]]> 备份文件名称.sql (相对路径和绝对路径都可以)
说明： 备份的文件并非一定要求后缀名为.sql，例如后缀名为.txt的文件也是可以的。
举例：
备份atguigudb1在当前目录下
mysqldump -uroot -p atguigudb1 > atguigudb1.sql
备份全部数据库
若想用mysqldump备份整个实例，可以使用 —all-databases 或 -A 参数：
mysqldump -uroot -pxxxxxx --all-databases > all_database.sqlmysqldump -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...]] > 备份文件名称.sq
排除某些表的备份
如果我们想备份某个库，但是某些表数据量很大或者与业务关联不大，这个时候可以考虑排除掉这些表，同样的，选项 —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 选项。
- 只备份结构
```shell mysqldump -uroot -p atguigu —no-data > atguigu_no_data_bak.sql
使用grep命令，没有找到insert相关语句，表示没有数据备份。
grep “INSERT” atguigu_no_data_bak.sql
- 只备份数据```shellmysqldump -uroot -p atguigu --no-create-info > atguigu_no_create_info_bak.sql#使用grep命令，没有找到create相关语句，表示没有数据结构。grep "CREATE" atguigu_no_create_info_bak.sql
备份中包含存储过程、函数、事件
mysqldump备份默认是不包含存储过程，自定义函数及事件的。可以使用 —routines 或 -R 选项来备份存储过程及函数，使用 —events 或 -E 参数来备份事件。
举例：
mysqldump -uroot -p -R -E --databases atguigu > fun_atguigu_bak.sql
mysql命令恢复数据
基本语法：
mysql –u root –p [dbname] < backup.sql
单库备份中恢复单库
使用root用户，将之前练习中备份的atguigu.sql文件中的备份导入数据库中，命令如下：
如果备份文件中包含了创建数据库的语句，则恢复的时候不需要指定数据库名称，如下所示：
mysql -uroot -p < atguigu.sql
否则需要指定数据库名称，如下所示：
mysql -uroot -p atguigu4< atguigu.sql
全量备份恢复
如果我们现在有昨天的全量备份，现在想整个恢复，则可以这样操作：
mysql –u root –p < all.sqlmysql -uroot -pxxxxxx < all.sql
执行完后，MySQL数据库中就已经恢复了all.sql文件中的所有数据库。
从全量备份中恢复单库
可能有这样的需求，比如说我们只想恢复某一个库，但是我们有的是整个实例的备份，这个时候我们可以从全量备份中分离出单个库的备份。
# 分离单库的操作sed -n '/^-- Current Database: `atguigu`/,/^-- Current Database: `/p' all_database.sql > atguigu.sql#分离完成后我们再导入atguigu.sql即可恢复单个库
例如：
从所有数据库备份中恢复atguigudb1
从单库备份中恢复单表
这个需求还是比较常见的。比如说我们知道哪个表误操作了，那么就可以用单表恢复的方式来恢复。
举例：我们有atguigu整库的备份，但是由于class表误操作，需要单独恢复出这张表。
cat atguigu.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `class`/!d;q' > class_structure.sqlcat atguigu.sql | grep --ignore-case 'insert into `class`' > class_data.sql#用shell语法分离出创建表的语句及插入数据的语句后 再依次导出即可完成恢复use atguigu; # 注意此时是在mysql服务器上操作mysql> source class_structure.sql;mysql> source class_data.sql;
物理备份：直接复制整个数据库
直接将MySQL中的数据库文件复制出来。这种方法最简单，速度也最快。MySQL的数据库目录位置不一定相同：
- 在Windows平台下，MySQL 8.0存放数据库的目录通常默认为 “ C:\ProgramData\MySQL\MySQL
- Server 8.0\Data ”或者其他用户自定义目录；
- 在Linux平台下，数据库目录位置通常为/var/lib/mysql/；
- 在MAC OSX平台下，数据库目录位置通常为“/usr/local/mysql/data”
但为了保证备份的一致性。需要保证：
- 方式1：备份前，将服务器停止。
- 方式2：备份前，对相关表执行 FLUSH TABLES WITH READ LOCK 操作。这样当复制数据库目录中的文件时，允许其他客户继续查询表。同时，FLUSH TABLES语句来确保开始备份前将所有激活的索引页写入硬盘。
这种方式方便、快速，但不是最好的备份方法，因为实际情况可能 不允许停止MySQL服务器 或者 锁住表 ，而且这种方法 对InnoDB存储引擎 的表不适用。对于MyISAM存储引擎的表，这样备份和还原很方便，但是还原时最好是相同版本的MySQL数据库，否则可能会存在文件类型不同的情况。
注意，物理备份完毕后，执行 UNLOCK TABLES 来结算其他客户对表的修改行为。
说明： 在MySQL版本号中，第一个数字表示主版本号，主版本号相同的MySQL数据库文件格式相同。
物理恢复：直接复制到数据库目录
步骤：
- 演示删除备份的数据库中指定表的数据
- 将备份的数据库数据拷贝到数据目录下，并重启MySQL服务器
- 查询相关表的数据是否恢复。需要使用下面的 chown 操作。
要求：
- 必须确保备份数据的数据库和待恢复的数据库服务器的主版本号相同。
- 因为只有MySQL数据库主版本号相同时，才能保证这两个MySQL数据库文件类型是相同的。
- 这种方式对 MyISAM类型的表比较有效 ，对于InnoDB类型的表则不可用。
- 因为InnoDB表的表空间不能直接复制。
- 在Linux操作系统下，复制到数据库目录后，一定要将数据库的用户和组变成mysql，命令如下：
chown -R mysql.mysql /var/lib/mysql/dbname
其中，两个mysql分别表示组和用户；“-R”参数可以改变文件夹下的所有子文件的用户和组；“dbname”参数表示数据库目录。
提示 Linux操作系统下的权限设置非常严格。通常情况下，MySQL数据库只有root用户和mysql用户组下的mysql用户才可以访问，因此将数据库目录复制到指定文件夹后，一定要使用chown命令将文件夹的用户组变为mysql，将用户变为mysql。
表的导出与导入
导出
- 使用SELECT…INTO OUTFILE导出文本文件
在MySQL中，可以使用SELECT…INTO OUTFILE语句将表的内容导出成一个文本文件。 使用mysqldump命令导出文本文件
使用mysqldump命令将将atguigu数据库中account表中的记录导出到文本文件：
mysqldump命令执行完毕后，在指定的目录/var/lib/mysql-files/下生成了account.sql和account.txt文件。mysqldump -uroot -p -T "/var/lib/mysql-files/" atguigu account
使用mysql命令导出文本文件
使用mysql语句导出atguigu数据中account表中的记录到文本文件：mysql -uroot -p --execute="SELECT * FROM account;" atguigu> "/var/lib/mysqlfiles/account.txt"
导入
使用LOAD DATA INFILE方式导入文本文件
举例：
使用SELECT…INTO OUTFILE将atguigu数据库中account表的记录导出到文本文件：
从文本文件account.txt中恢复数据：SELECT * FROM atguigu.account INTO OUTFILE '/var/lib/mysql-files/account_0.txt';
LOAD DATA INFILE '/var/lib/mysql-files/account_0.txt' INTO TABLE atguigu.account;
使用mysqlimport方式导入文本文件
举例：
导出文件表
使用mysqlimport命令将account.txt文件内容导入到数据库atguigu的account表中：SELECT * FROM atguigu.account INTO OUTFILE '/var/lib/mysql-files/account.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '\"';
mysqlimport -uroot -p atguigu '/var/lib/mysql-files/account.txt' --fields-terminated- by=',' --fields-optionally-enclosed-by='\"'
数据库迁移
概述
数据迁移（data migration）是指选择、准备、提取和转换数据，并将数据从一个计算机存储系统永久地传输到另一个计算机存储系统的过程。此外， 验证迁移数据的完整性 和 退役原来旧的数据存储 ，也被认为是整个数据迁移过程的一部分。
数据库迁移的原因是多样的，包括服务器或存储设备更换、维护或升级，应用程序迁移，网站集成，灾难恢复和数据中心迁移。
根据不同的需求可能要采取不同的迁移方案，但总体来讲，MySQL 数据迁移方案大致可以分为 物理迁移 和 逻辑迁移 两类。通常以尽可能 自动化 的方式执行，从而将人力资源从繁琐的任务中解放出来。
迁移方案
- 物理迁移
物理迁移适用于大数据量下的整体迁移。使用物理迁移方案的优点是比较快速，但需要停机迁移并且要 MySQL 版本及配置必须和原服务器相同，也可能引起未知问题。
物理迁移包括拷贝数据文件和使用 XtraBackup 备份工具两种。不同服务器之间可以采用物理迁移，我们可以在新的服务器上安装好同版本的数据库软件，创建好相同目录，建议配置文件也要和原数据库相同，然后从原数据库方拷贝来数据文件及日志文件，配置好文件组权限，之后在新服务器这边使用 mysqld 命令启动数据库。 - 逻辑迁移
逻辑迁移适用范围更广，无论是 部分迁移 还是 全量迁移 ，都可以使用逻辑迁移。逻辑迁移中使用最多的就是通过 mysqldump 等备份工具。
删库了不敢跑，能干点啥？
delete：误删行
恢复数据比较安全的做法，是 恢复出一个备份 ，或者找一个从库作为 临时库 ，在这个临时库上执行这些操作，然后再将确认过的临时库的数据，恢复回主库。如果直接修改主库，可能导致对数据的 二次破坏 。
当然，针对预防误删数据的问题，建议如下：
把 sql_safe_updates 参数设置为 on 。这样一来，如果我们忘记在delete或者update语句中写where条件，或者where条件里面没有包含索引字段的话，这条语句的执行就会报错。
如果确定要把一个小表的数据全部删掉，在设置了sql_safe_updates=on情况下，可以在delete语句中加上where条件，比如where id>=0。
代码上线前，必须经过 SQL审计 。
truncate/drop：误删库/表
这种情况下，要想恢复数据，就需要使用 全量备份 ，加 增量日志 的方式了。这个方案要求线上有定期的全量备份，并且实时备份binlog。
在这两个条件都具备的情况下，假如有人中午12点误删了一个库，恢复数据的流程如下：
- 取最近一次 全量备份 ，假设这个库是一天一备，上次备份是当天 凌晨2点 ；
- 用备份恢复出一个 临时库 ；
- 从日志备份里面，取出凌晨2点之后的日志；
- 把这些日志，除了误删除数据的语句外，全部应用到临时库。
rm：误删MySQL实例
对于一个有高可用机制的MySQL集群来说，不用担心 rm删除数据 了。只是删掉了其中某一个节点的数据的话，HA系统就会开始工作，选出一个新的主库，从而保证整个集群的正常工作。我们要做的就是在这个节点上把数据恢复回来，再接入整个集群。
延迟复制备库
如果有 非常核心 的业务，不允许太长的恢复时间，可以考虑搭建延迟复制的备库。一般的主备复制结构存在的问题是，如果主库上有个表被误删了，这个命令很快也会被发给所有从库，进而导致所有从库的数据表也都一起被误删了。
延迟复制的备库是一种特殊的备库，通过 CHANGE MASTER TO MASTER_DELAY = N 命令，可以指定这个备库持续保持跟主库有 N秒的延迟 。比如你把N设置为3600，这就代表了如果主库上有数据被误删了，并且在1小时内发现了这个误操作命令，这个命令就还没有在这个延迟复制的备库执行。这时候到这个备库上执行stop slave，再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据。
预防误删库/表的方法
- 账号分离 。这样做的目的是，避免写错命令。
- 只给业务开发同学DML权限，而不给truncate/drop权限。而如果业务开发人员有DDL需求的话，可以通过开发管理系统得到支持。
- 即使是DBA团队成员，日常也都规定只使用 只读账号 ，必要的时候才使用有更新权限的账号。
- 制定操作规范 。
- 在删除数据表之前，必须先 对表做改名 操作。然后，观察一段时间，确保对业务无影响以后再删除这张表。
- 改表名的时候，要求给表名加固定的后缀（比如加 _to_be_deleted )，然后删除表的动作必须通过管理系统执行。并且，管理系统删除表的时候，只能删除固定后缀的表。
PS：
关于表的导入和导出和其他数据库备份可以在以下pdf中查找：
第19章_数据库备份与恢复.pdf
若有收获，就点个赞吧
0 人点赞
