数据库备份与恢复

在任何数据库环境中，总会有 不确定的意外 情况发生，比如例外的停电、计算机系统中的各种软硬件故障、人为破坏、管理员误操作等是不可避免的，这些情况可能会导致 数据的丢失、 服务器瘫痪 等严重的后果。存在多个服务器时，会出现主从服务器之间的 数据同步问题。

为了有效防止数据丢失，并将损失降到最低，应定期 对MySQL数据库服务器做 备份。如果数据库中的数据丢失或者出现错误，可以使用备份的数据 进行恢复。主从服务器之间的数据同步问题可以通过复制功能实现。

物理备份与逻辑备份

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

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

mysqldump逻辑备份数据

mysqldump是MySQL提供的一个非常有用的数据库备份工具。

备份一个数据库

mysqldump命令执行时，可以将数据库备份成一个文本文件，该文件中实际上包合多个CREATE和INSERT语句，使用这些语句可以重新创建表和插入数据

• 查出需要备份的表的结构，在文本文件中生成一个CREATE语句
• 将表中的所有记录转换成一条INSERT语句

基本语法:

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

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

举例：使用root用户备份atguigudb1数据库

mysqldump -uroot -p atguigudb1>atguigudb1.sql	#备份文件存储在当前目录下
mysqldump -uroot -p atguigudb1> /var/lib/mysql/backup/atguigudb1.sql

备份全部数据库

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

mysqldump -uroot -p --all-databases > all_databases.sql
mysqldump -uroot -p -A > all_databases.sql

备份部分数据库

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

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

举例：

mysqldump -uroot -p --databases atguigudb1 atguigudb2 > atguigudb1_2.sql
mysqldump -uroot -p -B atguigudb1 atguigudb2 > atguigudb1_2.sql

备份部分表

语法如下：

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

举例：备份studydb数据库下的user表

备份单表的部分数据

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

举例：备份studydb数据库下的user表id>2的数据：

排除某些表的备份

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

举例：备份atguigudb1数据库，排除student表

只备份结构或只备份数据

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

• 只备份结构

  mysqldump -uroot -p atguigudb1 --no-data > atguigudb1_no_data_bak.sql
  #使用grep命令，没有找到insert相关语句，表示没有数据备份。
  grep "INSERT" atquigudb1_no_data_bak.sql
  

• 只备份数据

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

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

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

举例:备份整个atguigudb1库，包含存储过程及事件:

使用下面的SOL可以查看当前库有哪些存储过程或者函数

SELECT SPECIFIC_NAME,ROUTINE_TYPE,ROUTINE_SCHEMA FROM information_schema.Routines WHERE ROUTINE_SCHEMA="atguigudb1";

下面备份atguigudb1库的数据，函数以及存储过程

mysqldump -uroot -p -R -E --databases atguigudb1 > fun_atguigudb1_bak.sql

查询备份文件中是否存在函数

grep -C 5 "rand_num" fun_atguigudb1_bak.sql

mysqldump常用选项

