mysql数据库备份与恢复

一、概述

关于mysql数据恢复和备份有以下两种方式

物理备份和恢复：物理方式是直接通过copy数据到某一目录，这种方式恢复速度比较快，但占用空间比 较大，MySQL中可以用 xtrabackup 工具来进行物理备份。

逻辑备份和恢复：逻辑备份就是 备份sql语句 ，在恢复的 时候执行备份的sql语句实现数据库数据的重现。逻辑备份恢复速度慢，但占用空 间小，更灵活。MySQL 中常用的逻辑备份工具为 mysqldump 。

二、逻辑备份和恢复

1、mysqldump实现逻辑备份

1.1、备份一个数据库

备份命令

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

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

案例：

mysqldump -uroot -p db1> /var/lib/mysql/lph.sql

1.2、 备份全部数据库

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

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

1.3 、备份部分数据库

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

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

mysqldump -uroot -p --databases lph lph12>two_database.sql
或
mysqldump -uroot -p -B lph lph12> two_database.sql

1.4、 备份部分表

比如，在表变更前做个备份。语法如下：

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

举例

mysqldump -uroot -p lph book> book.sql

可以看到，book文件和备份的库文件类似。不同的是，book文件只包含book表的DROP、CREATE和 INSERT语句。

备份多张表使用下面的命令，比如备份book和account表：

#备份多张表
mysqldump -uroot -p lph book account > 2_tables_bak.sql

1.5、备份单表的部分数据

有些时候一张表的数据量很大，我们只需要部分数据。这时就可以使用 --where 选项了。where后面附 带需要满足的条件。 举例：备份student表中id小于10的数据：

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

1.6、 排除某些表的备份

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

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

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

grep "student" no_stu_bak.sql

1.7 、只备份结构或只备份数据

只备份结构的话可以使用 --no-data 简写为 -d 选项；

mysqldump -uroot -p lph --no-data > lph_no_data_bak.sql

只备份数据可以使用 --no-create-info 简写为 -t 选项。

mysqldump -uroot -p lph --no-create-info > lph_no_create_info_bak.sql

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

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

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

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

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

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

mysqldump -uroot -p -R -E --databases lph > fun_lph_bak.sql

1.9、 mysqldump常用选项

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，禁止转储文件中的其他信息，例如程序版本、服务器版本和主机。--skipcomments与--comments=0的结果相同。默认值为1，即包括额外信息。
--compact：产生少量输出。该选项禁用注释并启用--skip-add-drop-tables、--no-set-names、--skipdisable-keys和--skip-add-locking选项。
--compatible=name：产生与其他数据库系统或旧的MySQL服务器更兼容的输出，值可以为ansi、MySQL323、
MySQL40、postgresql、oracle、mssql、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：在主复制服务器上，完成转储操作后删除二进制日志。该选项自动启用-masterdata。
--extended-insert，-e：使用包括几个VALUES列表的多行INSERT语法。这样使得转储文件更小，重载文件时可
以加速插入。
--flush-logs，-F：开始转储前刷新MySQL服务器日志文件。该选项要求RELOAD权限。
--force，-f：在表转储过程中，即使出现SQL错误也继续。
--lock-all-tables，-x：对所有数据库中的所有表加锁。在整体转储过程中通过全局锁定来实现。该选项自动关
闭--single-transaction和--lock-tables。
--lock-tables，-l：开始转储前锁定所有表。用READ LOCAL锁定表以允许并行插入MyISAM表。对于事务表（例
如InnoDB和BDB），--single-transaction是一个更好的选项，因为它根本不需要锁定表。
--no-create-db，-n：该选项禁用CREATE DATABASE /*!32312 IF NOT EXIST*/db_name语句，如果给出-
-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|SOCKET|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 ，可以获得特定版本的完整选项列表。

提示 如果运行mysqldump没有--quick或--opt选项，mysqldump在转储结果前将整个结果集装入内 存。如果转储大数据库可能会出现问题，该选项默认启用，但可以用--skip-opt禁用。如果使用最 新版本的mysqldump程序备份数据，并用于恢复到比较旧版本的MySQL服务器中，则不要使用--opt 或-e选项。

2、逻辑恢复

2.1、 单库备份中恢复单库

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

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

mysql -uroot -p < lph.sql

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

mysql -uroot -p lph4< lph.sql

2.2、 全量备份恢复

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

mysql –u root –p < all.sql

mysql -uroot -pxxxxxx < all.sq

2.3 、从全量备份中恢复单库

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

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

2.4 、从单库备份中恢复单表

这个需求还是比较常见的。比如说我们知道哪个表误操作了，那么就可以用单表恢复的方式来恢复。 举例：我们有test整库的备份，但是由于class表误操作，需要单独恢复出这张表。

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

use test;
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)

三、物理备份和恢复

1、 物理备份：直接复制整个数据库

直接将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数据库文件格式相 同。

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

2、物理恢复：直接复制到数据库目录

要求：

• 必须确保备份数据的数据库和待恢复的数据库服务器的主版本号相同。 因为只有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。

四、表的导出与导入

1、表的导出

方式1：以使用SELECT…INTO OUTFILE语句将表的内容导出成一个文本文件

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

