数据库备份与恢复

定期备份 MySQL 数据库服务器

1、备份分类

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

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

2. mysqldump

（1）实现逻辑备份

（2）mysqldump 由 MySQL 提供的数据库备份工具

 

备份数据库

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

（1）查出需要备份的表的结构，在文本文件中生成一个 CREATE 语句

（2）将表中的所有记录转换成一条 INSERT 语句

2、备份一个数据库

（1）备份文件后缀名不一定为 .sql，可以选择其他后缀名

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

3、备份全部数据库

（1）mysqldump 备份整个实例，可以使用 --all-databases 或 -A 参数

mysqldump -u 用户名称 -h 主机名称 -p密码 --all-databases > 备份文件名称.sql

mysqldump -u 用户名称 -h 主机名称 -p密码 -A > 备份文件名称.sql

4、备份部分数据库

（1）使用 --databases 或 -B 参数，该参数后面跟数据库名称，多个数据库间用空格隔开

（2）如果指定 databases 参数，备份文件中会存在创建数据库的语句，如果不指定参数，则不存在

mysqldump -u 用户名称 -h 主机名称 -p密码 --databases [数据库的名称1 ,[数据库的名称2……]] > 备份文件名称.sql

mysqldump -u 用户名称 -h 主机名称 -p密码 -B [数据库的名称1 ,[数据库的名称2……]] > 备份文件名称.sql

5、备份部分表 

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

6、备份单表的部分数据

（1）使用 --where 选项，后面附带需要满足的条件

mysqldump -u 用户名称 -h 主机名称 -p密码 待备份的数据库名称 --where="条件" 表名 > 备份文件名称.sql

7、排除某些表的备份

（1）备份某个库，但需要排除掉某些表，选项 --ignore-table 可以完成功能

mysqldump -u 用户名称 -h 主机名称 -p密码 待备份的数据库名称 --ignore-table=数据库名.表名1, 数据库.表名2…… > 备份文件名称.sql

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

（1）只备份结构，使用 --no-data，简写为 -d

mysqldump -u 用户名称 -h 主机名称 -p密码 待备份的数据库名称 --no-data > 备份文件名称.sql

（2）只备份数据，使用 --no-create-info，简写为 -t

mysqldump -u 用户名称 -h 主机名称 -p密码 待备份的数据库名称 --no-create-info > 备份文件名称.sql

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

（1）mysqldump 备份，默认不包含存储过程、自定义函数、事件

（2）使用 --routines 或 -R 选项来备份存储过程及函数

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

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

（3）使用 --events 或 -E 参数来备份事件

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

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

 

mysqldump 常用选项

1、--add-drop-database：在每个 CREATE DATABASE 语句前，添加 DROP DATABASE 语句

2、--add-drop-tables：在每个 CREATE TABLE 语句前，添加 DROP TABLE 语句

3、--add-locking：用 LOCK TABLES 和 UNLOCK TABLES 语句引用每个表转储，重载转储文件时插入得更快

4、--all-database、-A：转储所有数据库中的所有表，与使用 --database 选项相同，在命令行中命名所有数据库

5、--comment[=0 / 1]：如果设置为 0，禁止转储文件中的其他信息，例如程序版本、服务器版本、主机，--skip-comments 与 --comments=0 结果相同，默认值为 1，即包括额外信息

6、--compact：产生少量输出，该选项禁用注释并启用 --skip-add-drop-tables、--no-set-names、--skip-disable-keys、--skip-add-locking 选项

7、--compatible=name：产生与其他数据库系统或旧 MySQL 服务器更兼容的输出，值可以为 ansi、MySQL323、MySQL40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_table_options、no_field_options

8、--complete_insert、-c：使用包括列名的完整 INSERT 语句

9、--debug[=debug_options]、-#[debug-options]：写调试日志

10、--delete、-D：导入文本文件前清空表

11、--default-character-set=charset：使用 charsets 默认字符集，如果没有指定，则使用 utf8