参数	含义
–add-drop-database	在每个CREATE DATABASE语句前添加DROP DATABASE语句
–add-drop-tables	在每个CREATE TABLE语句前添加DROP TABLE语句
–add-locking	用LOCK TABLES和UNLOCK TABLES语句引用每个表转储。重载转储文件时插入得更快
–all-database，-A	转储所有数据库中的所有表。与使用–database选项相同，在命令行中命名所有数据库
–comment[=0|1]	如果设置为0，禁止转储文件中的其他信息，例如程序版本、服务器版本和主机。–skip-comments与–comments=0的结果相同。默认值为1，即包括额外信息。
–compact	产生少量输出。该选项禁用注释并启用–skip-add-drop-tables、–no-set-names、–skip-disable-keys和–skip-add-locking选项。
–compatible=name	产生与其他数据库系统或旧的MySQL服务器更兼容的输出，值可以为ansi、MySQL323、MySQL40、postgresgl、oracle、mssgl、db2、maxdb、no_key_options、no_table_options或者no_field_options
–complete_insert，-c	使用包括列名的完整的INSERT语句
–debug[=debug_options]，-#[debug_options]	写调试日志
–delete，-D	导入文本前清空表
–default-character-set=charset	使用charsets默认字符集。如果没有指定，就使用utf8
–delete–master-logs	在主复制服务器上，完成转储操作后删除二进制日志。该选项自动启用-master-data
–extended-insert，-e	使用包括几个VALUES列表的多行INSERT语法。这样使得转储文件更小，重载文件时可以加速插入
–flush-logs，-F	开始转储前刷新MySQL服务器日志文件，该选项要求RELOAD权限
–force，-f	在表转储过程中，即使出现SQL错误也继续
–lock-al1-tables，-x	对所有数据库中的所有表加锁。在整体转储过程中通过全局锁定来实现。该选项自动关闭–single-transaction和–lock-tables
–lock-tables，-l	开始转储前锁定所有表，用READ LOCAL锁定表以允许并行插入MyISAM表。对于事务表(例如InnoDB和BDB)，–single-transaction是一个更好的选项，因为它根本不需要锁定表
–no-create-db，-n	该选项禁用CREATE DATABASE IF NOT EXIST dbname语句，如果给出–database或–all-database选项，就包含到输出中
–no-create-info，-t	只导出数据，而不添加CREATE TABLE语句
–no-data，-d	不写表的任何行信息，只转储表的结构
–opt	该选项是速记，它可以快速进行转储操作并产生一个能很快装入MySQL服务器的转储文件。该选项默认开启，但可以用–skip-opt禁用
–password[=password]，-p[password]	当连接服务器时使用的密码
-port=port_num，-P port_num	用于连接的TCP/IP端口号
–protocol={TCP| SOCKE | PIPE | MEMORY}	使用的连接协议
–replace，-r -replace和–ignore	控制替换或复制唯一键值已有记录的输入记录的处理，如果指定–replace，新行替换有相同的唯一键值的已有行，如果指定–ignore，复制已有的唯一键值的输入行被跳过。如果不指定这两个选项，当发现一个复制键值时会出现一个错误，并且忽视文本文件的剩余部分
–silent，-s	沉默模式。只有出现错误时才输出
–socket=path，-S path	当连接localhost时使用的套接字文件 (为默认主机)
–user=user_name，-u user_name	当连接服务器时MySQL使用的用户名
–verbose，-v	冗长模式，打印出程序操作的详细信息
–xml，-X	产生XML输出

运行帮助命令mysqldump --help，可以获得特定版本的完整选项列表

mysql命令逻辑恢复数据

使用mysqldump命令将数据库中的数据备份成一个文本文件。需要恢复时，可以使用 mysql命令 来恢复备份的数据。

mysql命令可以执行备份文件中的CREATE语句和INSERT语句。通过CREATE语句来创建数据库和表，通过INSERT语句来插入备份的数据。

基本语法:

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

其中，dbname参数表示数据库名称。该参数是可选参数，可以指定数据库名，也可以不指定。指定数据库名时，表示还原该数据库下的表。此时需要确保MySQL服务器中已经创建了该名的数据库。不指定数据库名时，表示还原文件中所有的数据库。此时sql文件中包含有CREATE DATABASE语句，不需要MySQL服务器中已存在这些数据库。

单库备份中恢复单库

使用root用户，将之前练习中备份的atguigudb1.sql文件中的备份导入数据库中，命令如下:

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

mysql -uroot -p < atguigudb1.sql

否则需要指定数据库名称，如下所示

mysql -uroot -p atguigudb5 < atguigudb1.sql

全量备份恢复

mysql -u root -p <all.sql

如果使用-all-databases参数备份了所有的数据库，那么恢复时不需要指定数据库。对应的sql文件包含有CREATE DATABASE语句，可通过该语句创建数据库。创建数据库后，可以执行sql文件中的USE语句选择数据库，再创建表并插入记录。

从全量备份中恢复单库

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

举例：

sed -n '/^-- Current Database: `atguigudb1`/,/^-- Current Database: `/p' all_database.sql > atguigudb_all_1.sql
#分离完成后我们再导入atguigudb_all_1.sql即可恢复单个库
mysql -uroot -p < atguigudb_all_1.sql

从单库备份中恢复单表

举例: 我们有atguigudb1整库的备份，但是由于class表误操作，需要单独恢复出这张表

cat atguigudb1.sql |  sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `class`/!d;q' > class_structure.sql
cat atguigudb1.sql |  grep --ignore-case 'insert into `class`' > class_data.sql
#用shell语法分离出创建表的语句及插入数据的语句后 再依次导出即可完成恢复

