前言:
前面几篇文章为大家介绍了 MySQL 各种语句语法的用法及用户权限相关知识. 本篇文章将主要讲解 MySQL 数据库数据备份与恢复相关知识, 主要聚焦于逻辑备份, 介绍 mysqldump 工具的使用以及恢复方法.
这里简单讲下物理备份和逻辑备份的概念:
物理备份: 备份数据文件, 转储数据库物理文件到某一目录. 物理备份恢复速度比较快, 但占用空间比较大, MySQL 中可以用 xtrabackup 工具来进行物理备份.
逻辑备份: 对数据库对象利用工具进行导出工作, 汇总入备份文件内. 逻辑备份恢复速度慢, 但占用空间小, 更灵活. MySQL 中常用的逻辑备份工具为 mysqldump.
1. 备份全部数据库
若想用 mysqldump 备份整个实例, 可以使用 --all-databases 或 -A 参数:
- mysqldump -uroot -pxxxxxx --all-databases> /tmp/all_database.sql
- mysqldump -uroot -pxxxxxx -A> /tmp/all_database.sql
2. 备份部分数据库
有的时候我们会遇到只需要备份某些库的需求, 这个时候我们就可以使用 --databases 或 -B 参数了, 该参数后面跟数据库名称, 多个数据库间用空格隔开.
- mysqldump -uroot -pxxxxxx --databases testdb1 testdb2> /tmp/testdb.sql
- mysqldump -uroot -pxxxxxx -B testdb1 testdb2> /tmp/testdb.sql
3. 备份部分表
平时我们也会有备份部分表的需求, 比如说在表变更前做个备份, 那么我们可以这样做:
- # 只备份 testdb 库中的 test_tb 表
- mysqldump -uroot -pxxxxxx testdb test_tb> /tmp/test_tb.sql
- # 备份多张表
- mysqldump -uroot -pxxxxxx testdb tb1 tb2 tb3> /tmp/tb.sql
4. 备份单表的部分数据
有些时候一张表的数据量很大, 我们只需要部分数据, 那么该怎么办呢? 这时候就可以使用 --where 选项了. where 后面附带需要满足的条件. 例如: 我们只需要 tb1 表中 create_time 大于 2019-08-01 的数据, 那么可以这样导出:
mysqldump -uroot -pxxxxxx testdb tb1 --where="create_time>='2019-08-01 00:00:00' "> /tmp/tb1.sql
5. 排除某些表导出
如果我们想备份某个库, 但是某些表数据量很大或者与业务关联不大, 这个时候可以考虑排除掉这些表, 同样的, 选项 --ignore-table 可以完成这个功能.
mysqldump -uroot -pxxxxxx testdb --ignore-table=testdb.tb1> /tmp/testdb.sql
6. 只备份结构或只备份数据
只备份结构的话可以使用 --no-data 简写为 -d 选项; 只备份数据可以使用 --no-create-info 简写为 -t 选项.
- mysqldump -uroot -pxxxxxx testdb --no-data> /tmp/testdb_jiegou.sql
- mysqldump -uroot -pxxxxxx testdb --no-create-info> /tmp/testdb_data.sql
7. 备份中包含存储过程函数, 事件
mysqldump 备份默认是不包含存储过程, 自定义函数及事件的. 我们可以使用 --routines 或 -R 选项来备份存储过程及函数, 使用 --events 或 -E 参数来备份事件. 例如: 我们想备份整个 testdb 库, 包含存储过程及事件:
mysqldump -uroot -pxxxxxx -R -E --databases testdb> /tmp/testdb.sql
8. 以事务的形式备份
如果我们想在 dump 过程中保证数据的一致性, 减少锁表, 则可以用 --single-transaction 选项, 这个选项对 InnoDB 的数据表很有用, 且不会锁表.
mysqldump -uroot -pxxxxxx --single-transaction --databases testdb> /tmp/testdb.sql
9. 全量备份恢复
如果我们现在有昨天的全量备份, 现在想整个恢复, 则可以这样操作:
MySQL -uroot -pxxxxxx </tmp/all_database.sql
10. 从全量备份中恢复单库
可能有这样的需求, 比如说我们只想恢复某一个库, 但是我们有的是整个实例的备份, 这个时候我们想到能不能从全量备份中分离出单个库的备份, 答案是可以的, 下面这个简单的 shell 可以帮到你哦:
- sed -n '/^-- Current Database: `testdb`/,/^-- Current Database: `/p' all_databases.sql> testdb.sql
- # 分离完成后我们再导入 testdb.sql 即可恢复单个库
11. 从单库备份中恢复单表
这个需求还是比较常见的, 毕竟单库或全量恢复涉及的业务还是比较多的, 恢复时间也比较长, 比如说我们知道哪个表误操作了, 那么我们就可以用单表恢复的方式来恢复. 例如: 现在我们有 testdb 整库的备份, 但是由于 tb1 表误操作, 需要单独恢复出这张表, 那么我们可以这么做:
- cat testdb.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `tb1`/!d;q'> /tmp/tb1_jiegou.sql
- cat testdb.sql | grep --ignore-case 'insert into `tb1`'> /tmp/tb1_data.sql
- # 用 shell 语法分离出创建表的语句及插入数据的语句后 再依次导出即可完成恢复
总结:
本篇文章给出了在不同场景下的备份及恢复方法, 可能生产中还会有更复杂的场景, 需要大家灵活应变. 至此,『入门 MySQL』系列已经完结, 虽然这几篇文章写得并不是那么深入有趣, 但还是希望大家看完后能对 MySQL 有个基础的认识. 在这里也要感谢大家的阅读, 是你们一次次的阅读让我有写下去的动力! 后续我还会分享 MySQL 相关文章, 希望大家持续关注.
来源: http://www.tuicool.com/articles/riQ363e