12、--delete--master-logs：在主复制服务器上，完成转储操作后删除二进制日志，该选项自动启用 -master-data

13、--extended-insert、-e：使用包括几个 VALUES 列表的多行 INSERT 语法，使得转储文件更小，重载文件时可以加速插入

14、--flush-logs、-F：开始转储前，刷新 MySQL 服务器日志文件，该选项要求 RELOAD 权限

15、--force、-f：在表转储过程中，即使出现 SOL 错误也继续

16、--lock-all-tables、-x：对所有数据库中的所有表加锁，在整体转储过程中通过全局锁定来实现，该选项自动关闭 --single-transaction、--lock-tables

17、--lock-tables、-l：开始转储前锁定所有表，用 READ LOCAL 锁定表，以允许并行插入 MyISAM 表，对于事务表（例如：InnoDB、BDB），--single-transaction 是一个更好的选项，因为它根本不需要锁定表

18、--no-create-db、-n：该选项禁用 CREATE DATABASE /*!32312 IF NOT EXIST*/db_name 语句，如果给出 --database 或 --all-database 选项，就包含到输出中

19、--no-create-info、-t：只导出数据，而不添加 CREATE TABLE 语句

20、--no-data、-d：不写表的任何行信息，只转储表的结构

21、--opt：该选项是速记，它可以快速进行转储操作，并产生一个能很快装入 MySQL 服务器的转储文件，该选项默认开启，但可以用 --skip-opt 禁用

22、--password[=password]、-p[password]：当连接服务器时使用的密码

23、-port=port_num、-P port_num：用于连接的 TCP/IP 端口号

24、-protocol={TCP / SOCKET / PIPE / MEMORY}：使用的连接协议

25、--replace、-r -replace、--ignore：控制替换或复制唯一键值已有记录的输入记录的处理

（1）若指定 --replace，新行替换有相同的唯一键值的已有行，使用 REPLACE INTO 取代 INSERT INTO

（2）若指定 --ignore，复制己有的唯一键值的输入行被跳过

（3）若不指定以上两个选项，当发现一个复制键值时，会出现一个错误，并且忽视文本文件的剩余部分

26、-silent、-s：沉默模式，只有出现错误时才输出

27、--socket=path、-S path：当连接 localhost 时，使用的套接字文件（为默认主机）

28、--user=user_name、-u user_name：当连接服务器时，MySQL 使用的用户名

29、--verbose、-v：冗长模式，打印出程序操作的详细信息

30、--xml、-X：产生 XML 输出

31、mysqldump --help：获取特定版本的完整选项列表

32、事项

（1）如果运行 mysqldump 没有 --quick 或 --opt 选项，mysqldump 在转储结果前，将整个结果集装入内存，如果转储大数据库可能会出现问题，该选项默认启用，但可以使用 --skip-opt 禁用

（2）如果使用最新版本的 mysqldump 程序备份数据，并用于恢复到旧版本的 MySQL 服务器中，则不要使用 --opt 或 -e 选项

 

mysql 命令恢复数据

1、mysql 命令可以执行备份文件中的 CREATE、INSERT 语句

（1）通过 CREATE 来创建数据库和表

（2）通过 INSERT 来插入备份的数据

2、基本语法

mysql -u 用户名 -p 数据库名称 < 已备份的数据库文件名.sql

（1）数据库名称，为可选参数

3、单库备份中恢复单库

（1）指定数据库名时，表示还原该数据库下的表，此时需要确保 MySQL 服务器中已经创建同名数据库

mysql -u 用户名 -p 数据库名称 < 单库备份文件名.sql

（2）不指定数据库名时，表示还原文件中所有的数据库，若 sql 文件中包含 CREATE DATABASE 语句，不需要 MySQL 服务器中存在同名数据库

mysql -u 用户名 -p < 单库备份文件名.sql

4、全量备份恢复