use atguigudb1;
source class_structure.sql;
source class_data.sgl;

**注意：**在进入数据库进行恢复时，需保证在备份文件所在目录进入，否则会找不到备份文件

物理备份:直接复制整个数据库

InnoDB表并不适合物理备份与恢复，MyISAM较为适合

直接将MySQL中的数据库文件复制出来。这种方法最简单，速度也最快。MySQL的数据库目录位置不一定相同:

• 在Windows平台下，MySQL8.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数据库文件格式相同。

此外，还可以考虑使用相关工具实现备份。比如，MySQLhotcopy 工具。MysQLhotcopy是一个Perl脚本，它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。它是备份数据库或单个表最快的途径，但它只能运行在数据库目录所在的机器上，并且只能备份MyISAM类型的表。多用于mysql5.5之前。

物理恢复:直接复制到数据库目录

步骤:

1. 演示删除备份的数据库中指定表的数据
2. 将备份的数据库数据拷贝到数据目录下，并重启MySQL服务器
3. 查询相关表的数据是否恢复。需要使用下面的 chown 操作

要求:

• 必须确保备份数据的数据库和待恢复的数据库服务器的主版本号相同

  • 因为只有MySQL数据库主版本号相同时，才能保证这两个MySQL数据库文件类型是相同的

• 这种方式对MyISAM类型的表比较有效，对于InnoDB类型的表则不可用

  • 因为InnoDB表的表空间不能直接复制

• 在Linux操作系统下，复制到数据库目录后，一定要将数据库的用户和组变成mysql，命令如下：

  chown -R mysql.mysql /var/lib/mysql/dbname
  

  其中，两个mysql分别表示组和用户;“R”参数可以改变文件夹下的所有子文件的用户和组;“dbname”参数表示数据库目录。

表的导出与导入

表的导出

使用SELECT…INTO OUTFILE导出文本文件

在MySQL中，可以使用SELECT…INTOOUTFILE语句将表的内容导出成一个文本文件

举例：使用SELECT…INTO OUTFILE将studydb数据库中user表中的记录导出到文本文件

（1）选择数据库studydb，并查询user表，执行结果如下所示。

（2）mysql 默认对导出的目录有权限限制，也就是说使用命令行进行导出的时候，需要指定目录进行操作。

查询secure_file_priv值:

SHOW GLOBAL VARIABLES LIKE '%secure%';

参数secure_file_priv的可选值和作用分别是:

• 如果设置为empty，表示不限制文件生成的位置，这是不安全的设置;
• 如果设置为一个表示路径的字符串，就要求生成的文件只能放在这个指定的目录，或者它的子目录;
• 如果设置为NULL，就表示禁止在这个MySQL实例上执行select …into outfile 操作;

（3）上面结果中显示，secure_file_priv变量的值为/var/lib/mysql-files/，导出目录设置为该目录，SQL语句如下

SELECT * FROM user INTO OUTFILE "/var/lib/mysql-files/user.txt";

（4）查看/var/lib/mysql-files/user.txt文件

使用mysqldump命令导出文本文件

举例1: 使用mysqldump命令将将studydb数据库中user表中的记录导出到文本文件:

mysqldump -uroot -p -T "/var/lib/mysql-files/" studydb user

mysqldump命令执行完毕后，在指定的目录/var/lib/mysql-files/下生成了user.sql和user.txt文件.

• user.sql文件，其内容包含创建user表的CREATE语句。
• user.txt文件，其内容只包含user表中的数据

举例2:使用mysqldump将study数据库中的user表导出到文本文件，使用FIELDS选项，要求字段之间使用逗号“，”间隔，所有字符类型字段值用双引号括起来:

mysqldump -uroot -p -T "/var/lib/mysql-files/" studydb user --fields-terminated-by=',' --fields-optionally-enclosed-by='\"'

语句mysqldump语句执行成功之后，指定目录下会出现两个文件user.sql和user.txt

使用mysql命令导出文本文件

举例1:使用mysql语句导出studydb数据中user表中的记录到文本文件:

mysql -uroot -p --execute="SELECT * FROM user;" studydb > "/var/lib/mysql-files/user.txt"