SHOW GLOBAL VARIABLES LIKE '%secure%';

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

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

查看文件

1 张三 90
2 李四 100
3 王五 0

方式2： 使用mysqldump命令导出文本文件

案例1：使用mysqldump命令将将test数据库中account表中的记录导出到文本文件：

mysqldump -uroot -p -T "/var/lib/mysql-files/" test account

mysqldump命令执行完毕后，在指定的目录/var/lib/mysql-files/下生成了account.sql和account.txt文件。 打开account.sql文件，其内容包含创建account表的CREATE语句。

打开account.txt文件，其内容只包含account表中的数据。

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

mysqldump -uroot -p -T "/var/lib/mysql-files/" test account --fields-terminatedby=',' --fields-optionally-enclosed-by='\"'

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

打开account.txt文件，其内容包含创建account表的数据。从文件中可以看出，字段之间用逗号隔开，字 符类型的值被双引号括起来。

方式3：使用mysql命令导出文本文件

举例1：使用mysql语句导出test数据中account表中的记录到文本文件：

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

打开account.txt文件，其内容包含创建account表的数据

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

mysql -uroot -p --vertical --execute="SELECT * FROM account;" test> "/var/lib/mysql-files/account_1.txt"

打开account_1.txt文件，其内容包含创建account表的数据。

举例3：将test数据库account表中的记录导出到xml文件，使用--xml参数，具体语句如下。

mysql -uroot -p --xml --execute="SELECT * FROM account;" test>"/var/lib/mysql-files/account_3.xml

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

2、表的导入

方式1：使用LOAD DATA INFILE方式导入文本文件

举例1： 使用LOAD DATA INFILE将文本文件导入到test数据库account表中

LOAD DATA INFILE '/var/lib/mysql-files/account_0.txt' INTO TABLE test.account;

案例2：文本文件字段之间用逗号分隔，所有字段用双引号括起来的导入

LOAD DATA INFILE '/var/lib/mysql-files/account_1.txt' INTO TABLE test.account FIELDS TERMINATED BY ',' ENCLOSED BY '\"';

方式2：使用mysqlimport方式导入文本文件

案例1：导出的文本文件字段之间使用逗号"，"间隔，字段值用双引号括起来，导入命令

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

五、数据库迁移

1、数据库迁移概述

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

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

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

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

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

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

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

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

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

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

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

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

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

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

六、删库跑路

1、delete 误删行

恢复数据比较安全的做法，是 恢复出一个备份 ，或者找一个从库作为 临时库 ，在这个临时库上执 行这些操作，然后再将确认过的临时库的数据，恢复回主库。如果直接修改主库，可能导致对数 据的 二次破坏 。

当然，针对预防误删数据的问题，建议如下

• 把 sql_safe_updates 参数设置为 on 。这样一来，如果我们忘记在delete或者update语句 中写where条件，或者where条件里面没有包含索引字段的话，这条语句的执行就会报错
  
  如果确定要把一个小表的数据全部删掉，在设置了sql_safe_updates=on情况下，可以 在delete语句中加上where条件，比如where id>=0。
• 代码上线前，必须经过 SQL审计

2、truncate/drop 误删库/表

这种情况下，要想恢复数据，就需要使用 全量备份 ，加 增量日志 的方式了。这个方案要求线上有定期的 全量备份，并且实时备份binlog。

在这两个条件都具备的情况下，假如有人中午12点误删了一个库，恢复数据的流程如下：

1. 取最近一次 全量备份 ，假设这个库是一天一备，上次备份是当天 凌晨2点 ；

2. 用备份恢复出一个 临时库 ；

3. 从日志备份里面，取出凌晨2点之后的日志；

4. 把这些日志，除了误删除数据的语句外，全部应用到临时库。

3、 预防误删库/表的方法

1. 账号分离 。这样做的目的是，避免写错命令。比如：

只给业务开发同学DML权限，而不给truncate/drop权限。而如果业务开发人员有DDL需求的话，可 以通过开发管理系统得到支持。

即使是DBA团队成员，日常也都规定只使用 只读账号 ，必要的时候才使用有更新权限的账号。

2. 制定操作规范 。比如：

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

改表名的时候，要求给表名加固定的后缀（比如加 _to_be_deleted )，然后删除表的动作必须通 过管理系统执行。并且，管理系统删除表的时候，只能删除固定后缀的表。

3、延迟复制备库

如果有 非常核心 的业务，不允许太长的恢复时间，可以考虑搭建延迟复制的备库。一般的主备复制结构 存在的问题是，如果主库上有个表被误删了，这个命令很快也会被发给所有从库，进而导致所有从库的 数据表也都一起被误删了。

延迟复制的备库是一种特殊的备库，通过 CHANGE MASTER TO MASTER_DELAY = N 命令，可以指定这 个备库持续保持跟主库有 N秒的延迟 。比如你把N设置为3600，这就代表了如果主库上有数据被误删了， 并且在1小时内发现了这个误操作命令，这个命令就还没有在这个延迟复制的备库执行。这时候到这个备 库上执行stop slave，再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据。

4、 rm误删MySQL实例

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