mysql -u 用户名 -p < 单库备份文件名.sql

（1）如果使用 --all-databases 参数备份所有数据库，则恢复时不需要指定数据库

（2）对应 sql 文件包含有 CREATE DATABASE 语句，可通过该语句创建数据库

（3）创建数据库后，可以执行 sql 文件中的 USE 语句选择数据库，再创建表并插入记录

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

（1）从全量备份中，提取单库备份

sed -n '/^-- Current Database: `所提取的单库名`/,/^-- Current Database: `/p' 全量库备份文件名.sql > 单库备份文件名.sql

（2）分离完成后，再导入单库备份.sql，即可恢复单个库

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

（1）从单库备份，提取 CREATE TABLE，即单表的结构

cat 单库备份.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `所提取的表`/!d;q' > 只包含单表结构的文件.sql

（2）从单库备份，提取所要恢复单表的 insert to  语句

cat 单表备份.sql | grep --ignore-case 'insert into `所需单表`' > 只包含单表数据的文件.sql

（3）使用 USE，切换到数据库，使用 source 语句，导入 .sql 文件

USE 数据库名;

source 只包含单表结构的文件的完整路径;

source 只包含单表数据的文件的完整路径;

 

物理备份

1、直接复制 MySQL 中的数据库文件，最简单，速度最快

2、MySQL 数据库目录位置不一定相同

（1）Windows，MySQL 8.0 存放数据库的目录默认：C:\ProgramData\MySQL\MySQL Server 8.0\Data，或其他用户自定义目录

（2）Linux，数据库目录位置通常为 /var/lib/mysql/

（3）msc OS，数据库目录位置通常为 /usr/local/mysql/data

3、为了保证备份的一致性

（1）方式一：备份前，将服务器停止

（2）方式二：备份前，对相关表执行 FLUSH TABLES WITH READ LOCK 操作，当复制数据库目录中的文件时，允许其他客户继续查询表，同时，FLUSH TABLES 确保开始备份前，将所有激活的索引页写入硬盘；实际情况不允许停止 MySQL 服务器，或锁住表，且对 InnoDB 表不适用，对于 MyISAM 表，方便备份和还原，但还原时最好是相同版本的 MySQL 数据库，否则可能会存在文件类型不同的情况

4、物理备份完毕后，执行 UNLOCK TABLES 来结算其他客户对表的修改行为

5、在 MySQL 版本号中，第一个数字表示主版本号，主版本号相同的 MySQL 数据库文件格式相同

6、使用相关工具实现备份，比如：MySQLhotcopy 工具

（1）Perl 脚本，使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 来快速备份数据库

（2）备份数据库或单个表最快的途径，但只能运行在数据库目录所在的机器上，并且只能备份 MyISAM 的表，多用于 MySQL 5.5 之前

 

物理恢复

1、直接复制到数据库目录

2、要求

（1）必须确保备份数据的数据库，和待恢复的数据库服务器的主版本号相同，只有 MySQL 数据库主版本号相同时，才能保证这两个 MySQL 数据库文件类型是相同的

（2）对 MyISAM 表比较有效 ，对于 InnoDB 表则不可用，InnoDB 表的表空间不能直接复制

3、在 Linux 下，复制到数据库目录后，必须使用 chown 命令，将文件夹的用户组变为 mysql，将用户变为 mysql

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

（1）-R：可以改变文件夹下的所有子文件的用户和组

（2）两个 mysql 分别表示组和用户

（3）dbname：表示数据库目录

（4）Linux 权限设置非常严格，一般 MySQL 数据库只有 root 用户和 mysql 用户组下的 mysql 用户才可以访问，因此将数据库目录复制到指定文件夹后，

 

表的导出

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

SELECT 列名 FROM 表名 [WHERE 语句] INTO OUTFILE '目标文件' [OPTIONS];

（1）SELECT 查询所需要的数据，用 INTO OUTFILE 导出数据