举例2:将studydb数据库user表中的记录导出到文本文件，使用-veritcal参数将该条件记录分为多行显示

mysql -uroot -p --vertical --execute="SELECT * FROM user;" studydb > "/var/lib/mysql-files/user_1.txt"

举例3:将studydb数据库user表中的记录导出到xml文件，使用–xml参数，具体语如下

mysql -uroot -p --xml --execute="SELECT * FROM user;" studydb > "/var/lib/mysql-files/user_2.xml"

说明: 如果要将表数据导出到html文件中，可以使用–html 选项。然后可以使用浏览器打开

表的导入

使用LOAD DATAINFILE方式导入文本文件

举例1:

使用SELECT…INTOOUTFILE将studydb数据库中user表的记录导出到文本文件

SELECT * FROM studydb.user INTO OUTFILE '/var/lib/mysql-files/user_1.txt';

删除account表中的数据:

DELETE FROM studydb.user;

从文本文件user_1.txt中恢复数据

LOAD DATA INFILE '/var/lib/mysql-files/user_1.txt' INTO TABLE studydb.user;

举例2:

选择数据库atguigu，使用SELECT…INTO OUTFILE将studydb数据库user表中的记录导出到文本文件，使用FIELDS选项和LINES选项，要求字段之间使用逗号”，"间隔，所有字段值用双引号括起来

SELECT * FROM studydb.user INTO OUTFILE '/var/lib/mysql-files/user_2.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '\"';

删除user表中的数据:

DELETE FROM studydb.user;

从/var/lib/mysql-files/user_2.txt中导入数据到user表中

LOAD DATA INFILE '/var/lib/mysql-files/user_2.txt' INTO TABLE studydb.user FIELDS TERMINATED BY ',' ENCLOSED BY '\"';

使用mysqlimport方式导入文本文件

举例:

导出文件user.txt，字段之间使用逗号”，"间隔，字段值用双引号括起来:

SELECT * FROM studydb.user INTO OUTFILE '/var/lib/mysql-files/user.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '\"';

删除user表中的数据:

DELETE FROM studydb.user;

使用mysqlimport命令将user_3.txt文件内容导入到数据库studydb的user表中

mysqlimport -uroot -p studydb '/var/lib/mysql-files/user.txt' --fields-terminated-by=',' --fields-optionally-enclosed-by='\"'

除了前面介绍的几个选项之外，mysqlimport支持需要选项，常见的选项有:

参数	含义
-.columns=column_list,c column_list	该选项采用号分隔的列名作为其值。列名的顺序只是如何匹配数据文件列和表列
–compress，-C	压缩在客户端和服务器之间发送的所有信息 (如果二者均支持压缩)
-d，–delete	导入文本文件前请空表
–force，-f	忽视错误。例如，如果某个文本文件的表不存在，就继续处理其他文件。不使用–force，若表不存在，则mysqlimport退出
–host=host_name,-h host host_name	将数据导入给定主机上的MySQL服务器，默认主机是localhost
–ignore-lines=n	忽视数据文件的前n行
–local，-L	从本地客户端读入输入文件
–lock-tables，l	处理文本文件前锁定所有表，以便写入。这样可以确保所有表在服务器上保持同步
-password[=password],-p[password]	当连接服务器时使用的密码。如果使用短选项形式 (-p)，选项和密码之间不能有空格。如果在命令行中–password或-p选项后面没有密码值，就提示输入一个密码
–port=port_num，-P port_num	用户连接的TCP/IP端口号
–protocol={TCP | SOCKET | PIPE | MEMORY}	使用的连接协议
–replace，-r	–replace和-ignore选项控制复制唯一键值已有记录的输入记录的处理。如果指定-replace，新行替换有相同唯一键值的已有行;如果指定-ignore，复制已有唯一键值的输入行被跳过，如果不指定这两个选项，当发现一个复制键值时会出现一个错误，并且忽视文本文件的剩余部分。
–silent，-s	沉默模式。只有出现错误时才输出信息
–user=username，-u user_name	当连接服务器时MySQL使用的用户名
–verbose，-v	几长模式。打印出程序操作的详细信息
–version，-V	显示版本信息并退出。

数据库迁移

概述

