MySQL数据备份及还原（一）
关于删库跑路的事故现在已经屡见不鲜了，数据备份的必要性是企业数据管理极其重要的一项工作。关于数据备份、恢复也有很多场景及方法，本系列也会将主要的几种工具通过案例进行演示。
本系列将从逻辑备份及恢复开始讲起，逻辑备份的工具主要有mysqldump/mydumper等其中mydumper可以指定多线程工作，本文介绍的是mysqldump。
01
mysqldump 备份
mysqldump是MySQL数据库自带的逻辑备份工具，属于热备工具。它的备份结果是根据设置的参数将数据库中的信息通过生成创建库、表等对象以及对应表的insert语句组成。
mysqldump 参数选项特别多，可以通过mysqldump --help 查看对应的参数及说明
[root@testdb ~]# mysqldump --help
mysqldump Ver 10.13 Distrib 5.7.25-28, for Linux (x86_64)
Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]mysqldump常用的操作如下
1.1 备份指定表
mysqldump可以备份指定的单个表或指定库的多个表，例如备份testdb库的test1表的表结构和数据
/* 备份testdb库的test1表 */
/usr/local/mysql5.7/bin/mysqldump -uroot -p --socket=/tmp/mysql.sock --master-data=2 --default-character-set=utf8 --single-transaction testdb test1 > test1.sql备份后的文件主体部分如下：
主要参数说明：
--master-data=2 记录当前binlog信息，有1和2两个值,如果值等于1，就会添加一个CHANGE MASTER语句;如果值等于2，也会生成一个CHANGE MASTER，但是语句前添加注释，
如本例中所示（建议设置为2），此信息可以用作后期配置搭建主从同步用
--default-character-set=utf8 设置字符集，建议指明字符集
--single-transaction 配合RR隔离级别使用，用于保证innodb备份数据一致性，且不会锁表，此参数很重要，生产环境中一定要设置该参数
testdb 库名
test1 需要备份的表名，如果需要备份多张表 可以将表名直接罗列在后面 如 test1 test21.2. 备份单个数据库
mysqldump可以备份指定的数据库，可以是单个库也可以是多个库，先备份单个库，如下
/* 备份整个testdb库 */
/usr/local/mysql5.7/bin/mysqldump -uroot -p --socket=/tmp/mysql.sock --master-data=2 --default-character-set=utf8 --single-transaction testdb > testdb.sql结果如下，会将testdb库下的所有表的结构和数据都备份出来
1.3 备份多个库
备份多个数据库可以用如下命令
/* 备份monitor库及testdb库 */
/usr/local/mysql5.7/bin/mysqldump -uroot -p --socket=/tmp/mysql.sock --master-data=2 --default-character-set=utf8 --single-transaction --databases monitor testdb > mul_db.sql结果中主要信息如下，包含了创建库及切换库的内容
参数说明（其他参数之前也说明，此处不再赘述）：
--databases 后面填写需要备份的数据库名即可指定备份对应的库
1.4 备份所有的数据库
如果想备份所有的数据库，可以使用如下命令：
/* 备份所有数据库 */
/usr/local/mysql5.7/bin/mysqldump -uroot -p --socket=/tmp/mysql.sock --master-data=2 --default-character-set=utf8 --single-transaction --all-databases > all_db.sql注：备份中没有information_schema、performance_schema、sys库的信息（MySQL5.7及以上版本）
1.5 其他情况
实际使用中可能还会遇到只备份表结构、只备份数据，需要备份存储过程及事件等需求，相应参数如下：
--no-data 只备份表结构，不包含数据，可以简写为 -d
--no-create-info 只备份数据，不备份建表信息，也可以简写为-t
--routines 备份存储过程及函数,可以简写为 -R
--events 备份事件，可以简写为 -E
--triggers 备份触发器
--flush-logs 备份完成后切换日志
--flush-privileges 备份完成后刷新权限
--set-gtid-purged 开启了GTID的库需要设置该参数，值可以是ON, OFF 或 AUTO
--where 指定条件，例如每张表导出1000行的记录或者 导出每张表id<=10的记录等，可以参考历史文章查看示例
--skip-add-drop-table 不生成删除表的语句1.6 示例脚本
备份全部数据库，包含触发器、事件、存储过程，同时刷新日志及权限的实例
/usr/local/mysql5.7/bin/mysqldump -uroot -p --socket=/tmp/mysql.sock --master-data=2 --default-character-set=utf8 --routines --triggers --events --flush-logs --flush-privileges --single-transaction --all-databases >backup.sql注意：备份中存在drop表的情况，如果确定需要重建表则保持默认，否则保险起见，添加--skip-add-drop-table 参数
1.7 说点特别的
mysqldump也可以生成平面文件，同时指定分隔符等，此情况比较简单，可以自行测试
1.8 使用场景
mysqldump属于逻辑备份，使用的主要场景如下：
- 备份部分库或备份部分表
- 数据规模较小的库
- 有指定条件的备份等
02
模拟误删数据操作
模拟误删除testdb库的test1表中的一条记录
mysql> delete from test1 where name='孙权';
Query OK, 1 row affected (0.00 sec)03
恢复数据
通过mysqldump备份的数据还原比较简单，将备份的文件导入数据库中即可。
3.1 恢复test1表
可以使用如下三种方式恢复：
a) 登录进数据库里用source 命令恢复
/* 将备份数据恢复至 rec 库中 */
mysql> use rec;
Database changed
/* 恢复数据 */
mysql> source test1.sql;b) 使用 mysql -e 的方式恢复
/* 此方式等同于第一种 */
[root@testdb ~]# /usr/local/mysql5.7/bin/mysql --socket=/tmp/mysql.sock --port=3306 -uroot -p -e "use rec ; source test1.sql;"Enter password:c) 直接 MySQL 导入备份文件的方式
[root@testdb ~]# /usr/local/mysql5.7/bin/mysql --socket=/tmp/mysql.sock --port=3306 -uroot -p rec < test1.sql
Enter password:注：
a) 还原命令使用起来比较方便，但是实际生产环境中还原数据时不建议直接还原至目标表里（尤其处理误删除恢复数据时），而是建议先还原至其他实例或其他库里，确认无误后再将需要还原的记录导入至目标表里;
b) 要警惕备份文件中是否有删除库或删表的指令，否则如果选择在同一实例中还原即使选择了临时恢复的库，而备份文件里有use db；及drop table的语句，则会将目标表全部删掉。
3.2 恢复误删除的记录至目标表
mysql> insert into testdb.test1 select * from rec.test1 where name='孙权';
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0此时记录恢复至数据库中了。
04
mysqldump原理
通过查看备份文件可以看到大致的流程，也可以通过开启general log的方式看具体都执行哪些SQL。
4.1 开启general log
先查看配置
然后开启genera-log
4.2 开始备份
我们就用之前的推荐备份脚本进行备份
/usr/local/mysql5.7/bin/mysqldump -uroot -p --socket=/tmp/mysql.sock --master-data=2 --default-character-set=utf8 --routines --triggers --events --flush-logs --flush-privileges --single-transaction --all-databases > all_db.sql4.3 关闭general-log
因开启general-log对数据库性能影响大（表现特别明显），因此测试完毕后关闭general log。勿忘，切记。
4.4 查看general-log内容
后面开始备份具体库的内容
备份其他库的内容与此相同，省略。
以上内容，如需转载，请注明出处，谢谢）
使用mysqldump工具备份及恢复数据至此就基本介绍完毕了，如有需要补充或想获取原图的请联系我（关注公众号：数据库干货铺）。