（2）目标文件：指定将查询的记录导出到哪个文件，目标文件不能是一个已经存在的文件

（3）[OPTIONS] 为可选参数选项，OPTIONS 部分的语法包括 FIELDS 和 LINES 子句

（4）FIELDS TERMINATED BY '字符串'：设置字符串为字段之间的分隔符，可以为单个或多个字符，默认情况下为制表符 \t

（5）FIELDS [OPTIONALLY] ENCLOSED BY '字符'：设置字符来括上 CHAR、VARCHAR、TEXT 等字符型字段，如果使用 OPTIONALLY，则只能用来括上 CHAR 和 VARCHAR 等字符型字段

（6）FIELDS ESCAPED BY '字符'：设置如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为 \

（7）LINES STARTING BY '字符串'：设置每行开头的字符，可以为单个或多个字符，默认情况下不使用任何字符

（8）LINES TERMINATED BY '字符串'：设置每行结尾的字符，可以为单个或多个字符，默认值为 \n

（9）注意：FIELDS 和 LINES 两个子句都是自选的，但若两个都被指定，FIELDS 必须位于 LINES 前面

2、使用 mysqldump 命令导出文本文件

（1）-T 参数导出文本文件

3、使用 mysql 命令导出文本文件

 

表的导入

1、使用 LOAD DATA INFILE 导入文本文件

LOAD DATA INFILE '表文本文件的完整路径' INTO TABLE 数据库名.表名 [OPTIONS];

（1）[OPTIONS] 为可选参数选项，OPTIONS 部分的语法包括 FIELDS 和 LINES 子句，与导出用法相同

（2）FIELDS TERMINATED BY '字符串'：设置字符串为字段之间的分隔符，可以为单个或多个字符，默认情况下为制表符 \t

（3）FIELDS [OPTIONALLY] ENCLOSED BY '字符'：设置字符来括上 CHAR、VARCHAR、TEXT 等字符型字段，如果使用 OPTIONALLY，则只能用来括上 CHAR 和 VARCHAR 等字符型字段

（4）FIELDS ESCAPED BY '字符'：设置如何写入或读取特殊字符，只能为单个字符，即设置转义字符，默认值为 \

（5）LINES STARTING BY '字符串'：设置每行开头的字符，可以为单个或多个字符，默认情况下不使用任何字符

（6）LINES TERMINATED BY '字符串'：设置每行结尾的字符，可以为单个或多个字符，默认值为 \n

（7）注意：FIELDS 和 LINES 两个子句都是自选的，但若两个都被指定，FIELDS 必须位于 LINES 前面

2、使用 mysqlimport 导入文本文件

（1）--columns=column_list、-c column_list：该选项采用逗号分隔的列名作为其值，列名的顺序只是如何匹配数据文件列和表列

（2）--compress、-C：压缩在客户端和服务器之间发送的所有信息（若二者均支持压缩）

（3）-d、--delete：导入文本文件前清空表

（4）--force、-f：忽视错误，例如，如果某个文本文件的表不存在，就继续处理其他文件，不使用 --force，若表不存在，则 mysqlimport 退出

（5）--host=host_name、-h host host_name：将数据导入给定主机上的 MySQL 服务器，默认主机是 localhost

（6）--ignore、-i：与 --replace 选项相同

（7）--ignore-lines=n：忽视数据文件的前 n 行

（8）--local、-L：从本地客户端读入输入文件

（9）--lock-tables、-l：处理文本文件前锁定所有表，以便写入，可以确保所有表在服务器上保持同步

（10）--password[=password]、-p[password]：当连接服务器时使用的密码，如果使用短选项形式 -p，选项和密码之间不能有空格，如果在命令行中 --password 或 -p 选项后面没有密码值，提示输入密码

（11）--port=port_num、-P port_num：用户连接的 TCP/IP 端口号

（12）--protocol={TCP / SOCKET / PIPE / MEMORY}：使用的连接协议