数据迁移 (data migration)是指选择、准备、提取和转换数据，并将数据从一个计算机存储系统永久地传输到另一个计算机存储系统的过程。此外，验证迁移数据的完整性 和 退役原来旧的数据存储，也被认为是整个数据迁移过程的一部分。

数据库迁移的原因是多样的，包括服务器或存储设备更换、维护或升级，应用程序迁移，网站集成，灾难恢复和数据中心迁移。

根据不同的需求可能要采取不同的迁移方案，但总体来讲，MySQL 数据迁移方案大致可以分为 物理迁移 和 逻辑迁移两类。通常以尽可能自动化 的方式执行，从而将人力资源从繁琐的任务中解放出来。

迁移方案

• 物理迁移

  物理迁移适用于大数据量下的整体迁移。使用物理迁移方案的优点是比较快速，但需要停机迁移并且要求 MySQL版本及配置必须和原服务器相同，也可能引起未知问题。

  物理迁移包括拷贝数据文件和使用XtraBackup 备份工具两种。

  不同服务器之间可以采用物理迁移，我们可以在新的服务器上安装好同版本的数据库软件，创建好相同目录，建议配置文件也要和原数据库相同，然后从原数据库方拷贝来数据文件及日志文件，配置好文件组权限，之后在新服务器这边使用mysqld 命令启动数据库。

• 逻辑迁移

  逻辑迁移适用范围更广，无论是 部分迁移 还是 全量迁移，都可以使用逻辑迁移。逻辑迁移中使用最多的就是通过
  mysqldump等备份工具。

迁移注意点

相同版本的数据库之间迁移注意点

指的是在主版本号相同的MySQL数据库之间进行数据库移动。

方式1:因为迁移前后MySQL数据库的 主版本号相同，所以可以通过复制数据库目录来实现数据库迁移，但是物理迁移方式只适用于MyISAM引擎的表。对于InnoDB表，不能用直接复制文件的方式备份数据库。

方式2:最常见和最安全的方式是使用mysqldump命令导出数据，然后在目标数据库服务器中使用MySQL命令导入。

举例:

#host1的机器中备份所有数据库，并将数据库迁移到名为host2的机器上
mysqldump -h host1 -uroot -p --all-databases | mysql -h host2 -uroot -p

在上语句中，“|”符号表示管道，其作用是将mysaldump备份的文件给mysal命令“all-databases”表示要迁移所有的数据库。通过这种方式可以直接实现迁移。

不同版本的数据库之间迁移注意点

例如，原来很多服务器使用5.7版本的MySQL数据库，在8.0版本推出来以后，改进了5.7版本的很多缺陷，因此需要把数据库升级到8.0版本

旧版本与新版本的MySQL可能使用不同的默认字符集，例如有的旧版本中使用latin1作为默认字符集，而最新版本的MySQL默认字符集为utf8mb4。如果数据库中有中文数据，那么迁移过程中需要对 默认字符集进行修改，不然可能无法正常显示数据.

高版本的MySQL数据库通常都会 兼容低版本，因此可以从低版本的MySQL数据库迁移到高版本的MySQL数据库。

不同数据库之间迁移注意点

不同数据库之间迁移是指从其他类型的数据库迁移到MySQL数据库，或者从MySQL数据库迁移到其他类型的数据库。这种迁移没有普适的解决方法。

迁移之前，需要了解不同数据库的架构，比较它们之间的差异。不同数据库中定义相同类型的数据的 关键字可能会不同。例如，MySQL中日期字段分为DATE和TIME两种，而ORACLE日期字段只有DATE;SQL Server数据库中有ntext、lmage等数据类型，MysQL数据库没有这些数据类型;MySQL支持的ENUM和SET类型，这些SQL Server数据库不支持。

另外，数据库厂商并没有完全按照SQL标准来设计数据库系统，导致不同的数据库系统的 SQL语句有差别。例如，微软的SQL Server软件使用的是T-SQL语句，T-SQL中包含了非标准的SQL语句，不能和MySQL的SQL语句兼容。

不同类型数据库之间的差异造成了互相迁移的困难，这些差异其实是商业公司故意造成的技术壁垒。但是不同类型的数据库之间的迁移并 不是完全不可能。例如，可以使用 MyODBC 实现MySOL和SQL Server之间的迁移。MySQL官方提供的工具MySQL Migration Toolkit 也可以在不同数据之间进行数据迁移。MySQL迁移到Oracle时，需要使用mysqldump命令导出sql文件，然后，手动更改 sql文件中的CREATE语句。

