关于删库跑路的事故现在已经屡见不鲜了,数据备份的必要性是企业数据管理极其重要的一项工作。关于数据备份、恢复也有很多场景及方法,本系列也会将主要的几种工具通过案例进行演示。 本系列将从逻辑备份及恢复开始讲起,逻辑备份的工具主要有mysqldump/mydumper等其中mydumper可以指定多线程工作,本文介绍的是mysqldump。 1. mysqldump 备份mysqldump是MySQL数据库自带的逻辑备份工具,属于热备工具。它的备份结果是根据设置的参数将数据库中的信息通过生成创建库、表等对象以及对应表的insert语句组成。 mysqldump 参数选项特别多,可以通过mysqldump --help 查看对应的参数及说明() [root@testdb ~]# mysqldump --help
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,但是语句前添加注释,
1.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的记录等,可以参考历史文章查看示例 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属于逻辑备份,使用的主要场景如下:
2 模拟误删数据操作模拟误删除testdb库的test1表中的一条记录 mysql> delete from test1 where name='孙权'; Query OK, 1 row affected (0.00 sec)
3. 恢复数据通过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 此时记录恢复至数据库中了。 4. 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.sql 4.3 关闭general-log因开启general-log对数据库性能影响大(表现特别明显),因此测试完毕后关闭general log。勿忘,切记。 4.4 查看general-log内容后面开始备份具体库的内容 备份其他库的内容与此相同,省略。 以上内容,如需转载,请注明出处,谢谢)
使用mysqldump工具备份及恢复数据至此就基本介绍完毕了,如果补充或想获取原图的请联系我(关注公众号: 数据库干货铺)。
|
|