（13）--replace、-r --replace、-ignore：控制复制唯一键值已有记录的输入记录的处理，如果指定 --replace，新行替换有相同唯一键值的已有行；如果指定 --ignore，复制已有唯一键值的输入行被跳过;如果不指定这两个选项，当发现一个复制键值时会出现一个错误，并且忽视文本文件的剩余部分

（14）--silent、-s：沉默模式，只有出现错误时才输出信息

（15）--user=username、-u user_name：当连接服务器时 MySQL 使用的用户名

（16）--verbose、-v：冗长模式，打印出程序操作的详细信息

（17）--version、-v：显示版本信息并退出

 

数据库迁移

1、物理迁移

（1）适用于大数据量下的整体迁移

（2）比较快速，但需要停机迁移，并且要求 MySQL 版本及配置必须和原服务器相同，可能引起未知问题

（3）包括拷贝数据文件，和使用 XtraBackup 备份工具

（4）不同服务器之间可以采用物理迁移，可以在新服务器上安装相同版本的数据库软件，创建相同目录，建议配置文件和原数据库相同，然后从原数据库拷贝数据文件、日志文件，配置文件组权限，在新服务器使用 mysqld 命令启动数据库

2、逻辑迁移

（1）适用范围更广：部分迁移、全量迁移

（2）通过 mysqldump 等备份工具

 

误删数据分类

1、delete 误删数据行

2、drop table 或 truncate table 误删数据表

3、drop database 误删数据库

4、rm 命令误删整个 MySQL 实例

（1）对于高可用机制的 MySQL 集群，rm 删掉其中某一个节点数据，HA 系统就会开始工作，选出一个新的主库，从而保证整个集群的正常工作

（2）只需要这个节点上把数据恢复回来，再接入整个集群

 

delete：误删行

1、处理措施一：数据恢复

（1）使用 Flashback 工具恢复数据

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

（3）使用前提：binlog_format=row 和 binlog_row_image=FULL

（4）恢复数据：恢复一个备份，或使用一个从库作为临时库，在临时库上执行操作，然后再将确认过的临时库的数据，恢复回主库，如果直接修改主库，可能导致对数据二次破坏

2、处理措施二：预防

（1）代码上线前，必须 SQL 审查、审计

（2）打开安全模式，sql_safe_updates 设置为 on，强制要求加 where 条件，且 where 后需要是索引字段，否则必须使用 limit，否则报错

 

truncate / drop：误删库 / 表

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

2、使用 truncate / drop table、drop database 删除的数据

（1）无法通过 Flashback 恢复，即使配置 binlog_format=row，执行这三个命令时，记录 binlog 还是 statement 格式

（2）binlog 只有一个 truncate / drop，无法恢复数据

3、恢复方案

（1）全量备份 + 增量日志

（2）要求线上有定期的全量备份，并且实时备份 binlog

（3）流程：取最近一次全量备份，用备份恢复出一个临时库，从日志备份中，取出之后的日志，除误删除数据的语句外的语句，全部应用到临时库，最后恢复到主库

4、预防

（1）权限分离

（2）制定操作规范

（3）设置延迟复制备库，单位为秒

CHANGE MASTER TO MASTER_DELAY=N;

 

MySQL 常用命令

1、mysql：mysql 客户端工具

mysql [options] [database]

2、mysqladmin：执行管理操作的客户端程序，检查服务器的配置和当前状态、创建并删除数据库等

3、mysqlbinlog：日志管理工具，查看以二进制格式保存的二进制日志文件

4、mysqldump：客户端备份数据库工具，或在不同数据库之间进行数据迁移，备份内容包含创建表，及插入表的 SQL 语句

5、mysqlimport：客户端数据导入工具，用来导入 mysqldump 加 -T 参数后导出的文本文件

6、source：导入 .sql 文件

7、mysqlshow：客户端对象查找工具，查找存在哪些数据库、数据库中的表、表中的列、索引