迁移小结

预防误删

传统的高可用架构是不能预防误删数据的，因为主库的一个drop table命令，会通过binlog传给所有从库和级联从库，进而导致整个集群的实例都会执行这个命令。

为了找到解决误删数据的更高效的方法，我们需要先对和MySQL相关的误删数据，做下分类:

1. 使用delete语句误删数据行;
2. 使用drop table或者truncate table语句误删数据表；
3. 使用drop database语句误删数据库；
4. 使用rm命令误删整个MySQL实例

delete: 误删行

处理措施1: 数据恢复

使用Flashback工具恢复数据。

原理：修改binlog 内容，拿回原库重放。如果误删数据涉及到了多个事务的话，需要将事务的顺序调过来再执行。

使用前提: binlog_format=row 和 binlog_row_image=FULL。

处理措施2:预防

• 代码上线前，必须 SQL审查、审计。
• 建议可以打开 安全模式，把sql_safe_updates 参数设置为on 。强制要求加 where 条件且where后需要是索引字段，否则必须使用limit。否则就会报错。

truncate/drop: 误删库/表

背景:

delete全表是很慢的，需要生成回滚日志、写redo、写binlog。所以，从性能角度考虑，优先考虑使用truncate table或者drop table命令。

使用delete命令删除的数据，你还可以用Flashback来恢复。而使用truncate /drop table和drop database命令删除的数据，就没办法通过Flashback来恢复了。因为，即使我们配置了binlog_format=row，执行这三个命令时，记录的binlog还是statement格式。binlog里面就只有一个truncate/drop 语句，这些信息是恢复不出数据的。

方案:

这种情况下恢复数据，需要使用 全量备份与增量日志 结合的方式。

方案的前提: 有定期的全量备份，并且实时备份binlog。

举例:有人误删了一个库，时间为下午3点。步骤如下：

1. 取最近一次 全量备份。假设设置数据库库是一天一备，最近备份数据是当天 凌晨2点
2. 用备份恢复出一个 临时库; (注意:这里选择临时库，而不是直接操作主库)
3. 取出凌晨2点之后的binlog日志
4. 剔除误删除数据的语句外，其它语句全部应用到临时库。 (前面讲过binlog的恢复)
5. 最后恢复到主库

预防使用truncate/drop误删库/表

上面我们说了使用truncate /drop语句误删库/ 的恢复方案，在生产环境中可以通过下面建议的方案来尽量的避免类似的误操作。

权限分离

• 限制帐户权限，核心的数据库，一般都 不能随便分配写权限，想要获取写权限需要 审批。比如只给业务开发人员DML权限，不给truncate/drop权限。即使是DBA团队成员，日常也都规定只使用只读账号，必要的时候使用有更新权限的账号。
• 不同的账号，不同的数据之间要进行 权限分离，避免一个账号可以删除所有库。

制定操作规范

比如在删除数据表之前，必须先对表做改名操作(比如加_to_be_deleted )。然后，观察一段时间，确保对业务无影响以后再删除这张表。

设置延迟复制备库

简单的说延迟复制就是设置一个固定的延迟时间，比如1个小时，让从库落后主库一个小时。出现误删除操作1小时内，到这个备库上执行 stop slave，再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据.这里通过 CHANGE MASTER TO MASTER_DELAY = N 命令，可以指定这个备库持续保持跟主库有N秒的延迟。比如把N设置为3600，即代表1个小时。

此外，延迟复制还可以用来解决以下问题:

①用来做 延迟测试，比如做好的数据库读写分离，把从库作为读库，那么想知道当数据产生延识的时候到底会发生什么，就可以使用这个特性模拟延迟。

②用于 老数据的查询等需求，比如你经常需要查看某天前一个表或者字段的数值，你可能需要把备份恢复后进往查看，如果有延迟从库，比如延迟一周，那么就可以解决这样类似的需求。

rm: 误删MySQL实例

对于一个有高可用机制的MySQL集群来说，不用担心 rm删除数据。因为只删掉其中某一个节点数据的话，HA系统就会选出一个新的主库，从而保证整个集群的正常工作。我们把这个节点上的数据恢复回来后，再接入整个集群就好了。