专栏首页MySQL技术入门MySQL——备份与恢复

入门MySQL——备份与恢复

前言:

前面几篇文章为大家介绍了 MySQL 各种语句语法的用法及用户权限相关知识。本篇文章将主要讲解 MySQL 数据库数据备份与恢复相关知识,主要聚焦于逻辑备份,介绍mysqldump工具的使用以及恢复方法。

这里简单讲下物理备份和逻辑备份的概念: 物理备份:备份数据文件,转储数据库物理文件到某一目录。物理备份恢复速度比较快,但占用空间比较大,MySQL中可以用 xtrabackup 工具来进行物理备份。 逻辑备份:对数据库对象利用工具进行导出工作,汇总入备份文件内。逻辑备份恢复速度慢,但占用空间小,更灵活。MySQL 中常用的逻辑备份工具为mysqldump。

1.备份全部数据库

若想用mysqldump备份整个实例,可以使用 --all-databases-A 参数:

mysqldump -uroot -pxxxxxx --all-databases > /tmp/all_database.sqlmysqldump -uroot -pxxxxxx -A > /tmp/all_database.sql

2.备份部分数据库

有的时候我们会遇到只需要备份某些库的需求,这个时候我们就可以使用 --databases-B 参数了,该参数后面跟数据库名称,多个数据库间用空格隔开。

mysqldump -uroot -pxxxxxx --databases testdb1 testdb2 > /tmp/testdb.sqlmysqldump -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.sqlmysqldump -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.sqlcat testdb.sql | grep --ignore-case  'insert into `tb1`' > /tmp/tb1_data.sql
#用shell语法分离出创建表的语句及插入数据的语句后 再依次导出即可完成恢复

总结:

本篇文章给出了在不同场景下的备份及恢复方法,可能生产中还会有更复杂的场景,需要大家灵活应变。至此,『入门MySQL』系列已经完结,虽然这几篇文章写得并不是那么深入有趣,但还是希望大家看完后能对MySQL有个基础的认识。

本文分享自微信公众号 - MySQL技术(MySQL_SRE),作者:MySQL技术

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-09-06

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 在线修改主从复制选项

    MySQL最常用的架构就是主从复制了,其实主从复制有很多选项,特别是在从库端,我们可以设置复制过滤,比如说忽略某张表或某个库。这些过滤选项都是可以在线修改而不用...

    MySQL技术
  • INT类型知多少

    整型是MySQL中最常用的字段类型之一,通常用于存储整数,其中int是整型中最常用的,对于int类型你是否真正了解呢?本文会带你熟悉int类型相关知识,也会介绍...

    MySQL技术
  • 【MySQL】zabbix监控MySQL配置

    MySQL技术
  • 网络威胁预警 Flash跨域数据劫持漏洞

    目前互联网上许多网站都允许普通用户上传文件,但是很多网站在开发设计过程中由于程序员缺乏安全意识,逻辑处理方面不够严谨,导致在用户上传文件的过程中没有...

    安恒信息
  • 冼牛:5G的低延迟特性将给多媒体应用带来变革

    LiveVideoStack:冼牛你好,简单介绍下自己的工作经历,以及在即构科技负责的工作内容和感兴趣的技术方向。

    LiveVideoStack
  • 飞林沙:商品推荐算法&推荐解释

    这是今天看到的一篇蛮有新意的讲稿,由于不是一篇完整的论文,所以理解起来稍微有些困难,就顺着写个笔记,仅供参考。 ? 在这篇文章中,我分成两部分,我们先顺着作者的...

    机器学习AI算法工程
  • 65行代码,零基础入门自动驾驶

    无人驾驶虽然入门门槛很高,但是还是可以通过一些简化的模型迅速上手。例如比较有名的DonkeyCar,其实就是一个简单低成本的玩具车,基于树莓派和Python,利...

    用户3468945
  • Jenkins常见问题集锦(二)

    参考:通俗理解,Blue Ocean可以看作是Jenkins推出的新的UI界面,有更现代的外观和更好的交互。

    左手编程右手文化
  • SSM的疑难杂症--持续更新

    单例bean存在线程安全问题,当多线程操作成员变量时会有冲突发生,如果定义在方法入参处那样在栈中则不会。避免使用成员变量或推荐使用ThreadLocal成员变量...

    Howl
  • 内存数据库应用之NBA篮球图文直播室存储设计

      内存数据库适用于实时性访问要求很高的业务应用系统,尤其是实时数据直播报类系统,如篮球比赛图文直播室,足球比赛图文直播室等各类实时播放类的体育赛事。本文以NB...

    数据饕餮

扫码关注云+社区

领取腾讯云代金券

玩转腾讯云 有奖征文活动